# Design Postgis Tables

Comprehensive PostGIS spatial table design reference covering geometry types...

- Repository: https://github.com/timescale/pg-aiguide (1877 stars)
- Category: Design
- License: Apache-2.0
- Last updated: 2026-10-28
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add timescale/pg-aiguide --skill design-postgis-tables -g -a claude-code -y`
Codex: `npx skills add timescale/pg-aiguide --skill design-postgis-tables -g -a codex -y`
Cursor: `npx skills add timescale/pg-aiguide --skill design-postgis-tables -g -a cursor -y`
Gemini CLI: `npx skills add timescale/pg-aiguide --skill design-postgis-tables -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add timescale/pg-aiguide`

## Description

Comprehensive PostGIS spatial table design reference covering geometry types, coordinate systems, spatial indexing, and performance patterns for location-based applications

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# 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

The rest: https://github.com/timescale/pg-aiguide/blob/HEAD/skills/design-postgis-tables/SKILL.md