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)

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 ~

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)

ST_MinimumClearance

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 ~

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

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

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.

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

• 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