Felix Kunde

slides.com/fxku/postgis-v3

3

About Me

Database Engineer @ Zalando

Geoinformatics background  

Guest lecturer on spatial databases

Postgres Operator, 3DCityDB and pgMemento

@FlxKu

What is PostGIS?

  • Extension to PostgreSQL database
  • Comes with it's own datatypes for geodata
  • Supports coordinate reference systems
  • Spatial indexing for fast geo queries
  • Open Source under GPLv2
  • More infos under http://postgis.net/

+

Why is it great?

  • Faster and more robust than your GIS
  • So much geo power with just some SQL
  • Great acceptance in the spatial industry
  • Follows international OGC/ISO standards
  • Build on top of one of the best databases

PostgreSQL

Forks & Extensions

Sharding

GPU

Time Series

Cloud

DW / MPP / Hadoop

Streaming SQL

NoSQL

PG-Storm

@delawen on PostGIS day

The

hidden

Things

NeW On-Disc format

  • More optional bytes for new things
  • Probably: Efficient point type
  • Probably: Faster joins
  • Maybe: Precision model
  • Upgrade support (no dump and restore)

Minimal Toast Decompression

  • Big geoms are sliced and compressed
  • When read, decompression takes time
  • Postgres 12 can "sneak" into first slice
  • E.g. read BBox to decide to skip geom

Optimizer

Support Functions

  • Problem <v3: Function inlining to trigger index
    • Hard for planner to consider parellel query

​​

  • Solution: Give optimizer insights about functions ...
    • ... and see more parallel spatial queries

ST_Intersects(geom1, geoms2)

SELECT geom1 && geom2 AND _ST_Intersects(geom1, geoms2)

SQL function

triggers index

C function

ST_Intersects(geom1, geoms2)

SUPPORT postgis_index_supportfn

C function triggers index

O

L

D

N

E

W

Breaking Things

Postgis_raster

my_postgis_db=# ALTER EXTENSION postgis UPDATE TO '3.0.0';
WARNUNG:  unpackaging raster
WARNUNG:  PostGIS Raster functionality has been unpackaged
TIP:  type `SELECT postgis_extensions_upgrade();` to finish
      the upgrade. After upgrading, if you want to drop raster,
      run: DROP EXTENSION postgis_raster;
ALTER EXTENSION

my_postgis_db=# SELECT postgis_extensions_upgrade();
HINWEIS:  Packaging extension postgis_raster

Strip Minor from lib

  • ​Before: ALTER EXTENSION postgis UPDATE first to fix it

  • Now: Lib is called postgis-3.so for all minor releases

  • Upgrade your Postgres with pg_upgrade and get the new PostGIS functions when running ALTER EXTENSION etc.

$> pg_upgrade
ERROR: could not access file "$libdir/postgis-2.5": No such file or directory

Broken indexes

  • REINDEX your HASH indexes 
  • REINDEX your BTREE indexes
  • REINDEX your nD spatial indexes
    • But hey, SP-GiST and GiST now support nD box operators for overlaps, contains, within, equals

Bye Bye

  • ST_Accum(), use array_agg
  • ST_AsGeoJSON(version, geometry)
  • ST_AsKML(version, geometry)
  • Remove SFCGAL support for functions which are already covered by GEOS
  • postgissvnversion() > postgislibrevision()
  • liblwgeom headers, librttopo if you need
  • PostgreSQL 9.5 support (3.1+)

The

Shiny

Things

Demo Time

SELECT ST_GeneratePoints(poly, 3)
  FROM geom, generate_series(1,5);

same result

O

L

D

N

E

W

SELECT ST_GeneratePoints(poly, 3)
  FROM geom, generate_series(1,5);

SELECT ST_GeneratePoints(poly, 3, seed := 1)
  FROM geom, generate_series(1,5);

random result

Really Random Points

Hilbert Curve order

  • Switch from Z-Curve in 2.x to Hilbert Curve
  • More compact spatial alignment
  • 30% faster algorithm also boosts GiST creation

Playing with TINS

  • ST_ConstrainedDelaunayTriangles in SFCGAL
  • TINs can be passed to GEOS functions
  • Now supported by all output functions
  • ST_3DIntersects (2D, Solid),  ST_3DDistance (Solid)

PostGIS

PostGIS SFCGAL

More LRS for polygons

  • ST_LocateBetween/Elevations now support:
    • GeometryCollection, Polygon, TIN, Triangle

triangles < -2m

Topology tests for Geometrycollection

  • ST_Overlaps, ST_Contains, ST_ContainsProperly, ST_Covers, ST_CoveredBy, ST_Crosses, ST_Touches, ST_Disjoint, ST_Relate, ST_Equals now work with GeometryCollection
  • Think about all the queries where you needed to ST_Dump before (like after ST_LocateBetween ;)

geom::json

  • Geometry can be casted with ::json / ::jsonb
  • ST_AsGeoJSON( record ) > GeoJSON Feature
  • Geography columns supported when in row

faster vector Tiles

  • ST_AsMVT boost with parallel aggregation
  • ST_AsMVTGeom more robust output
  • Wagyu for validation and clipping (GEOS job in the future)
  • Feature ID support
  • Serving MVT from PostGIS is easy

ST_Tileenvelope

  • BBox of Tile per zoom level
  • WebMercator (EPSG 3857) bounds by default
  • Custom bounds possible
  • Margin in PostGIS 3.1

Grids (3.1)

ST_SquareGrid

ST_HexagonGrid

Default 3D/4D (3.1)

  • Additional argument for ST_ForceXXX functions
  • Define default Z/M, still 0 when left out
  • [open] Have ST_SetZ, ST_SetM function

Hidden HeroEs

SF

SF

  • Many performance improvements
  • postgis_geos_noop (SQL <-> GEOS)
  • out-db raster support since 2.4
  • check out ogr_fdw extension!
  • Make it totally independent
  • New: ST_ConstrainedDelaunayTriangles

PostGIS Future

2020/21

  • New efficient geom types? External storage type?
  • Point density surfaces (weighted, kriging)
  • Tolerance & Precision (#1629)?
  • Much work in GEOS 3.9
  • 3D-aware geography?
  • Index-only scans with geometry?
  • https://trac.osgeo.org/postgis/wiki/PostGIS3

Thanks

to

Regina, Paul, Sandro, Darafei, Raúl, Bborie,

Alex, Andrea, Andreas, Anne, Arthur, Barbara, Bas, Ben, Bernhard, Bill, Björn, Brian, Bruce, Bruno, Bryce, Carl, Charlie, Chris, Christian, Dan, Dane, David, Eduin, Even, Esteban, Frank, George, Gerald, Gino, Guillaume, Iida, Ingvild, Jason, Jeff, Joe, Jorge, Jose Carlos, Julien, Hugh, Kashif, Kevin, Klaus, Kris, Kristian, Laurenz, Leo, Loic, Luca, Lucas, Maria, Mark, Markus, Mateusz, Matti, Maxime, Michael, Mike, Nathan, Nathaniel, Nicklas, Nikita, Norman, Olivier, Pierre, Rafal, Ralph, Rémi, Richard, Silvio, Steffen, Stephen, Steven, Sunveer, Tom, Vincent, Yuri

 

Teams behind GEOS/JTS, Proj, GDAL and (SF)CGAL!

The whole PostgreSQL community!

The funding companies, organisations and individuals!

Made with Slides.com