Base de datos geoespacial en PostGIS: cómo estructurarla

Una base de datos geoespacial institucional no falla por falta de mapas: falla porque cada área guardó sus capas a su manera, con sistemas de referencia distintos, geometrías rotas y códigos escritos a mano. Este artículo resume cómo la ordenamos cuando el dato debe durar años y pasar por auditoría. Todo el código se puede ejecutar en una base vacía con PostgreSQL 16 y PostGIS 3.4.

¿Por qué empezar por esquemas por dominio?

Un solo esquema public con cuarenta tablas es el origen de casi todos los problemas posteriores: nadie sabe qué es catálogo, qué es dato de campo y qué es resultado de un proceso. Separar por dominio da orden, permisos por rol y respaldos selectivos.

En proyectos con bases institucionales de información técnica georreferenciada, como el caso SGIA, usamos una división sencilla:

• catalogos: listas controladas (coberturas, estados, tipos de unidad). Cambian poco y las lee todo el mundo.
• levantamiento: lo que se captura o se carga: puntos, recorridos, unidades cartográficas.
• auditoria: el historial de cambios, con permisos de solo inserción.
• publicacion: vistas y tablas derivadas que consumen visores y servicios OGC. Nada se edita aquí.

Con esta división, el rol de la aplicación escribe en levantamiento, el visor solo lee en publicacion y el equipo de calidad nunca toca catalogos sin pasar por un cambio controlado.

¿Qué sistema de referencia debe usar una base colombiana?

Fije uno solo para almacenar. En Colombia, el origen nacional es MAGNA-SIRGAS / Origen Nacional (CTM12), identificado como EPSG:9377 y expresado en metros. Medir áreas, distancias y traslapes en metros evita los errores de redondeo de los grados y coincide con la cartografía oficial.

La regla práctica es: se almacena en 9377, se intercambia en 4326 cuando el formato lo exige (GeoJSON, GPX, la mayoría de los visores web) y se transforma con ST_Transform solo en el borde. Declarar el SRID en el tipo de la columna, geometry(Point, 9377), hace que el motor rechace cualquier geometría con otro sistema.

¿Cómo se crea el modelo mínimo?

El siguiente script crea los esquemas, un catálogo, una tabla de puntos y una tabla de polígonos particionada por vigencia. Lo importante está en las restricciones: la geometría válida, la clave foránea al catálogo y las columnas de auditoría.

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE SCHEMA IF NOT EXISTS catalogos;
CREATE SCHEMA IF NOT EXISTS levantamiento;
CREATE SCHEMA IF NOT EXISTS auditoria;

CREATE TABLE catalogos.tipo_cobertura (
    codigo   text PRIMARY KEY,
    nombre   text NOT NULL,
    vigente  boolean NOT NULL DEFAULT true
);

INSERT INTO catalogos.tipo_cobertura (codigo, nombre) VALUES
    ('BOS', 'Bosque'),
    ('PAS', 'Pastos'),
    ('CUL', 'Cultivos');

CREATE TABLE levantamiento.punto_muestreo (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    codigo         text NOT NULL UNIQUE,
    cobertura      text NOT NULL REFERENCES catalogos.tipo_cobertura (codigo),
    precision_m    numeric(6,2) CHECK (precision_m >= 0),
    geom           geometry(Point, 9377) NOT NULL,
    creado_en      timestamptz NOT NULL DEFAULT now(),
    creado_por     text NOT NULL DEFAULT current_user,
    modificado_en  timestamptz,
    modificado_por text,
    CONSTRAINT ck_punto_geom_valida CHECK (ST_IsValid(geom))
);
CREATE INDEX ix_punto_geom ON levantamiento.punto_muestreo USING gist (geom);

CREATE TABLE levantamiento.unidad_cartografica (
    id             bigint GENERATED ALWAYS AS IDENTITY,
    vigencia       smallint NOT NULL,
    codigo         text NOT NULL,
    cobertura      text NOT NULL REFERENCES catalogos.tipo_cobertura (codigo),
    geom           geometry(MultiPolygon, 9377) NOT NULL,
    creado_en      timestamptz NOT NULL DEFAULT now(),
    creado_por     text NOT NULL DEFAULT current_user,
    modificado_en  timestamptz,
    modificado_por text,
    CONSTRAINT pk_unidad PRIMARY KEY (id, vigencia),
    CONSTRAINT uq_unidad_codigo UNIQUE (vigencia, codigo),
    CONSTRAINT ck_unidad_vigencia CHECK (vigencia BETWEEN 2000 AND 2100),
    CONSTRAINT ck_unidad_geom_valida CHECK (ST_IsValid(geom) AND NOT ST_IsEmpty(geom))
) PARTITION BY LIST (vigencia);

CREATE TABLE levantamiento.unidad_cartografica_2025
    PARTITION OF levantamiento.unidad_cartografica FOR VALUES IN (2025);
CREATE TABLE levantamiento.unidad_cartografica_2026
    PARTITION OF levantamiento.unidad_cartografica FOR VALUES IN (2026);

CREATE INDEX ix_unidad_geom
    ON levantamiento.unidad_cartografica USING gist (geom);

CREATE OR REPLACE FUNCTION auditoria.marcar_modificacion()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.modificado_en  := now();
    NEW.modificado_por := current_user;
    RETURN NEW;
END;
$$;

CREATE TRIGGER tg_punto_modificacion
    BEFORE UPDATE ON levantamiento.punto_muestreo
    FOR EACH ROW EXECUTE FUNCTION auditoria.marcar_modificacion();

¿Cómo se valida la calidad del dato que ya existe?

Las restricciones protegen lo que entra desde hoy. Para lo heredado hay que medir antes de corregir. Si migra tablas antiguas, cree las restricciones como NOT VALID y use esta consulta para listar, una por una, las filas que no cumplen:

-- Geometrías inválidas, vacías o con SRID distinto del oficial
SELECT 'unidad_cartografica' AS tabla, id, vigencia, codigo,
       ST_IsValidReason(geom) AS motivo
FROM levantamiento.unidad_cartografica
WHERE NOT ST_IsValid(geom) OR ST_IsEmpty(geom) OR ST_SRID(geom) <> 9377
UNION ALL
-- Traslapes entre unidades de la misma vigencia
SELECT 'unidad_cartografica', a.id, a.vigencia, a.codigo,
       'traslape con ' || b.codigo || ': '
       || round(ST_Area(ST_Intersection(a.geom, b.geom))::numeric, 2) || ' m2'
FROM levantamiento.unidad_cartografica a
JOIN levantamiento.unidad_cartografica b
  ON a.vigencia = b.vigencia AND a.id < b.id
 AND a.geom && b.geom AND ST_Overlaps(a.geom, b.geom)
ORDER BY 1, 3, 4;

El operador && usa el índice GiST para descartar pares lejanos antes de calcular la intersección; sin él, la comparación crece con el cuadrado de los registros. Para corregir lo hallado, ST_MakeValid repara muchas geometrías, pero revise el resultado: a veces convierte un polígono en una colección y cambia el tipo.

Una vez limpia la tabla, la consulta de resumen sirve para el tablero de control y para el informe de entrega:

SELECT u.vigencia,
       c.nombre AS cobertura,
       count(*) AS unidades,
       round((sum(ST_Area(u.geom)) / 10000)::numeric, 2) AS hectareas
FROM levantamiento.unidad_cartografica u
JOIN catalogos.tipo_cobertura c ON c.codigo = u.cobertura
GROUP BY u.vigencia, c.nombre
ORDER BY u.vigencia, hectareas DESC;

¿Qué decisiones de diseño conviene tomar desde el inicio?

La tabla resume las decisiones que más cuesta cambiar después y el criterio que aplicamos.

Decisión	Criterio recomendado	Por qué
Esquemas	Uno por dominio: catálogos, levantamiento, auditoría, publicación	Permisos, respaldos y responsabilidades claras
SRID de almacenamiento	9377 (MAGNA-SIRGAS / Origen Nacional) en una sola columna geom	Mediciones en metros; el motor rechaza otros sistemas
Tipo de geometría	Declarado en la columna: Point, MultiPolygon, MultiLineString	Evita mezclar polígonos y multipolígonos en una misma capa
Índices	GiST sobre geom; B-tree sobre llaves y campos de filtro frecuentes	El GiST acelera &&, ST_Intersects y ST_DWithin
Calidad	CHECK con ST_IsValid, claves foráneas a catálogos, NOT NULL	El error se rechaza al entrar, no se descubre meses después
Vigencias	Particionado por lista o rango sobre vigencia	Archivar un año completo es desvincular una partición
Trazabilidad	creado_en/por y modificado_en/por más una tabla de historial	Responde quién cambió qué, cuándo y con qué valor anterior
Catálogos	Tablas con código estable y bandera vigente, no listas de texto libre	Un valor retirado no se borra: se deja de ofrecer

Dos advertencias de experiencia. Primera: en una tabla particionada, la llave primaria debe incluir la columna de partición, por eso aquí es (id, vigencia). Segunda: las columnas de auditoría no reemplazan un historial completo; para registrar el valor anterior de cada campo cree una tabla en auditoria alimentada por un disparador, con permiso de solo inserción.

¿Qué pasa con las capas pesadas y la publicación?

Una base bien modelada puede recibir cartografía de gran tamaño. En el caso SGIA la ingesta se hizo por flujo continuo hacia PostGIS, con memoria constante, y la publicación por teselas vectoriales con caché, lo que redujo el tiempo de respuesta por tesela de 2,5 s a 7 ms. Ese rendimiento no salió de hardware: salió de índices GiST bien usados, de un SRID único y de no recalcular lo que ya se puede guardar.

Para publicar, cree vistas en el esquema publicacion que transformen a 4326 con ST_Transform(geom, 4326) y expongan solo las columnas necesarias. Así el visor nunca toca las tablas de edición.

¿Cómo se protegen los datos y se asegura la continuidad?

Un buen modelo también se defiende de los accidentes. Cree roles por función y no por persona: uno de lectura para el visor, otro de escritura para la aplicación y otro de administración que casi nunca se usa. Conceda permisos por esquema, de modo que un error en la aplicación no pueda borrar un catálogo. Programe copias de seguridad con pg_dump o con archivado continuo y, sobre todo, pruebe restaurarlas: una copia que nunca se restauró es una hipótesis. Documente cada tabla y columna con COMMENT ON y mantenga un diccionario de datos; quien llegue después de usted lo agradecerá. Y registre los cambios del modelo como migraciones versionadas, no como scripts sueltos, porque el esquema también es un producto que evoluciona.

¿Cómo empezar con su propia base?

Haga tres cosas esta semana: inventaríe las capas y sus sistemas de referencia, ejecute una consulta de validación como la anterior sobre las dos tablas más usadas y defina los catálogos que hoy viven como texto libre. Con ese diagnóstico se decide si basta con ajustes o si conviene una migración por etapas.

Si su entidad o empresa necesita ordenar, migrar o modelar una base geoespacial, conozca nuestro servicio de estructuración de bases de datos y cuéntenos su caso: le respondemos con un alcance claro y un plan por etapas.