Postpass – a public OSM PostGIS instance

General talk
1

(this is an updated and translated version of an announcement I did a week ago on the German forum)

Hi,

frequently on these forums someone asks “how can I extract this or that feature from OSM”, and someone else then replies with an Overpass query or even a ready-made Overpass Turbo link.

For some things, Overpass is the ideal solution. But (having used SQL for 30+ years) I often think, well, this would be an easy query for someone who has a PostGIS populated with the OSM planet. Obviously a reply like “load an extract, install PostGIS, import data with osm2pgsql, and THEN you can…” is not super helpful for many people who want instant gratification! (And believe me, I’ve written many such responses over the years.)

That’s why I made a public service that lets you query the OSM database, just like Overpass does, but based on PostGIS. Hence the name “Postpass”. In a first attempt, I had used the standard OSM Carto database schema (planet_osm_point and so on) because I thought that’s the “standard”, but after discussions at this year’s FOSSGIS conference where I soft-launched the thing, it was clear that people would prefer a somewhat leaner setup. It is still a schema with PostGIS geometries though – i.e. you’re not working with raw OSM data like you do in Overpass, but instead with ready-made geometries. Accordingly, the responses you get are GeoJSON, and not OSM XML.

Here’s a query that finds fast food restaurants in Karlsruhe:

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

Using curl and st_makeenvelope is a bit clumsy of course but don’t despair: You can use Overpass Turbo with a {{bbox}} marker just like you’re used to if you put a magic line in front telling it to access my server:

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

Of course these are just the simplest examples; a few more are on the Github page. SQL lets you make quite complex queries.

There’s the three main tables postpass_point, postpass_line, and postpass_polygon, but you also have combined views like postpass_pointpolygon and so on, to save you having to do lots of UNIONing if you want to combine points and polygons (which you often will when looking for POIs).

Queries must always return a geometry, otherwise the GeoJSON conversion fails. But you can use any sort of PostGIS geometry operation to arrive at that geometry, for example here’s a 0.1 degree buffer around Germany:

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT st_buffer(geom,0.01)
FROM postpass_polygon
WHERE tags->>'boundary'='administrative' 
AND tags->>'admin_level'='2'
AND tags->>'name'='Deutschland'

or if you want exactly 1000 metres, the same with a little reprojection using the geography data type in PostGIS:

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT st_buffer(geom::geography, 1000)::geometry
FROM postpass_polygon
WHERE tags->>'boundary'='administrative' 
AND tags->>'admin_level'='2'
AND tags->>'name'='Deutschland'

Queries like that are not lightning fast but they work. Here’s one that finds points in Germany where the addr:postal_code tag doesn’t match the postal_code boundary they’re in:

SELECT
   p.osm_id, p.geom,
   p.tags->>'addr:postcode' as point_zip,
   zip.tags->>'postal_code' as poly_zip
FROM postpass_point p, postpass_polygon zip
WHERE zip.geom && st_makeenvelope(5.53,47.23,15.38,54.96, 4326)
AND st_contains(zip.geom, p.geom)
AND p.tags->>'addr:postcode' <> zip.tags->>'postal_code'
AND zip.tags->>'boundary'='postal_code'

You can also compute and return intersections or geometry unions.

If you use a particular flag to tell the system you do not want geometries, you can send SQL queries that count stuff, for example “what amenity values do we have in Karlsruhe and how many of each”:

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"

You can even use Overpass Turbo for non-geometry queries if you add geojson=false to the magic {{data:sql}} line:

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/,geojson=false}}
SELECT count(*), tags->>'amenity' as amenity
FROM postpass_point
WHERE tags?'amenity'
AND geom && {{bbox}}
GROUP BY amenity

Of course there’s many things that work better or faster with Overpass, especially when you want to work closer to the OSM data. Postpass cannot do any history either.

This project has its home in two GitHub repositories. One is GitHub - woodpeck/postpass: a simple API wrapper around PostGIS for the software itself (which can theoretically be used with any kind of PostGIS database). The other is GitHub - woodpeck/postpass-ops: operational issues about the Postpass instance run on postpass.geofabrik.de and documents the concrete instance I am running, together with its data schema and examples. If you want to add examples via pull requests, or here in the thread, or start a Wiki page for that, go right ahead, I’m eager to see what people come up with!

All this is still young and probably not perfect. The system has three queues for slow, medium, and fast requests; even if the “slow” queue is clogged by a few people trying funny things, you will still be able to sneak a less expensive query past them in the fast lane. The system currently lacks a capability to check its load from the outside, or pre-flight a request to find out how expensive it is (but see Endpoint to check complexity of query · Issue #4 · woodpeck/postpass · GitHub).

Play around with this if you’re interested. I’m happy to hear feedback. Thanks to Martin Raifer and Daniel Schep for adding support for this in Overpass Turbo/Ultra!

If you want to hear me speak in German, here’s my FOSSGIS presentation: Overpass Turbo goes PostGIS - media.ccc.de – the examples in the slides are still using the old table schema though.

Cheers
Frederik

40 Likes