Map IP Address to a Geographical Location

  • Oscar D. Garcia

    SSC Eights!

    Points: 864

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

    https://ozkary.com

  • Lamprey13

    SSCertifiable

    Points: 6190

    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)

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • tdcheek

    SSC Journeyman

    Points: 76

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

  • antoine.gemis

    SSC Rookie

    Points: 29

    Nice article. Thanks for sharing.

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

    Regards,

  • Misha_SQL

    SSCertifiable

    Points: 5388

    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!

  • Mark Cowne

    One Orange Chip

    Points: 26748

    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
  • Oscar D. Garcia

    SSC Eights!

    Points: 864

    great feedback!

    I like how simple and clean that looks

    https://ozkary.com

  • seth.yukna.ctr

    Newbie

    Points: 7

    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?

  • antoine.gemis

    SSC Rookie

    Points: 29

    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!

  • srcopeland

    Grasshopper

    Points: 20

    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!

  • Jeff Moden

    SSC Guru

    Points: 996449

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

  • Jeff Moden

    SSC Guru

    Points: 996449

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

  • Mark Cowne

    One Orange Chip

    Points: 26748

    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
  • Jeff Moden

    SSC Guru

    Points: 996449

    @ 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)
    Forum FAQ

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

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