design-postgis-tables

1# PostGIS Spatial Table Design
2 
3## Before You Start (5 Questions)
4 
51. What is the geographic scope (single city/region vs global)?
62. What are your primary query patterns (within-radius, bbox, intersects, nearest-neighbor)?
73. What units do you need for distance/area (meters vs CRS units), and how accurate must they be?
84. What is the expected scale (rows, write rate), and is the data mostly append-only?
95. Do you need 3D (Z) or measures (M), or is 2D enough?
10 
11**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.
12 
13## Core Rules
14 
15- **Always use PostGIS geometry/geography types** instead of PostgreSQL's built-in geometric types (`POINT`, `LINE`, `POLYGON`, `CIRCLE`). PostGIS types provide true spatial capabilities.
16- **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.
17- **Always specify SRID** (Spatial Reference Identifier) when creating geometry columns. Use `4326` (WGS84) for GPS/global data, appropriate local projections for regional data.
18…