view --main postgis-skill-prakticheskie-navyki-i-instrumenty-dlya-postgis.md
postgis-skill — Практические навыки и инструменты для PostGIS
//
postgis-skill – это набор практических навыков и инструментов для эффективной работы с пространственными данными в SQL-базе данных PostGIS, расширении для реляционной СУБД PostgreSQL. Он позволяет решать сложные задачи геопространственного анализа и управления данными, обеспечивая высокую производительность и точность.
Модуль акцентирует внимание на написании понятного и документированного SQL-кода, включая подробные комментарии к каждому запросу и временным таблицам (CTE). Это гарантирует возможность воспроизведения и понимания логики работы в будущем, а также упрощает отладку и поддержку. Использование PL/pgSQL и функций PostGIS, таких как ST_Segmentize, позволяет создавать сложные пространственные операции.
Особое внимание уделяется оптимизации производительности, включая создание BRIN-индексов для больших таблиц и использование опции including для ускорения поиска по ключевым полям. Вся документация поддерживается в актуальном состоянии, что облегчает интеграцию и использование модуля в различных проектах, связанных с геоинформационными системами (GIS) и пространственными запросами к базе.
.weblate
Makefile.comments.in
Makefile.in
README
ZMSgeoms.txt
administration.xml
bnf-wkb.txt
bnf-wkt.txt
credits.xml
database_tuning.xml
developer.md
development
README.md
3 KB
SKILL.md
2.9 KB
compatibility
0 B
contributing.md
5.4 KB
environment
0 B
governance
0 B
internals
0 B
maintenance
0 B
manual.md
10.3 KB
release-process.md
9.3 KB
release
0 B
style.md
3.6 KB
testing
0 B
todo.md
8.3 KB
tools.md
1.1 KB
website.md
5.5 KB
doxygen.cfg.in
extras.xml
extras_historytable.xml
extras_topology.xml
geography.txt
html
example-blocks.js
57.4 KB
images
0 B
style.css
22.2 KB
test_example_blocks.js
29.1 KB
installation.xml
introduction.xml
man
pgsql2shp.1
3.2 KB
pgtopo_export.1
1.1 KB
pgtopo_import.1
1.3 KB
postgis.1
670 B
postgis_restore.1
1.3 KB
raster2pgsql.1
7.1 KB
shp2pgsql.1
7 KB
performance_tips.xml
po
Makefile.local
983 B
README
1.6 KB
be
0 B
da
0 B
de
0 B
es
0 B
fix_broken_xml.sh
407 B
fr
0 B
it_IT
0 B
ja
0 B
ka
0 B
ko_KR
0 B
pl
0 B
pt_BR
0 B
ro
0 B
ru
0 B
strip_untranslatable.sh
662 B
sv
0 B
templates
0 B
uk
0 B
zh_Hans
0 B
postgis-review.html
postgis.xml
postgis_special.xml
reference.xml
reference_accessor.xml
reference_bbox.xml
reference_cluster.xml
reference_constructor.xml
reference_coverage.xml
reference_editor.xml
reference_exception.xml
reference_guc.xml
reference_input.xml
reference_lrs.xml
reference_management.xml
reference_measure.xml
reference_operator.xml
reference_output.xml
reference_overlay.xml
reference_processing.xml
reference_raster.xml
reference_relationship.xml
reference_sfcgal.xml
reference_sfcgal_legacy.xml
reference_srs.xml
reference_trajectory.xml
reference_transformation.xml
reference_troubleshooting.xml
reference_type.xml
reference_validation.xml
reference_version.xml
release_notes.xml
reporting.xml
rfc
postgis_rfc_02.txt
5.9 KB
postgis_rfc_03.txt
157 B
postgis_rfc_03_sheet.txt
35.9 KB
postgis_rfc_04.txt
4.2 KB
skills
README.md
896 B
postgis
0 B
sql-programming
0 B
template.xml
texstyle-common.sty
texstyle.sty
using_postgis_dataman.xml
using_postgis_query.xml
using_raster_dataman.xml
xmlformat.conf
xsl-config.xml
xsl
LICENSE
1.5 KB
common_cheatsheet.xsl
7.3 KB
common_comments.xsl
954 B
common_utils.xsl
1.8 KB
list_3d_functions.xml.xsl
2 KB
list_aggregates.xml.xsl
2 KB
list_box_functions.xml.xsl
2 KB
list_curved_geometry_functions.xml.xsl
2.1 KB
list_dump_functions.xml.xsl
2.1 KB
list_geography_functions.xml.xsl
2.1 KB
list_new_functions.xml.xsl
3.7 KB
list_polyhedral_functions.xml.xsl
2.1 KB
list_raster_functions.xml.xsl
2.1 KB
list_sqlmm_functions.xml.xsl
2 KB
list_window_functions.xml.xsl
2.2 KB
node_by_xpath.xsl
573 B
post_gis_day_cards.html.xsl
8.2 KB
postgis-block-labels-common.xsl
1019 B
postgis-chunk-html.xsl.in
429 B
postgis-dblatex-block-labels.xsl
6.2 KB
postgis-html-block-labels.xsl
17.5 KB
postgis-html.xsl.in
431 B
postgis_cheatsheet.html.xsl
3.9 KB
postgis_comments.sql.xsl
4.1 KB
postgis_gardentest.sql.xsl
51.4 KB
postgis_reference.xml.xsl
1.5 KB
raster_cheatsheet.html.xsl
3.8 KB
raster_comments.sql.xsl
4.6 KB
raster_gardentest.sql.xsl
35 KB
sfcgal_cheatsheet.html.xsl
3.7 KB
sfcgal_comments.sql.xsl
3.2 KB
sfcgal_gardentest.sql.xsl
50.9 KB
table_body_function_support_matrix.xml.xsl
9.8 KB
test_gardentest_escaping.xsl
1.5 KB
topology_cheatsheet.html.xsl
3.6 KB
topology_comments.sql.xsl
3.7 KB
topology_gardentest.sql.xsl
43.5 KB
readonly
--- lines
---
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.
Инициализация мануала...
package.json
$ install --global
skills.sh
npx skills add https://github.com/postgis/postgis/tree/master/doc
$ download --local
man
[HINT] Скачивает всю директорию скилла с GitHub: SKILL.md и все связанные файлы