Postpass/QL Overview
This page contains a list of the basic features of Geofabrik's Postpass server.
If you are not familiar with SQL in any way, check out some SQL tutorial.
Tables
| Name | Description | Comment |
|---|---|---|
postpass_point
|
OSM point features | |
postpass_line
|
line features | osm2pgsql splits way and relations into lines and polygons |
postpass_polygon
|
polygon features | osm2pgsql splits way and relations into lines and polygons |
postpass_pointline
|
point and line features | Just a view in the two corresponding tables |
postpass_pointpolygon
|
point and polygon features | Just a view in the two corresponding tables |
postpass_linepolygon
|
line and polygon features | Just a view in the two corresponding tables |
postpass_pointlinepolygon
|
All OSM features | Just a view in the three corresponding tables |
Note: This is just an overview. The full schema can be found here.
Rows
| Name | Description | Comment |
|---|---|---|
osm_type
|
Node, Way or Relation
|
|
osm_id
|
ID of the OSM Object | |
tags
|
json_b containing all OSM tags
|
|
geom
|
Actual PostGIS geometry | |
length_m
|
Length of the line in meters | Only on line features |
area_m2
|
Area of the polygon in square meters | Only on polygon features |
Note: This is just an overview. The full schema can be found here.
Simple Filters
Filters can be placed behind the WHERE in the SQL query and can be concatenated using AND and OR.
Bounding Box
geom && ST_MakeEnvelope(south,west,north,east, 4326)
Overpass-turbo:
geom && {{bbox}}
Overpass-ultra:
geom && ST_MakeEnvelope({{wsen}},4326)
Has Key
Has key
tags ? 'key'
Does not have key
NOT tags ? 'name'
Has at least on of the keys
tags ?| ARRAY['key1','key2']
Has all keys
tags ?& ARRAY['key1','key2']
Key Value
Has key value pair
tags ->> 'key' = 'value'
Does not have key value pair
tags ->> 'key' != 'value'
Multiple key value pairs
tags @> '{"key1": "value1", "key2": "value2"}'::JSONB
Note: This is just an overview. More information can be found in the Postgres Documentation
Multiple elements
Multiple elements can be queried by using a JOIN. Behind the ON statement the combination filters can be stated.
SELECT a.osm_id, a.tags, a.geom
FROM postpass_point AS a
JOIN postpass_point AS b
ON ST_DWithin(a.geom::geography, b.geom::geography, 5) AND a.osm_id < b.osm_id
WHERE a.geom && {{bbox}} AND b.geom && {{bbox}}