Felix Kunde

slides.com/fxku/postgis

About Me

Research Assistant @ Beuth University Berlin

Geoinformatics background  

Guest lecturer on spatial databases

Core dev for 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
  • You can do all the operations known from a GIS
  • 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

Hidden HeroEs

PROJ

SF

Vector Support

  • Simple Features, Arcs
  • Geometry vs. Geographpy
  • Z- und M-Values
  • Linear Referencing
  • Topological Filter (Spatial Joins)
  • K Nearest Neighbour searches
  • Easy grouping and unnesting

ST_Split

ST_Snap

ST_MakeValid

ST_FlipCoordinates

ST_OffsetCurve

ST_ConcavHull

ST_Subdivide

(ST_Segmentize for lines)

ST_DelaunayTriangles

ST_VoronoiPolygons ST_VoronoiLines

http://zderadicka.eu/voronoi-diagrams/

ST_ClusterKMeans

(simple, fast)

ST_ClusterDBSCAN

(more realistic, but harder)

https://smathermather.com/2013/12/04/postgis-with-sfcgal/

SFCGAL

postgis_sfcgal extension

 

ST_Extrude

ST_3DArea, ST_Volume

ST_3DIntersection/Union/Difference

ST_Tesselate

ST_Orientation

ST_ApproximateMedialAxis

etc.

Grids (3.1)

ST_SquareGrid

ST_HexagonGrid

Topology & Routing

  • Model of nodes, edges and faces
  • Support editing, simplification etc.
  • Output to GML and TopoJSON
  • postgis_topology extension
  • pgRouting project
    • Dijkstra, A*, Shooting Star
    • Driving Distance, TSP

http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html

Raster Support

  • Im- and export via GDAL
  • in-db vs. out-db storage
  • Tiling, Union, Rescaling etc.
  • Terrain analysis functions
  • Joins between rasters and vector geometry
  • Convert to vector

Web Mapping

ST_AsMVTGeom (for geometry)

ST_AsMVT (MVTGeom + attributes)

ST_AsGeoBuff (lossless, meant for exchange)

ST_AsGeoJSON, ST_AsKML, ST_AsX3D

ST_Simplify, ST_RemoveRepeatedPoints

https://carto.com/blog/inside/MVT-mapnik-vs-postgis/

@__phiphou__ (https://twitter.com/__phiphou__/status/878599027473952769)

Compress Geometry with

ST_AsTWKB

SELECT
  pg_size_pretty(sum(ST_MemSize(geom))) AS original,
  pg_size_pretty(sum(length(ST_AsTWKB(geom)))) AS twkb
FROM
  vorarlberg.wald; 

original |   twkb
--------------------
  17 MB  |  2333 kB

 

More: https://carto.com/blog/smaller-faster/

PostGIS 2.5

2018/09/23

https://programmizm.sourceforge.io/blog

SP-GIST Index for geometry

 

  • Space-partitioned trees like kd tree, quadtree etc.
  • Indxing points and bounding boxes
  • Faster search on "spaghetti" data (with many overlaps)
  • 2D and 3D opclass
  • Yet no KNN support
  • Only works with PostgreSQL v11 (compress option)

 

ST_OrientedEnvelope

ST_FilterByM

ST_ChaikinSmoothing

ST_Buffer with 'side={left | both | right}'

(ST_MinimumRectangle)

(Iterate to create Bezier curves)

There's More

  • Better parallel query support (#3561 #3751 #3927)
  • ST_AsText(geom, maxdecimaldigits)
  • ST_GeomFromGeoJSON can consume JSON/B
  • ST_LineInterpolatePoints with repeat function
  • ST_Angle between 3 pts or 2 vectors
  • ST_Greyscale for raster
  • Extended out-db band settings
  • ST_QuantizeCoordinates to reduce precision
  • Geometry is hashed in CTEs
  • etc. etc.

9.4+

PostGIS 3.0

2019/20

  • Make upgrades less paintful! Change lib name.
  • New disk format? External storage type?
  • Function COSTs depending on geometry size
  • Raster in or out of Core?
  • Tolerance & Precision (#1629)?
  • 3D-aware geography?
  • Index-only scans with geometry?
  • nD-Geometry, e.g. for trajectories?
  • Cast to JSON / JSONB (#3687)?
  • osm2topology converter?
  • Apply some "modern" C?
  • https://trac.osgeo.org/postgis/wiki/PostGIS3

PostGIS Fund me

  • Spatial partitioning via PG 10+ (~ #181)
  • Fixed precision for overlays (#4001)
  • Geoprocessing with GEOGRAPHY (#3973)
  • ST_Blur & ST_Sharpen for raster (#2598, #2599)
  • Raster KDE (#2894)
  • Improvements for postgis_topology
  • https://trac.osgeo.org/postgis/milestone/PostGIS%20Fund%20Me

One Way to fund

External Projects You should know

Thanks

to

Regina, Paul, Sandro, Mark, Bborie,

Jorge, Nicklas, Dan, Olivier, Björn, Mateusz, Pierre, Darafei

Chris, Kevin, Dave, Jeff, Mark, David

Alex, Alex, Andrea, Andreas, Andreas, Anne, Arthur, Barbara, Ben, Bernhard, Brian, Bruce, Bruno, Bryce, Carl, Charlie, Dane, David, David, Eduin, Even, Esteban, Frank, George, Gerald, Gino, Guillaume, Iida, Ingvild, Jason, Jeff, Jose Carlos, Julien, Kashif, Klaus, Kris, Leo, Loic, Luca, Maria, Mark, Markus, Maxime, Maxime, Michael, Mike, Nathan, Nathaniel, Nikita, Norman, Rafal, Ralph, Rémi, Richard, Silvio, Steffen, Stephen, Tom, Vincent, Vincent

Teams behind GEOS, GDAL and Proj!

The whole PostgreSQL community!

The funding companies, organisations and individuals!

Made with Slides.com