Postpass/Overpass API by Example

This page is a work in progress! The content is likely incomplete, inaccurate or empty.

This article lists equivalent syntax to convert between Postpass and Overpass: Overpass_API/Overpass_API_by_Example

Users new to either PostGIS/PostgreSQL/SQL or Overpass can refer to the other side they are more familiar with. It's also useful for copying code snippets without saving queries.

SQL can be highlighted Template:OTUP

Possible to-do: Make side-by-side table similar to Template:OverpassTurboRosetta to compare directly here (But page creator thinks using split view in browser may be more convenient)

Introduction

The Taginfo Example

Post boxes

{{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'

amenity=post_box only expected as points

Some Standard Features

Restaurants by their centerpoints

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT osm_type, osm_id, ST_Centroid(geom), tags
  FROM postpass_pointpolygon
  WHERE 
    geom && {{bbox}} AND
    tags->>'amenity'='restaurant'

amenity=restaurant not expected in lines, so only FROM postpass_pointpolygon

Rare Tags

Names

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT osm_type, osm_id, geom, tags
  FROM postpass_pointlinepolygon
  WHERE  
    tags->>'name'='Belsenplatz'

Don't do this when it's more than hundreds (You can use QLever for higher numbers)

Understanding Data

Tagging

Tagged Nodes

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}}

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.

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 '% ').

See also

• Postpass/Examples