design-postgis-tables

# PostGIS Spatial Table Design ## Before You Start (5 Questions) 1. What is the geographic scope (single city/region vs global)? 2. What are your primary query patterns (within-radius, bbox, intersects, nearest-neighbor)? 3. What units do you need for distance/area (meters vs CRS units), and how accurate must they be? 4. What is the expected scale (rows, write rate), and is the data mostly append-only? 5. Do you need 3D (Z) or measures (M), or is 2D enough? **SQL injection note:** When turning these patterns into application code, use parameterized queries for user-provided values (WKT/WKB, coordinates, IDs, radii). Avoid string-concatenating untrusted input into SQL; for dynamic identifiers, use safe identifier quoting/whitelisting. ## Core Rules - **Always use PostGIS geometry/geography types** instead of PostgreSQL's built-in geometric types (`POINT`, `LINE`, `POLYGON`, `CIRCLE`). PostGIS types provide true spatial capabilities. - **Choose between GEOMETRY and GEOGRAPHY** based on your use case: GEOMETRY for projected/local data with Cartesian math; GEOGRAPHY for global data requiring accurate spherical calculations. - **Always specify SRID** (Spatial Reference Identifier) when creating geometry columns. Use `4326` (WGS84) for GPS/global data, appropriate local projections for regional data. - **Create spatial indexes** on all geometry/geography columns using GiST (default). Consider BRIN only for very large **GEOMETRY** tables where rows are naturally ordered on disk...