design-postgis-tables

-- 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;

-- 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)

-- 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)

-- 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)

-- Any geometry type (flexible schema)
geom GEOMETRY(GEOMETRY, 4326)
 
-- Collection of mixed types
features GEOMETRY(GEOMETRYCOLLECTION, 4326)

-- 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;

-- 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;

-- 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);

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

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;

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));

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 DOUBLE PRECISION,
    accuracy_m DOUBLE PRECISION
);
 
-- Composite index for device + time queries
CREATE INDEX idx_gps_device_time ON gps_tracks (device_id, recorded_at DESC);
 
-- Spatial index for location queries
CREATE INDEX idx_gps_location ON gps_tracks USING GIST (location);
 
-- Note: GEOGRAPHY supports GiST; BRIN is for GEOMETRY (when appropriate).
 
-- Create linestring from track points
SELECT
    device_id,
    ST_MakeLine(location::GEOMETRY ORDER BY recorded_at) AS track_line,
    MIN(recorded_at) AS start_time,
    MAX(recorded_at) AS end_time
FROM gps_tracks
WHERE device_id = 'device_001'
  AND recorded_at >= '2024-01-01'
GROUP BY device_id;

CREATE TABLE service_zones (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    zone_name TEXT NOT NULL,
    zone_type TEXT NOT NULL,
    boundary GEOMETRY(POLYGON, 4326) NOT NULL,
    population INTEGER,
    active BOOLEAN NOT NULL DEFAULT true,
    CONSTRAINT valid_zone_type CHECK (zone_type IN ('delivery', 'service', 'coverage')),
    CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary))
);
 
CREATE INDEX idx_zones_boundary ON service_zones USING GIST (boundary);
CREATE INDEX idx_zones_active ON service_zones (active) WHERE active = true;
 
-- Check if location is within any active service zone
SELECT zone_name, zone_type
FROM service_zones
WHERE active = true
  AND ST_Contains(boundary, ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326));

-- SLOW: calculates distance for all rows
SELECT * FROM pois
WHERE ST_Distance(location, ref_point) < 1000;
 
-- FAST: uses spatial index
SELECT * FROM pois
WHERE ST_DWithin(location, ref_point, 1000);

-- Bounding box operator leverages spatial index
SELECT * FROM parcels
WHERE boundary && ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326)
  AND ST_Intersects(boundary, search_polygon);

-- SLOW: function prevents index usage
SELECT * FROM parcels WHERE ST_Area(boundary) > 10000;
 
-- FAST: use generated column with regular index
ALTER TABLE parcels ADD COLUMN area_sqm DOUBLE PRECISION
    GENERATED ALWAYS AS (ST_Area(boundary::GEOGRAPHY)) STORED;
CREATE INDEX idx_parcels_area ON parcels (area_sqm);
SELECT * FROM parcels WHERE area_sqm > 10000;

-- Reduce complexity for web display (tolerance in CRS units)
SELECT
    id,
    name,
    ST_AsGeoJSON(ST_Simplify(boundary, 0.0001)) AS geojson
FROM parcels;

-- Reduce coordinate precision for storage efficiency
UPDATE locations SET geom = ST_ReducePrecision(geom, 0.000001);
 
-- GeoJSON with limited decimal places
SELECT ST_AsGeoJSON(location, 6) AS geojson FROM pois;

-- Add validity constraint
ALTER TABLE parcels ADD CONSTRAINT valid_geom CHECK (ST_IsValid(boundary));
 
-- Find and fix invalid geometries
SELECT id, ST_IsValidReason(boundary) AS reason
FROM parcels
WHERE NOT ST_IsValid(boundary);
 
-- Attempt to fix invalid geometries
UPDATE parcels
SET boundary = ST_MakeValid(boundary)
WHERE NOT ST_IsValid(boundary);

-- Verify SRID consistency
SELECT DISTINCT ST_SRID(geom) FROM spatial_table;
 
-- Enforce SRID with constraint
ALTER TABLE locations ADD CONSTRAINT enforce_srid
    CHECK (ST_SRID(location) = 4326);

-- Ensure coordinates are within valid WGS84 bounds
ALTER TABLE global_locations ADD CONSTRAINT valid_coords CHECK (
    ST_X(location::GEOMETRY) BETWEEN -180 AND 180 AND
    ST_Y(location::GEOMETRY) BETWEEN -90 AND 90
);