pg-aiguide

What the source does

• FilesystemDeclared4 observations
• NetworkDeclared4 observations
• ShellDeclared5 observations
• DependenciesNot pinnedSome versions float
• SecretsFoundSee the findings; values are never shown

Five audit layers

Gates applied: no behavioural pass.

return f"…"

.env.sample

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

hash_suffix = hashlib.md5(path.encode()).hexdigest()[:8]

new URL('../../skills/', import.meta.url), // src/prompts/index.ts -> skills (checkout/Docker)

@openrouter/ai-sdk-provider, @opentelemetry/api, dotenv, gray-matter, migrate, pg, zod, @biomejs/biome

`$1` = schema name, `$2` = foreign table name. Server/table options are listed by **name only**: option values and user mappings may contain connection details or credentials. Don't collect or report

> You are a senior software engineer. You are given a task to generate a Postgres schema for an IoT device company.

Audit 0.4.1 · source sha c8ed45084a23 · 23 Oct 2026Report an issue or request a re-scan

• Publishertimescale GitHub organizationOwner of the repository this skill is read from
• Source foldertimescale/pg-aiguideThe exact folder we audited
• LicenceApache-2.0As the repository states it
• Commit pinned for this verdictc8ed45084a23A new audit runs when the source changes
• Repository statusNot archivedLast push 1 day ago
• Sandboxed runNot runSkills are read, not executed; layer L4 is skipped

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

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

Excerpt, first 6,000 characters of SKILL.md, verbatim from the audited commit. It is text from someone else’s repository, quoted so you can judge it before your agent reads it.

1. 2026-10-23
   !CAUTION B 85 / 100

   first audit · source c8ed45084a23

Questions about pg-aiguide

• github.com/timescale/pg-aiguide — the audited source at commit c8ed45084a23 and every finding aboveObserved · 23 Oct 2026

Spotted something wrong? Report a correction · Methodology