{"id":"design-postgis-tables","name":"design-postgis-tables","summary":"位置ベースアプリケーション向けの幾何学タイプ、座標系、空間インデックス、パフォーマンスパターンを網羅した包括的なPostGIS空間テーブル設計参考書","body":"# PostGIS Spatial Table Design\n\n## Before You Start (5 Questions)\n\n1. What is the geographic scope (single city/region vs global)?\n2. What are your primary query patterns (within-radius, bbox, intersects, nearest-neighbor)?\n3. What units do you need for distance/area (meters vs CRS units), and how accurate must they be?\n4. What is the expected scale (rows, write rate), and is the data mostly append-only?\n5. Do you need 3D (Z) or measures (M), or is 2D enough?\n\n**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.\n\n## Core Rules\n\n- **Always use PostGIS geometry/geography types** instead of PostgreSQL's built-in geometric types (`POINT`, `LINE`, `POLYGON`, `CIRCLE`). PostGIS types provide true spatial capabilities.\n- **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.\n- **Always specify SRID** (Spatial Reference Identifier) when creating geometry columns. Use `4326` (WGS84) for GPS/global data, appropriate local projections for regional data.\n- **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.\n- **Use constraint-based type enforcement** with `GEOMETRY(type, SRID)` syntax to ensure data integrity.\n\n## Geometry vs Geography\n\n### When to Use GEOMETRY\n\n- **Local/regional data** within a single coordinate system\n- **Projected coordinates** (meters, feet) for accurate area/distance calculations\n- **Complex spatial operations** (buffering, unions, intersections)\n- **Performance-critical queries** (Cartesian math is faster)\n- **Data already in a projected CRS** (UTM, State Plane, etc.)\n\n```sql\n-- Regional data with projected coordinates (UTM Zone 10N for California)\nCREATE TABLE local_parcels (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    parcel_number TEXT NOT NULL,\n    boundary GEOMETRY(POLYGON, 26910),  -- UTM Zone 10N (meters)\n    area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (ST_Area(boundary)) STORED\n);\n```\n\n### When to Use GEOGRAPHY\n\n- **Global data** spanning multiple continents/hemispheres\n- **GPS coordinates** (latitude/longitude in decimal degrees)\n- **Accurate distance calculations** on Earth's surface (great circle)\n- **Simple spatial operations** (distance, containment)\n- **Data from GPS devices, geocoding services, or web maps**\n\n```sql\n-- Global data with geodetic calculations\nCREATE TABLE global_offices (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    name TEXT NOT NULL,\n    city TEXT NOT NULL,\n    location GEOGRAPHY(POINT, 4326)  -- WGS84 (lat/lon)\n);\n\n-- Distance in meters (accurate spherical calculation)\nSELECT\n    a.name AS office_a,\n    b.name AS office_b,\n    ST_Distance(a.location, b.location) / 1000 AS distance_km\nFROM global_offices a\nCROSS JOIN global_offices b\nWHERE a.id < b.id;\n```\n\n### Comparison Table\n\n| Aspect            | GEOMETRY                              | GEOGRAPHY                 |\n| ----------------- | ------------------------------------- | ------------------------- |\n| Coordinate system | Any SRID (projected or geodetic)      | WGS84 (SRID 4326) only    |\n| Distance units    | CRS units (degrees, meters, feet)     | Meters (always)           |\n| Distance accuracy | Depends on projection                 | True spheroidal distance  |\n| Area accuracy     | Accurate in projected CRS             | Accurate on sphere        |\n| Function support  | Full (300+ functions)                 | Limited (~40 functions)   |\n| Performance       | Faster (Cartesian math)               | Slower (spherical math)   |\n| Index type        | GiST, BRIN, SP-GiST                   | GiST only                 |\n| Best for          | Regional/local data, complex analysis | Global data, GPS tracking |\n\n## Geometry Types\n\n### Point Types\n\n```sql\n-- Single location (stores, sensors, events)\nlocation GEOMETRY(POINT, 4326)\n\n-- Multiple discrete locations (multi-branch business)\nlocations GEOMETRY(MULTIPOINT, 4326)\n\n-- 3D point with elevation\nlocation_3d GEOMETRY(POINTZ, 4326)\n\n-- Point with measure value (linear referencing)\nlocation_m GEOMETRY(POINTM, 4326)\n```\n\n**Use POINT for:** Store locations, sensor positions, event coordinates, addresses, POIs\n**Use MULTIPOINT for:** Multiple related locations stored as single feature\n\n### Line Types\n\n```sql\n-- Single path (road segment, river, route)\npath GEOMETRY(LINESTRING, 4326)\n\n-- Multiple paths (road network, transit lines)\nnetwork GEOMETRY(MULTILINESTRING, 4326)\n\n-- 3D line with elevation profile\ntrail_3d GEOMETRY(LINESTRINGZ, 4326)\n```\n\n**Use LINESTRING for:** Roads, rivers, pipelines, GPS tracks, routes\n**Use MULTILINESTRING for:** Disconnected road segments, river systems\n\n### Polygon Types\n\n```sql\n-- Single area (parcel, building footprint, zone)\nboundary GEOMETRY(POLYGON, 4326)\n\n-- Multiple areas (archipelago, fragmented habitat)\nterritories GEOMETRY(MULTIPOLYGON, 4326)\n\n-- 3D polygon (building with height)\nfootprint_3d GEOMETRY(POLYGONZ, 4326)\n```\n\n**Use POLYGON for:** Property boundaries, administrative areas, service zones\n**Use MULTIPOLYGON for:** Countries with islands, fragmented regions\n\n### Generic Types\n\n```sql\n-- Any geometry type (flexible schema)\ngeom GEOMETRY(GEOMETRY, 4326)\n\n-- Collection of mixed types\nfeatures GEOMETRY(GEOMETRYCOLLECTION, 4326)\n```\n\n**Use GEOMETRY for:** Flexible schemas accepting multiple types\n**Avoid GEOMETRYCOLLECTION:** Prefer homogeneous types for better indexing\n\n## Coordinate Systems (SRID)\n\n### Common SRIDs\n\n| SRID        | Name              | Use Case                     | Units   |\n| ----------- | ----------------- | ---------------------------- | ------- |\n| 4326        | WGS84             | GPS, global data, web maps   | Degrees |\n| 3857        | Web Mercator      | Web map tiles (display only) | Meters  |\n| 26910-26919 | UTM Zones (US)    | Regional analysis            | Meters  |\n| 32601-32660 | UTM Zones (North) | Regional analysis            | Meters  |\n| 32701-32760 | UTM Zones (South) | Regional analysis            | Meters  |\n\n### SRID Best Practices\n\n- **Store in WGS84 (4326)** for interoperability and GPS data\n- **Transform to projected CRS** for accurate measurements\n- **Never mix SRIDs** in spatial operations without explicit transformation\n- **Use appropriate local CRS** for area/distance calculations requiring high precision\n\n```sql\n-- Store in WGS84, calculate in UTM\nCREATE TABLE survey_points (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    location GEOMETRY(POINT, 4326),  -- Storage: WGS84\n    CONSTRAINT valid_location CHECK (ST_IsValid(location))\n);\n\n-- Calculate distance in meters using UTM projection\nSELECT\n    a.id AS point_a,\n    b.id AS point_b,\n    ST_Distance(\n        ST_Transform(a.location, 26910),  -- Transform to UTM\n        ST_Transform(b.location, 26910)\n    ) AS distance_meters\nFROM survey_points a\nCROSS JOIN survey_points b\nWHERE a.id < b.id;\n```\n\n## Spatial Indexing\n\n### GiST Index (Default)\n\nMost versatile spatial index. Use for all geometry/geography columns.\n\n```sql\n-- Geometry (most common)\nCREATE INDEX idx_your_table_geom_gist ON your_table_name USING GIST (geom);\n\n-- Geography (GiST is the supported option)\nCREATE INDEX idx_your_table_geog_gist ON your_table_name USING GIST (geog);\n\n-- Analyze after index creation\nVACUUM ANALYZE your_table_name;\n```\n\n**Supports:** All spatial operators (`&&`, `@>`, `<@`, `~=`, `<->`)\n**Best for:** General-purpose spatial queries, mixed query patterns\n\n### BRIN Index\n\nBlock Range Index for very large, naturally ordered datasets.\n\n```sql\n-- BRIN for very large, append-only GEOMETRY tables (geography uses GiST)\nCREATE INDEX idx_your_table_geom_brin\n    ON your_table_name\n    USING BRIN (geom)\n    WITH (pages_per_range = 128);\n```\n\n**Supports:** Bounding box operators (`&&`, `@>`, `<@`)\n**Best for:** Append-only tables, time-series spatial data, very large datasets (>100M rows)\n**Trade-off:** Much smaller than GiST, but less precise filtering\n\n### SP-GiST Index\n\nSpace-partitioned GiST for point data with specific distributions.\n\n```sql\n-- SP-GiST for GEOMETRY(POINT, ...) only\nCREATE INDEX idx_sensors_location_spgist\n    ON sensors\n    USING SPGIST (location);\n```\n\n**Best for:** Point-only data, quadtree-friendly distributions\n**Not for:** Complex geometries, mixed types\n\n### Index Selection Guide\n\n| Scenario                         | Index Type    | Reasoning                                  |\n| -------------------------------- | ------------- | ------------------------------------------ |\n| General spatial queries          | GiST          | Most versatile, supports all operators     |\n| Very large, append-only          | BRIN          | Tiny footprint, good for time-ordered data |\n| Point-only, uniform distribution | SP-GiST       | Efficient for point lookups                |\n| Geography columns                | GiST          | Only supported option                      |\n| Composite spatial + attribute    | GiST + B-tree | Separate indexes or expression index       |\n\n## Table Design Examples\n\n### Points of Interest (POI)\n\n```sql\nCREATE TABLE pois (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    name TEXT NOT NULL,\n    category TEXT NOT NULL,\n    location GEOGRAPHY(POINT, 4326) NOT NULL,\n    address TEXT,\n    metadata JSONB DEFAULT '{}',\n    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n    CONSTRAINT valid_category CHECK (category IN (\n        'restaurant', 'hotel', 'gas_station', 'hospital', 'school'\n    ))\n);\n\n-- Spatial index\nCREATE INDEX idx_pois_location ON pois USING GIST (location);\n\n-- Category + location for filtered spatial queries\nCREATE INDEX idx_pois_category ON pois (category);\n\n-- Find restaurants within 1km\nSELECT name, address,\n       ST_Distance(\n         location,\n         ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY\n       ) AS distance_m\nFROM pois\nWHERE category = 'restaurant'\n  AND ST_DWithin(\n    location,\n    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY,\n    1000\n  )\nORDER BY distance_m;\n```\n\n### Property Parcels\n\n```sql\nCREATE TABLE parcels (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    parcel_id TEXT NOT NULL UNIQUE,\n    owner_name TEXT,\n    boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL,\n    centroid GEOMETRY(POINT, 4326) GENERATED ALWAYS AS (ST_Centroid(boundary)) STORED,\n    area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (\n        ST_Area(boundary::GEOGRAPHY)\n    ) STORED,\n    perimeter_m DOUBLE PRECISION GENERATED ALWAYS AS (\n        ST_Perimeter(boundary::GEOGRAPHY)\n    ) STORED,\n    CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary)),\n    CONSTRAINT closed_boundary CHECK (ST_IsClosed(ST_ExteriorRing(ST_GeometryN(boundary, 1))))\n);\n\nCREATE INDEX idx_parcels_boundary ON parcels USING GIST (boundary);\nCREATE INDEX idx_parcels_centroid ON parcels USING GIST (centroid);\n\n-- Find parcels intersecting a search area\nSELECT parcel_id, owner_name, area_sqm\nFROM parcels\nWHERE ST_Intersects(boundary, ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326));\n```\n\n### GPS Tracking\n\n```sql\nCREATE TABLE gps_tracks (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    device_id TEXT NOT NULL,\n    recorded_at TIMESTAMPTZ NOT NULL,\n    location GEOGRAPHY(POINT, 4326) NOT NULL,\n    speed_kmh DOUBLE PRECISION,\n    heading DOUBLE PRECISION,\n    accuracy_m DOUBLE PRECISION\n);\n\n-- Composite index for device + time queries\nCREATE INDEX idx_gps_device_time ON gps_tracks (device_id, recorded_at DESC);\n\n-- Spatial index for location queries\nCREATE INDEX idx_gps_location ON gps_tracks USING GIST (location);\n\n-- Note: GEOGRAPHY supports GiST; BRIN is for GEOMETRY (when appropriate).\n\n-- Create linestring from track points\nSELECT\n    device_id,\n    ST_MakeLine(location::GEOMETRY ORDER BY recorded_at) AS track_line,\n    MIN(recorded_at) AS start_time,\n    MAX(recorded_at) AS end_time\nFROM gps_tracks\nWHERE device_id = 'device_001'\n  AND recorded_at >= '2024-01-01'\nGROUP BY device_id;\n```\n\n### Service Areas / Coverage Zones\n\n```sql\nCREATE TABLE service_zones (\n    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    zone_name TEXT NOT NULL,\n    zone_type TEXT NOT NULL,\n    boundary GEOMETRY(POLYGON, 4326) NOT NULL,\n    population INTEGER,\n    active BOOLEAN NOT NULL DEFAULT true,\n    CONSTRAINT valid_zone_type CHECK (zone_type IN ('delivery', 'service', 'coverage')),\n    CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary))\n);\n\nCREATE INDEX idx_zones_boundary ON service_zones USING GIST (boundary);\nCREATE INDEX idx_zones_active ON service_zones (active) WHERE active = true;\n\n-- Check if location is within any active service zone\nSELECT zone_name, zone_type\nFROM service_zones\nWHERE active = true\n  AND ST_Contains(boundary, ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326));\n```\n\n## Performance Patterns\n\n### Use ST_DWithin Instead of ST_Distance\n\n```sql\n-- SLOW: calculates distance for all rows\nSELECT * FROM pois\nWHERE ST_Distance(location, ref_point) < 1000;\n\n-- FAST: uses spatial index\nSELECT * FROM pois\nWHERE ST_DWithin(location, ref_point, 1000);\n```\n\n### Use && for Bounding Box Pre-filtering\n\n```sql\n-- Bounding box operator leverages spatial index\nSELECT * FROM parcels\nWHERE boundary && ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326)\n  AND ST_Intersects(boundary, search_polygon);\n```\n\n### Avoid Functions on Indexed Columns\n\n```sql\n-- SLOW: function prevents index usage\nSELECT * FROM parcels WHERE ST_Area(boundary) > 10000;\n\n-- FAST: use generated column with regular index\nALTER TABLE parcels ADD COLUMN area_sqm DOUBLE PRECISION\n    GENERATED ALWAYS AS (ST_Area(boundary::GEOGRAPHY)) STORED;\nCREATE INDEX idx_parcels_area ON parcels (area_sqm);\nSELECT * FROM parcels WHERE area_sqm > 10000;\n```\n\n### Simplify Geometries for Display\n\n```sql\n-- Reduce complexity for web display (tolerance in CRS units)\nSELECT\n    id,\n    name,\n    ST_AsGeoJSON(ST_Simplify(boundary, 0.0001)) AS geojson\nFROM parcels;\n```\n\n### Use Appropriate Precision\n\n```sql\n-- Reduce coordinate precision for storage efficiency\nUPDATE locations SET geom = ST_ReducePrecision(geom, 0.000001);\n\n-- GeoJSON with limited decimal places\nSELECT ST_AsGeoJSON(location, 6) AS geojson FROM pois;\n```\n\n## Data Validation\n\n### Geometry Validity Checks\n\n```sql\n-- Add validity constraint\nALTER TABLE parcels ADD CONSTRAINT valid_geom CHECK (ST_IsValid(boundary));\n\n-- Find and fix invalid geometries\nSELECT id, ST_IsValidReason(boundary) AS reason\nFROM parcels\nWHERE NOT ST_IsValid(boundary);\n\n-- Attempt to fix invalid geometries\nUPDATE parcels\nSET boundary = ST_MakeValid(boundary)\nWHERE NOT ST_IsValid(boundary);\n```\n\n### SRID Consistency\n\n```sql\n-- Verify SRID consistency\nSELECT DISTINCT ST_SRID(geom) FROM spatial_table;\n\n-- Enforce SRID with constraint\nALTER TABLE locations ADD CONSTRAINT enforce_srid\n    CHECK (ST_SRID(location) = 4326);\n```\n\n### Coordinate Range Validation\n\n```sql\n-- Ensure coordinates are within valid WGS84 bounds\nALTER TABLE global_locations ADD CONSTRAINT valid_coords CHECK (\n    ST_X(location::GEOMETRY) BETWEEN -180 AND 180 AND\n    ST_Y(location::GEOMETRY) BETWEEN -90 AND 90\n);\n```\n\n## Do Not Use\n\n- **PostgreSQL built-in types** (`POINT`, `LINE`, `POLYGON`, `CIRCLE`) - use PostGIS types instead\n- **SRID 0** (undefined) - always specify the correct SRID\n- **ST_Distance for filtering** - use ST_DWithin for index-supported distance queries\n- **Mixed SRIDs** in operations - always transform to common SRID first\n- **GEOGRAPHY for complex analysis** - use GEOMETRY with appropriate projection\n- **Over-precise coordinates** - GPS accuracy is ~3-5m, 6 decimal places (0.1m) is sufficient\n\n## Common Pitfalls\n\n1. **Longitude/Latitude order**: PostGIS uses `(longitude, latitude)` = `(X, Y)`, not `(lat, lon)`\n2. **GEOGRAPHY distance units**: Always in meters, regardless of display\n3. **Index not used**: Run `EXPLAIN ANALYZE` to verify spatial index usage\n4. **Transform performance**: Cache transformed geometries for repeated queries\n5. **Large geometries**: Consider ST_Subdivide for very complex polygons\n6. **SQL injection / unsafe dynamic SQL**: Don't concatenate untrusted input into SQL. Parameterize values; for dynamic identifiers use safe quoting (`quote_ident`, `format('%I', ...)`) or strict allowlists.","author":"@timescale","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/design-postgis-tables","license":"Apache-2.0","category":"writing","lang":"en","tokens":4094,"stars":0,"calls30d":1,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":[]}}