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 Friday, July 10, 2009 8:31 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 5, 2014 12:35 PM
Points: 57, Visits: 135
Comments posted to this topic are about the item Map IP Address to a Geographical Location

http://ozkary.blogspot.com
http://og-bit.com
Post #751105
Posted Friday, July 10, 2009 9:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 292, Visits: 1,624
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)

Post #751194
Posted Friday, July 10, 2009 10:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 17,825, Visits: 15,758
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
Post #751231
Posted Friday, July 10, 2009 9:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 21, 2011 10:44 AM
Points: 2, Visits: 10
IP's fit much better in binary(4) than bigint - the conversion is pretty simple and cuts down heavily on reads and index size
Post #751521
Posted Saturday, July 11, 2009 10:46 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 1:00 PM
Points: 3, Visits: 52
Nice article. Thanks for sharing.
It gave me the idea to do the same with SQL Server 2008 and spatial data.

Regards,
Post #751625
Posted Sunday, July 12, 2009 1:55 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 538, Visits: 774
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!



Post #751752
Posted Monday, July 13, 2009 2:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:29 AM
Points: 1,678, Visits: 19,554
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



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #751843
Posted Monday, July 13, 2009 7:36 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 5, 2014 12:35 PM
Points: 57, Visits: 135
great feedback!

I like how simple and clean that looks


http://ozkary.blogspot.com
http://og-bit.com
Post #752034
Posted Monday, July 13, 2009 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 23, 2010 7:41 AM
Points: 1, Visits: 13
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?
Post #752116
Posted Monday, July 13, 2009 10:19 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 1:00 PM
Points: 3, Visits: 52
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!
Post #752206
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse