From pg
Guides PostGIS spatial table design with geometry/geography types, SRIDs, GiST indexing, constraints, and performance patterns for location apps.
npx claudepluginhub timescale/pg-aiguide --plugin pgThis skill uses the workspace's default tool permissions.
1. What is the geographic scope (single city/region vs global)?
Designs PostgreSQL table schemas with best practices for normalization, data types, indexing, constraints, performance patterns, and advanced features.
Designs PostgreSQL tables and schemas using best practices for data types, primary/foreign keys, constraints, indexes, normalization, JSONB, and partitioning.
Designs and reviews PostgreSQL table schemas using best practices for data types, indexing, constraints, normalization, performance patterns, and PostgreSQL gotchas.
Share bugs, ideas, or general feedback.
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.
POINT, LINE, POLYGON, CIRCLE). PostGIS types provide true spatial capabilities.4326 (WGS84) for GPS/global data, appropriate local projections for regional data.GEOMETRY(type, SRID) syntax to ensure data integrity.-- 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
);
-- 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;
| 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 |
-- 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
-- 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
-- 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
-- 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
| 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 |
-- 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;
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
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
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
| 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 |
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
);
POINT, LINE, POLYGON, CIRCLE) - use PostGIS types instead(longitude, latitude) = (X, Y), not (lat, lon)EXPLAIN ANALYZE to verify spatial index usagequote_ident, format('%I', ...)) or strict allowlists.