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 «««23456»»

Map IP Address to a Geographical Location Expand / Collapse
Author
Message
Posted Sunday, April 18, 2010 3:41 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(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 #905613
Posted Sunday, April 18, 2010 10:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #905688
Posted Wednesday, April 21, 2010 5:56 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 6:52 PM
Points: 19, 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
Post #908234
Posted Friday, April 23, 2010 7:35 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
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.
Post #909449
Posted Monday, November 8, 2010 4:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 11, 2013 3:27 PM
Points: 127, Visits: 132
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
Post #1017518
Posted Monday, November 8, 2010 4:33 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 both the bulk insert procedure and needed format files for the geocity csv, per gratis.

Thank you maxmind!!!
Post #1017521
Posted Monday, November 8, 2010 4:34 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
Link to format files for the maxmind geocity csv

http://www.dyndnsservices.com/Format-files.zip
Post #1017522
Posted Monday, November 8, 2010 4:41 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
Geocity light is intentionally left wanting. Purchase the DB for more accurate results!
Post #1017526
Posted Monday, February 28, 2011 4:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 28, 2011 4:08 AM
Points: 1, 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.
Post #1070411
Posted Friday, July 20, 2012 7:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
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"
Post #1332941
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse