postgis

What this skill does

# PostGIS 3.6.1 Spatial Database

## Overview

PostGIS 3.6.1 (with GEOS 3.14) brings significant improvements: ST_CoverageClean for topology repair, enhanced SFCGAL 3D operations, bigint topology support for massive datasets, and improved PostgreSQL 18 integration. This skill ensures you leverage these capabilities correctly.

**Core principle:** Spatial is special. Generic database patterns often fail with geographic data.

**Announce at start:** "I'm applying postgis to ensure PostGIS 3.6.1 spatial best practices."

## When This Skill Applies

This skill is MANDATORY when ANY of these patterns are touched:

| Pattern | Examples |
|---------|----------|
| `**/*geo*` | models/geography.ts, geo_utils.py |
| `**/*spatial*` | lib/spatial.ts |
| `**/*location*` | services/locationService.ts |
| `**/*coordinate*` | types/coordinates.ts |
| `**/*polygon*` | db/polygons.sql |
| `**/*geometry*` | migrations/add_geometry.sql |
| `**/*postgis*` | setup/postgis.sql |
| `**/*gis*` | utils/gis.ts |

Or when files contain:

```sql
-- These patterns trigger this skill
ST_*
geography
geometry
SRID
```

## PostGIS 3.6.1 Features

### 1. ST_CoverageClean (New in 3.6.1)

Coverage cleaning repairs topological errors in polygon collections. Requires GEOS 3.14:

```sql
-- Clean a set of polygons that should form a seamless coverage
-- Fixes: overlaps, gaps, edge inconsistencies
SELECT ST_CoverageClean(
  ARRAY[polygon1, polygon2, polygon3]::geometry[]
) AS cleaned_polygons;

-- Use case: Administrative boundaries, parcels, zones
-- Before: Manual repair with ST_MakeValid, ST_SnapToGrid
-- After: Single function handles entire coverage

-- Example: Clean municipal boundaries
WITH boundaries AS (
  SELECT geom FROM municipalities
)
SELECT ST_CoverageClean(array_agg(geom))
FROM boundaries;
```

**When to use:**
- Importing GIS data with topological errors
- Merging datasets from different sources
- Ensuring seamless coverage (no gaps/overlaps)
- Cadastral/parcel data management

### 2. SFCGAL 3D Functions

PostGIS 3.6.1 includes enhanced SFCGAL support for 3D operations:

```sql
-- Enable SFCGAL (if not already enabled)
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal;

-- 3D intersection (true 3D, not projection)
SELECT ST_3DIntersection(
  ST_GeomFromText('POLYHEDRALSURFACE Z (...)'),
  ST_GeomFromText('POLYHEDRALSURFACE Z (...)')
);

-- 3D union
SELECT ST_3DUnion(geom1, geom2);

-- 3D area (actual surface area in 3D)
SELECT ST_3DArea(polyhedral_surface);

-- Minkowski sum (for buffer-like operations in 3D)
SELECT ST_MinkowskiSum(geometry1, geometry2);

-- Straight skeleton (for building roofs, etc.)
SELECT ST_StraightSkeleton(polygon);

-- Extrude 2D to 3D
SELECT ST_Extrude(polygon, 0, 0, height);
```

**Use cases:**
- Building/structure modeling
- Underground infrastructure
- Airspace management
- 3D terrain analysis

### 3. Bigint Topology Support

PostGIS 3.6.1 supports bigint topology IDs for massive datasets:

```sql
-- Create topology with bigint IDs (new in 3.6.1)
SELECT CreateTopology('massive_parcels', 4326, 0.0000001, true);
-- Last parameter: use_bigint = true

-- Supports > 2 billion features per topology
-- Previous limit: ~2 billion (int4 max)

-- Add layer
SELECT AddTopoGeometryColumn('massive_parcels', 'public', 'parcels', 'topogeom', 'POLYGON');

-- TopoGeometry operations work the same
SELECT ST_CreateTopoGeo('massive_parcels', geom);
```

**When to use:**
- National/continental scale datasets
- High-resolution parcel data
- OpenStreetMap imports
- Any topology > 2 billion edges

### 4. PostgreSQL 18 Interrupt Handling

PostGIS 3.6.1 properly handles PostgreSQL 18's improved query cancellation:

```sql
-- Long-running spatial operations can now be cancelled cleanly
-- No more orphaned locks or corrupted state

-- Example: Cancellable heavy operation
SELECT ST_Union(geom)
FROM very_large_table
GROUP BY region;
-- ^C now works properly

-- COPY operations with PostGIS also respect cancellation
COPY (SELECT id, ST_AsGeoJSON(geom) FROM features) TO '/tmp/export.json';
```

## Data Types

### Geometry vs Geography

```sql
-- GEOMETRY: Planar coordinates, any SRID
-- Faster computations, less accurate over large distances
CREATE TABLE places_geometry (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  location geometry(Point, 4326)  -- WGS84
);

-- GEOGRAPHY: Spherical coordinates, always WGS84
-- Accurate distances/areas, slower computations
CREATE TABLE places_geography (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  location geography(Point, 4326)  -- Always WGS84
);

-- When to use GEOMETRY:
-- - Local/city-scale applications
-- - Need complex operations (union, intersection)
-- - Performance critical
-- - Non-earth data (game maps, floor plans)

-- When to use GEOGRAPHY:
-- - Global applications
-- - Distance/area accuracy matters
-- - Simple operations (distance, contains)
-- - User-facing distance calculations
```

### Choosing SRID

```sql
-- Common SRIDs:
-- 4326: WGS84 (GPS coordinates, web maps)
-- 3857: Web Mercator (tile-based web maps, display only)
-- Local projections for accurate measurements

-- ALWAYS store in 4326 (WGS84) as source of truth
-- Transform for calculations when needed

CREATE TABLE locations (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  name text NOT NULL,
  location geography(Point, 4326),  -- Storage
  location_local geometry(Point)     -- NULL, computed as needed
);

-- Transform for local calculations
SELECT ST_Transform(
  location::geometry,
  32610  -- UTM Zone 10N (California)
) FROM locations WHERE name = 'San Francisco';
```

## Index Strategy

### Spatial Indexes

```sql
-- GiST index: Default for most spatial queries
CREATE INDEX idx_locations_geom ON locations USING gist(location);

-- BRIN index: For very large, naturally ordered datasets
-- (e.g., GPS tracks ordered by time)
CREATE INDEX idx_tracks_geom ON gps_tracks USING brin(location);

-- SP-GiST: For non-overlapping data (points, IP ranges)
CREATE INDEX idx_points_spgist ON points USING spgist(location);
```

### Index Best Practices

```sql
-- Always include spatial index
CREATE TABLE features (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  geom geometry(Polygon, 4326),
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_features_geom ON features USING gist(geom);

-- Partial spatial index for active records
CREATE INDEX idx_features_geom_active ON features USING gist(geom)
  WHERE deleted_at IS NULL;

-- Composite index for common query patterns
CREATE INDEX idx_features_type_geom ON features USING gist(geom)
  WHERE feature_type = 'building';
```

### Index Clustering

```sql
-- Cluster table by spatial index for range query performance
CLUSTER features USING idx_features_geom;

-- For large tables, recluster periodically
-- Schedule during maintenance window
```

## Query Patterns

### Distance Queries

```sql
-- Find points within distance (geography, in meters)
SELECT * FROM locations
WHERE ST_DWithin(
  location,
  ST_MakePoint(-122.4194, 37.7749)::geography,
  1000  -- 1km radius
);

-- Find points within distance (geometry, in SRID units)
SELECT * FROM locations
WHERE ST_DWithin(
  location,
  ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326),
  0.01  -- ~1km at this latitude (degrees)
);

-- K-nearest neighbors (KNN)
SELECT *, location <-> ST_MakePoint(-122.4194, 37.7749)::geography AS distance
FROM locations
ORDER BY location <-> ST_MakePoint(-122.4194, 37.7749)::geography
LIMIT 10;
-- Uses index for efficient KNN
```

### Containment Queries

```sql
-- Points within polygon
SELECT * FROM points
WHERE ST_Within(location, (
  SELECT boundary FROM regions WHERE name = 'California'
));

-- Polygon contains point
SELECT * FROM regions
WHERE ST_Contains(boundary, ST_MakePoint(-122.4194, 37.7749));

-- Intersects (overlaps in any way)
SELECT * FROM features
WHERE ST_Intersects(geom, query_polygon);
```

### Aggregation

```sql
-- Union all geometries
SELECT ST_Union(geom) FROM parcels WHERE owner = 'City';

-- Collect without merging (faster, preserves individua