[SCM] PostGIS branch master updated. 3.6.0rc2-237-geebec8c1c

[git at osgeo.org]

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  eebec8c1cdf57805289c85ce9e155d386a860256 (commit)
      from  69f409965635b20fe42da38f86947c0a6d8aa888 (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 eebec8c1cdf57805289c85ce9e155d386a860256
Author: Darafei Praliaskouski <me at komzpa.net>
Date:   Sun Nov 30 03:27:49 2025 +0400

    LLM-friendly gotchas list to make them behave during PostGIS debug
    
    References #5638

diff --git a/doc/SKILL.md b/doc/SKILL.md
new file mode 100644
index 000000000..5d2504c44
--- /dev/null
+++ b/doc/SKILL.md
@@ -0,0 +1,67 @@
+---
+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.

-----------------------------------------------------------------------

Summary of changes:
 doc/SKILL.md | 67 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 67 insertions(+)
 create mode 100644 doc/SKILL.md


hooks/post-receive
-- 
PostGIS