[SCM] PostGIS branch master updated. 3.6.0rc2-501-g65e081184
git at osgeo.org
git at osgeo.org
Mon Jun 8 01:07:12 PDT 2026
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, master has been updated
via 65e0811847b3a6572ce496bd219ba69dc62e364f (commit)
via 7826c60d05d8875df8d1b2152eb5a7baadc94d9f (commit)
from 8b7cbaa81af3561d34c0393fbc0e2d56d97be4ef (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 65e0811847b3a6572ce496bd219ba69dc62e364f
Merge: 8b7cbaa81 7826c60d0
Author: Darafei Praliaskouski <me at komzpa.net>
Date: Mon Jun 8 12:06:48 2026 +0400
Merge branch 'codex/postgis-skill-scope'
commit 7826c60d05d8875df8d1b2152eb5a7baadc94d9f
Author: Darafei Praliaskouski <me at komzpa.net>
Date: Mon May 4 08:54:27 2026 +0400
docs: keep PostGIS skill scope specific
diff --git a/doc/SKILL.md b/doc/SKILL.md
deleted file mode 100644
index 5d2504c44..000000000
--- a/doc/SKILL.md
+++ /dev/null
@@ -1,67 +0,0 @@
----
-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.
diff --git a/skills/postgis/SKILL.md b/skills/postgis/SKILL.md
new file mode 100644
index 000000000..7ee79755e
--- /dev/null
+++ b/skills/postgis/SKILL.md
@@ -0,0 +1,38 @@
+---
+name: postgis
+description: PostGIS-specific SQL tips and gotchas. Use when working with geometry, geography, raster, projections, spatial indexes, or geospatial processing in PostgreSQL through PostGIS.
+---
+
+## Naming
+
+ - Spell PostGIS functions as they appear in the manual (`st_segmentize` -> `ST_Segmentize`).
+ - Call geometry columns `geom`; call geography columns `geog`.
+
+## Checks
+
+ - Check `select postgis_full_version();` to confirm the installed PostGIS, GEOS, PROJ, GDAL, and SFCGAL versions when debugging extension behavior.
+
+## Raster
+
+ - Do not work with GDAL on the filesystem. Import things into database and deal with data there.
+
+## 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)` when invalid geometry must be repaired, but first prefer fixing the source of invalid geometry upstream.
+ - `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.
+ - Needing `ST_MakeValid` is a sign you're doing something wrong on the previous step.
+ - Be extra attentive when calling `ST_SetSRID`: first check the actual projection of input data. Prefer setting the SRID during input when the source format supports it, for example with `ST_GeomFromGeoJSON`, EWKT like `SRID=4326;POINT(1 2)`, or EWKB; use `ST_Transform` instead if reprojection is needed.
+ - When looking for relation between point and polygon, prefer `ST_Intersects` to other topology predicates.
+ - When generating complex geometry by walking raster or grid, it 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 the `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.
diff --git a/skills/sql-programming/SKILL.md b/skills/sql-programming/SKILL.md
new file mode 100644
index 000000000..ef035204e
--- /dev/null
+++ b/skills/sql-programming/SKILL.md
@@ -0,0 +1,39 @@
+---
+name: sql-programming
+description: General SQL programming, formatting, debugging, and query-shaping tips. Use for SQL work that is not specifically about PostGIS spatial data.
+---
+
+## Documentation
+
+ - Make sure every create statement or CTE has a 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
+
+ - 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.
+
+## Indexing
+
+ - Consider BRIN indexes for very large naturally ordered tables that will be used for ad-hoc range queries.
+ - If you have a cache table that has a primary key, it can make sense to add frequently read values into `including` on the same index for faster lookup.
+
+## Debugging
+
+ - Make sure error messages towards developers are better than just "500 Internal Server Error".
+ - Don't stub stuff out with insane fallbacks like `lat = 0` and `lon = 0`; instead make the rest of the code work around data absence and inform the user.
+ - SQL files should be idempotent: drop table if exists + create table as; add comments to make queries faster to grasp.
+ - Create both "up" and "down/rollback" migrations when the project expects reversible migrations.
+ - Don't run one SQL file from another SQL file - this quickly becomes a mess with relative file paths.
+
+## 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 with indexes.
+ - You can't create an ordered table and then rely on it to be ordered on scan without `order by`.
-----------------------------------------------------------------------
Summary of changes:
doc/SKILL.md | 67 -----------------------------------------
skills/postgis/SKILL.md | 38 +++++++++++++++++++++++
skills/sql-programming/SKILL.md | 39 ++++++++++++++++++++++++
3 files changed, 77 insertions(+), 67 deletions(-)
delete mode 100644 doc/SKILL.md
create mode 100644 skills/postgis/SKILL.md
create mode 100644 skills/sql-programming/SKILL.md
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list