Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Map IP Address to a Geographical Location Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, July 10, 2009 8:31 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, August 21, 2013 8:04 AM Points: 56, Visits: 133
 Comments posted to this topic are about the item Map IP Address to a Geographical Location http://ozkary.blogspot.comhttp://og-bit.com
Post #751105
 Posted Friday, July 10, 2009 9:40 AM
 SSC Veteran Group: General Forum Members Last Login: Today @ 11:30 AM Points: 292, Visits: 1,617
 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 numberSELECT 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
 SSC-Insane Group: General Forum Members Last Login: Today @ 1:10 PM Points: 20,485, Visits: 14,141
 Nice article. This could prove very useful. Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL ServerSQL RNNRPosting Performance Based Questions - Gail ShawPosting Data Etiquette - Jeff ModenHidden RBAR - Jeff ModenVLFs and the Tran Log - Kimberly Tripp
Post #751231
 Posted Friday, July 10, 2009 9:32 PM
 Forum 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 Group: General Forum Members Last Login: 2 days ago @ 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 Group: General Forum Members Last Login: Friday, April 18, 2014 5:57 PM Points: 535, Visits: 729
 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 Group: General Forum Members Last Login: Tuesday, April 08, 2014 6:13 AM Points: 1,694, Visits: 19,550
 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 numberSELECT 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 bigintasbegin 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)endcreate function dbo.ConvertNum2Ip(@ip bigint)returns nvarchar(15)asbegin 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 forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537Never 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 Group: General Forum Members Last Login: Wednesday, August 21, 2013 8:04 AM Points: 56, Visits: 133
 great feedback!I like how simple and clean that looks http://ozkary.blogspot.comhttp://og-bit.com
Post #752034
 Posted Monday, July 13, 2009 9:02 AM
 Forum 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 Group: General Forum Members Last Login: 2 days ago @ 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

 Permissions