Map IP Address to a Geographical Location
-
Comments posted to this topic are about the item Map IP Address to a Geographical Location
-
Nice article. I've used other commercial geo-location products in the past and it is nice to have a free solution that is able to get reasonable results for BI tasks without spending an arm and a leg. 🙂
One comment about the ConvertIP2Num function, it seems overly complicated to me. Perhaps something a little more simple might help readability and possibly enhance performance? Here is a relatively concise way to do the conversion:
DECLARE @IPAddress VARCHAR(15)SET @IPAddress = '196.169.25.3'
--Convert to number
SELECT
CAST(PARSENAME(@IPAddress, 4) AS BIGINT) * POWER(256, 3)
+ CAST(PARSENAME(@IPAddress, 3) AS BIGINT) * POWER(256, 2)
+ CAST(PARSENAME(@IPAddress, 2) AS BIGINT) * POWER(256, 1)
+ CAST(PARSENAME(@IPAddress, 1) AS BIGINT) * POWER(256, 0)
-
Nice article. This could prove very useful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
IP's fit much better in binary(4) than bigint - the conversion is pretty simple and cuts down heavily on reads and index size
-
Nice article. Thanks for sharing.
It gave me the idea to do the same with SQL Server 2008 and spatial data.
Regards,
-
This was great. I am actually working with something like this. Got one question about the conversion function (ConvertIP2Num). Why do we need this section CAST((col%@SUBNET_MASK) as float)? The col contains our IP segment, such as 192, why do we need to get the mod? It's still going to be the same number in this case.
Thank you!
-
Lamprey13 (7/10/2009)
Nice article. I've used other commercial geo-location products in the past and it is nice to have a free solution that is able to get reasonable results for BI tasks without spending an arm and a leg. 🙂One comment about the ConvertIP2Num function, it seems overly complicated to me. Perhaps something a little more simple might help readability and possibly enhance performance? Here is a relatively concise way to do the conversion:
DECLARE @IPAddress VARCHAR(15)SET @IPAddress = '196.169.25.3'
--Convert to number
SELECT
CAST(PARSENAME(@IPAddress, 4) AS BIGINT) * POWER(256, 3)
+ CAST(PARSENAME(@IPAddress, 3) AS BIGINT) * POWER(256, 2)
+ CAST(PARSENAME(@IPAddress, 2) AS BIGINT) * POWER(256, 1)
+ CAST(PARSENAME(@IPAddress, 1) AS BIGINT) * POWER(256, 0)
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
create function dbo.ConvertNum2Ip(@ip bigint)
returns nvarchar(15)
as
begin
return cast(@ip/16777216 as nvarchar(15)) + '.' +
cast((@ip/65536)%256 as nvarchar(15)) + '.' +
cast((@ip/256)%256 as nvarchar(15)) + '.' +
cast(@ip%256 as nvarchar(15))
end
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
great feedback!
I like how simple and clean that looks
-
How the heck does parsename grab the correct values? Looking at the description of PARSENAME it's meant to grab object/server names etc? Is it meant to parse strings like an IP address or it is just the way the object/server names are formatted allowing one to parse the IP address?
-
It seems that currently parsename does not check for a valid identifier.
As IP V4 address looks like 4 parts names. It works.
Nice trick!
-
I used combinations of substring and charindex (probably very functionally similar to the parsename route) to rewrite the functions. The performance, when dealing with large amounts of data, increased by leaps and bounds. On one subset of data, a simple query went from several minutes down to 3 seconds.
Very nice article though. I'm currently implementing this at my company as well. Thanks!
-
tdcheek (7/10/2009)
IP's fit much better in binary(4) than bigint - the conversion is pretty simple and cuts down heavily on reads and index sizeWhat is your method of conversion? Can you post the code, please?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Mark (7/13/2009)
I think both functions can be simplifiedcreate 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.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (7/24/2009)
Mark (7/13/2009)
I think both functions can be simplifiedcreate 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)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
@ Mark...
Heh... that's what I get for looking at stuff before the caffeine reaches the brain. Thanks, Mark... nicely done.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply