Post reply

Is their a variation on RBarry Young's function that allows you to get the cities and states X miles from a long and lat value ?

  • February 3, 2014 at 12:58 pm

    #289025

    http://www.sqlservercentral.com/scripts/T-SQL/65739/

    Thanks

  • February 3, 2014 at 1:11 pm

    #1685621

    certainly possible, i think it goes directly to your data;

    do you have polygon boundaries for your cities, or a simple single point per city?

    either way, you can build an equivalent function or proc that that returns the data, but i'd have to see a lot more detail on what you are trying to accomplish.

    i have this saved, it might help you get started:

    http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 3, 2014 at 1:16 pm

    #1685622

    isuckatsql (2/3/2014)

    http://www.sqlservercentral.com/scripts/T-SQL/65739/

    Thanks

    What city data do you have? Given that this is SQL 2012, I would do this using Spatial data types and the NEAREST capability (or more likely Isaac Kunen's Power of 2 mechanism). This is very simple to do.

    I built my own proprietary method that seems to beat even Isaac's method (for a particular set of data I was working with), but I haven't legally locked it down or rigorously benchmarked it yet so I won't release it.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 3, 2014 at 1:39 pm

    #1685629

    The data i have for City is: Long decimal(9,6), Lat decimal(9,6), Zipcode, Areacode.

    Thanks

  • February 3, 2014 at 1:47 pm

    #1685630

    http://technet.microsoft.com/en-us/library/ff929109.aspx

    http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx

    http://books.google.com/books/about/Pro_Spatial_with_SQL_Server_2012.html?id=NdvJXu7hT8oC

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 3, 2014 at 1:48 pm

    #1685631

    i have this saved in my snippets; it's calculating the min and max longitudes/latittudes for a specific point, which you then use to bracket in the values search a given table for matching values.

    this one is for a table for zip codes, but it would work whether you were after cities, as long as the data supported it.

    declare @radius decimal(18,2)

    DECLARE @radians float

    SELECT @radians = 57.295779513082323 --> 180/PI

    SELECT @radius=5

    with cteDistCalc

    as(

    SELECT t1.*,

    DistanceInMiles = 3963.0*acos(sin(t1.Latitude/(@radians)) * sin(t2.Latitude/(57.295779513082323))

    + cos(t1.Latitude/(@radians)) * cos(t2.Latitude/(@radians)) * cos(t2.Longitude/(@radians) - t1.Longitude/(@radians)))

    FROM dbo.ZipCodes t1

    JOIN dbo.ZipCodes t2

    on t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius

    and t1.Longitude between t2.Longitude - @radius and t2.Longitude + @radius

    where t1.ZipCode='10023'

    )

    select * from cteDistCalc WHERE DistanceInMiles < @radius

    ORDER BY DistanceInMiles

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 3, 2014 at 4:01 pm

    #1685674

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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