Tips and Tricks for writing PostGIS Spatial Queries (bostongis.com)
The guys from bostongis.com gave a presentation at OSCON 2009 last week about writing PostGIS spatial queries. You can download the presentation here, but these are main points (please note that these tips & tricks are all meant for PostGIS 1.4):
- Aggregates of geometries is way faster than it used to be;
- tip: add indexes after bulk insert;
- tip: always run vacuum analyze after bulk insert;
- tip: keep data in form most suitable for your workload;
- tip: use the graphical explain in pgAdmin;
- tip: different scenarios for nearest neighbor queries;
- tip: left joint!!!!!
- tip: simplify geometry to gain performance;
- tip: use CTEs to organize queries;
- tip: use populate_geometry_columns in PostGIS 1.4;
- tip: use the knife trick to bisect geometries;
Some neat tricks… Never the less, I still need to check several new functions: CTE, and windowing. Both are new Postgresql features, are apparently nice optimizations for queries…
I’ll keep you updated!
Pingback: PostgreSQL Tips and Tricks | giswhat.be