PostGIS

What it is

PostGIS is an open-source add-on for the PostgreSQL database server
that lets a column hold a shape, such as a delivery address, a river
or a property boundary, and lets ordinary SQL ask where those shapes
sit in relation to one another. It began as a research project at
Refractions Research, a GIS and database consultancy, and is now
developed and released as an open-source project of its own.

It is installed per database with one statement, `CREATE EXTENSION
postgis;`, and three things arrive with it.

  * Spatial types. `geometry` describes shapes on a flat plane in
whatever coordinate system the data was captured in. `geography`
describes them on the curved surface of the Earth and measures in
metres.

  * Spatial functions. A large family of functions whose names start
with `ST_`: `ST_Intersects` and `ST_Contains` for relationships,
`ST_Area` and `ST_Length` for measurement, `ST_Transform` for moving
between coordinate systems, `ST_AsGeoJSON` for handing results to a
web map.

  * Spatial indexes. PostGIS builds an R-tree on top of PostgreSQL's
GiST framework, and also supports SP-GiST and block-range indexes
for particular shapes of data.

The shapes follow the Simple Features model published by the Open
Geospatial Consortium and adopted as ISO 19125: points, line strings
and polygons, their multi-part versions, and collections of mixed
types. Every stored value carries an SRID, a number that points to a
row in the `spatial_ref_sys` table and says what the coordinates
mean. SRID 4326 means longitude and latitude in degrees on the WGS
84 datum, the system GPS reports in. A projected system, by
contrast, measures in metres east and north of a fixed origin and is
accurate only within the region it was designed for.

Raster data, topology and some 3D operations come as separate
extensions, `postgis_raster`, `postgis_topology` and
`postgis_sfcgal`, so a database carries only the parts it uses.

Why it matters

The decision PostGIS forces is where location data should live.
Without it, coordinates tend to sit in two numeric columns while the
actual spatial work happens somewhere else: in application code that
loops over rows to compute distances, in a desktop GIS that someone
exports to every Monday, or in a separate geographic service with
its own copy of the data. Each of those copies drifts. With PostGIS,
“which customers are within two kilometres of a depot that has
stock” is one query that joins stock levels, customer records and
shapes, inside one transaction, under the same backups and the same
permissions as everything else in the database.

That also means spatial data gets the guarantees the rest of the
schema already has. A check constraint can reject an invalid polygon
at insert time. Row-level security can limit a regional team to the
parcels in its own region. A failed import rolls back instead of
leaving half a layer behind.

The second decision is geometry or geography, and the PostGIS manual
is direct about it. Data that stays within a city or a country is
usually best stored as `geometry` in a projection chosen for that
area, because the full function set is available and the arithmetic
is fast. Data that spans continents, or whose users ask for
distances in metres between points anywhere on Earth, is easier to
get right as `geography`, which does the curved-surface maths itself
at the cost of a smaller set of functions.

And sometimes the answer is that PostGIS is more than the job needs.
An app that only stores a latitude and longitude per shop and drops
pins on a map can live with two numeric columns. PostGIS earns its
place once the questions are about distance, containment or overlap,
or once the data includes lines and polygons rather than single
points.

What it looks like in practice

A map application serving real-time spatial queries to many users
ran on a Microsoft SQL Server database, and latency was hurting both
the map and the services behind it. The fix was a move to PostgreSQL
with PostGIS, with a schema designed for spatial data, GiST and
SP-GiST indexes on the geometry and geography columns, and
constraints on the spatial coordinates. More than 50 million spatial
records went through ETL pipelines that transformed them to
EPSG:4326 on the way in. Average response time, 2.5 seconds on the
old database, dropped below 250 milliseconds once PostGIS answered
the queries, a tenfold improvement, and backend CPU load fell by
65%. That is PostGIS as the reason for a migration.

It also works as the engine of a clean-up that began with data
rather than speed. A large geospatial analytics effort turned up
more than 1,000 inconsistencies across geographic and time-series
datasets. GDAL, QGIS and ArcGIS were used to find and correct the
spatial errors, while PostGIS ran the spatial queries and the
spatial indexing, with PL/pgSQL procedures transforming the data
inside PostgreSQL and the cleaned result shown in Mapbox. Spatial
query run times fell by 35%.

Where it goes wrong

Distances come back as tiny decimals. A query asks how far apart two
towns are and gets `0.08`. The column is `geometry` in SRID 4326, so
the answer is in degrees, and a degree of longitude shrinks from
about 111 kilometres at the equator to nothing at the poles. Cast to
`geography` for an answer in metres, or use `ST_Transform` to move
the data into a projected system suited to the area before
measuring.

A radius search reads every row. The table has a spatial index, yet
the plan shows a sequential scan. The usual cause is a filter
written as `ST_Distance(a, b) < 500`. `ST_Distance` is absent from
the manual's list of functions that use the index on their own,
while `ST_DWithin` is on it. Wrapping the indexed column in a cast
or a transform has the same effect, because an index on `geom` says
nothing about `geom::geography`.

```sql
i-- a geography column, indexed as stored, measured in metres		engineer.company	70
CREATE INDEX depots_geog_idx ON depots USING GIST (geog);

SELECT id FROM depots
WHERE ST_DWithin(geog, ST_MakePoint(12.57, 55.68)::geography, 500);
```

If the data has to stay in a 4326 `geometry` column, either add a
`geography` column like this one and index it, or create the index
on the same expression the query uses. Run `ANALYZE` after a bulk
load so the planner knows the index is worth taking.

Points land in the sea. Every shape keeps its pattern but sits in
the wrong place, often on another continent. Two mistakes produce
this. `ST_SetSRID` only relabels a geometry and leaves its numbers
alone, so using it where `ST_Transform` was meant stamps metres as
degrees. And longitude and latitude were swapped somewhere upstream:
PostGIS, like GeoJSON, expects x before y, longitude before
latitude. Use `ST_SetSRID` only to declare what the numbers already
are, `ST_Transform` to convert them, and test every import with one
point whose location you know.

Area and overlap calculations throw errors. An import from a
shapefile succeeds, and later `ST_Intersection` fails with a
topology exception. PostGIS stores invalid geometry on purpose, so
it can be found and repaired, and a polygon whose boundary crosses
itself is invalid. Check with `ST_IsValid`, repair with
`ST_MakeValid`, which keeps every input vertex, and add a check
constraint so that a bad shape is refused at the door instead of
discovered in a report.

Further reading

  * PostGIS Documentation — the project's own index to the manual,
the FAQ and its training material.

  * Data Management — geometry and geography, SRIDs, validity rules
and the three index types, as the manual defines them.

  * Spatial Indexing — the workshop chapter on bounding boxes and
the functions that use an index without being told.

  * ST_DWithin — the reference page for the distance test, with its
units for each type.

  * Simple Feature Access – Part 1 — the OGC standard behind the
geometry types PostGIS stores.

hThis page on the web	URL:https://engineer.company/glossary/postgis/	engineer.company	70
