PostGIS

PostGIS adds geometry and geography types to PostgreSQL, along with spatial indexes, several hundred spatial functions, and coordinate transformation. It is the single strongest argument for choosing Postgres over any other open-source relational database if the data has a location.

Why a real spatial type matters

The naive alternative is latitude and longitude columns. That works for storing points and fails at every question worth asking. “Which stations are within 50 km of this one” becomes a full table scan with distance computed per row. “Which measurements fall inside this catchment” is not expressible at all.

PostGIS makes these index-supported queries. A GiST index on a geometry column lets the planner eliminate most of the table before computing anything, which is the difference between a query that returns immediately and one that scans everything. For any spatial dataset beyond a few thousand rows this is the whole game.

Projections are handled properly

Every geometry carries an SRID identifying its coordinate reference system, and ST_Transform converts between systems using the same proj library everything else in the geospatial stack uses.

The SRID being attached to the data rather than assumed is what prevents the quiet failure mode: mixing geometries in different systems raises an error instead of silently computing a meaningless distance. Given how easily a projection mismatch produces plausible wrong answers — the same problem OpenLayers handles on the display side — having the database refuse is worth a great deal.

The related trap it removes: distance in degrees is not distance. A degree of longitude is about 111 km at the equator and near zero at the poles, so computing distances in a lat-lon system gives values that vary with latitude. Either use the geography type, which does spherical calculations in metres, or transform to a projected system first. Choosing between them is a real decision — geography is correct over long distances and slower, projected coordinates are fast and only valid within the projection’s zone.

In a data portal

For a data portal serving environmental data it does the work the frontend cannot: filtering, clipping, and aggregating spatially before anything reaches the browser. Sending a full dataset to the client and filtering there stops working almost immediately.

SQLModel does not know about PostGIS types, so spatial columns need explicit handling through GeoAlchemy2.

See also: PostgreSQL, spatial reference systems, QGIS for the desktop side, and OpenLayers for display.