10 Things I've Learnt About PostGIS
summary
Spatial indices cut query time dramatically so adding indexes and using them properly is the first win. ST_Subdivide turns one huge polygon vs 1.1 million lidar grid squares from ~2 minutes to ~0.33 seconds. Centroiding 300,000 building outlines runs in ~3 seconds in PostGIS versus ~23 seconds elsewhere. Store reusable functions for suburb assignment using ST_DWithin ST_Intersects and area rules, enforce geometry types and no-overlap checks, and use normalize_geometry and the pgAdmin4 geometry viewer for debugging.
AI-written summary · about
chapters (10)
- 0:00Introduction
- 0:45Spatial indices as assets
- 2:09Speed gains from spatial indexing
- 3:40Optimizing slow queries with ST_Subdivide
- 5:08Consistency in spatial analysis
- 6:34Reusable spatial functions
- 8:42Geometry type constraints
- 10:48Open-source PostGIS tools
- 12:15PgAdmin geometry viewer
- 13:19Enjoying spatial problem-solving
related talks
47mPostGIS and pgRouting: Extensions for spatial data in PostgreSQL
45mBreakout Session
53mThis Is PostGIS
21mThe Open Source Geospatial Community, PostGIS, & Postgres
26mPostgreSQL performance tips you have never seen before V2.0
42mPostgreSQL Tips & Tricks For App Devs
40mGIS capability of PostgreSql using POSTGIS & OSM DATA
23mH3 Indexing