A curated, example-driven reference for PostGIS ST_* functions and spatial operators — usable from the terminal, as a single self-contained HTML page, or as structured data.

Ships a hand-written dataset of 92 PostGIS functions and operators in data/functions/*.yaml. Every entry carries more than a signature:

• signature(s), return type, the PostGIS version it appeared in, and per-argument descriptions;
• a one-paragraph plain-English summary of what it is for, not just what it returns;
• a SQL example together with the result it actually produces;
• a psycopg 3 snippet and a GeoAlchemy2 / SQLAlchemy 2.0 snippet;
• srid_notes — the CRS gotchas: geometry vs geography, degrees vs metres, when you must ST_Transform and when transforming will silently destroy your index;
• index_usage — whether a GiST index can serve it, whether it is sargable as written, and whether you need an explicit && prefilter;
• two or three common_mistakes per entry, and see_also cross-references.

A few interfaces read that one dataset:

The official PostGIS reference is complete and accurate, and that is exactly the problem when you are mid-query. It tells you that ST_DWithin(geometry, geometry, double precision) returns a boolean. It does not lead with the three things that actually cost you an afternoon:

1. The units are not what you assume. ST_DWithin(geom, point, 500) on SRID 4326 means 500 degrees, which matches the entire planet. The same call on geography means 500 metres. Nothing errors; you just get wrong results, or a query that returns everything.
2. Whether it uses the index is a property of how you wrote it, not of the function. ST_Distance(a, b) < 500 and ST_DWithin(a, b, 500) are logically identical and differ by orders of magnitude in runtime, because only the second emits the && bounding-box term that a GiST index can answer. <-> is index-assisted in ORDER BY ... LIMIT and not in WHERE. ST_Disjoint cannot be index-assisted at all.
3. The near-synonyms differ in exactly one case, and it is the case you have. ST_Contains is false for a point on the boundary; ST_Covers is true. ST_Centroid can land outside a crescent-shaped polygon; ST_PointOnSurface cannot.

This tool front-loads those three things. Each entry is built around the SRID behaviour, the index behaviour, and the mistakes people actually make — with a worked example whose printed result you can check against your own database.

Cloned and run in place; there is no package to install.

git clone <this repo>
cd st-function-cheatsheet
python3 -m venv .venv
.venv/bin/pip install -r requirements.txt

Python 3.10 or newer. Runtime dependencies are PyYAML and rich. For the test suite, use requirements-dev.txt instead.

The verify subcommand additionally needs a PostgreSQL driver, kept out of the base requirements so that everything else works without one:

.venv/bin/pip install -r requirements-verify.txt

A bare argument is treated as a query. Search covers name, summary and tags, and tolerates typos:

$ python -m st_cheatsheet knn
4 match(es) for 'knn'
┏━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┓
┃ name            ┃ category      ┃ idx ┃ summary                                        ┃ matched ┃
┡━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
│ <#>             │ operators     │ yes │ Returns the distance between the bounding box… │ tag     │
│ <->             │ operators     │ yes │ The KNN distance operator.                     │ tag     │
│ ST_DWithin      │ relationships │ yes │ Returns true when the two geometries are with… │ summary │
│ ST_ClosestPoint │ processing    │ no  │ Returns the point on the first geometry that … │ summary │
└─────────────────┴───────────────┴─────┴────────────────────────────────────────────────┴─────────┘

The idx column is the quick answer to "can this use my GiST index?". Misspellings still land:

$ python -m st_cheatsheet buffr
1 match(es) for 'buffr'
┏━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ name      ┃ category   ┃ idx ┃ summary                            ┃ matched          ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ ST_Buffer │ processing │ no  │ Returns a polygon covering every … │ fuzzy name (91%) │
└───────────┴────────────┴─────┴────────────────────────────────────┴──────────────────┘

An unknown name exits non-zero and suggests neighbours:

$ python -m st_cheatsheet show ST_Buffr
error: no entry named 'ST_Buffr'
did you mean: ST_Buffer, ST_Boundary?
$ echo $?
1

$ python -m st_cheatsheet show '<->'
╭─ <-> ────────────────────────────────────────────────────────────────────────────────────╮
│                                                                                          │
│  Signature                                                                               │
│    geometry <-> geometry -> double precision                                             │
│    geography <-> geography -> double precision                                           │
│                                                                                          │
│  Summary                                                                                 │
│    The KNN distance operator. In an ORDER BY it lets a GiST index return rows in order   │
│    of increasing distance from a reference point, which is the only way to get a         │
│    nearest-neighbour query that does not read the whole table. Since PostGIS 2.2 on      │
│    PostgreSQL 9.5+ it returns true geometry-to-geometry distance, not box distance.      │
│                                                                                          │
│    Category  operators                                                                   │
│    Returns   double precision                                                            │
│    Since     PostGIS 2.0 (true distance since 2.2)                                       │
│    Index     GiST-indexable - sargable                                                   │
│    Tags      knn, nearest-neighbour, distance, order-by, index, operator                 │
│                                                                                          │
│  Arguments                                                                               │
│      name    type        description                                                     │
│      A       geometry    Left operand. For index use, this must be the indexed           │
│                          column.                                                         │
│      B       geometry    Right operand, normally a constant reference geometry.          │
│                                                                                          │
│  SQL                                                                                     │
│    SELECT ROUND(('POINT(0 0)'::geometry <-> 'POINT(3 4)'::geometry)::numeric, 3) AS d;   │
│    Result                                                                                │
│       d                                                                                  │
│    -------                                                                               │
│     5.000                                                                                │
│                                                                                          │
│  psycopg                                                                                 │
│    with conn.cursor() as cur:                                                            │
│        cur.execute(                                                                      │
│            """                                                                           │
│            SELECT id, name, geom <-> ST_SetSRID(ST_MakePoint(%s, %s), 4326) AS dist      │
│            FROM stops                                                                    │
│            ORDER BY geom <-> ST_SetSRID(ST_MakePoint(%s, %s), 4326)                      │
│            LIMIT 10                                                                      │
│            """,                                                                          │
│            (lon, lat, lon, lat),                                                         │
│        )                                                                                 │
│        nearest = cur.fetchall()                                                          │
│                                                                                          │
│  GeoAlchemy2                                                                             │
│    from sqlalchemy import select                                                         │
│    from geoalchemy2.functions import ST_MakePoint, ST_SetSRID                            │
│                                                                                          │
│    here = ST_SetSRID(ST_MakePoint(lon, lat), 4326)                                       │
│    stmt = select(Stop.id,                                                                │
│    Stop.name).order_by(Stop.geom.distance_centroid(here)).limit(10)                      │
│                                                                                          │
│  SRID notes                                                                              │
│    On geometry the result is in SRID units, so on 4326 you get degrees - useless as a    │
│    distance but perfectly valid as an ordering key at small extents. For metres either   │
│    cast both sides to geography (the geography <-> is also index-assisted) or store a    │
│    projected column. Ordering by degrees and ordering by metres differ noticeably at     │
│    high latitudes.                                                                       │
│                                                                                          │
│  Index usage                                                                             │
│    Index-assisted only in an ORDER BY with a LIMIT, where one side is a constant and     │
│    the other is the indexed column. Put it in a WHERE clause and you get a sequential    │
│    scan; use ST_DWithin there instead.                                                   │
│                                                                                          │
│  Common mistakes                                                                         │
│    - Using <-> in WHERE (geom <-> point < 1000). That is not index-assisted - use        │
│      ST_DWithin for a radius filter.                                                     │
│    - Omitting LIMIT, which makes the planner prefer a sort over the index scan and       │
│      reads every row.                                                                    │
│    - Reading the geometry result as metres on SRID 4326, where it is degrees.            │
│                                                                                          │
│  See also  <#>, ST_DWithin, ST_Distance, ST_ClosestPoint                                 │
│  Guide     https://www.postgis-python.com/mastering-core-spatial-query-patterns/         │
│                                                                                          │
╰──────────────────────────────────────────────────────────────────────────────────────────╯

--snippet prints the raw text with no framing, so it pipes cleanly into an editor or the clipboard:

$ python -m st_cheatsheet show ST_Subdivide --snippet geoalchemy
from sqlalchemy import select
from geoalchemy2.functions import ST_Subdivide

stmt = select(Country.id, ST_Subdivide(Country.geom, 128).label("part"))

$ python -m st_cheatsheet show ST_DWithin --snippet sql | pbcopy

$ python -m st_cheatsheet categories
Categories
┏━━━━━━━━━━━━━━━━━┳━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ category        ┃   n ┃ description                                                              ┃
┡━━━━━━━━━━━━━━━━━╇━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ constructors    │  10 │ Build geometry values from coordinates, text, binary or GeoJSON.         │
│ accessors       │  13 │ Inspect a geometry: type, dimension, SRID, coordinates, validity.        │
│ measurement     │  10 │ Distances, areas, lengths and perimeters.                                │
│ relationships   │  13 │ Boolean spatial predicates and the DE-9IM model behind them.             │
│ processing      │  19 │ Derive new geometries: buffers, unions, simplification, clustering.      │
│ editors         │   9 │ Modify an existing geometry in place: SRID, validity, densification.     │
│ output          │   8 │ Serialise geometry to text, JSON, binary or vector tiles.                │
│ operators       │   6 │ Bounding-box and KNN operators that drive index access.                  │
│ utility         │   4 │ Version, configuration and housekeeping helpers.                         │
└─────────────────┴─────┴──────────────────────────────────────────────────────────────────────────┘

--index-only narrows any listing to the functions a GiST index can actually serve — 17 of the 92, which is itself the useful lesson:

$ python -m st_cheatsheet list --index-only --category operators
6 entries
┏━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ name ┃ category  ┃ since        ┃ returns          ┃ summary                                     ┃
┡━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ &&   │ operators │ 1.0          │ boolean          │ Returns true when the 2D bounding boxes of… │
│ &&&  │ operators │ 2.0          │ boolean          │ The n-dimensional counterpart of &&: it re… │
│ <#>  │ operators │ 2.0          │ double precision │ Returns the distance between the bounding … │
│ <->  │ operators │ 2.0 (true d… │ double precision │ The KNN distance operator.                  │
│ @    │ operators │ 1.0          │ boolean          │ Returns true when the bounding box of A is… │
│ ~    │ operators │ 1.0          │ boolean          │ Returns true when the bounding box of A co… │
└──────┴───────────┴──────────────┴──────────────────┴─────────────────────────────────────────────┘

$ python -m st_cheatsheet build --out dist/
wrote dist/index.html (490 kB, 92 functions, no external requests)

That is the one command that produces the page; build output is not committed. The result is a single file you can open with file://, drop on an intranet, or commit to a wiki:

• instant client-side fuzzy search, category filter, and a "GiST-indexable only" toggle;
• keyboard navigation — / focuses search, arrows move through results, Enter jumps to the card, Esc clears;
• a deep-linkable #fragment per function, copy-to-clipboard on every snippet;
• light and dark via prefers-color-scheme, responsive down to phone width;
• no external requests at all — CSS, JS and data are inlined, and the build asserts it.

Every card is rendered server-side, so the page still reads, prints and deep-links with JavaScript disabled; the script only filters and reorders nodes that already exist.

$ python -m st_cheatsheet validate
dataset is valid
checked 92 entries across 9 categories

validate exits 1 on a bad dataset, which makes it a one-line CI gate. export emits the whole structured dataset, honouring --category and --index-only:

$ python -m st_cheatsheet export --format json --category operators --out examples/operators.json
wrote examples/operators.json (6 functions)

examples/ holds two such artefacts: operators.json (all six spatial operators) and gist-indexable.json (every entry a GiST index can serve). --format ndjson emits one compact object per line for streaming consumers.

validate proves the dataset is well-formed. verify proves it is true: it executes every entry's example.sql against a real server, each in its own rolled-back transaction, and compares the output to the example.result printed beside it.

$ python -m st_cheatsheet verify --dsn postgresql://postgres:pw@127.0.0.1:5432/verify
server  PostGIS 3.4.3 / GEOS 3.9.0-CAPI-1.16.2 / 16.4 (Debian 16.4-1.pgdg110+2)

matched 90  mismatched 0  failed 0  since-suspect 0  skipped 2
92 entries verified against PostGIS 3.4.3

The DSN may also come from $ST_CHEATSHEET_DSN or $DATABASE_URL. --verbose lists the skipped entries and the reason each was exempted; --category and --index-only narrow the run.

Values are compared as the server prints them. Once a first execution reveals the column types, a passthrough text loader is registered for each, so a boolean arrives as t rather than True — which is what the result blocks contain.

Not every entry can match everywhere, and the exemptions are declared per-entry in the data rather than hardcoded in the verifier (see the verify field below). Five outcomes are possible:

An example that errors on a server older than its own floor is skipped as unavailable, because that is evidence for the dataset rather than against it. The reverse case is the interesting one: if an entry claims since: 3.4 but its example runs and matches on PostGIS 3.3, the version claim is not supported by the software, and verify reports it as since-suspect. That is a documentation bug worth a human's attention, so it is surfaced by default but only fails the build under --strict-since.

The dataset is the product. data/functions/*.yaml is hand-written, one file per category, and the Python is a thin loader / searcher / renderer over it.

Validation is two-phase. Per-entry checks happen during parsing, in FunctionEntry.from_dict: required fields, types, known category, at least one signature, at least two common mistakes, https://-only docs_url, and — deliberately — rejection of unknown keys, so that a typo like retruns: fails loudly instead of silently dropping content. Errors name the file, index and function (measurement.yaml#3 [ST_Area]: ...). Cross-entry checks then run over the whole set in validate_dataset: duplicate names, see_also targets that do not resolve, and self-references. build refuses to run on a dataset that fails.

The verify field. An optional per-entry mapping that tells verify how to treat the stated result. It is optional and defaults to {mode: exact}, so a newly added entry is checked strictly unless someone deliberately exempts it — and every exemption has to say why.

min_version exists for the case where an example deliberately exercises an overload newer than the function itself. ST_Point dates from 1.0, but its example passes the SRID argument added in 3.2; without the override, servers between 1.0 and 3.2 would report a false failure instead of skipping the entry as unavailable. It is a narrow escape hatch, not a way to lower the bar: since remains the source of truth everywhere else.

Search ranking is explainable, not statistical. Score bands, best first: exact name (an st_ prefix is optional, so dwithin matches ST_DWithin), name prefix, name substring, exact tag, tag substring, summary substring, and finally a difflib similarity fallback above 0.62 to catch typos. Ties break on shorter name, so simplify returns ST_Simplify before ST_SimplifyPreserveTopology. Every result reports why it matched in the matched column. The bands are spaced widely enough that no lower-band bonus can outrank a higher band.

The HTML page embeds a JSON island of just the fields the client needs (name, slug, category, summary, tags, GiST flag) and reimplements the same band scoring in JavaScript, with a subsequence matcher in place of difflib. Ranking therefore differs slightly between terminal and page for typo-heavy queries; exact, prefix and substring behaviour is identical.

Limitations, honestly:

• No database is consulted. The result field of each example is authored, not executed at build time. Results were chosen to be independently verifiable (a 10×10 box has area 100; LINESTRING(0 0, 3 4) has length 5), and floating point is rounded in the SQL where the exact digits would be uncertain. Output that legitimately varies by installation — version strings, ST_MemSize byte counts, protobuf blobs — is presented as a comparison or a property rather than a fixed value.
• Version numbers are conservative. Where PostGIS documents no availability note, entries say 1.0 rather than guessing. Where a feature arrived later than the function, the since field says so inline (2.0 (true distance since 2.2)).
• index_usage describes the common case. Whether the planner chooses an index also depends on statistics, row counts and cost settings; the flags tell you whether the query is written so that it can, which is the part you control.
• Coverage is curated, not exhaustive. 92 entries covering the functions that carry real gotchas. Raster, topology and 3D-surface functions are out of scope.

There is no config file; behaviour is entirely by flag.

Exit codes: 0 success, 1 no results, validation failure, or a real verify mismatch, 2 usage error, 3 the dataset could not be loaded, the site could not be built, or verify could not reach a server.

Adding an entry is a matter of appending to the relevant YAML file and running validate; the schema is enforced, so an incomplete entry cannot reach the page.

$ .venv/bin/pip install -r requirements-dev.txt
$ .venv/bin/python -m pytest
249 passed in 3.61s

The suite runs offline and needs no PostgreSQL, PostGIS, Docker or network — including the verify tests, which fake the connection but assert against psycopg's real exception hierarchy. It covers:

• the real shipped dataset against the schema — bad data fails CI, including checks that every Python snippet actually compiles, that see_also resolves, that slugs are unique, and that no entry claims to be sargable without GiST support;
• search ranking and tie-breaking, including the typo fallback;
• snippet extraction, and preservation of the leading whitespace that keeps psql result columns aligned;
• the HTML builder — expected sections present, the embedded JSON parses, escaping of HTML metacharacters and of </script> inside prose, and an offline-safety assertion that no http(s):// reference appears outside an href;
• CLI exit codes for every command, including two real python -m st_cheatsheet subprocess runs;
• the verify classification matrix — match, mismatch, unavailable-below-floor, suspect since, and each exemption mode — plus a check that every since in the shipped dataset parses and that every exact entry's result block is actually comparable, since an unparseable one would be silently skipped rather than checked.

Tests prove the dataset is well-formed. They cannot prove it is true, so it is worth being explicit about where each field's authority comes from.

Every entry was checked field-by-field against its official PostGIS documentation page, which produced 24 corrections — mostly since versions (Availability: and Changed: mean different things, and a rename is not an introduction) and missing overloads in signatures. Every stated SQL result was recomputed by hand and none needed changing.

All 92 SQL examples are executed on every push by the verify job, each in its own rolled-back transaction, across five PostGIS releases. That check used to be an uncommitted local script against a single server; it is now a committed subcommand and a CI matrix, which is what makes the numbers below reproducible rather than remembered.

The examples are written defensively — rounded magnitudes, feature counts and boolean assertions rather than raw coordinate dumps — which is why 89 of 92 are byte-identical across a GEOS 3.9 to 3.13 span. Three entries are exempt, each with its reason recorded in the data:

• PostGIS_Full_Version and PostGIS_GEOS_Version are version-string. Their output is the running installation's banner, so it can never equal a fixed literal; verify requires only that the call succeeds and returns something. The stated results quote a real 3.4.2 box and are kept for shape.
• ST_SimplifyPreserveTopology is geos-sensitive, and this one was found by the matrix rather than assumed. On GEOS 3.9.0 it returns the unchanged square POLYGON((0 0,10 0,10 10,0 10,0 0)); on GEOS 3.13.1 it simplifies further, to the triangle POLYGON((0 10,10 0,10 10,0 10)). The contrast the example exists to draw — plain ST_Simplify returns NULL at this tolerance while the topology-preserving variant returns a valid non-empty polygon — holds on both, so only the literal vertex list is exempt.

One since-related correction came out of this. ST_Point's example passes the three-argument SRID form, which the entry's own since string dates to 3.2, while its leading version is 1.0 — so the example had a higher floor than the function. Confirmed by running it against postgis/postgis:13-3.1, which fails with function st_point(numeric, numeric, integer) does not exist. The since prose was already correct and was left alone; the example now declares verify.min_version: "3.2", so servers below 3.2 skip it as unavailable instead of reporting a false failure.

No since value was contradicted in the other direction. The since-suspect check — an entry whose example runs and matches on a server older than it claims to need — fired zero times, but that is a weak result rather than a clean bill of health: the highest floor in the dataset is ST_TileEnvelope at 3.0, and the oldest image in the matrix is 3.3, so there is currently nothing in range for it to catch. The check earns its place on the next entry that claims 3.4 or later. Note also that it can only test the leading version in a since string; parenthetical claims like "geography since 2.0" describe overloads the examples do not exercise, and remain doc-sourced.

What that verification can and cannot back:

• Signatures, return types, since versions and documented behaviour are doc-sourced. Where a page carries no Availability: line at all — common for functions predating PostGIS 1.0 — the value is a reasonable inference, not a citation.
• index_usage and much of srid_notes are editorial. The upstream pages mostly do not discuss GiST behaviour or sargability, so these reflect PostGIS practice rather than quotable documentation. They are the fields most worth confirming against EXPLAIN on your own version before you rely on them. The gist: true predicates and the gist: false markings on ST_Disjoint and ST_Relate have since been confirmed empirically with EXPLAIN ANALYZE against PostGIS 3.4.3 over a GiST-indexed fixture table. ST_Equals was previously marked conservatively on the grounds that its page, alone among the predicates, states no automatic bounding-box comparison; that marking turned out to be wrong. It carries the same postgis_index_supportfn as ST_Intersects and emits a ~= (identical bounding box) index condition, so it is index-assisted and is now marked as such.
• Claims that quote a literal error message were dropped unless the wording could be confirmed, and the underlying lesson kept instead. An invented error string is worse than no error string, because it looks checkable.

If you find something wrong, an issue with the doc sentence that settles it is the fastest way to get it fixed.

The docs_url on an entry points at a deeper guide for that specific topic. The ones referenced by this dataset:

• Mastering core spatial query patterns — the && prefilter, ST_DWithin radius filters, KNN with <->, and spatial joins.
• Advanced GiST indexing and optimization — partial and composite indexes, index-only scans, reading EXPLAIN (ANALYZE, BUFFERS), and when SP-GiST or BRIN beats GiST.
• SQLAlchemy and GeoAlchemy2 integration workflows — model mapping, sessions, hybrid geometry properties, type coercion and async streaming.
• Spatial schema migrations and evolution — adding geometry columns to live tables, in-place SRID reprojection, concurrent index builds.
• Spatial performance monitoring and observability — pg_stat_statements, GiST bloat detection and autovacuum tuning.

MIT — see LICENSE.