tags column in the above tables stores information about each map layer element. This information is as well available in columns. The set of columns varies based on the geometry type of the element, for example there are different columns for points and for lines.
OpenStreetMap (OSM) has well-documented and extensive tagging conventions, including categories like highway, railway, waterway, etc.
These are called OSM keys (e.g., highway) and values (e.g., residential, motorway, footway).
For example for lines the most common keys are:
This is the most commonly used tag for anything transport-related on land:
motorway
Major highways, no pedestrian access
trunk
Important roads, like expressways
primary
Major roads in a region
secondary
Less major roads
tertiary
Local connecting roads
residential
Streets in residential areas
unclassified
Minor rural roads
service
Service drives (to parking lots etc.)
footway
Pedestrian-only paths
cycleway
Bike paths
path
Multi-use or undefined small paths
track
Rural or agricultural tracks
Example
Find the the motorways (autostrade) in central Italy, with a speed limit more than 100 km per hour
select name, tags->'maxspeed' as maxspeed,
tags
from planet_osm_line
where highway in ('motorway')
and (tags->'maxspeed')::int > 100;
rail
Standard train tracks
light_rail
Urban light rail
subway
Underground metro
tram
Street-level tram lines
platform
Railway platforms
abandoned
No longer used rail lines
Example
Find the the high speed train lines in central Italy
select name, tags->'maxspeed' as maxspeed,
tags
from planet_osm_line
where railway in ('rail')
and (tags->'maxspeed')::int > 250;
river
Major river
stream
Smaller river or creek
canal
Man-made water channel
drain
Urban drainage
ditch
Rural water ditches
Example
Show rivers in central Italy that are navigable, boat=yes
select name,
tags
from planet_osm_line
where waterway in ('river')
and (tags->'boat')='yes';
Value
Meaning
line
High-voltage power line
minor_line
Lower voltage lines
cable
Underground cables
pole
Single support structure
For a complete and updated list of map features, consult OSM Wiki link
In PostgreSQL, hstore is a special column type that stores key-value pairs, like a mini-dictionary inside a single column.
OSM data often uses this format (when you import with osm2pgsql --hstore) to keep all tags that don't have dedicated columns.
Example tags column value:
"highway"=>"residential", "maxspeed"=>"50", "surface"=>"asphalt"
Let us explare some basic operations on hstore data
SELECT tags->'maxspeed'
FROM planet_osm_line
WHERE tags ? 'maxspeed';
tags->'maxspeed' → gets the value of maxspeed
tags ? 'maxspeed' → tests if the tag exists
SELECT *
FROM planet_osm_line
WHERE tags ? 'surface';
tags ? 'surface' gets record that in the tags column have a key named surface.
Another example
SELECT name
FROM planet_osm_line
where
tags ? 'maxspeed'
lists all lines that have a maxspeed key defined in the hstore column
Below there are a few examples
SELECT *
FROM planet_osm_line
WHERE tags ? 'surface';
select name,tags
from planet_osm_point
where tags?'cuisine'
If there is a need to convert the key value use :: operator for converting the value to the desired data type.
Examples
select name, tags->'maxspeed' as maxspeed,
tags
from planet_osm_line
where railway in ('rail')
and (tags->'maxspeed')::int > 250;
All restaurants with italian cuisine
select name,tags
from planet_osm_point
where tags?'cuisine'
and tags->'cuisine'='italian'