[postgis-users] Query: find the country of a location / city

[Phil James]

Asssuming the geometries are stored as the_geom the nearest city to a point would be

Select 
name, 
ST_DISTANCE(the_geom, GEOMFROMTEXT('POINT(lon lat)', 4326)) AS dist
FROM 
cities
ORDER BY
DIST ASC
LIMIT 1;

Will find you the nearest city to the point specified by lon lat above. I am assuming table cities exists and has a column called name and a column called the_geom and is stored in WGS84.  There are any number of open country / city datasets available that could be used for the cities/country table.

You could also use ST_DWITHIN and  specify a search radius as well.

Hope this helps

Phil



>________________________________
>From: Torsten Mohr <tmohr at s.netic.de>
>To: postgis-users at postgis.refractions.net
>Sent: Sunday, 6 November 2011, 15:49
>Subject: [postgis-users] Query: find the country of a location / city
>
>Hello,
>
>i search for a SQL query that lets me find the country of a given location 
>(mercaator latitude / longitude) or a city name.
>
>Can anybody give me a hint on how to formulate such a query?
>
>
>Thanks for any hints,
>Torsten.
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111106/d408e5c5/attachment.html>