Postpass/Examples

This page collects Postpass queries examples.

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.

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

Count (and render) housenumber coverage per municipality

Calculate fraction of buildings with/without house number per municipality. This example includes MapLibre styling for Overpass 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)