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

Spatial

PostGIS 3.2 New and Improved

Avatar for Paul Ramsey

Paul Ramsey

4 min read

Last month, just under the wire for a 2021 release, the 3.2 version of PostGIS hit the streets! This new PostGIS also supports the latest 3.10 release of GEOS, which underpins a few of the new features.

Raster Algorithms

The postgis_raster extension uses the GDAL raster library for things like vectorizing rasters and rasterizing vectors (yes!). This release exposed a few more cool GDAL algorithms.

  • The new ST_InterpolateRaster function allows collections of measurement points to be interpolated into a continuous raster surface.

wsdot_surface-1

  • Those continuous raster surfaces can be fed to the new ST_Contour function, to calculate the iso-lines of the surface.

contour3-1

  • The same raster surfaces can be used as input to add dimensional attributes to 3D and 4D vector objects, with ST_SetZ and ST_SetM.

contour7-1

Raster Cloud Access

One of the more exciting trends in geospatial over the last five years is the growing consensus that big geospatial data should live in the cloud, in HTTP object stores (like AWS S3, Google Cloud Storage or Azure Blob Storage). This changes the default location of major raw data archives from 'on the USB drive under Joe's desk' to something publicly accessible and, thus, available for integration into analysis chains.

With raster data in the cloud, it is possible to do raster-based analysis using the postgis_raster extension without ever importing the source raster data. You can even do it in a (appropriately configured) cloud database service like Crunchy Bridge.

Our initial blog posts about cloud raster analysis were fun, but they showed up a limitation in cloud-raster-from-the-database: there was no good way to access private objects.

With PostGIS 3.2, it's now possible to supply credentials to cloud object stores, so you can do remote analysis against your private raster collections too.

Faster/Better Validity

One of our development focuses over the past couple years has been making the computational geometry engine that PostGIS uses, the GEOS library, faster and more modern.

For this release cycle, two common user facing functions were modernized, ST_IsValid and ST_MakeValid.

  • ST_IsValid was re-written to be more efficient, and is about 30% faster depending upon inputs.
  • A new implementation of ST_MakeValid was added, that is twice as fast, and respects the structural makeup of inputs a little better. The result is (depending on your interpretation) more "reasonable" corrections to validity mistakes.

overlapping-multi-1

(Optional) Faster GIST Index Build

This year a Google Summer of Code student took on an ambitious PostGIS project: using the new GIST "sort support" hooks for PostgreSQL 14 to cut index build times.

The work has been committed and does substantially speed up GIST index build times, between two and five times.

However, there is a catch: the indexes built in the manner are less well structured than the default incremental index builds. This can result in query performance degradation ranging from none to 30% or more.

Because we expect that index query work will outweigh index build for most use cases, we have left the new sort support hooks out of the default geometry operator class.

If you want to enable the faster index build (at the expense of query performance) you can add the sort support function to the default operator class before building your index.

ALTER OPERATOR FAMILY gist_geometry_ops_2d USING gist
  ADD FUNCTION 11 (geometry)
  geometry_gist_sortsupport_2d (internal);

You can turn the operator back off by sort support from the operator class.

ALTER OPERATOR FAMILY gist_geometry_ops_2d using gist
  DROP FUNCTION 11 (geometry);

Remember that any indexes you built with sort support on will still have the suboptimal structure, even after you turn sort support off.

FlatGeoBuf Format

In the category of 'maybe this will take off' is the new support for the FlatGeoBuf format. As with other MVT and GeoBuf formats supported by PostGIS, the ST_AsFlatGeoBuf function takes in a row, or set of rows, and outputs a single binary bytea that encodes all the data in those rows.

The FlatGeoBuf format is a vector take on the same principle as the Cloud Optimized GeoTiff: a format in which features that are near one another in space are also near each other in the format byte stream. This allows spatial windows of data to be extracted with relatively few HTTP range reads. 'Cloud optimization' is all about reducing the number of accesses needed to service common use cases.

So far there is not a lot of use of FlatGeoBuf in the wild, but it fills an un-met need in the vector cloud storage space, so that may change quickly.

Many Other Things

Any summary of changes for a release will miss a huge number of niche enhancement and fixes, so true aficionados should review the closed ticket backlog and the release NEWS file.

Related Articles