postgis / postgis-skill

Skill Content

---
name: postgis-skill
description: PostGIS-focused SQL tips, tricks and gotchas. Use when in need of dealing with geospatial data in Postgres.
---

## Documentation

 - Make sure every create statement or CTE has descriptive comment `--` in front of it.
 - Write enough comments so you can deduce what was a requirement in the future and not walk in circles.
 - Every feature needs to have comprehensive up-to-date documentation near it.

## Style

 - PostGIS functions follow their spelling from the manual (`st_segmentize` -> `ST_Segmentize`).
 - SQL is lowercase unless instructed otherwise.
 - Values in databases and layers should be absolute as much as possible: store "birthday" or "construction date" instead of "age".
 - Do not mix tabs and spaces in code.
 - Add empty lines between logical blocks.
 - Format the code nicely and consistently.
 - Call geometry column `geom`; geography column `geog`.

## Indexing

 - Create brin for all columns when creating large table that will be used for ad-hoc queries.
 - If you have cache table that has a primary key, it makes sense to add values into `including` on same index for faster lookup.

## Debugging

 - Make sure that error messages towards developer are better than just "500 Internal server error".
 - Don't stub stuff out with insane fallbacks (like lat/lon=0) - instead make the rest of the code work around data absence and inform user.
 - SQL files should to be idempotent: drop table if exists + create table as; add some comments to make people grasp queries faster.
 - Create both "up' and "down/rollback" migration when creating new migrations for ease of iteration.
 - Check `select postgis_full_version();` to see if all upgrades happened successfully.
 - Don't run one SQL file from other SQL file - this quickly becomes a mess with relative file paths.

## Raster

 - Do not work with GDAL on the filesystem. Import things into database and deal with data there.

## SQL gotchas

 - `sum(case when A then 1 else 0 end)` is just `count() filter (where A)`
 - `row_number() ...  = 1` can likely be redone as `order by + limit 1` (possibly with `distinct on` or `lateral`)
 - `exists(select 1 from ...)` is just `exists(select from ...)`
 - `tags ->> 'key' = 'value'`  is just `tags @> '{"key": "value"}` - works faster for indexes
 - you can't just create ordered table and then rely on it to be ordered on scan without `order by`

## PostGIS gotchas

 - Do not use geometry typmod unless requested (things like `geometry(multilinestring, 4326)`) - use plain `geometry` or `geography` instead. This removes clutter of `ST_Multi` and errors via `ST_SetSRID`.
 - `ST_UnaryUnion(ST_Collect(geom))` is just `ST_Union(geom)`
 - `ST_Buffer(geom, 0)` should be `ST_MakeValid(geom)`
 - `select min(ST_Distance(..))` should be `select ST_Distance() ... order by a <-> b limit 1` to enable knn gist
 - `order by ST_Distance(c.geog, t.geog)` should be `order by c.geog <-> t.geog`
 - `ST_UnaryUnion` is a sign you're doing something wrong
 - `ST_MakeValid` is a sign you're doing something wrong on the previous step
 - be extra attintive when calling `ST_SetSRID`: check the actual projection of input data, check if it can be set correctly during input (`ST_GeomFromGeoJSON`, `EWKT`-style `SRID=4326;POINT(...`, `EWKB` allow that). Check if `ST_Transform` is needed instead.
 - when looking for relation between point and polygon, prefer `ST_Intersects` to other topology predicates
 - when generating complex geometry by walking raster or grid, may make sense to `ST_Simplify(geom, 0)`
 - to generate neighbourhoods of predictable size, use `ST_ClusterKMeans` with k=2 and `max_radius` set to your distance.
 - use `ST_AsEWKB` for binary representation instead of `ST_AsWKB` to keep SRID.
 - Choosing projection:
    SRID=4326 (2D longlat) when input or output is longitude and latitude and coordinate value is to be shown to user.
    SRID=3857 (2D Spherical Mercator) when output will be shown on web map, ST_AsMVT, or 2D KNN requests of short distance are to be executed.
    SRID=4978 (3D XYZ) when performing internal computations, line-of-sight, clustering and averaging across antimeridian. Beware: only use 3D-aware operations, ST_Force3DZ on 2D CRS data before calling ST_Transform to it.
 - Instead of using `ST_Hexagon` / `ST_HexagonGrid` use `h3` extension.
 - When you know the data is going to be dumped in binary form, gzipped and moved around, consider using `ST_QuantizeCoordinates` if precision is known.