Open-Source PostGIS Tools

The guides on this site describe how PostGIS behaves and what to do about it. These five tools do some of that work for you: they read a query plan, a workload, a schema or a test suite, and report what a spatial engineer would report after looking at the same input by hand. All five are published under github.com/postgis-python, all are MIT-licensed, and all are written in Python 3.10 or newer.

None of them run against production data by default. The plan analyser and the cheatsheet never open a database connection at all, and the index advisor’s analysis step does not either — it reads a workload file and a JSON catalog snapshot, so the recommendations can be produced somewhere the database is not reachable. Collecting that snapshot is a separate, explicit step (collect) that does connect, and reads only catalog metadata and table statistics, never row data. That split is deliberate: the analysis is the valuable part, and requiring credentials to get it would put it out of reach in exactly the situations where it is most useful, such as a CI job reviewing a plan captured from staging.

How to Install Them

None of these tools are published packages. There is no pip install for any of them, and there is no PyPI entry to find. You clone the repository, create a virtualenv, install the requirements file, and run the tool as a module from the repository root:

bash
git clone https://github.com/postgis-python/spatial-index-advisor.git
cd spatial-index-advisor
python -m venv .venv
.venv/bin/pip install -r requirements.txt
.venv/bin/python -m spatial_index_advisor analyze --help

The same four steps work for all five repositories; only the module name at the end changes. Each repository carries its own requirements.txt and a requirements-dev.txt for the test suite.

The Tools

Tool Reads Tells you
postgis-explain-visualizer an EXPLAIN plan why the GiST index was not used on this query
spatial-index-advisor a workload plus a catalog snapshot which indexes to build or drop across the whole workload
geoalchemy-model-generator a PostGIS schema the SQLAlchemy 2.0 models and query helpers for it
postgis-fixtures a pytest run realistic, seeded spatial data to test against
st-function-cheatsheet a function name the SRID and index behaviour of that ST_* function

postgis-explain-visualizer

Repository: github.com/postgis-python/postgis-explain-visualizer

A generic plan visualiser tells you which node was slow. For a spatial query that is rarely the interesting part — the slow node is almost always a sequential scan or a nested loop, and the real question is why the GiST index was not usable. The answers form a short, well-known list, and they look nearly identical in a general-purpose plan viewer.

This tool parses EXPLAIN (FORMAT JSON) output — or plain-text EXPLAIN (ANALYZE, BUFFERS) output — into one normalized node tree, then runs ten spatial diagnostics over every node in it. The rules are missing-gist-index, st-distance-not-sargable, knn-without-index, loose-bbox-prefilter, missing-bbox-operator, spatial-nested-loop, spatial-row-misestimate, transform-defeats-index, cold-cache-reads and function-filter-on-geometry, each with a severity of critical, warning or info. It never connects to a database.

Reach for it when a single query is slow and you have its plan. A concrete example: given a plan for SELECT id, zone_code FROM parcels_noidx WHERE ST_Intersects(geom, ST_MakeEnvelope(...)), it reports one critical finding — a sequential scan evaluating st_intersects row by row, 1,244 rows returned and 58,756 removed by the filter — notes that there is no && anywhere in the plan because there was no index to attach one to, and emits the fix:

sql
CREATE INDEX CONCURRENTLY parcels_noidx_geom_idx
    ON parcels_noidx USING GIST (geom);

Output is a terminal report with per-node timing bars, a JSON document for pipelines, or Markdown for a pull-request comment. --fail-on critical exits 2 when a finding at or above that severity exists, which is enough to gate a CI job on plan regressions.

The findings map directly onto the guides here: the sargability rules onto core spatial query patterns and specifically ST_DWithin radius searches and KNN nearest neighbor queries; the index rules onto advanced GiST indexing and optimization and query plan analysis with EXPLAIN; and the misestimate and cold-cache rules onto spatial performance monitoring and observability.

spatial-index-advisor

Repository: github.com/postgis-python/spatial-index-advisor

A plan describes one execution. A workload describes what your database actually spends its time on, and those are different questions. A Seq Scan in one EXPLAIN looks survivable; the same scan run 1.3 million times a day is the single largest thing the database does, while a query that looks pathological in isolation may run twice a week and deserve no attention.

This tool takes a workload — a pg_stat_statements export, a PostgreSQL CSV log, or a plain file of SQL with optional -- calls: N hints — plus a JSON catalog snapshot, and produces a ranked list of recommendations. It covers missing GiST indexes, BRIN on large append-mostly tables whose geometry correlates with physical order, GiST for KNN-dominated columns, partial indexes when a hot statement always carries the same literal filter, composite btree_gist indexes, CLUSTER for range-heavy scans against an uncorrelated heap, redundant index drops, and rewrite advisories for queries no index can save. Each recommendation carries the DDL, the reason, an estimated index size, a modelled benefit, a confidence level and the statement fingerprints behind it.

Reach for it before an indexing sprint, or when you have a pg_stat_statements export and no idea where to start. A concrete example from the shipped fleet-tracking sample: against a 412-million-row vehicle_positions table it reports that 1,330,300 executions filter geom with ST_Contains and ST_DWithin against a column with no spatial index, estimates the resulting GiST index at 17.1 GB, and separately flags 32,400 executions of an ST_Distance comparison as a rewrite that no index will ever fix. It also names the tables the catalog snapshot did not describe, rather than guessing about them.

The cost model is documented rather than magic — PostgreSQL’s own default planner constants, plus a 5000× charge for a spatial predicate taken from the procost PostGIS declares. The README is candid about what that buys: on a calibration run the ranking held up well (Spearman ρ = 0.87 against measured savings) while individual speedup magnitudes ranged from 0.06× to 12× of the estimate. Treat a modelled speedup as “this is the biggest win available”, then confirm with EXPLAIN (ANALYZE, BUFFERS).

Background for its recommendations lives in advanced GiST indexing and optimization — particularly choosing a spatial index type, partial GiST indexes and composite spatial indexes. Applying the DDL it emits is a migration problem, covered in spatial schema migrations and evolution and concurrent index builds; getting a good workload export in the first place is covered in pg_stat_statements for spatial workloads.

geoalchemy-model-generator

Repository: github.com/postgis-python/geoalchemy-model-generator

Reflection tools that predate SQLAlchemy 2.0 emit old-style Column(...) declarations and know nothing about PostGIS. They lose the spatial type metadata that matters: geometry is opaque at the pg_attribute level, and the geometry type, SRID and coordinate dimension live in the geometry_columns and geography_columns views. A model that omits the SRID will happily insert rows in the wrong reference system.

This tool introspects a live PostGIS database — or a JSON description of one — and writes two files. models.py holds SQLAlchemy 2.0 declarative models in Mapped[...] / mapped_column(...) style, with GeoAlchemy2 Geometry(...) / Geography(...) columns carrying the real type, SRID and dimension, relationship(...) pairs with back_populates on both sides, and unique constraints, check constraints and non-trivial indexes in __table_args__. queries.py holds four typed helpers per indexed geometry table: ST_DWithin on geography, KNN ordering with <->, an && prefilter paired with ST_Intersects, and an envelope search. Both files are compiled before they are written, so the tool never emits source that cannot be imported.

Reach for it when adopting an ORM over an existing spatial schema, or when a hand-maintained model file has drifted from the database. A concrete example of the care it takes: a 3D road centreline column comes back as Geometry(geometry_type="LINESTRINGZ", srid=4326, dimension=3) — because geometry_columns reports a PointZ column as plain POINT with coord_dimension 3, and taking the type name at face value silently flattens every 3D column to 2D. And a table whose geometry column has no spatial index gets no query helpers at all, only a comment saying why and the CREATE INDEX to run, rather than helpers that would quietly sequential-scan.

The generated code is exactly the pattern described in SQLAlchemy and GeoAlchemy2 integration workflows, notably model mapping with GeoAlchemy2 and type coercion and serialization. Its unindexed-column warnings point at advanced GiST indexing and optimization, and regenerating models after a schema change belongs with spatial schema migrations and evolution.

postgis-fixtures

Repository: github.com/postgis-python/postgis-fixtures

Spatial test suites tend to fail in one of two ways. Either they have no data — three hand-written points near the origin, four rows in the table, every query getting a sequential scan, so the one thing the suite could have caught (a predicate the index cannot support) is the one thing it never sees. Or the data is random, so the tests are too, and assert len(rows) > 0 becomes the assertion.

This pytest plugin fixes both. A session-scoped fixture provides a database: an ephemeral postgis/postgis container via testcontainers, or a DSN you configure when a service container already exists, as in CI. Seeded generators produce clustered points, route-like linestrings and valid polygons in WGS 84 and reproject them to Web Mercator or a projected national grid — the same seed always yields byte-identical WKT, because coordinates are rounded to fixed precision before serialisation. Named datasets ship with it: cities, delivery_routes, service_areas, sensor_readings (timestamp-correlated, for BRIN-shaped tests) and edge_cases. The plugin also emits the DDL, including CREATE INDEX ... USING GIST, and a COPY-based bulk loader.

Reach for it when writing tests that must exercise real index behaviour rather than a four-row table scan. The edge-case catalogue is the concrete payoff: an antimeridian linestring whose naive ST_Envelope spans the globe, a polygon with an interior ring, a zero-length linestring, duplicate points, a non-NULL empty geometry, a SQL NULL geometry, and a wrong-SRID row — degree coordinates tagged as EPSG:3857, so distances come out near zero. That table’s geometry column is deliberately unconstrained, because a constrained column would reject the wrong-SRID row, which is the row most worth testing against.

The assertion helpers — assert_geometry_valid, assert_srid, assert_within_distance, assert_geometries_equal and assert_uses_index — fail with a message that says by how much, in what units and in which CRS. A distance failure, for example, reports “70,197.140 metre … exceeds the limit of 1,000.000 metre” and adds that a limit off by a factor of ~100,000 usually means the query is measuring degrees.

assert_uses_index is the direct link to advanced GiST indexing and optimization and query plan analysis with EXPLAIN; the queries the example suite exercises are the ones in mastering core spatial query patterns; the SRID assertions guard the operations described in in-place SRID reprojection; and the optional postgis_engine fixture connects it to SQLAlchemy and GeoAlchemy2 integration workflows.

st-function-cheatsheet

Repository: github.com/postgis-python/st-function-cheatsheet

The official PostGIS reference is complete and accurate, which is exactly the problem mid-query. It tells you that ST_DWithin(geometry, geometry, double precision) returns a boolean. It does not lead with the two things that cost you an afternoon: that ST_DWithin(geom, point, 500) on SRID 4326 means 500 degrees — matching the entire planet — while the same call on geography means 500 metres; and that whether a call uses the index is a property of how you wrote it, not of the function.

This is a curated dataset of 92 ST_* functions and operators, hand-written as YAML, with three interfaces over it. Each entry carries its signatures, return type and introducing PostGIS version, a plain-English summary, a SQL example together with the result it actually produces, a psycopg 3 snippet and a GeoAlchemy2 / SQLAlchemy 2.0 snippet, srid_notes covering the CRS gotchas, index_usage describing whether a GiST index can serve it as written, two or three common mistakes, and see_also cross-references.

Reach for it when you are about to write a predicate you only half-remember. A concrete example: the <-> entry states that it is 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, so use ST_DWithin there instead. Its listed mistakes include omitting the LIMIT, which makes the planner prefer a sort over the index scan and read every row. The --index-only filter narrows any listing to the functions a GiST index can actually serve: 17 of the 92, which is itself the lesson.

Search is from the terminal and tolerates typos (buffr finds ST_Buffer); build emits a single self-contained, searchable HTML file with no external requests, which you can open over file:// or drop on an intranet; validate schema-checks the dataset and exits 1 on failure, so it works as a CI gate. The README is explicit about provenance: signatures and versions are documentation-sourced, all 92 SQL examples were executed against PostGIS 3.4.3 / PostgreSQL 16, and the index_usage and srid_notes fields are editorial — worth confirming with EXPLAIN on your own version.

Entries link out to mastering core spatial query patterns for the &&, ST_DWithin and <-> behaviour; advanced GiST indexing and optimization for what makes a predicate indexable; SQLAlchemy and GeoAlchemy2 integration workflows for the GeoAlchemy2 snippets; and spatial schema migrations and evolution for the ST_Transform and SRID operations the notes warn about.

Using Them Together

The five tools cover a workflow rather than five unrelated jobs:

  • Generate models from the existing schema with geoalchemy-model-generator, and read its
  • Write tests against postgis-fixtures datasets, asserting index usage with assert_uses_index
  • Keep st-function-cheatsheet open while writing predicates, checking index_usage before
  • Capture EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) for slow queries and run postgis-explain-visualizer over the output; gate CI with --fail-on critical
  • Export pg_stat_statements periodically and run spatial-index-advisor across the whole
  • Verify every applied recommendation with EXPLAIN (ANALYZE, BUFFERS) before and after — every

Frequently Asked Questions

How do I install these tools?

Clone the repository and install its requirements.txt into a virtualenv, then run the tool as a module from the repository root. None of the five are published to PyPI, so there is no pip install form; the clone is the installation. postgis-fixtures additionally needs to be on your PYTHONPATH (or vendored into your tree) and enabled with pytest_plugins = ["postgis_fixtures.plugin"] in your root conftest.py.

Do these tools need access to my production database?

Mostly no. postgis-explain-visualizer and st-function-cheatsheet never connect to a database at all. spatial-index-advisor analyses files; only its optional collect subcommand touches a database, and a read-only role is enough. geoalchemy-model-generator can introspect a live database but also reads a JSON schema dump, so models can be regenerated by someone with no database access. postgis-fixtures needs a database by design, but starts a throwaway container rather than using yours.

Is spatial-index-advisor a replacement for EXPLAIN?

No, and it says so. It has no planner and no runtime feedback; its numbers come from a static cost model and exist to rank recommendations against each other. It tells you where to point EXPLAIN (ANALYZE, BUFFERS), and every change it proposes should be measured before and after. See query plan analysis with EXPLAIN.