Fossgis: postgis in action - Oliver Tonnhofer¶

Tags: fossgis, geo

(One of my summaries of a talk at the 2017 fossgis conference).

They combine openstreetmap data with ALKIS data. ALKIS is the german cadastre database. Both are imported into postgres/postgis.

In postgres, it is handy to use separate namespaces (postgres calls them “schemas”, but that is a bit of a confusing name).

When doing a project for 38 “Kreisen” in the Ruhr area, they imported all the 38 datasets each into a separate namespace/schema to keep them apart.

In the end, it is handiest to end up with one table per map layer. You can select multiple data by doing an sql UNION. You’ll probably need a lots of sql tricks to get it all working.

Instead of working with these complex queries, it is better to create a view with the complex query so that your map server has just a simple table/view to talk to.

Optimization: first measure what you have to optimize. log_min_duration_statement is a good postgis setting to see long-running queries in your logfile. EXPLAIN ANALYZE tells you what postgres does behind the scenes.

Watch out with the spatial queries that mapnik and mapserver wrap around your own query: they add a bounding box query on the geometry. But…. if the geometry is a calculated value (centroid, for instance), the bbox query cannot use an index. Solution is to add !BBOX! in your query, this tells mapnik to do the bbox query there inside your original query, which allows postgres to use the index anyway.

What also helps: simplifying your data. Leaving out unneeded information with database filters or geometrical simplifications (straightening lines a bit when that level of detail is not visible).

His sheets (in German) with more details are here: https://talks.omniscale.de/2017/fossgis/postgis/