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 <[email protected]>
>To: [email protected]
>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
>[email protected]
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
