locked
Proximity query locations near specific lat long using SQL Server R2 RRS feed

  • Question

  • This is not uncommon but a quick Google Search turns up nothing useful

    I want a query that will return records from a table that are located within a radius of a LAT LONG

    So we get a city state or zip and lookup the lat/long for that post office from one table

    then we go to our PLACES table and we want to locate all PLACES that are within say 25 miles
    all our places have LAT/LONG fields defined.

    So really the proximity query is the missing part - I understand that SQL SERVER R2 may have some special tools or field type for LAT/LONG locaton

    Any help on this would be great. I do have a set of queries that do this in Access and I will be attempting to transfer them to SQL server R2 - but I am thinking that improvements could be made using the latest techniques.

     


    - 10spotdomain for all your domain and domain related needs
    • Moved by Todd McDermid Tuesday, July 26, 2011 3:50 PM Spatial query (From:SQL Server Integration Services)
    Tuesday, July 26, 2011 3:10 PM

All replies

  • You probably want to take a look at the Geography data type, and the methods that are exposed with it. A sample query using this could be as simple a:

    Declare @Lat numeric(38, 8),

    @Lon numeric(38, 8),

    @MaxDistance int

    SELECT *

    FROM PLACES

    WHERE geogField.STDistance(GEOGRAPHY::Point(@Lat, @Lon, 4326)) < @MaxDistance

     

    This assumes you would add a field to the Places table to store the place Geography.

    • Marked as answer by Isaac Kunen Wednesday, July 27, 2011 2:29 PM
    • Unmarked as answer by Mothermugger' Wednesday, July 27, 2011 7:49 PM
    Tuesday, July 26, 2011 6:20 PM
  • LongHoss

    Hey thanks for the suggestion and esp. the example not sure about adding a field 'Geography' but will investigate and get back with results...

     

     


    - 10spotdomain for all your domain and domain related needs
    Tuesday, July 26, 2011 6:35 PM
  • @longhoss is absolutely correct - the geography datatype is specifically designed for storing and operating on data defined using latitude/longitude coordinates. It provides a range of methods to easily identify features (points, linestrings, or polygons) that lie within, cross over, overlap, or otherwise intersect a particular area (STIntersects) or that lie within a certain distance of another feature (STDistance), as well as calculating metrics of such features (STLength, STArea) etc.
    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Tuesday, July 26, 2011 7:08 PM
    Answerer
  • Thanks for the input Tano Shimi 

    I am exploring this now - more questions than solutions at this point but it seems like a great feature.

    I am thankful for this forum as the documentation provided by Microsoft is always verbose

    I wish they would write the documentation more like a news paper article with the summery and an explanatory 'picture' or example at the start and then go in to the long winded details later. 


    - 10spotdomain for all your domain and domain related needs
    Tuesday, July 26, 2011 7:34 PM
  • My sample query:

    SELECT * FROM Venues WHERE geo.STDistance(GEOGRAPHY::Point(73.94, 42.81,  4326)) < 1000

    Part of sample data that should be returned last field is the GEO field

    Albany Pine Bush Preserve    Albany    NY    12205    0xE6100000010CA72215C6165A454032ACE28DCC7552C0

    but noting is returned -

     

    on the other had should it be this simple? I am looking to return a radius of locations around a given point

     

    also is the distance expressed in miles ?


    - 10spotdomain for all your domain and domain related needs
    Wednesday, July 27, 2011 7:49 PM
  • Now this query seems to work

     

    WHERE geo.STDistance(GEOGRAPHY::Point(39.65337, -105.17643,  4326)) < 10000

     

    but distance what is the measure of the distance?


    - 10spotdomain for all your domain and domain related needs
    Wednesday, July 27, 2011 7:59 PM
  • Your query is currently searching for venues within 1000 metres of a point at latitude 73.97, longitude 42.81.... that's somewhere in the Siberian Ocean quite close to the North pole.

    Assuming that you're were looking for somewhere in America, you firstly need to swap your coordinates around (latitude first, then longitude), and also your longitude coordinate needs to be negative since it's west of the prime meridian. So:

    SELECT * FROM Venues WHERE geo.STDistance(GEOGRAPHY::Point(42.81, -73.94, 4326)) < 1000
    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, July 27, 2011 8:02 PM
    Answerer