Postpass/Examples
< Postpass
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.
Count addresses in a municipality
This query counts all housenumbers in in Ochtrup, Germany (OSM relation ID 155790):
{{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:
{{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 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.
{{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
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_pointpolygon
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,
};
};