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.
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');
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;