Postpass/Examples

This page collects Postpass queries examples. Some include styling information for generating maps with Ultra.

Mind that servers running Postpass might have different database layouts. Your SQL queries need to be adapted if you run them against a Postpass instance with a different layout.

Notes about Performance

A database, such as PostgreSQL, can only respond fast if the query makes use of an index. If the database cannot utilise an index to answer a query, it must do an expensive full table scan.

Usually, a spatial filter (geom && YOUR_BOUNDING_BOX_GOES_HERE) makes the database utilise a spatial index. If you query data worldwide only by its tags, you should use the @> operator.

Query Examples

Nodes by Tag and Bounding Box

This query will return all nodes with amenity=post_box in a provided bounding box:

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT osm_id, geom, tags
  FROM postpass_point
  WHERE geom && {{bbox}} AND tags @> '{"amenity": "post_box"}'::JSONB

Linear Way by Tag without Spatial Filter

If you do not know beforehand where you would expect a tag, but if you are pretty sure that there are not much more than some hundred elements, then you can drop the bounding box.

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT osm_id, geom, tags
  FROM postpass_line
  WHERE geom && {{bbox}} AND tags @> '{"name": "Belsenplatz"}'::JSONB

All Tagged Nodes in a Bounding Box

The following query returns all nodes in the current bbox having at least one tag.

Apart from the spatial filter &&, no filter is necessary in the WHERE clause because the postpass_point table contains nodes with tags only.

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT osm_id, geom, tags
  FROM postpass_point
  WHERE geom && {{bbox}};

The idea for this query comes from Overpass API by example.

Nodes with exactly one specific tag

The aim of the following query is to find out all nodes with a specific tag and no other tag.

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
WITH named AS (
  SELECT
      osm_id, tags, geom,
      (SELECT count(*) FROM jsonb_object_keys(tags)) AS tag_count
  FROM postpass_point
  WHERE geom && {{bbox}} AND tags ? 'name'
)
SELECT
    osm_id, tags, geom
  FROM named
  WHERE tag_count = 1

Please replace the name with whatever tag may fit your needs. The pitfall of this approach is that you will not find objects that have a meaningless second tag. If you want to do so then you should filter out all acceptable tags.

The idea for this query comes from Overpass API by example.

Leading & Trailing spaces in names

Find name=* values with leading and trailing space characters, or multiple consecutive spaces.

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT osm_id, osm_type, tags, geom
  FROM postpass_pointlinepolygon
  WHERE geom && {{bbox}}
    AND tags ? 'name' AND tags->>'name' ~ '^[[:blank:]]|[[:blank:]]$|[[:blank:]]{2,}'

The same but for all keys starting with name, e.g. name=*, name:en=*:

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
-- Will return one row per feature
WITH all_features AS (
  SELECT osm_id, osm_type, tags, geom
    FROM postpass_pointlinepolygon
    WHERE geom && {{bbox}}
),
-- Will return one row per feature and key
all_keys AS (
  SELECT
      osm_id, osm_type, jsonb_object_keys(tags) AS key, tags, geom
    FROM all_features
)
SELECT
    osm_id, osm_type, tags, geom
  FROM all_keys
  WHERE key LIKE 'name%' AND tags->>key ~ '^[[:blank:]]|[[:blank:]]$|[[:blank:]]{2,}'

If you look for leading and trailing spaces only, you can simplify the WHERE condition to (tags->>key LIKE ' %' OR tags->>key LIKE '% ').

The idea for this query came from Overpass API by example.

Restaurants and pubs in close proximity of train stations

This query returns all restaurants and pubs in close proximity of train stations.

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT
    restaurants.tags->>'name' AS restaurant_name,
    stations.tags->>'name' AS station,
    restaurants.osm_id AS osm_id,
    restaurants.geom AS geom
  FROM postpass_pointpolygon AS restaurants
  JOIN postpass_pointpolygon AS stations
    ON (
        restaurants.tags @> '{"amenity": "restaurant"}'::jsonb OR restaurants.tags @> '{"amenity": "pub"}'::jsonb
      ) AND ST_DWithin(restaurants.geom, stations.geom, 0.001)
      AND (
        stations.tags @> '{"railway": "station"}'::jsonb
        OR stations.tags @> '{"railway": "halt"}'::jsonb
      )
      -- It is not necessary to use the && operator for both stations and restaurants but it makes the PostgreSQL query planner to use the spatial indexes better. Therefore, Postpass executes the query in a faster queue.
      AND stations.geom && {{bbox}}
      AND restaurants.geom && {{bbox}}
      AND ST_Distance(restaurants.geom::geography, stations.geom::geography) < 100

Count addresses in a municipality

This query counts all housenumbers in in Ochtrup, Germany (OSM relation ID 155790):

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/,geojson=false}}
SELECT
    count(1) AS hn_count
  FROM
    postpass_pointpolygon address,
    postpass_polygon ochtrup
  WHERE
    address.tags?'addr:housenumber'
    AND st_contains(ochtrup.geom, address.geom)
    -- && operator to make better use of the spatial index
    AND ochtrup.geom && address.geom
    AND ochtrup.osm_id=155790 and ochtrup.osm_type='R'

If you want to eliminate all duplicates when points of interest mapped as nodes and their building (way) have the same address, you have to group all by housenumber, street and place:

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/,geojson=false}}
SELECT
    count(1) AS hn_count
  FROM
    (
      SELECT
          address.tags->>'addr:housenumber' AS housenumber,
          address.tags->>'addr:street' AS street,
          address.tags->>'addr:place' AS place
        FROM
          postpass_pointpolygon address,
          postpass_polygon ochtrup
        WHERE
          address.tags?'addr:housenumber'
          AND st_contains(ochtrup.geom, address.geom)
          -- && operator to make better use of the spatial index
          AND ochtrup.geom && address.geom
          AND ochtrup.osm_id=155790 and ochtrup.osm_type='R'
        GROUP BY housenumber, street, place
    ) AS a

Find overlapping postal code boundaries

This query will return areas where the interiors of two postal code boundary relations intersect.

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/,geojson=true}}
SELECT
    a.osm_id AS id1,
    b.osm_id AS id2,
    a.tags->>'boundary' AS boundary1,
    b.tags->>'boundary' AS boundary2,
    a.tags->>'postal_code' AS postal_code1,
    b.tags->>'postal_code' AS postal_code2,
    ST_Intersection(a.geom, b.geom) AS geom
FROM postpass_polygon AS a
JOIN postpass_polygon AS b
  ON
    a.geom && {{bbox}}
    -- operator&& for performance improvements compared to just ST_Intersects
    AND a.geom && b.geom
    -- Don't return the same pair twice (<), don't join an entry with itself (!=)
    AND a.osm_id < b.osm_id
    -- ST_Intersects performs better as ST_Relate and every pair which meets the second criteria has ST_Intersects()=TRUE.
    AND ST_Intersects(a.geom, b.geom)
    AND ST_Relate(a.geom, b.geom, 'T********')
    AND a.osm_type = 'R'
    AND b.osm_type = 'R'
    -- tags ?& [key1,key2] might be slightly faster than two tags?'key', both use the index on the tags column
    AND a.tags ?& ARRAY['boundary', 'postal_code']
    AND b.tags ?& ARRAY['boundary', 'postal_code']

Count (and render) housenumber coverage per municipality

Calculate fraction of buildings with/without house number per municipality. This example includes MapLibre styling for Ultra rendering the area of each municipality depending on the calculated coverage.

Note: Housenumbers mapped as nodes inside the building or as an entrance node are taken into account. If you remove that LEFT OUTER JOIN, the query becomes faster.

---
---
type: postpass
style:
  layers:
    - type: fill
      fill-opacity: 0.5
      fill-color:
        - "interpolate-hcl"
        - ["linear"]
        - ["get", "fraction"]
        - 0.0
        - "#d7191c"
        - 0.25
        - "#fdae61"
        - 0.5
        - "#ffffbf"
        - 0.75
        - "#abd9e9"
        - 0.95
        - "#2c7bb6"
    - type: line
      line-color: '#000'
      line-width: 2
      line-opacity: 0.5
---
SELECT
    osm_id,
    osm_type,
    name,
    geom,
    building_count,
    buildings_with_number::FLOAT / building_count AS fraction
  FROM (
SELECT
    osm_id,
    osm_type,
    name,
    geom,
    COUNT(1) AS building_count,
    SUM(has_number) AS buildings_with_number
  FROM (
SELECT
    admin.osm_id AS osm_id,
    admin.osm_type AS osm_type,
    admin.tags->>'name' AS name,
    admin.geom AS geom,
    CASE WHEN buildings.tags ? 'addr:housenumber' OR housenumbers.osm_id IS NOT NULL THEN 1::INTEGER END AS has_number
  FROM postpass_polygon AS admin
  JOIN postpass_polygon AS buildings
    ON
      buildings.tags ? 'building'
      AND admin.geom && buildings.geom
      AND ST_Intersects(admin.geom, buildings.geom)
  LEFT OUTER JOIN postpass_point AS housenumbers
    ON
      -- Skip joining of buildings with house number on their polygon
      NOT buildings.tags ? 'addr:housenumber'
      AND housenumbers.tags ? 'addr:housenumber'
      -- Filter by our query bounding box makes it faster
      AND ST_MakeEnvelope({{wsen}},4326) && housenumbers.geom
      AND housenumbers.geom && buildings.geom
      AND ST_Intersects(housenumbers.geom, buildings.geom)
  WHERE
    admin.tags @> '{"boundary": "administrative", "admin_level": "8"}'::jsonb
    AND admin.osm_type = 'R'
    AND admin.geom && ST_MakeEnvelope({{wsen}},4326)
    AND buildings.tags->>'building' NOT IN ('no', 'garage', 'roof', 'shed', 'barn', 'farm_auxiliary', 'garages', 'school', 'greenhouse', 'service')
  ) AS a
  GROUP BY osm_id, osm_type, name, geom
  ) AS b

Irish Road name coverage, by hexagon

Ireland: Generate a hex grid (10km size, in Irish Grid 29903), showing how many km of roads there are in each hexagon, and what percentage have a name tag.

try it yourself in overpass-turbo

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}

with
ireland as materialized (
select geom from postpass_polygon where tags->>'boundary' = 'administrative' and tags->>'name' IN ('Éire / Ireland', 'Northern Ireland / Tuaisceart Éireann'))
,hexes AS materialized (select i, j, PostGIS_AddBBox(ST_Transform(geom, 4326)) as geom from ST_HexagonGrid(10000, ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-11, 51), ST_Point(-5, 56)), 4326), 29903)) )
,roads_irel AS materialized (select r.tags?'name' as has_name, ST_Length(r.geom::geography) as length_m, h.i, h.j from postpass_line r join ireland ON (ST_Intersects(r.geom, ireland.geom)) join hexes h ON (ST_Intersects(r.geom, h.geom))  where r.tags?'highway' AND tags->>'highway' NOT IN ('service', 'footway') )
,roads_hexes AS (select i, j,
   sum(length_m) as sum_length_m,
   sum(length_m) filter (where has_name) as sum_named_length_m
   from roads_irel r group by i, j
  )
select 
sum_length_m/1000 as sum_length_km,
sum_named_length_m/1000 as sum_named_length_km,
(sum_length_m - sum_named_length_m)/1000 as sum_unnamed_length_km,
100*sum_named_length_m/sum_length_m as perc_named,
geom
from roads_hexes join hexes USING (i, j)

Find potentially duplicate supermarkets

---
title: Duplicate supermarkets
description: Find potentially duplicate supermarkets in OpenStreetMap using [Postpass](https://github.com/woodpeck/postpass)
options:
  center: [10.447683, 51.163375]
  zoom: 7
type: postpass
---
WITH candidates AS (
  SELECT
    osm_id,
    osm_type,
    tags,
    geom,
    ST_PointOnSurface(geom) AS center
  FROM postpass_pointpolygon
  WHERE
    tags->>'shop' = 'supermarket'
    AND geom && ST_MakeEnvelope({{wsen}}, 4326)
    AND (
      tags->>'name' LIKE 'REWE%' OR
      tags->>'brand' = 'REWE' OR
      tags->>'brand:wikidata' = 'Q16968817'
    )
),
clustered AS (
  SELECT
    *,
    ST_ClusterDBSCAN(
      ST_Transform(center, 3857),
      eps := 50,        -- meters
      minpoints := 2
    ) OVER () AS cluster_id
  FROM candidates
),
dupes AS (
  SELECT * FROM clustered WHERE cluster_id IS NOT NULL
),
cluster_sizes AS (
  SELECT
    cluster_id,
    COUNT(*) AS cluster_size
  FROM dupes
  GROUP BY cluster_id
  HAVING COUNT(*) > 1
)
SELECT
  d.osm_id,
  d.osm_type,
  d.tags,
  d.geom,
  d.cluster_id,
  s.cluster_size
FROM dupes d
JOIN cluster_sizes s USING (cluster_id)
ORDER BY s.cluster_size DESC, d.cluster_id, d.osm_type, d.osm_id

Load query in Overpass Ultra

Representative points

Options for placing labels, and simplifying spatial analysis. Compares the result of ST_Centroid (blue), ST_PointOnSurface (middle), and ST_MaximumInscribedCircle (red). Need to use type: javascript with source code to insert lineMetrics: true for line-gradient: visualizing line direction.

---
type: javascript
style:  
  layers:
    - type: fill
      filter: [==, [get, osm_type], W]
      fill-color: gray
      fill-opacity: 0.5
    - type: fill
      filter: [==, [get, osm_type], R]
      fill-color: black
      fill-opacity: 0.5
    - type: line
      filter: [==, [geometry-type], LineString]
      line-width: 10
      line-gradient:
        - interpolate
        - [linear]
        - [line-progress]
        - 0
        - blue
        - 0.1
        - royalblue
        - 0.3
        - cyan
        - 0.5
        - lime
        - 0.7
        - yellow
        - 1
        - red
---
const query = `
SELECT osm_type, osm_id, tags, ST_Collect(ARRAY[geom, ST_MakeLine(ARRAY[ST_Centroid(geom), ST_PointOnSurface(geom), (MIC).center]::geometry[]), ST_Buffer((MIC).center,(MIC).radius)])
  FROM
  (
    SELECT osm_type, osm_id, tags, geom, ST_MaximumInscribedCircle(geom) AS MIC
      FROM postpass_polygon
      WHERE 
        geom && ST_MakeEnvelope({{wsen}},4326) AND 
        tags->>'landuse'='residential' AND tags->>'residential'='apartments' 
  ) 
`;

// Copy & paste https://gitlab.com/trailstash/ultra/-/blob/main/lib/queryProviders/postpass.js

export const source = async () => {
  const server = "https://postpass.geofabrik.de/api/0.2/interpreter";
  const resp = await fetch(server, {
    method: "POST",
    body: new URLSearchParams([["data", query]]),
    headers: {
      "Content-Type": "application/x-www-form-urlencoded",
      Accept: "application/json",
    },
  });
  if (!resp.ok) {
    throw new Error(
      `Postpass API returned ${resp.status}:\n${await resp.text()}`,
    );
  }
  const data = await resp.json();
  return {
    type: "geojson",
    data: data,
    attribution:
      '\u003Ca href="https://www.openstreetmap.org/copyright" target="_blank"\u003E© OpenStreetMap contributors\u003C/a\u003E',
    generateId: true,
    lineMetrics: true,
  };
};

Load query in Overpass Ultra

London Orbital stations catchment

Find stations inside M25. Draw a half-mile circle around them.

Hulls, projections, buffers

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}

WITH London AS
(
  SELECT osm_id, osm_type, tags, ST_ConvexHull(geom) AS geom
  FROM postpass_line
  WHERE 
  "osm_type"='R' AND "osm_id" IN ('106164', '3929584')
)
SELECT stations.osm_id, stations.osm_type, stations.tags, ST_Transform(ST_Buffer(ST_Transform(stations.geom,27700),805.67),4326)
FROM postpass_pointpolygon AS stations, London
WHERE
  ST_Intersects(stations.geom,London.geom) AND
  stations.tags->>'railway'='station'

Needs to be projected to get the distance correct (otherwise gets an ellipse from different lat and lon lengths), then reprojected back to be displayed in the tool. Some impreciseness along the M25 border from how convex hulls work.

Output Formatting

CSV output

This Unix command line will create a count of various amenity types in a fixed bounding box, and output the result as a CSV file with the help of the general-purpose JSON conversion utility "jq".

curl -g https://postpass.geofabrik.de/api/0.2/interpreter \
    --data-urlencode "options[geojson]=false" --data-urlencode "data=
      SELECT count(*), tags->>'amenity' as amenity
      FROM postpass_point
      WHERE tags?'amenity'
      AND geom && st_makeenvelope(8.34,48.97,8.46,49.03,4326)
      GROUP BY amenity" |
jq -r '["amenity","count"], (.result[] | [ .amenity, .count ]) | @csv' > myfile.csv

Markdown output

Unfortunately, jq can't (yet) convert JSON to Markdown table syntax, so you'll have to peruse a scripting language of your choice – in this example, Perl:

curl -g https://postpass.geofabrik.de/api/0.2/interpreter \
    --data-urlencode "options[geojson]=false" --data-urlencode "data=
      SELECT count(*), tags->>'amenity' as amenity
      FROM postpass_point
      WHERE tags?'amenity'
      AND geom && st_makeenvelope(8.34,48.97,8.46,49.03,4326)
      GROUP BY amenity" |
perl -e 'use JSON; undef $/; $i=<>; $s = decode_json $i; foreach my $h(@{$s->{"result"}}) { foreach my $k(keys %$h) { $len{$k}=length($k) unless defined($len{$k}); $len{$k}=length($h->{$k}) if ($len{$k}<length($h->{$k})); } } print "|"; $l="|"; foreach my $h(keys %len) { printf " %-*s |", $len{$h}, $h; $l .= " "; $l .= "-" x $len{$h}; $l .= " |"; } print "\n$l\n"; foreach my $h(@{$s->{"result"}}) { print "|"; foreach my $k(keys %len) { printf " %-*s |", $len{$k}, $h->{$k}; } print "\n"; }' > myfile.md

See also

• Postpass/Overpass QL
• Postpass/Overpass API by Example
• Ultra/Styling
• Ultra/Examples