Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Map IP Address to a Geographical Location Expand / Collapse
Author
Message
Posted Saturday, July 25, 2009 1:13 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
Of course not!
I just googled the keywords and the link popped up.



N 56°04'39.16"
E 12°55'05.25"
Post #759680
Posted Saturday, July 25, 2009 9:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 PM
Points: 36,752, Visits: 31,208
Heh... sorry Peter... Thought you were trying to flatten the trajectory of a pork chop for some reason. I'm a bit touchy because I'm trying to quit smoking, again. Right now, I hate just about everything... even the damned TV. I was watching "Moonshot" to try to take my mind off of smokes and guess what the freakin' astronauts and their wives were doing? SMOKING!! I can't win. Scotty, beam me the hell out of here!

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #759712
Posted Tuesday, December 8, 2009 8:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:33 PM
Points: 20, Visits: 157
Great article!

I had downloaded the following scripts for a very similar database schema, that speeds up "select" queries against the blocks table. It increased my "lookups per second" from around 45 to 3500, really.

I dropped indexes on GeoLiteCity_blocks table and created the following two indexes:
CREATE NONCLUSTERED INDEX [NCL_END_IP] ON [dbo].[GeoLiteCity_blocks] 
(
[endIpnum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NCL_IND_STARTIP] ON [dbo].[GeoLiteCity_blocks]
(
[startIpnum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Then to find the LocationID or any other column for a specific IP number is as follows:

declare @IP bigint

set @IP=123456789

select * from GeoLiteCity_blocks
where startIpnum=(select max(startIpnum) from GeoLiteCity_blocks where startIpnum<=@IP)
and endIpnum >=@IP

Please check this on a development environment with backups and tell me the results. Any comparisons would be great actually, I never had the time to do that.

By the way, I could not find the original source of this trick. If anyone knows, you may add it also.
Post #830703
Posted Thursday, April 15, 2010 10:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:04 PM
Points: 7, Visits: 32
http://www.dyndnsservices.com/knowshow.aspx?ID=4 has a complete solution for the maxmind geo city database
Post #904552
Posted Friday, April 16, 2010 4:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:35 PM
Points: 11,192, Visits: 11,094
Two things on the PARSENAME conversion routines:

First, T-SQL scalar functions have no place in this world - the routines would be much more happily expressed in an in-line table-valued function.

Second, PARSENAME is non-deterministic, despite what Books Online says. See https://connect.microsoft.com/SQLServer/feedback/details/488058/parsename-incorrectly-documented-as-deterministic

A deterministic iTVF IP-to-BIGINT function:
CREATE FUNCTION [dbo].[itfn_IPv4_Octets]
(@IP NVARCHAR(15))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT octet1 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, 1, (CHARINDEX(N'.', @IP, 1)) - 1)), N'') ELSE N'' END,
octet2 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, 1)) + 1, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) - (CHARINDEX(N'.', @IP, 1)) - 1)), N'') ELSE N'' END,
octet3 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)) - (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) - 1)), N'') ELSE N'' END,
octet4 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)) + 1, LEN(@IP) - (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)))), N'') ELSE N'' END;

That happens to split the IP address into octets, so converting further into a BINARY(4) - as suggested earlier - is not too hard:
SELECT  CONVERT
(
BINARY(4),
CHAR(FN.octet1) +
CHAR(FN.octet2) +
CHAR(FN.octet3) +
CHAR(FN.octet4)
)
FROM [dbo].[itfn_IPv4_Octets] ('162.74.5.51') FN;

Conversion from BIGINT to dotted-octets:
CREATE  FUNCTION dbo.IntToIP 
(@IP BIGINT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT ip_address =
CONVERT(VARCHAR(3), ((@IP & 0xFF000000) / 256 / 256 / 256)) + '.' +
CONVERT(VARCHAR(3), ((@IP & 0xFF0000) / 256 / 256)) + '.' +
CONVERT(VARCHAR(3), ((@IP & 0xFF00) / 256)) + '.' +
CONVERT(VARCHAR(3), (@IP & 0xFF));

Notwithstanding some icky implicit conversions, that function will decode the BINARY(4) representation too.

Now those functions might look a bit hairy, but being deterministic, they're fully foldable, so the estimated execution plans for the following statements:
SELECT * FROM dbo.itfn_IPv4_Octets(N'162.74.5.51');
SELECT * FROM dbo.IntToIP(0xA24A0533);

...are both Constant Scans, with the answers fully listed in the operator - all the maths is done at compilation time, not execution time...which I think is remarkable.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #904669
Posted Friday, April 16, 2010 5:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:35 PM
Points: 11,192, Visits: 11,094
Jeff Moden (7/25/2009)
...I'm a bit touchy because I'm trying to quit smoking, again...

Good job I'm never giving up




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #904727
Posted Friday, April 16, 2010 6:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:19 AM
Points: 327, Visits: 123
Very good article.

In practice, have you checked the accuracy against other geocoding techniques? I'm curious with users who do not posess static IP addresses whether IP resolves to the Telco location rather than the actual user.

Does anyone have insight on this?

Post #904778
Posted Friday, April 16, 2010 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:17 PM
Points: 12,881, Visits: 31,820
i must have tunnel vision this morning;
i didn't see any BULK insert scripts for the files; after downloading the csv files, i see they are UNIX style(slash r for the row delimiter, right?), but they are also dbl-quote delimited, so i need a format file.

did anyone already do this, or do i need to get some coffee and sweat it out?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #904809
Posted Friday, April 16, 2010 7:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:04 PM
Points: 7, Visits: 32
Lowell (4/16/2010)
i must have tunnel vision this morning;
i didn't see any BULK insert scripts for the files; after downloading the csv files, i see they are UNIX style(slash r for the row delimiter, right?), but they are also dbl-quote delimited, so i need a format file.

did anyone already do this, or do i need to get some coffee and sweat it out?


http://www.dyndnsservices.com/knowshow.aspx?ID=4 has the bulk insert/import and the required format files etc. Also importing the data can be a little like beating a dead horse unless you restore correct ASCII formatting to the files. Just open and save them using wordpad before importing.
Post #904845
Posted Friday, April 16, 2010 8:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:27 PM
Points: 219, Visits: 648
Mark-101232 (7/24/2009)
Jeff Moden (7/24/2009)
Mark (7/13/2009)
I think both functions can be simplified


create function dbo.ConvertIp2Num(@ip nvarchar(15))
returns bigint
as
begin
return ((cast(parsename(@ip,4) as bigint)*256+
cast(parsename(@ip,3) as bigint))*256+
cast(parsename(@ip,2) as bigint))*256+
cast(parsename(@ip,1) as bigint)
end


That won't return the correct answer... the octets must be multiplied by powers of 256.



select dbo.ConvertIp2Num('192.15.10.125') -- gives 3222211197

The nesting of the multiplys gives the effect of multiplying by powers of 256. (Look at the brackets)

Surprisingly, the function above seemed to take longer on average than the function below (which is what was shown earlier):

CREATE FUNCTION [dbo].[fnDot2LongIP]
(@ipaddr varchar(15))
RETURNS bigint
AS
BEGIN
DECLARE @longip bigint
SELECT @longip = CAST(PARSENAME(@ipaddr, 1) AS bigint) % 256
+ CAST(PARSENAME(@ipaddr, 2) AS bigint) % 256 * 256
+ CAST(PARSENAME(@ipaddr, 3) AS bigint) % 256 * 65536
+ CAST(PARSENAME(@ipaddr, 4) AS bigint) % 256 * 16777216
RETURN @longip
END

As for the modulus operations, I tried the function with and without them. It seemed slightly quicker WITH them rather than without them. Does anyone else have the same results?


Zycon - The Search Engine for Manufacturers. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.
Post #904883
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse