Design postgis tables

--- name: design-postgis-tables description: A comprehensive reference for designing PostGIS spatial tables, 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 sufficient? SQL injection note: When implementing these patterns in application code, use parameterized queries for user-provided values (WKT/WKB, coordinates, IDs, radii). Avoid concatenating untrusted input into SQL strings; for dynamic identifiers, use safe identifier quoting or 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 using 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, and 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 the `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.) `sql -- 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** sql -- 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)