Map IP Address to a Geographical Location

  • Comments posted to this topic are about the item Map IP Address to a Geographical Location

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

  • 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[/url]
    Learn Extended Events

  • IP's fit much better in binary(4) than bigint - the conversion is pretty simple and cuts down heavily on reads and index size

  • Nice article. Thanks for sharing.

    It gave me the idea to do the same with SQL Server 2008 and spatial data.

    Regards,

  • 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!

  • 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

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • great feedback!

    I like how simple and clean that looks

  • 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?

  • 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!

  • I used combinations of substring and charindex (probably very functionally similar to the parsename route) to rewrite the functions. The performance, when dealing with large amounts of data, increased by leaps and bounds. On one subset of data, a simple query went from several minutes down to 3 seconds.

    Very nice article though. I'm currently implementing this at my company as well. Thanks!

  • tdcheek (7/10/2009)


    IP's fit much better in binary(4) than bigint - the conversion is pretty simple and cuts down heavily on reads and index size

    What is your method of conversion? Can you post the code, please?

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

  • Mark (7/13/2009)


    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

    That won't return the correct answer... the octets must be multiplied by powers of 256.

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

  • Jeff Moden (7/24/2009)


    Mark (7/13/2009)


    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

    That won't return the correct answer... the octets must be multiplied by powers of 256.

    select dbo.ConvertIp2Num('192.15.10.125') -- gives 3222211197

    The nesting of the multiplys gives the effect of multiplying by powers of 256. (Look at the brackets)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

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

    Heh... that's what I get for looking at stuff before the caffeine reaches the brain. Thanks, Mark... nicely done.

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

Viewing 15 posts - 1 through 15 (of 52 total)

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