City/Country from Lat/Long

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

Answer 1

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/

Answer 2

Thank you!  I thought SQL Server would support reverse-geocoding natively, but I was mistaken.


Pasha

---

Thanks, Pasha

Answer 3

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/

Answer 4

Got it!

---

Thanks, Pasha