Is it possible to get City from Longitude/Latitude?

sql, sql-server, sql-server-2008

Solution

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

Problem

Original source