SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Map IP Address to a Geographical Location


Map IP Address to a Geographical Location

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207911 Visits: 41966
sam-610757 (4/16/2010)
I smoked for 20+ years. I have not had a cigarette in 2.5 years give or take. This is what it took for me to quit.

I chewed the gum and suckwed on the lozenges for 8 months, 4MG one after another. I switched to 2MG about a month before I stopped completely. After many months on the gum you will notice that you will wake up and not even think to get a piece sometimes, and after the habit of having a smoke has been broken choose a day like this to quit.

Sneaking even one cigarette is to start all over, try to avoid people who smoke while your quitting. I can be around smokers now without issue. I thought I loved cigarettes as well, but I wouldn’t go back for anything.


Really interesting. I know what "the box" says but did you smoke while you chewed the gum and sucked on the lozenges or did you stop smoking as soon as started the substitutes? I'm asking because I'd really like to quit.

And, you're correct. I quit twice for a year each and once for 3 months and not sure why I went back to it other than I like it and just wanted "one". The old saying holds true... "You're a puff away from a pack a day."

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23138 Visits: 5314
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?

I built a quick SSIS 2008 package to do this, it truncs the table and then repopulates it from the files.

http://downloads.novaconceptsltd.com/SSC/GeoLiteLoad.zip

Put files in C:\Temp

Pretty straightforward package..

CEWII
richardn-1128243
richardn-1128243
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 206
This is great - despite the limitations on accuracy.

I have a collection of IP Addresses in the Visitor_Log table, which I convert to an IPNum - no problems there - then I wish to do a join to the Blocks table and thus the Location table. If I do very small subset (say 13 IPNums) and do a INNER JOIN with the BETWEEN criteria it takes about 13 seconds. When I do it with 300 IPNums it takes over 10 mins. Looks like the BETWEEN is too heavy.

Any ideas how I could make this thing run like "a cut cat"?

Here's the query being used:-
SELECT     
l.Referrer,
l.IPAddress,
l.IPNum,
b.locId,
cl.country,
cl.region,
cl.city,
cl.postalCode,
cl.latitude,
cl.longitude,
cl.areaCode,
cl.metroCode,
b.startIpNum,
b.endIpNum
FROM
Visitor_Log AS l INNER JOIN
GeoLiteCity_Blocks AS b ON l.IPNum BETWEEN b.startIpNum AND b.endIpNum INNER JOIN
GeoLiteCity_Location AS cl ON b.locId = cl.locId



-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com

Matthew Lehn
Matthew Lehn
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 805
richardn-1128243 (4/21/2010)
This is great - despite the limitations on accuracy.

I have a collection of IP Addresses in the Visitor_Log table, which I convert to an IPNum - no problems there - then I wish to do a join to the Blocks table and thus the Location table. If I do very small subset (say 13 IPNums) and do a INNER JOIN with the BETWEEN criteria it takes about 13 seconds. When I do it with 300 IPNums it takes over 10 mins. Looks like the BETWEEN is too heavy.

Any ideas how I could make this thing run like "a cut cat"?

Here's the query being used:-
SELECT     
l.Referrer,
l.IPAddress,
l.IPNum,
b.locId,
cl.country,
cl.region,
cl.city,
cl.postalCode,
cl.latitude,
cl.longitude,
cl.areaCode,
cl.metroCode,
b.startIpNum,
b.endIpNum
FROM
Visitor_Log AS l INNER JOIN
GeoLiteCity_Blocks AS b ON l.IPNum BETWEEN b.startIpNum AND b.endIpNum INNER JOIN
GeoLiteCity_Location AS cl ON b.locId = cl.locId



Use the "trick" given in post 830703. I've definitely noticed an improvement in my queries from the BETWEEN operator. Also, I put a 100% fillfactor clustered index on StartIP, EndIP and not separate indexes on each. Even though it takes additional space, to reduce the time for the query to execute even further, I made an indexed persisted computed column on the log tables (where the IP is stored) and use the computed column (LongIP) in the queries instead of computing it on the fly.

Zycon - The Search Engine for Manufacturers. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.
Rahul The Dba
Rahul The Dba
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 133
I am not getting the data for the ip address 180.215.5.170 it's @ipnum coming to 3033990570. But when i checked it over the net it shows me City:Bangalore Region:Karnataka Country:India, but in the database no data for that.
And same happened with the ip address that you have used in your article that is 192.15.10.125 you have show a entry for that but with i am not getting it, i have used this query :-

declare @ip varchar(50), @ipnum bigint
set @ip = '192.15.10.125'
select @ipnum = dbo.ConvertIp2Num(@ip)
print @ipnum

select loc.locId,loc.country,loc.region,loc.city,loc.postalCode,loc.latitude,loc.longitude,loc.areaCode
from [GeoLiteCity_blocks] blk (nolock)inner join [GeoLiteCity_location] loc (nolock)on blk.locId = loc.locId
where @ipnum between blk.startIpNum and blk.endIpNum
is this query right?
i ahve used the latest csv files.
please help i am really wanting to extract something new form this, but not getting the basic.

Rahul:-P
sam-610757
sam-610757
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 33
http://www.dyndnsservices.com/knowshow.aspx?ID=4 has both the bulk insert procedure and needed format files for the geocity csv, per gratis.

Thank you maxmind!!!
sam-610757
sam-610757
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 33
Link to format files for the maxmind geocity csv

http://www.dyndnsservices.com/Format-files.zip
sam-610757
sam-610757
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 33
Geocity light is intentionally left wanting. Purchase the DB for more accurate results!
umaipde1
umaipde1
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
ozkary (7/10/2009)
Comments posted to this topic are about the item <A HREF="/articles/SQL+Server/67215/">Map IP Address to a Geographical Location</A>




hi friend
you can get the location details of a particular ip address using this site
www.ip-details.com
they provide information about a particular ip address.
SwePeso
SwePeso
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9269 Visits: 3433
DECLARE   @IPvalue BIGINT = 3222211197,
@IPstring VARCHAR(15) = '192.15.10.125'

-- SwePeso (value to string)
SELECT CAST(CAST(SUBSTRING(Data, 1, 1) AS TINYINT) AS VARCHAR(3))
+ '.' + CAST(CAST(SUBSTRING(Data, 2, 1) AS TINYINT) AS VARCHAR(3))
+ '.' + CAST(CAST(SUBSTRING(Data, 3, 1) AS TINYINT) AS VARCHAR(3))
+ '.' + CAST(CAST(SUBSTRING(Data, 4, 1) AS TINYINT) AS VARCHAR(3)) AS IP
FROM (
VALUES (CAST(@IPvalue AS BINARY(4)))
) AS d(Data)

-- SwePeso (string to value)
SELECT CAST(16777216E * PARSENAME(@IPstring, 4)
+ 65536E * PARSENAME(@IPstring, 3)
+ 256E * PARSENAME(@IPstring, 2)
+ 1E * PARSENAME(@IPstring, 1) AS BIGINT) AS IP




N 56°04'39.16"
E 12°55'05.25"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search