Features Since V2

Felix Kunde

slides.com/fxku/postgis2

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
  • You can do all the operations known from a GIS
  • Open Source under GPLv2
  • More infos under http://postgis.net/

(you all know what it is, right?)

Why is it great?

  • Build on top of one of the best DBMS
  • Faster and more robust than your GIS
  • So much geo power with just some SQL
  • Great acceptance in the spatial industry

@delawen on PostGIS day

PostGIS 2.0

2012/04/03

PostGIS 2.0

  • New binary serialization (OGC compliance)
  • Typemod for geo types >> geometry(Point,4326)
  • Raster support got integrated
  • 3D landed in PostGIS (new types and functions)
  • n-dimensional indexing
  • Indexed KNN queries
  • Many new topology functions

s3.cleverelephant.ca/foss4gna2012-postgis2.pdf

ST_Split

ST_Snap

ST_MakeValid

ST_FlipCoordinates

ST_OffsetCurve

ST_ConcavHull

PostGIS 2.1

2013/08/17

 ~ Raster ~

Raster Master

 

Many functions rewritten in C

Join functions (ST_Contains, ST_DWithin etc.)

Splitting up rasters with ST_Tile

Multiband-aware ST_Union

More terrain analysis functions (e.g. Roughness)

etc. etc. etc.

ST_DelaunayTriangles

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

SFCGAL

New backend based on wrapper lib to CGAL

 

ST_Extrude

ST_3DArea

ST_3DIntersection

ST_Tesselate

ST_Orientation

etc.

There'S More

  • Performance boost for several functions
  • ST_Distance for arcs
  • TopoGeometry >> TopoJSON
  • New R-Tree Node Splitting

9.1+

PostGIS 2.2

2015/10/07

 ~ 3D & 4D ~

ST_Subdivide

(ST_Segmentize for lines)

Temporal functions

 

ST_ClosestPointOfApproach

ST_DistanceCPA (or |=| operator)

ST_CPAWithin

ST_IsValidTrajectory

flightradar24.com

More SFCGAL

 

ST_Volume (e.g. for estimating energy demand for 3D buildings)

ST_MakeSolid (for PolyhedralSurface)

ST_IsSolid

ST_3DUnion & ST_3DDifference

ST_ApproximateMedialAxis (e.g. to create rooftops)

 

SFCGAL can be added as extension:

CREATE EXTENSION postgis_sfgcal;

3D city model of Vienna

ST_CreateOverview & ST_Retile

(Raster pyramids)

Original

Factor 2

Factor 4

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/

There's More

  • KNN for geography
  • Exact KNN via recheck (PG 9.5)
  • A new way to simplify: ST_SimplifyVW
  • Mark importance of points with ST_SetEffectiveArea
  • ST_RemoveRepeatedPoints with tolerance
  • ST_SwapOrdinates between all dimensions
  • PostGIS Topology API in C

9.1+

PostGIS 2.3

2016/09/26

 ~ Analytics ~

ST_VoronoiPolygons ST_VoronoiLines

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

ST_GeneratePoints

ST_Points

(returns MultiPoint)

Performance: http://www.danbaston.com/posts/2016/12/17/generating-test-data-in-postgis.html

Polygon splitting: http://blog.cleverelephant.ca/2018/06/polygon-splitting.html

ST_ClusterKMeans

(simple, fast)

ST_ClusterDBSCAN

(more realistic, but harder)

Minimum Clearance

(a.k.a. how close you are to invalidity)

ST_MinimumClearance

ST_MinimumClearanceLine

There's More

  • Parallel query through PG 9.6
  • BRIN Index support
  • ST_Expand seperatly for each dimension
  • ST_MakeLine for MultiPoints
  • ST_MinimumBoundingRadius (and circle)
  • box3d::geometry = 3D geometry

9.2+

PostGIS 2.4

2017/09/30

 ~ Vector Tiles ~

Vector Tiles Export

https://www.mapbox.com/vector-tiles/

ST_AsMVTGeom (for geometry)

ST_AsMVT (MVTGeom + attributes)

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

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

ST_FrechetDistance

(a.k.a. how similar a two curves)

Source: http://www.mdpi.com/1424-8220/16/10/1768

Bang, Y. ; Kim, J. ; Yu, K. (2016): An improved map-matching technique based on the Fréchet distance approach for pedestrian navigation services. In: Sensors 16.10: 1768.

Languages (Help and Manual)

Source: http://www.bostongis.com/blog/index.php?/archives/267-PostGIS-db-help-and-manual-in-different-languages.html#extended

There's More

  • Geometry and raster aggregates are PARALLEL SAFE
  • Lossless binary compression with ST_AsGeobuf
  • Check and force orientation of polygons
  • GeomA = GeomB actually means it! (unlike ST_Equals)
  • Covers support for GEOGRAPHY

9.3+

PostGIS 2.5

2018/09/23

 ~ Miscellanuous ~

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?
  • 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!

PostGIS v2

By fxku

PostGIS v2

Feature introduced since PostGIS 2.0+

  • 6,022