Ever Wanted to Own and Operate a Fully Featured Dynamic DNS Service?
Now You Can With MintDNS 2009 Enterprise - - Our Award Winning Dynamic DNS (DDNS) Server Suite!!!
It's never been easier to run your own DDNS service.

Looking for free Dynamic DNS (DDNS) services? Please use our free DDNS service at http://www.dynddns.us

MintDNS Knowledge Base



Go Back
MaxMind GeoLite City T-SQL import, IP to numeric and Stored Procedures
 
I'm posting this for anyone looking for GeoIP solutions. I dont see many resources ou there for the MaxMind GeoLite City database. http://www.maxmind.com/app/geolitecity

When you first get the GeoLite DB you will need to open it in wordpad.exe and use file/save as to restore correct ASCII format. After that the below T-SQL will work fine.

The attached zip file contains the format files used by the import procedure.

Import Procedure
T-SQL Code:
USE [yourdb]
GO
/****** Object:  StoredProcedure [dbo].[MaxMind_Import]    Script Date: 11/13/2008 04:16:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MaxMind_Import]
(
@GeoLiteCity_Blocks nvarchar(4000),
@GeoLiteCity_Location nvarchar(4000)
)
AS
declare @SQL varchar(500)
declare @SQL1 varchar(500)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GeoLook')BEGIN
DROP TABLE GeoLook
END


CREATE TABLE GeoLook (
                 startIpNum bigint,
                 endIpNum bigint,
                 locId  bigint
)

SET @SQL='BULK INSERT GeoLook FROM ''' + @GeoLiteCity_Blocks + ''' WITH (FIRSTROW = 3, formatfile=''c:\pathto\GeoLiteCity-Blocks.fmt'')'
EXEC(@sql)
CREATE CLUSTERED INDEX Geo_IP_Look
ON GeoLook
([StartIpNum], [endIpNum],  [locId])
   

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GeoLoc')BEGIN
DROP TABLE GeoLoc
END


CREATE TABLE GeoLoc (
                 locId bigint,
                 country nvarchar(2),
                 region nvarchar(3),
                 city nvarchar(100),
                 postalCode nvarchar(10),
                 latitude nvarchar(15),
                 longitude nvarchar(15),
                 metroCode nvarchar(5),
                 areaCode nvarchar(5)
)

SET @SQL1='BULK INSERT GeoLoc FROM ''' + @GeoLiteCity_Location + ''' WITH (FIRSTROW = 3, formatfile=''c:\pathto\GeoLiteCity-Location.fmt'')'
EXEC(@sql1)
CREATE CLUSTERED INDEX Geo_Info_Look
ON GeoLoc
([locId], [country], [region], [city], [latitude], [longitude])


IP To numeric function Possibly derived from the IPtoNumeric function found at netnerds.net
T-SQL Code:
USE [yourdb]
GO
/****** Object:  UserDefinedFunction [dbo].[IP_Numeric]    Script Date: 11/13/2008 04:19:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IP_Numeric]  (@IP varchar(255))
RETURNS bigint
AS
BEGIN
DECLARE @IPNum bigint
        IF (LEN(@IP)-LEN(REPLACE(@IP,'.','')))/LEN('.') = 3   -- check to ensure there are 3 dots
    SET @IPNum = (16777216 *  CAST(PARSENAME(@IP,4) as bigint) + 65536 * PARSENAME(@IP,3) + 256 * PARSENAME(@IP,2) + PARSENAME(@IP,1))
RETURN @IPNum
END


Stored procedure to lookup an IP address and return Geo Location info. (Change yourdb to your database name.)
T-SQL Code:
USE [yourdb]
GO
/****** Object:  StoredProcedure [dbo].[Lookup_geoip]    Script Date: 11/13/2008 04:21:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Lookup_geoip] (
@IP nvarchar(20)

   )
AS
SET NOCOUNT ON

DECLARE @IPNUM bigint
set @IPNUM =  dbo.IP_Numeric(@IP)

SELECT
Geoloc.country,
Geoloc.region,
Geoloc.city,
Geoloc.latitude,
Geoloc.longitude,
FROM GeoLook
INNER JOIN Geoloc ON GeoLook.locId = Geoloc.locId
WHERE  (@IPNUM BETWEEN GeoLook.startIpNum AND GeoLook.endIpNum)



Usage example in classic ASP. (Easily adaptable to any language, please let me know if you need a sample in another language.)

Classic ASP usage example
T-SQL Code:
<%@ Language=VBScript %>

<%
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "connection string"

Set GeoLook = Server.CreateObject("ADODB.Recordset")
strSQL = "Lookup_geoip @IP='24.116.98.21'"
Set GeoLook = Conn.Execute(strSQL)
  If Not GeoLook.EOF Then
   Response.Write GeoLook("city") & "<br>"
   Response.Write GeoLook("region") & "<br>"
   Response.Write GeoLook("country") & "<br>"
   Response.Write GeoLook("latitude") & "<br>"
   Response.Write GeoLook("longitude") & "<br>"
  End If
GeoLook.Close

Conn.Close
Set Conn = Nothing
%>

Download The Required Format Files Here

Go Back