how to get all cities with coordinates of a region?

Question

Hi I imported the ism file into posture/PostGIS database. Please guide me how can I get all cities and towns of a region with coordinates (Only I need Lat & lon). Also How can I get all hamlet of a city or town with coordinates too.

Many thanks for all people working on this great project.

Accepted Answer

A simple approach would be (assuming you used osm2pgsql without the -l (ell) option for importing):

 select st_astext(st_transform(way, 4326)),name 
 from planet_osm_point where place='city';

You can add a condition to limit the area, like

 ... and way && st_transform(st_makebox2d(st_point(x1,y1), st_point(x2,y2)),900913):

where x1,x2,y1,y2 are two corners of your bounding rectangle. If you want to find all suburbs within a city named "Mycity" you could do

 select st_astext(st_transform(way, 4326)),name 
 from planet_osm_point 
 where place='suburb' 
 and way && (select way from planet_osm_polygon 
     where boundary='administrative' 
     and admin_level='8' and name='Mycity');

however this only works if the specified city boundary exists and is unique. Use `st_contains`` instead of the && operator to achieve real polygon operations insead of just bounding boxes.