Design Postgis TablesSAFE

MCP server and Claude plugin for Postgres skills and documentation. Helps AI coding tools generate better PostgreSQL code.

The instruction file, verbatim from the audited commit — this is the text the model reads, and the surface the audit's instruction layer examines. Quoted here so you can judge it without cloning anything.

---
name: design-postgis-tables
description: Comprehensive PostGIS spatial table design reference 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 enough?

**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.

## 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 with 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, 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 `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)   |
| 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 |

## Geometry Types

### Point Types

```sql
-- 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

### Line Types

```sql
-- 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

### Polygon Types

```sql
-- 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

### Generic Types

```sql
-- Any geometry type (flexible schema)
geom GEOMETRY(GEOMETRY, 4326)

-- Collection of mixed types
features GEOMETRY(GEOMETRYCOLLECTI

SAFEgrade B · trust 89/100 Nothing in the source contradicts what it says it does. Grade A is reserved for packages that have also passed the behavioural sandbox.

What the source does

Filesystem

none-observed

Network

none-observed

Shell

none-observed

Dependencies

pinned

Secrets in source

none-found

Findings (5)

skills/postgres/references/backfill-strategies.md

skills/postgres/references/complete-example.md

skills/postgres/references/design-postgis-tables.md

skills/postgres/references/design-postgres-tables.md

skills/postgres/references/find-hypertable-candidates.md

Gates applied: no_behavioural_pass.