Spatial Databases: PostGIS & PostgreSQL

A folder of GIS files is a filing cabinet: it holds things, but it does nothing. It cannot stop two people saving over each other, cannot guarantee that roads means the same thing everywhere, and cannot answer a question - it can only hand you a drawer. A database is a librarian sitting on top of the same shelves. It holds each dataset once, controls who may change it, keeps the changes consistent, and - crucially - answers questions you pose in a query language instead of making you open and eyeball every file.

A spatial database is an ordinary database that also understands geometry. Alongside columns for plot_id and area_m2, a table has a geom column holding the actual shape of each feature. Because the shape lives in the table next to its attributes, you can ask questions that mix the two - which residential plots lie inside the flood polygon? - in a single sentence, and the database does the geometry and the filtering together.

PostgreSQL is a mature, free, open-source relational database used by banks, governments and start-ups the world over. PostGIS is an extension you switch on inside PostgreSQL that adds the geometry type and roughly a thousand spatial functions. Together they are the de facto standard for serious open-source GIS work; the current stable PostGIS is version 3.6.2. It is not a toy alternative to the paid tools - QGIS talks to it natively through the DB Manager, and ArcGIS Pro can connect to it as a database or read it through a query layer, so the same database can serve a mixed studio.

Esri's own answer to the same need is the enterprise geodatabase, which layers Esri's data model on top of a database like PostgreSQL or SQL Server. The idea is identical: one managed store, many editors, versioned truth. For learning - and for a great deal of professional work - PostGIS costs nothing and teaches you the concepts that transfer everywhere.

A fair question: if I only need one file I can hand to a colleague, do I need a server? Often not. The OGC GeoPackage - a single .gpkg file that is itself a SQLite database - is the honest middle ground: it gives you many layers and even spatial SQL in one portable file, without running a server. Reach for PostGIS when data is shared, concurrent or large; reach for GeoPackage when it is portable and personal.

SQL (Structured Query Language) is the plain-ish sentence you use to question any database: SELECT these columns FROM this table WHERE some condition holds. Spatial SQL adds functions - by convention prefixed ST_ for spatial type - that take geometries as arguments.

Counting is a plain query: SELECT count(*) FROM plots WHERE use = 'Resi'. Measuring uses a spatial function: SELECT plot_id, ST_Area(geom) FROM plots gives each plot's area in the units of its CRS - which is exactly why you keep spatial data in a projected, metre-based CRS such as EPSG:32643 rather than degrees. The move that files simply cannot make is the spatial join - relating two tables by where they are rather than by a shared id. SELECT p.plot_id FROM plots p, flood f WHERE ST_Intersects(p.geom, f.geom) returns every plot a flood polygon touches, with no common key between the tables at all. Other everyday relationships read just as clearly: ST_Within, ST_Contains, ST_Distance, ST_Buffer. You are writing the same overlay questions from Module 4, but now in a sentence you can save, share and re-run.

If a spatial join had to test the exact geometry of every plot against every flood polygon, a city dataset would crawl. It does not, because of a spatial index. When you build one (PostGIS uses a GiST index), the database first wraps every feature in its bounding box - the smallest upright rectangle that contains it - which is trivially cheap to compare. A query narrows to the handful of features whose boxes overlap the area of interest, and only then does the expensive exact-geometry test, on that short-list. The result is the same; the work is a fraction.

The practical lesson is small and permanent: after loading a big table, create a spatial index on its geom column before you run analysis. In QGIS DB Manager and in enterprise geodatabases this is a one-click or one-line step, and it is the difference between a query that returns while you blink and one you abandon.

None of this makes files wrong. A single designer analysing one site is faster in a GeoPackage than standing up a server; formats like GeoPackage and GeoTIFF are the right unit for sharing and archiving results. The signal to move to a full database is organisational, not technical: more than one person editing the same data; a need to record who changed what and when; datasets too large to open comfortably; or a web map that must read live from one authoritative source. In India this is exactly the direction statutory data is heading - AMRUT and NUIS master-plan programmes build city geo-databases precisely so that many departments query one shared store rather than emailing shapefiles around. Learning PostGIS is learning the shape of that future.