PostGIS is amazing. The types of queries you can write continually blow my mind. One warning is that ultra fancy queries can be pretty slow if you're going beyo

PostGIS is amazing. The types of queries you can write continually blow my mind. One warning is that ultra fancy queries can be pretty slow if you're going beyond a million rows. Some tips: • Materialized views with indexing are an easy way to solve many speed issues where you'd like to use a fancy query quickly. • The GEOGRAPHY data type is great for data integrity, but often slower for queries. I've made sure our primary data is stored as GEOGRAPHY then added expression indices [1] casting to GEOMETRY. Spatial joins and filters can be done on the casted column where faster queries are needed. • Source data is often very high resolution. If you don't need it, simplifying high accuracy data (5m or whatever) to something much lower resolution (500m, 1km, or whatever) in a derived view or table using PostGIS' simplification functions can greatly improve spatial predicate performance. [1] https://www.postgresql.org/docs/14/indexes-expressional.html https://www.postgresql.org/docs/14/indexes-expressional.html