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 and you can tolerate coarser filtering.
• Use constraint-based type enforcement with GEOMETRY(type, SRID) syntax to ensure data integrity.

Geometry vs Geography

When to Use GEOMETRY