postgis-skill — Практические навыки и инструменты для PostGIS

--- 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.