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;
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