locked
City/Country from Lat/Long RRS feed

  • Question

  • Hi All!

    I'm completely new to spatial and geo data in SQL Server, but I have a datasource with some transactional data and each row includes lat/long coordinates.  Is there an easy way to convert these coordinates to the city and country? 

    Thanks,

    Pasha


    Thanks, Pasha

    Saturday, February 18, 2012 12:25 AM

Answers

  • That's generally referred to as reverse-geocoding. You can retrieve the closest street address/city/county/country associated with a given latitude/longitude coordinate from several reverse geocoding webservices, such as the Bing Maps REST Locations API: http://msdn.microsoft.com/en-us/library/ff701710.aspx

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by Peja Tao Monday, February 20, 2012 7:46 AM
    • Marked as answer by Pasha Furman Monday, February 20, 2012 4:17 PM
    Saturday, February 18, 2012 10:25 PM
    Answerer

All replies

  • That's generally referred to as reverse-geocoding. You can retrieve the closest street address/city/county/country associated with a given latitude/longitude coordinate from several reverse geocoding webservices, such as the Bing Maps REST Locations API: http://msdn.microsoft.com/en-us/library/ff701710.aspx

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by Peja Tao Monday, February 20, 2012 7:46 AM
    • Marked as answer by Pasha Furman Monday, February 20, 2012 4:17 PM
    Saturday, February 18, 2012 10:25 PM
    Answerer
  • Thank you!  I thought SQL Server would support reverse-geocoding natively, but I was mistaken.


    Pasha

    Thanks, Pasha

    Tuesday, February 21, 2012 7:33 AM
  • If you created a table of placename data with lat/long coordinates (say, imported from http://geonames.org) then you could always write your own reverse-geocoding function in SQL Server.... at it's simplest it would just be a nearest-neighbour query to get the closet record in the table to the supplied @LatLong:

    SELECT TOP 1 Placename
    FROM TableOfPlaces
    WHERE Location.STDistance(@LatLong) IS NOT NULL
    ORDER BY Location.STDistance(@LatLong) ASC;
    

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Tuesday, February 21, 2012 9:52 AM
    Answerer
  • Got it!


    Thanks, Pasha

    Wednesday, February 22, 2012 4:07 PM