Is it possible to get City from Longitude/Latitude?

Question

I have heard that SQL Server now supports Geography like things. Is it possible to get City from Longitude/Latitude? I have search a lot but could not find any?

Answer 1

You need to load some data with city locations - Geonames(you might want cities15000.zip) or Natural Earth(Populated places) might be good places to start.

CREATE TABLE cities ( 
  name VARCHAR(200) PRIMARY KEY,
  location GEOGRAPHY, 
);
CREATE SPATIAL INDEX idx_cities_location ON cities(location);

INSERT INTO cities (name, location) VALUES
  ('Auckland', geography::STGeomFromText('POINT(174.7833 -36.85)', 4326)),
  ('London', geography::STGeomFromText('POINT(-0.1062 51.5171)', 4326))
;

• Note the order of all coordinates is (Longitude Latitude)
• Spatial indexes make it fast.
• the 4326 is the coordinate system code for basic Longitude/Latitude.

Then to find the closest city to a given location you want a query like:

DECLARE @g geography = 'POINT(103.75 1.3667)';
SELECT TOP(1) name FROM cities
WHERE location.STDistance(@g) IS NOT NULL
ORDER BY location.STDistance(@g);

More examples in the SQL Server docs:

• Create, Construct, and Query geography Instances
• Query Spatial Data for Nearest Neighbor

Answer 2

The functionality you're describing is called "Reverse-Geocoding" - taking a lat/lng coordinate, and returning the name of the nearest place to that location. (Geocoding is the opposite - providing a placename in order to retrieve the associated lat/lng coordinates).

You can certainly build a reverse-geocoding service that uses SQL Server as the backend database, but there's nothing built-in to provide such functionality.

If you simply want to reverse-geocode a set of existing data, rather than create this functionality in SQL Server, you might find it easier to use an existing webservice. For example, the geonames findNearbyPlaceName has a REST interface that you access via the following URL template:

http://api.geonames.org/findNearbyPlaceName?lat=52.62&lng=1.28&username=demo

This example returns information about the nearest known entity to the requested lat/lng coordinates (52.62,1.28) from the geonames database, as follows:

<geonames>
  <geoname>
    <toponymName>Norwich</toponymName>
    <name>Norwich</name>
    <lat>52.62783</lat>
    <lng>1.29834</lng>
    <geonameId>2641181</geonameId>
    <countryCode>GB</countryCode>
    <countryName>United Kingdom</countryName>
    <fcl>P</fcl>
    <fcode>PPLA2</fcode>
    <distance>1.51318</distance>
  </geoname>
</geonames>