Features Since V2
Felix Kunde
slides.com/fxku/postgis2
About Me
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/
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
One Way to fund
External Projects You should know
- Flexible routing engine with pgRouting
- Storing pointclouds with pgpointcloud
- OSM ETL: osm2pgsql, imposm
- Query on files (and more) with ogr_fdw
- Vector Tiles: Tegola, t-rex, tilesplash
- 3D City Database with export to Cesium
- <enter_your_domain_here>
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!