Actual trees, not b-trees: or how I found PostgreSQL through PostGIS
summary
Starts from a CSV and ends with a web map: uses PostGIS/PostgreSQL, GeoServer and OpenLayers to import tree records (neighbor exports ~4,000 trees/year with ~100 attributes) and prototype a national merge that estimates 2 billion trees. Handles messy inputs: inconsistent species names, free‑text comments containing addresses, buildings exported as trees, many projections and lat/long swapped values. Implements history tables, partitioning, attribute‑specific index tables, stores photos on the filesystem, and shows styled maps with clickable current records and optional historic views.
AI-written summary · about
chapters (18)
- 0:00Introduction to talk
- 1:10Starting the tree project
- 3:35Choosing PostgreSQL and PostGIS
- 5:02Importing CSV data challenges
- 7:18Cleaning and structuring tree data
- 9:10Handling geodata projections
- 10:56Mapping and visualizing trees
- 12:00Adding environmental layers
- 13:27Optimizing queries and storage
- 14:34Storing tree images externally
- 15:21Database size and growth
- 16:24Government involvement in tree monitoring
- 17:06Searching free text fields
- 17:47Project timeline and funding
- 18:56Data provider integration
- 19:39Optimizing attribute queries
- 20:23AI for tree detection from satellite images
- 20:44Q&A session
related talks
24mActual trees, not b-trees...
37mProcessing Open Street Map Data with Python and PostgreSQL
50m–Mom, can we have G**gle Maps? –We have G**gle Maps at home
21mThe Open Source Geospatial Community, PostGIS, & Postgres
27mCartography on the Web
50mUnearthing the Past with PostgreSQL...
21mA Walking Tour of PostgreSQL
02h 44mBuilding Geographical APIs with Open Data, PostgreSQL & PostGIS