Examples of querying a OSM PostgreSQL table with the hstore tags column

I have found some tutorials (1 and 2) for using the hstore column type in PostgreSQL. This blog post is specific to querying OpenStreetMap data using the tags column in relations ways and nodes (the tags column is a hstore).

OpenStreetMap data in the "snapshot schema" uses a hstore column to store all tags for ways and nodes. This is different from the older "simple" schema, that used separate key-value relations node_tags and way_tags which requiring a join.

Below are some examples of selecting rows based on tags in the hstore column.

Examples: Ways

Select ways that are tagged with "tagplace=city":

SELECT * FROM ways WHERE tags @> '"place"=>"city"'::hstore

Select distinct tag-values for the tag-key 'place':

SELECT DISTINCT tags->'place' FROM ways;

Select all cities, towns and villages:

SELECT tags->'name' AS name, tags->'place' AS place FROM ways WHERE tags->'place' IN ('city','town', 'village');

Examples: Nodes

Select cities, towns, villages and hamlets from nodes, coordinates in EPSG:25832:

SELECT 
    st_x(ST_Transform(geom,25832)) AS x, 
    st_y(ST_Transform(geom,25832)) AS y, 
    tags->'name' AS name, tags->'place' AS place, 
    tags->'population' AS population 
FROM 
    nodes 
WHERE 
    tags->'place' IN ('city','town', 'village', 'hamlet');

Get tags keys used in nodes:

-- takes a long time if there is no index on tags
SELECT DISTINCT skeys(tags) FROM nodes;