Post reply

Map IP Address to a Geographical Location

  • April 16, 2010 at 8:29 am

    #1152871

    will come in handy. thanks

  • April 16, 2010 at 9:10 am

    #1152923

    Nice summary. We bought IP to city range a few years ago for $100 and it's served us nicely since, allowing us to show (albeit not ENTIRELY accurate) our customers numbers of page views within a distance range from their location. Also it's nice to have it mapped to zipcode.

  • April 16, 2010 at 9:26 am

    #1152937

    Jeff Moden (7/25/2009)

    Heh... sorry Peter... Thought you were trying to flatten the trajectory of a pork chop for some reason. 😀 I'm a bit touchy because I'm trying to quit smoking, again....

    Hi Jeff,

    I'm rooting for you. I don't smoke, but I've seen how hard it is for people to quit. It can be done, though. And given what I have seen of the quality of your code and tenacity in solving SQL problems, you sure seem to have the stick–to–itiveness to achieve your goal!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • April 16, 2010 at 12:51 pm

    #1153077

    webrunner (4/16/2010)

    Jeff Moden (7/25/2009)

    Heh... sorry Peter... Thought you were trying to flatten the trajectory of a pork chop for some reason. 😀 I'm a bit touchy because I'm trying to quit smoking, again....

    Hi Jeff,

    I'm rooting for you. I don't smoke, but I've seen how hard it is for people to quit. It can be done, though. And given what I have seen of the quality of your code and tenacity in solving SQL problems, you sure seem to have the stick–to–itiveness to achieve your goal!

    - webrunner

    Heh... thanks. My biggest problem is that I really, really like smoking.

    --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)

  • April 16, 2010 at 1:06 pm

    #1153087

    Great timing and write up. I started down this path with SQL 2K8 and GeoCity data. I'm an ESRI user and plan to mix this with spatial data stored in SQL spatial and ESRI's ArcSDE. This will provide a good sampling of our map application's usage and reach in....a map application--how novel.

    Instead of using a bulk insert for user logs wouldn't IIS logging in SQL work well here?

  • April 16, 2010 at 1:20 pm

    #1153112

    I enjoyed revisiting this article. This is a job well done.

    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

  • April 16, 2010 at 3:07 pm

    #1153164

    Jeff Moden (4/16/2010)

    webrunner (4/16/2010)

    Jeff Moden (7/25/2009)

    Heh... sorry Peter... Thought you were trying to flatten the trajectory of a pork chop for some reason. 😀 I'm a bit touchy because I'm trying to quit smoking, again....

    Hi Jeff,

    I'm rooting for you. I don't smoke, but I've seen how hard it is for people to quit. It can be done, though. And given what I have seen of the quality of your code and tenacity in solving SQL problems, you sure seem to have the stick–to–itiveness to achieve your goal!

    - webrunner

    Heh... thanks. My biggest problem is that I really, really like smoking.

    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.

  • April 18, 2010 at 11:10 am

    #1153487

    Off topic -

    Hey Jeff, head to Australia where they're contemplating $20 (!!!) a packet, am sure this could influence your behaviour 😉

    Steve.

  • April 18, 2010 at 3:10 pm

    #1153510

    Whilst the article was very good, I would like to issue a note of caution; even if the address does not appear to be that of a DHCP pool used by an ISP, the results may still be misleading as:

    1) The traffic is routed via the company WAN to a quite different address - in an extreme case I have seen this give my address as being the United States whilst I was in England.

    2) The address is that of the failities management company rather then the actual address. Again I have seen this give an address in Germany whereas I was definitly in the UK.

  • April 18, 2010 at 3:30 pm

    #1153513

    stevefromOZ (4/18/2010)

    Off topic -

    Hey Jeff, head to Australia where they're contemplating $20 (!!!) a packet, am sure this could influence your behaviour 😉

    Heh... it sure would. I'd build a quarter acre greenhouse and grow my own as an "insecticide". 😉

    --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)

  • April 18, 2010 at 3:41 pm

    #1153514

    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.

    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)

  • April 18, 2010 at 10:48 pm

    #1153579

    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

  • April 21, 2010 at 5:56 pm

    #1155784

    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

    [font="Arial"]-----------------------------------------------------------------
    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    [/font]

  • April 23, 2010 at 7:35 am

    #1156829

    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.

    [font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

  • November 8, 2010 at 4:28 pm

    #1247940

    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.

    [font="Comic Sans MS"]Rahul:-P[/font]

Viewing 15 posts - 31 through 45 (of 52 total)

You must be logged in to reply to this topic. Login to reply