https://blog.crunchydata.com/blog/postgis-3.2-new-and-improved Crunchy Data Home * Home * Cloud Crunchy Bridge Overview Documentation Pricing Get Started Cloud Partners AWS Azure GCP Create your account Login Contact us * Products Featured Products Crunchy Bridge Fully managed cloud Postgres available on AWS, Azure, & GCP. Crunchy PostgreSQL for Kubernetes The Postgres Operator easily lets you automate your Postgres in Kubernetes, whether it's one or thousands of instances. Crunchy High Availability PostgreSQL Integrated high availability PostgreSQL solution for enterprises with always on requirements. Crunchy Hardened PostgreSQL Hardened, secure, access controlled PostgreSQL to meet advanced security requirements. All Crunchy Products Crunchy Bridge Crunchy PostgreSQL for Kubernetes Crunchy High Availability PostgreSQL Crunchy Hardened PostgreSQL Crunchy Certified PostgreSQL Crunchy PostgreSQL for Cloud Foundry Crunchy MLS PostgreSQL Crunchy Spatial * Solutions Industries Government Healthcare Finance SaaS Use Cases Internal Database-as-a-Service Embedded PostgreSQL Edge Computing PostGIS Integrations Ansible Tower JFrog Kong OpenShift * Learn Crunchy Data Developer Portal Software Documentation Value of a Subscription * Customers * Blog * Contact Us * Download * Login Access Portal Crunchy Bridge [drew-coffm] PostGIS 3.2 New and Improved January 13, 2022 Paul Ramsey PostGIS 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 * Finally, the ST_Value function (and ST_SetZ and ST_SetM) has been upgraded to support bilinear interpolation of values from rasters. 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. * Tweet * * A Postgres Primer for Oracle DBAs [hands-g065] Like what you're reading? Stay informed by subscribing for our newsletter! [ ] Newsletter Like what you're reading? Stay informed by subscribing for our newsletter! Read More Crunchy News Crunchy Data Solutions logo * * * * * Privacy Policy * (c) 2018-2022 Crunchy Data Solutions, Inc. Products * Crunchy Bridge * Crunchy PostgreSQL for Kubernetes * Crunchy High Availability PostgreSQL * Crunchy Certified PostgreSQL * Crunchy PostgreSQL for Cloud Foundry * Crunchy MLS PostgreSQL * Crunchy Spatial Services & Support * Enterprise PostgreSQL Support * Red Hat Partner * Trusted PostgreSQL * Crunchy Data Subscription Resources * Customer Portal * Software Documentation * Blog * Events * Videos * DISA STIG for PostgreSQL * CIS Benchmark for PostgreSQL Company * About Crunchy Data * News * Careers * Contact Us * Newsletter