Useful SQL queries for OSM and PostGIS

As a follow up to my previous post on setting up a local instance of OSM data in a PostGIS DB, this post is a list of useful queries I've come up with (or borrowed*) to get useful results out of the OSM data

Once you are on the command line, run psql to drop into the postgres sql shell and run the \d command, which should give you the list of tables and views as below.

gis=> \d
               List of relations
 Schema |        Name        | Type  |  Owner   
--------+--------------------+-------+----------
 public | geography_columns  | view  | postgres
 public | geometry_columns   | view  | postgres
 public | planet_osm_line    | table | gisuser
 public | planet_osm_nodes   | table | gisuser
 public | planet_osm_point   | table | gisuser
 public | planet_osm_polygon | table | gisuser
 public | planet_osm_rels    | table | gisuser
 public | planet_osm_roads   | table | gisuser
 public | planet_osm_ways    | table | gisuser
 public | spatial_ref_sys    | table | postgres
(10 rows)

Useful queries

Retrieve all cities in alphabetical order:

select name, place, ST_XMin(way), ST_YMin(way) from planet_osm_point where place='city' order by name;

Retrieve all suburbs in alphabetical order:

select name, place, ST_XMin(way), ST_YMin(way) from planet_osm_point where place='suburb' order by name;

* Borrowed means referred to the below sites