Felix Kunde
slides.com/fxku/postgis
About Me
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
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/
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
One Way to fund
External Projects You should know
- Query on files (and more) with ogr_fdw
- OSM ETL: osm2pgsql, imposm
- Storing pointclouds with pgpointcloud
- 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!