<html><body><div style="color:#000; background-color:#fff; font-family:Courier New, courier, monaco, monospace, sans-serif;font-size:12pt"><div><span>Asssuming the geometries are stored as the_geom the nearest city to a point would be</span></div><div><span><br></span></div><div><span>Select </span></div><div><span><span class="Apple-tab-span" style="white-space:pre"> </span>name, </span></div><div><span><span class="Apple-tab-span" style="white-space:pre"> </span>ST_DISTANCE(the_geom, GEOMFROMTEXT('POINT(lon lat)', 4326)) AS dist</span></div><div><span>FROM </span></div><div><span><span class="Apple-tab-span" style="white-space:pre"> </span>cities</span></div><div><span>ORDER BY</span></div><div><span><span class="Apple-tab-span" style="white-space:pre"> </span>DIST ASC</span></div><div><span>LIMIT 1;</span></div><div><span><br></span></div><div><span>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.</span></div><div><span><br></span></div><div><span>You could also use ST_DWITHIN and specify a search radius as well.</span></div><div><span><br></span></div><div><span>Hope this helps</span></div><div><span><br></span></div><div><span>Phil</span></div><div><br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><div style="font-size: 12pt; font-family: 'Courier New', courier, monaco, monospace, sans-serif; "><div style="font-size: 12pt; font-family: 'times new roman', 'new york', times, serif; "><font size="2" face="Arial"><hr size="1"><b><span style="font-weight:bold;">From:</span></b> Torsten Mohr <tmohr@s.netic.de><br><b><span style="font-weight: bold;">To:</span></b>
postgis-users@postgis.refractions.net<br><b><span style="font-weight: bold;">Sent:</span></b> Sunday, 6 November 2011, 15:49<br><b><span style="font-weight: bold;">Subject:</span></b> [postgis-users] Query: find the country of a location / city<br></font><br>Hello,<br><br>i search for a SQL query that lets me find the country of a given location <br>(mercaator latitude / longitude) or a city name.<br><br>Can anybody give me a hint on how to formulate such a query?<br><br><br>Thanks for any hints,<br>Torsten.<br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@postgis.refractions.net" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br></div></div></blockquote></div></div></body></html>