home / skills / nevaberry / nevaberry-plugins / postgis-knowledge-patch

postgis-knowledge-patch skill

safe

/plugins/postgis-knowledge-patch/skills/postgis-knowledge-patch

This skill helps you craft and optimize PostGIS 3.5+ queries by applying latest function changes, topology, and raster enhancements across spatial analyses.

npx playbooks add skill nevaberry/nevaberry-plugins --skill postgis-knowledge-patch

Review the files below or copy the command above to add this skill to your agents.

Files (2)

SKILL.md

3.8 KB

---
name: postgis-knowledge-patch
description: This skill should be used when writing PostGIS queries, using spatial functions, geography types, raster operations, topology, or any PostGIS features from 3.5+ (2024-2026).
license: MIT
metadata:
  author: Nevaberry
  version: "3.6.1"
---

# PostGIS 3.5+ Knowledge Patch

Claude's baseline knowledge covers PostGIS through 3.4. This skill provides features from 3.5 (Sep 2024) onwards.

**Source**: PostGIS news at https://postgis.net/news/

## Additional Resources

### Reference Files

For detailed descriptions, code examples, and full context on each change, consult:
- **`references/postgis-3.5-3.6-details.md`** — Detailed changelog organized by topic (breaking changes, new vector/raster/topology/SFCGAL functions)

## 3.5.0 (2024-09-25)

**Requires**: PostgreSQL 12-17, GEOS 3.8+, Proj 6.1+. SFCGAL 1.5 for all SFCGAL features.

### Breaking Changes
| Change | Impact |
|--------|--------|
| SFCGAL `ST_*` → `CG_*` prefix | All SFCGAL functions renamed. Old `ST_` names deprecated. |
| `ST_DFullyWithin` semantics | Now `ST_Contains(ST_Buffer(A, R), B)`. May return different results. |
| `ST_AsGeoJSON(record)` signature | Can promote column as Feature `id`. Materialized views need rebuild. |
| `ST_GeneratePoints` seed | Seeded random points produce different results. Regenerate if needed. |
| `ST_Clip` variants replaced | New `touched` param added. Materialized views need rebuild. |

### New Functions
| Function | Purpose |
|----------|---------|
| `ST_HasZ(geom)` / `ST_HasM(geom)` | Boolean Z/M dimension checks |
| `ST_CurveN(geom, n)` / `ST_NumCurves(geom)` | CompoundCurve accessors |
| `ST_RemoveIrrelevantPointsForView(geom, ...)` | Viewport-based simplification |
| `ST_RemoveSmallParts(geom, ...)` | Remove small polygon parts (slivers) |
| `TopoGeo_LoadGeometry(topo, geom, tol)` | Load geometry into topology |
| `CG_Visibility(polygon, point)` | Visibility polygon (SFCGAL) |
| `CG_*Partition` functions | Polygon partitioning algorithms (SFCGAL) |
| `ST_ExtrudeStraightSkeleton(geom)` | Extrude along straight skeleton (SFCGAL) |

### ST_Clip `touched` (Raster)
```sql
-- Include pixels touched by geometry, not just centers-inside
SELECT ST_Clip(rast, geom, touched => true) FROM raster_table;
```

## 3.5.1 (2024-12-22)

**Breaking**: `ST_TileEnvelope` now clips envelopes to tile plane extent. Edge tiles return smaller geometries than before.

## 3.6.0 (2025-09-01)

**Requires**: PostgreSQL 12-18, GEOS 3.8+, Proj 6.1+. GEOS 3.14+ for full features. SFCGAL 2.2+ for all SFCGAL features.

### Breaking Changes
| Change | Impact |
|--------|--------|
| TIN/PolyhedralSurface accessors | `ST_NumGeometries`/`ST_GeometryN` return 1. Use `ST_NumPatches`/`ST_PatchN` instead. |
| Topology bigint | Topology IDs now `bigint`. Integer functions replaced with bigint versions. |
| `st_approxquantile(raster, double precision)` | Removed (ambiguous). Use variant with additional params. |

### New Functions
| Function | Purpose |
|----------|---------|
| `ST_CoverageClean(geom[])` | Clean polygonal coverage — edge match + gap removal (GEOS 3.14) |
| `ST_AsRasterAgg(...)` | Aggregate: create raster from geometries |
| `ST_ReclassExact(rast, ...)` | Remap exact values in raster |
| `ST_IntersectionFractions(rast, geom)` | Raster pixel/geometry intersection fractions (GEOS 3.14) |
| `ValidateTopologyPrecision` / `MakeTopologyPrecise` | Topology precision validation and repair |

### New SFCGAL Functions (SFCGAL 2.2)
| Function | Purpose |
|----------|---------|
| `CG_Simplify` | 3D geometry simplification |
| `CG_3DAlphaWrapping` | Tight 3D surface around point set |
| `CG_Scale` / `CG_Translate` / `CG_Rotate` | 3D affine transformations |
| `CG_Buffer3D` | 3D buffering |
| `CG_StraightSkeletonPartition` | Partition polygon via straight skeleton |

SFCGAL now supports M coordinates (SFCGAL >= 1.5.0).

Overview

This skill encapsulates PostGIS 3.5+ changes and new features for writing modern spatial SQL. It highlights breaking changes, new vector/raster/topology functions, and SFCGAL 3D advances so you can update queries, materialized views, and pipelines safely. Use it as a concise reference when targeting PostGIS releases 3.5 through 3.6+ (2024–2026).

How this skill works

The skill summarizes additions and breaking changes by release, focusing on function renames, signature changes, and new capabilities (raster, topology, SFCGAL/3D, coverage and aggregation). It calls out required library versions and examples of semantic shifts you must address in SQL, views, and topology workflows. Use the entries to locate which functions were introduced, replaced, or deprecated and to guide query rewriting and data migration.

When to use it

Upgrading PostGIS extensions from 3.4 or earlier to 3.5/3.6
Rewriting queries that use SFCGAL 3D functions or ST_* SFCGAL names
Adjusting materialized views after changed function signatures (ST_AsGeoJSON, ST_GeneratePoints, ST_Clip)
Converting topology or raster pipelines affected by bigint IDs, new precision rules, or ST_Clip touched semantics
Implementing new raster aggregation, exact reclassification, or coverage-cleaning workflows

Best practices

Audit and rebuild materialized views when function signatures changed (ST_AsGeoJSON, ST_Clip, ST_GeneratePoints)
Search and replace renamed SFCGAL ST_* functions to CG_* and test 3D behavior on sample geometries
When upgrading topology, migrate to bigint IDs and adjust any functions that assumed integer topology IDs
Regenerate random-seeded outputs (ST_GeneratePoints) and re-evaluate tests that depend on deterministic results
Validate and test critical spatial predicates after ST_DFullyWithin semantics changes and when using new buffering/partitioning functions

Example use cases

Replace legacy SFCGAL ST_Buffer3D and visibility calls with CG_Buffer3D and CG_Visibility, then re-check application logic
Use ST_RemoveSmallParts to clean sliver polygons before topological operations and area calculations
Apply ST_AsRasterAgg to create raster tiles from vector features for visualization or analysis
Clip rasters with ST_Clip(..., touched=>true) to include boundary pixels in analysis and export
Run ValidateTopologyPrecision and MakeTopologyPrecise before importing topologies into systems that require consistent precision

FAQ

Do I need to rebuild materialized views after upgrading?

Yes — functions like ST_AsGeoJSON(record), ST_GeneratePoints (seed), and ST_Clip variants changed signatures or behavior, so rebuild and test affected views.

What changed for SFCGAL functions?

SFCGAL function names were renamed from ST_* to CG_*; old ST_* names are deprecated. Also check SFCGAL version compatibility for 3D and M coordinate support.