Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

Spatial

PostGIS Day 2020 Roundup

Avatar for Kat Batuigas

Kat Batuigas

7 min read

Crunchy Data's second annual PostGIS Day took place a couple weeks ago on November 19th, and as a first-time attendee I was blown away by the knowledge-sharing and sense of community that I saw, even as I was tuning in remotely from my computer at home. This year's PostGIS Day was virtual, which allowed even more attendees from all over the world to participate. The talks had a live Q&A section and we set up a Discord server so attendees and speakers could all interact freely with each other.

To relive the experience a little bit, I thought I'd put together highlights from the Q&A's and chat. Huge thanks to everyone that participated! And if you weren't able to make it this year, I hope these highlights encourage you to attend next year.

PostGIS + QGIS

If you're part of the spatial community or have already worked with spatial data in some capacity, chances are you're at least familiar with QGIS, the open source GIS desktop application. QGIS runs on multiple platforms and is pretty flexible in the data formats it can work with.

Jessica Kane kicked off PostGIS Day with her talk "Using QGIS with PostGIS: A Dynamic Duo," and we saw that our event attendees were eager to learn how to harness the power of PostGIS and QGIS together. Here are just a few pointers that were shared in the group:

What version of QGIS is the most stable with the latest version of PostGIS?

The long term releases are reliable, but people do use the latest QGIS release (3.16 at the time of writing) with Postgres 13/PostGIS 3.0 with no issues.

What are some ways to make sure QGIS doesn't get bogged down when visualizing large amounts of spatial data?

QGIS should only pull the features within the map view (technically a bounding box conditional), but if the application slows down, you can suspend rendering by unchecking "Render" in the lower right corner of the status bar.

Bringing in the data as a compressed vector tile layer should also help with performance.

Is there a way to share QGIS styles through PostGIS if they use custom SVG markers?

Yes, these can also be saved in PostGIS - styles are saved in the public.layer_styles table. The related table for each style is tracked in the f_table_catalog, f_table_schema, and f_table_name columns in public.layer_styles.

Are there any best practices for versioning and tracking edits to database changes made in QGIS?

You could create a history tracking table in Postgres and write trigger functions to populate that table. With QGIS, here are a couple of plugins for working with that audit history:

PostGIS FTW: pg_tileserv and pg_featureserv

Among this year's PostGIS Day speakers were Paul Ramsey and Martin Davis on the Crunchy Spatial team. pg_tileserv and pg_featureserv are spatial microservices that provide access to spatial data in a Postgres/PostGIS database, without the need for middleware. Here are some of the questions that came up:

What is the difference / use cases for using pg_tileserv vs pg_featureserv?

pg_tileserv serves map tiles (MVT), while pg_featureserv serves data (features). There is some crossover, and the case for using both depends on your app. Paul's demo was a good example of how the two services can work together. (Note: Paul is hosting another live demo on December 4 - sign up here.)

Could pg_featureserv just serve attributes, i.e. no spatial?

Yes, if you define a function that returns a table with no geometry, pg_featureserv returns the data as JSON. See this example in the official user guide.

Would it be possible with pg_tileserv or pg_featureserv to have PostGIS functions return a buffered geometry of a query?

In the simplest case, you could write a function that runs a given query, which includes a buffered geometry. This does have some limitations--for example, if you wanted to work with arbitrary tables. With that said, there's actually a currently undocumented feature in pg_featureserv that lets you specify a geometry transform pipeline right in the query. Keep an eye out for a future blog post about the transform parameter.

More tips and tricks

Viewing spatial data

Aside from dedicated GIS applications, there are database client applications that can also visualize spatial data. pgAdmin4 has a Geometry Data Viewer that allows you to view spatial objects. DBeaver has a spatial data viewer as well.

PostGIS

What is the recommended version for PostgreSQL and PostGIS for the best MVT generation performance?

The latest version is always recommended; optimizations are being added all the time. The difference between PostGIS 2.5 and 3.0 is stark, and 3.1 is faster yet. Later versions of PostgreSQL allow more parallelism as well.

I still use the ST_SimplifyPreserveTopology() function and it is still a slow operation. Does anybody have another suggestion for that?

You might be able to use the precision reduction coming in PostGIS 3.1. Not sure if that's faster--probably depends on the data and the precision. Also, it makes the geometries "pixelated," whereas ST_SimplifyPreserveTopology() is a bit cleaner. If not, you can do ST_Union() with a precision.

When working with linestrings I have noticed less capability compared to polygons. For example 1) ST_Union frequently just makes a multi, and does not dissolve the points into a single line. 2) there is no ST_MakeSimple, equivalent to ST_MakeValid. Is there a workaround for this?

You can use ST_LineMerge() for the line stitching behavior you want, and a combo of ST_Union() / ST_LineMerge() to get the "make simple" you want.

My setup of PostGIS 3.0 (Proj 6) doesn’t seem to project from NAD 27 (4267) to WGS 84 (4326) correctly. Is there a good way to check if the conus grid shift files are installed correctly? Or is there a newer way with Proj 6?

You might try to go NAD 27 - NAD 83 (for which there is a grid shift), and then NAD 83 - WGS 84, (for which the transform is null). The grids live in /usr/share/proj or /usr/local/share/proj usually (Unix).

Curious for more on how you are using Jupyter (in the context of Brent Tucker's talk, "PostGIS in the data science pipeline").

Jupyter is used for model pre-processing and visualization (as a client to PostGIS spatial functionality). It's also used for prototyping code and testing a parameterized notebook in a data pipeline.

pgRouting

pgRouting extends PostGIS/Postgres to provide geospatial routing functionality. The book pgRouting: A Practical Guide is also available as a resource. And for QGIS users, you can check out the pgRouting plugin too.

Does pgRouting have the Rural Postman Problem Algorithm?

No, but it does support the Chinese postman problem (experimental). You can also use the osm2pgrouting tool to import OSM data into Postgres, and all the data and routing constraints will be imported.

Resources

While there were attendees who had been using PostGIS for a while, we certainly also had attendees who were newer to PostGIS. Here are some resources that were shared:

Documentation

Books

Courses

Communities

You can also level up your PostGIS powers simply by learning more PostgreSQL and PL/pgSQL. (And while we're here, I'll put in a plug for Crunchy Data's Learning Portal: we have quick, interactive courses on all things Postgres, from the basics, to functions and procedural languages, and PostGIS.)

Stay tuned

The PostGIS Day sessions were recorded and we'll be sharing those recordings as well as slides very soon. I'd recommend subscribing to our Crunchy Data newsletter or following us on Twitter to be the first to know!

Related Articles