PostGIS for cadastre: a getting started guide

PostGIS is the geospatial extension for PostgreSQL and, in our opinion, the best database for cadastral management in Colombia. At GEOSAT we use it as the data engine in Terraes and in most of our GIS projects. This guide covers the essentials to get started.

What is PostGIS

PostGIS adds geometric and geographic data types to PostgreSQL, along with hundreds of spatial functions. This means you can store, query, and analyze geospatial information directly in the database, without relying on shapefiles or proprietary geodatabases.

The advantages for cadastre are clear:

• Multi-user: multiple technicians can work simultaneously on the same database
• Referential integrity: relationships between parcels, owners, and rights with real database constraints
• Spatial queries: questions like "which parcels fall within this risk zone?" are answered with SQL
• No licensing fees: PostGIS is free software, just like PostgreSQL

Basic installation

On a Linux server (Ubuntu/Debian):

sudo apt update
sudo apt install postgresql postgis
sudo -u postgres createdb cadastre
sudo -u postgres psql -d cadastre -c "CREATE EXTENSION postgis;"

On Windows, the easiest way is to install PostgreSQL from the official EnterpriseDB installer and include the PostGIS extension from the Stack Builder.

Once installed, you can verify the version:

SELECT PostGIS_Full_Version();

Data structure for cadastre

A basic cadastral schema in PostGIS could look like this:

CREATE TABLE parcels (
  id SERIAL PRIMARY KEY,
  parcel_code VARCHAR(30) NOT NULL UNIQUE,
  land_area NUMERIC(12,2),
  geom GEOMETRY(MultiPolygon, 9377)
);

CREATE TABLE buildings (
  id SERIAL PRIMARY KEY,
  parcel_id INTEGER REFERENCES parcels(id),
  floor_count INTEGER,
  built_area NUMERIC(12,2),
  geom GEOMETRY(MultiPolygon, 9377)
);

CREATE TABLE owners (
  id SERIAL PRIMARY KEY,
  parcel_id INTEGER REFERENCES parcels(id),
  name VARCHAR(200),
  document_id VARCHAR(20),
  right_type VARCHAR(50)
);

SRID 9377 corresponds to MAGNA-SIRGAS / Origen Nacional, the official reference system for cadastre in Colombia. Using the correct SRID from the start is critical to avoid reprojection issues.

Essential spatial queries

Calculate parcel area

SELECT parcel_code, ST_Area(geom) AS area_m2
FROM parcels
WHERE parcel_code = '05001000100010001';

Find parcels within a zone

SELECT p.parcel_code
FROM parcels p, risk_zones z
WHERE z.name = 'Medellin river flood zone'
AND ST_Contains(z.geom, p.geom);

Detect overlaps between parcels

SELECT a.parcel_code AS parcel_a,
       b.parcel_code AS parcel_b,
       ST_Area(ST_Intersection(a.geom, b.geom)) AS overlap_area
FROM parcels a, parcels b
WHERE a.id < b.id
AND ST_Intersects(a.geom, b.geom)
AND NOT ST_Touches(a.geom, b.geom);

This last query is especially useful for topological quality control, one of IGAC's requirements for cadastral information delivery.

Calculate total area by district

SELECT d.name AS district,
       COUNT(p.id) AS num_parcels,
       SUM(ST_Area(p.geom)) AS total_area_m2
FROM parcels p
JOIN districts d ON ST_Contains(d.geom, ST_Centroid(p.geom))
GROUP BY d.name
ORDER BY num_parcels DESC;

Connecting to QGIS

QGIS connects natively to PostGIS. To set up the connection:

1. Open QGIS and go to Layer > Add Layer > Add PostGIS Layer
2. Create a new connection with your server details (host, port, database, user)
3. Connect and select the tables you want to load as layers

Once connected, any edits made in QGIS are saved directly to the database. No need to export, import, or synchronize files.

This integration is what we use in Terraes: field technicians work in QGIS connected to the central PostGIS database, and changes are immediately available to the entire team.

Why GEOSAT chose PostGIS for Terraes

When we designed Terraes, we evaluated several options: Esri File Geodatabase, GeoPackage, Oracle Spatial, and PostGIS. We chose PostGIS because:

• Zero licensing cost: critical for municipalities with limited budgets
• Proven performance: we manage databases with hundreds of thousands of parcels without issues
• LADM-COL compatibility: tools like ili2db allow creating the LADM-COL schema directly in PostGIS
• Open ecosystem: integrates with QGIS, GeoServer, Django, and any tool that supports standard SQL

PostGIS is not the only valid option, but for multipurpose cadastre in Colombia it is, in our experience, the most balanced choice between cost, performance, and flexibility.

If you want to learn how we implement PostGIS in cadastral projects, contact us.