Postpass

A simple wrapper around PostGIS that allows random people on the internet to run PostGIS queries without ruining everything

This is inspired by the great success in OpenStreetMap circles of the Overpass API together with Overpass Turbo. Postpass is intended to do approximately the same things that Overpass API does, just based on a PostGIS database.

While all documentation assumes that you will want to use this with an OpenStreetMap database, Postpass itself is totally agnostic about the data you have in your database and you could theoretically use it with anything else as well.

See woodpeck/postpass-ops for docs on the instance of this software at postpass.geofabrik.de.

Building

A simple

go build -o postpass-server postpass/main.go

should do the trick.

You can also type

make

You will need go v1.24 for this.

Setup and Installation

You want a local PostGIS database with some sort of OpenStreetMap data import. We assume that you will use osm2pgsql to import your data, possibly along the lines discussed in the OSM Carto installation guide. (The instance on postpass.geofabrik.de uses a slightly different data schema, see woodpeck/postpass-ops for details.)

It is very much recommended to create a read-only user in your database, else you'll have random people on the Internet doing the "little bobby tables" joke on you:

create user readonly with password 'readonly';
grant select on all tables in schema public to readonly;

You could now start the Postpass software manually (it will listen on its own port, by default 8081); for a halfway reliable production environment you will probably want to create a unix user postpass and a systemd service like so:

[Unit]
Description=postpass server
StartLimitIntervalSec=0
StartLimitInterval=0

[Service]
User=postpass
ExecStart=/srv/postpass/postpass-server # or whereever your binary is
StandardOutput=journal
StandardError=journal
Restart=on-failure
RestartSec=10

[Install]
WantedBy=multi-user.target

and you will probably want to configure a standard web server to sit in front of Postpass, for example Apache:

<VirtualHost *:80>
  ServerName my.postpass.server

  DocumentRoot /var/www/html

  ProxyTimeout 3600
  RewriteEngine on
  RewriteRule /api/0.2/(.*) http://localhost:8081/$1 [P]
  RewriteRule /api/(.*) http://localhost:8081/$1 [P]
</VirtualHost>

Using

Curl

While GET requests are supported, POST requests are probably the better way to use the service. Here's a simple test query that will load fast food POIs from your local osm2pgsql database:

curl -g https://postpass.geofabrik.de/api/0.2/interpreter --data-urlencode "data=
    SELECT name, way 
    FROM planet_osm_point
    WHERE amenity='fast_food' 
    AND way && st_setsrid(st_makebox2d(st_makepoint(8.34,48.97),st_makepoint(8.46,49.03)), 4326)"

LLM

This prompt helps to generate good results with LLMs like ChatGPT.

Please generate a curl command containing a SQL query that will be sent to the Postpass API, which exposes a PostGIS-enabled PostgreSQL database with OpenStreetMap data.

The API endpoint is https://postpass.geofabrik.de/api/0.2/interpreter

The underlying database schema is described at: https://github.com/woodpeck/postpass-ops/blob/main/SCHEMA.md

The database uses the osm2pgsql flex schema, storing tags as jsonb. You have access to the following main geometry tables:

• postpass_point (geometry: Point)
• postpass_line (geometry: MultiLineString)
• postpass_polygon (geometry: MultiPolygon)

Additionally, combined geometry views are available:

• postpass_pointpolygon
• postpass_pointline
• postpass_linepolygon
• postpass_pointlinepolygon

Tags are stored in a jsonb column named tags. Use tags->>'key' to retrieve values or tags ? 'key' to check for tag presence.

By default, the API returns results as GeoJSON (geometry included). If geometry is not required, include the following in the request: --data-urlencode "options[geojson]=false". Use geojson=false when: No geometry column (geom) is selected; You are returning aggregated results (e.g., COUNT, GROUP BY).

---

Examples:

1. Return geometries (default GeoJSON):

curl -g https://postpass.geofabrik.de/api/0.2/interpreter --data-urlencode "data= SELECT name, geom FROM postpass_point WHERE tags->>'amenity' = 'fast_food' AND geom && ST_SetSRID(ST_MakeBox2D(ST_MakePoint(8.34, 48.97), ST_MakePoint(8.46, 49.03)), 4326)"

2. Return aggregated result (no geometry, use geojson=false):

curl -g https://postpass.geofabrik.de/api/0.2/interpreter --data-urlencode "options[geojson]=false" --data-urlencode "data= SELECT admin.tags->>'name' AS country, COUNT(point.*) AS ref_count FROM postpass_point AS point JOIN postpass_polygon AS admin ON ST_Contains(admin.geom, point.geom) WHERE point.tags->>'natural' = 'tree' AND point.tags ? 'ref' AND admin.tags->>'boundary' = 'administrative' AND admin.tags->>'admin_level' = '2' GROUP BY admin.tags->>'name'"

---

Always:

• Return a full curl command
• Never include a ; at the end of the SQL query.
• Use the correct table or view based on the geometry type requested
• Add geojson=false whenever geometry is not requested in the result set

---

MY QUESTION: …