name: design-postgis-tables

description: Comprehensive PostGIS spatial table design reference covering geometry types, coordinate systems, spatial indexing, and performance patterns for location-based applications

license: Apache-2.0

compatibility: Requires PostgreSQL 15+ with the PostGIS extension

metadata:

author: tigerdata

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

• Local/regional data within a single coordinate system
• Projected coordinates (meters, feet) for accurate area/distance calculations
• Complex spatial operations (buffering, unions, intersections)
• Performance-critical queries (Cartesian math is faster)
• Data already in a projected CRS (UTM, State Plane, etc.)

-- Regional data with projected coordinates (UTM Zone 10N for California)
CREATE TABLE local_parcels (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parcel_number TEXT NOT NULL,
    boundary GEOMETRY(POLYGON, 26910),  -- UTM Zone 10N (meters)
    area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (ST_Area(boundary)) STORED
);

When to Use GEOGRAPHY

• Global data spanning multiple continents/hemispheres
• GPS coordinates (latitude/longitude in decimal degrees)
• Accurate distance calculations on Earth's surface (great circle)
• Simple spatial operations (distance, containment)
• Data from GPS devices, geocoding services, or web maps

-- Global data with geodetic calculations
CREATE TABLE global_offices (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    location GEOGRAPHY(POINT, 4326)  -- WGS84 (lat/lon)
);

-- Distance in meters (accurate spherical calculation)
SELECT
    a.name AS office_a,
    b.name AS office_b,
    ST_Distance(a.location, b.location) / 1000 AS distance_km
FROM global_offices a
CROSS JOIN global_offices b
WHERE a.id < b.id;

Comparison Table

Geometry Types

Point Types

-- Single location (stores, sensors, events)
location GEOMETRY(POINT, 4326)

-- Multiple discrete locations (multi-branch business)
locations GEOMETRY(MULTIPOINT, 4326)

-- 3D point with elevation
location_3d GEOMETRY(POINTZ, 4326)

-- Point with measure value (linear referencing)
location_m GEOMETRY(POINTM, 4326)

Use POINT for: Store locations, sensor positions, event coordinates, addresses, POIs

Use MULTIPOINT for: Multiple related locations stored as single featur