postgis-skill

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

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.

postgis-skill

NPX Install

Tags

SKILL.md Content

Documentation

Style

Indexing

Debugging

Raster

SQL gotchas

PostGIS gotchas