NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

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.

asked 22 Oct '12, 19:09

usef's gravatar image

usef
6111
accept rate: 0%


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.

permanent link

answered 22 Oct '12, 19:42

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×165
×134
×107
×14

question asked: 22 Oct '12, 19:09

question was seen: 6,587 times

last updated: 22 Oct '12, 19:42

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum