---

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

| Aspect | GEOMETRY | GEOGRAPHY | | ----------------- | ------------------------------------- | ------------------------- | | Coordinate system | Any SRID (projected or geodetic) | WGS84 (SRID 4326) only | | Distance units | CRS units (degrees, meters, feet) | Meters (always) | | Distance accuracy | Depends on projection | True spheroidal distance | | Area accuracy | Accurate in projected CRS | Accurate on sphere | | Function support | Full (300+ functions) | Limited (~40 functions) | | Performance | Faster (Cartesian math) | Slower (spherical math) | | Index type | GiST, BRIN, SP-GiST | GiST only | | Best for | Regional/local data, complex analysis | Global data, GPS tracking |

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 feature

Line Types

-- Single path (road segment, river, route)
path GEOMETRY(LINESTRING, 4326)

-- Multiple paths (road network, transit lines)
network GEOMETRY(MULTILINESTRING, 4326)

-- 3D line with elevation profile
trail_3d GEOMETRY(LINESTRINGZ, 4326)

Use LINESTRING for: Roads, rivers, pipelines, GPS tracks, routes Use MULTILINESTRING for: Disconnected road segments, river systems

Polygon Types

-- Single area (parcel, building footprint, zone)
boundary GEOMETRY(POLYGON, 4326)

-- Multiple areas (archipelago, fragmented habitat)
territories GEOMETRY(MULTIPOLYGON, 4326)

-- 3D polygon (building with height)
footprint_3d GEOMETRY(POLYGONZ, 4326)

Use POLYGON for: Property boundaries, administrative areas, service zones Use MULTIPOLYGON for: Countries with islands, fragmented regions

Generic Types

-- Any geometry type (flexible schema)
geom GEOMETRY(GEOMETRY, 4326)

-- Collection of mixed types
features GEOMETRY(GEOMETRYCOLLECTION, 4326)

Use GEOMETRY for: Flexible schemas accepting multiple types Avoid GEOMETRYCOLLECTION: Prefer homogeneous types for better indexing

Coordinate Systems (SRID)

Common SRIDs

| SRID | Name | Use Case | Units | | ----------- | ----------------- | ---------------------------- | ------- | | 4326 | WGS84 | GPS, global data, web maps | Degrees | | 3857 | Web Mercator | Web map tiles (display only) | Meters | | 26910-26919 | UTM Zones (US) | Regional analysis | Meters | | 32601-32660 | UTM Zones (North) | Regional analysis | Meters | | 32701-32760 | UTM Zones (South) | Regional analysis | Meters |

SRID Best Practices

• Store in WGS84 (4326) for interoperability and GPS data
• Transform to projected CRS for accurate measurements
• Never mix SRIDs in spatial operations without explicit transformation
• Use appropriate local CRS for area/distance calculations requiring high precision

-- Store in WGS84, calculate in UTM
CREATE TABLE survey_points (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location GEOMETRY(POINT, 4326),  -- Storage: WGS84
    CONSTRAINT valid_location CHECK (ST_IsValid(location))
);

-- Calculate distance in meters using UTM projection
SELECT
    a.id AS point_a,
    b.id AS point_b,
    ST_Distance(
        ST_Transform(a.location, 26910),  -- Transform to UTM
        ST_Transform(b.location, 26910)
    ) AS distance_meters
FROM survey_points a
CROSS JOIN survey_points b
WHERE a.id < b.id;

Spatial Indexing

GiST Index (Default)

Most versatile spatial index. Use for all geometry/geography columns.

-- Geometry (most common)
CREATE INDEX idx_your_table_geom_gist ON your_table_name USING GIST (geom);

-- Geography (GiST is the supported option)
CREATE INDEX idx_your_table_geog_gist ON your_table_name USING GIST (geog);

-- Analyze after index creation
VACUUM ANALYZE your_table_name;

Supports: All spatial operators (&&, @>, <@, ~=, <->) Best for: General-purpose spatial queries, mixed query patterns

BRIN Index

Block Range Index for very large, naturally ordered datasets.

-- BRIN for very large, append-only GEOMETRY tables (geography uses GiST)
CREATE INDEX idx_your_table_geom_brin
    ON your_table_name
    USING BRIN (geom)
    WITH (pages_per_range = 128);

Supports: Bounding box operators (&&, @>, <@) Best for: Append-only tables, time-series spatial data, very large datasets (>100M rows) Trade-off: Much smaller than GiST, but less precise filtering

SP-GiST Index

Space-partitioned GiST for point data with specific distributions.

-- SP-GiST for GEOMETRY(POINT, ...) only
CREATE INDEX idx_sensors_location_spgist
    ON sensors
    USING SPGIST (location);

Best for: Point-only data, quadtree-friendly distributions Not for: Complex geometries, mixed types

Index Selection Guide

| Scenario | Index Type | Reasoning | | -------------------------------- | ------------- | ------------------------------------------ | | General spatial queries | GiST | Most versatile, supports all operators | | Very large, append-only | BRIN | Tiny footprint, good for time-ordered data | | Point-only, uniform distribution | SP-GiST | Efficient for point lookups | | Geography columns | GiST | Only supported option | | Composite spatial + attribute | GiST + B-tree | Separate indexes or expression index |

Table Design Examples

Points of Interest (POI)

CREATE TABLE pois (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    address TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT valid_category CHECK (category IN (
        'restaurant', 'hotel', 'gas_station', 'hospital', 'school'
    ))
);

-- Spatial index
CREATE INDEX idx_pois_location ON pois USING GIST (location);

-- Category + location for filtered spatial queries
CREATE INDEX idx_pois_category ON pois (category);

-- Find restaurants within 1km
SELECT name, address,
       ST_Distance(
         location,
         ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY
       ) AS distance_m
FROM pois
WHERE category = 'restaurant'
  AND ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY,
    1000
  )
ORDER BY distance_m;

Property Parcels

CREATE TABLE parcels (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parcel_id TEXT NOT NULL UNIQUE,
    owner_name TEXT,
    boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL,
    centroid GEOMETRY(POINT, 4326) GENERATED ALWAYS AS (ST_Centroid(boundary)) STORED,
    area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (
        ST_Area(boundary::GEOGRAPHY)
    ) STORED,
    perimeter_m DOUBLE PRECISION GENERATED ALWAYS AS (
        ST_Perimeter(boundary::GEOGRAPHY)
    ) STORED,
    CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary)),
    CONSTRAINT closed_boundary CHECK (ST_IsClosed(ST_ExteriorRing(ST_GeometryN(boundary, 1))))
);

CREATE INDEX idx_parcels_boundary ON parcels USING GIST (boundary);
CREATE INDEX idx_parcels_centroid ON parcels USING GIST (centroid);

-- Find parcels intersecting a search area
SELECT parcel_id, owner_name, area_sqm
FROM parcels
WHERE ST_Intersects(boundary, ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326));

GPS Tracking

CREATE TABLE gps_tracks (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device_id TEXT NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    speed_kmh DOUBLE PRECISION,
    heading DOUBL