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

[Torsten Mohr]

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
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[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 <tm...@s.netic.de>
To: postgi...@postgis.refractions.net
Sent: Sunday, 6 November 2011, 15:49
Subject: [postgis-users] Query: find the country of a location / city

[pcr...@pcreso.com]

try something like select placename from table where ST_Contains(geom,point);   This will return the placename for all polygons (geom) which contain the specified point. HTH,    Brent Wood --- On Mon, 11/7/11, Torsten Mohr <tm...@s.netic.de> wrote: