1) Asegurar clave primaria en metadatos (solo si no existe) metadatos

La tabla metadatos centraliza la descripción, fuente y responsable de cada dataset. Primero garantizamos que el campo id sea único para poder referenciarlo desde otras tablas.

ALTER TABLE metadatos
ADD CONSTRAINT pk_metadatos PRIMARY KEY (id); 

Si tu tabla ya tiene una PK, este paso no hará cambios. Si no la tiene, quedará creada.

2) Agregar id_metadatos en cada tabla y crear las FKs

Luego añadimos la columna id_metadatos a las tablas temáticas y definimos la restricción de clave foránea a metadatos(id). Usamos ON DELETE SET NULL para no borrar registros principales si se elimina un metadato.

lotes_limpios

ALTER TABLE lotes_limpios
    ADD COLUMN id_metadatos integer,
    ADD CONSTRAINT fk_lotes_metadatos FOREIGN KEY (id_metadatos)
        REFERENCES metadatos(id) ON DELETE SET NULL;

vias

ALTER TABLE vias
    ADD COLUMN id_metadatos integer,
    ADD CONSTRAINT fk_vias_metadatos FOREIGN KEY (id_metadatos)
        REFERENCES metadatos(id) ON DELETE SET NULL;

datos_distrito

ALTER TABLE datos_distrito
    ADD COLUMN id_metadatos integer,
    ADD CONSTRAINT fk_distrito_metadatos FOREIGN KEY (id_metadatos)
        REFERENCES metadatos(id) ON DELETE SET NULL;

actualizaciones_campo

ALTER TABLE actualizaciones_campo
    ADD COLUMN id_metadatos integer,
    ADD CONSTRAINT fk_actu_metadatos FOREIGN KEY (id_metadatos)
        REFERENCES metadatos(id) ON DELETE SET NULL;

3) Poblar la relación con los registros correctos

Si la tabla metadatos tiene un campo nombre_tabla (p.ej. "lotes_limpios", "vias"), podemos completar automáticamente los id_metadatos en cada tabla.

UPDATE lotes_limpios
SET id_metadatos = m.id
FROM metadatos m
WHERE m.nombre_tabla = 'lotes_limpios';

UPDATE vias
SET id_metadatos = m.id
FROM metadatos m
WHERE m.nombre_tabla = 'vias';

UPDATE datos_distrito
SET id_metadatos = m.id
FROM metadatos m
WHERE m.nombre_tabla = 'datos_distrito';

UPDATE actualizaciones_campo
SET id_metadatos = m.id
FROM metadatos m
WHERE m.nombre_tabla = 'actualizaciones_campo';

Si tienes múltiples metadatos por capa (p. ej. versiones), ajusta los WHERE para elegir el registro correcto (por fecha, estado, etc.).

4) Consultas útiles con metadatos

a) Lotes con sus metadatos

SELECT 
    l.id,
    l.geom,
    l.id_predio,
    l."DISTRITO",
    l."MANZANO",
    l."LOTE",
    l.propietari,
    l.uso_suelo,
    l.valid,
    m.nombre_tabla,
    m.descripcion AS descripcion_metadatos,
    m.fuente,
    m.responsable
FROM lotes_limpios l
LEFT JOIN metadatos m ON l.id_metadatos = m.id;

b) Vías residenciales con fuente y responsable

SELECT 
    v.id,
    v.geom,
    v.highway,
    v.name,
    m.fuente,
    m.responsable
FROM vias v
LEFT JOIN metadatos m ON v.id_metadatos = m.id
WHERE v.highway = 'residential';

c) Lotes + datos distritales + responsable

SELECT 
    l.id_predio,
    l."DISTRITO",
    d.nombre_y_apellidos,
    d.tipo_inmueble,
    d.supconstruccion_m2,
    d.uso_de_edificacion,
    m.responsable
FROM lotes_limpios l
LEFT JOIN datos_distrito d 
    ON l.id_predio = d.id_predio
LEFT JOIN metadatos m 
    ON d.id_metadatos = m.id;

d) Actualizaciones de campo con su metadato

SELECT 
    a.id,
    a.id_predio,
    a.fecha_actualizacion,
    a.descripcion,
    a.fotografia,
    m.nombre_tabla,
    m.responsable
FROM actualizaciones_campo a
LEFT JOIN metadatos m ON a.id_metadatos = m.id
ORDER BY a.fecha_actualizacion DESC;

e) Relación espacial entre lotes y vías

SELECT 
    l.id_predio,
    l.geom AS lote_geom,
    v.name AS via_nombre,
    v.highway
FROM lotes_limpios l
JOIN vias v 
    ON ST_Intersects(l.geom, v.geom);

f) Lotes y vías que se tocan

SELECT 
    l.id_predio,
    l."DISTRITO",
    v.name AS nombre_via,
    v.highway AS tipo_via
FROM lotes_limpios l
JOIN vias v 
    ON ST_Intersects(l.geom, v.geom)
WHERE l.id_predio = '10102';

g) Lotes y vías cercanas (tolerancia de 10 m)

SELECT 
    l.id_predio,
    l."DISTRITO",
    v.name AS nombre_via,
    v.highway AS tipo_via,
    ST_Distance(l.geom, v.geom) AS distancia_m
FROM lotes_limpios l
JOIN vias v 
    ON ST_DWithin(l.geom, v.geom, 10)  -- distancia máxima 10 m
WHERE l.id_predio = '10102'
ORDER BY distancia_m ASC; 

h) Buscar lotes que colinden con una calle específica

SELECT 
    l.id_predio,
    l."DISTRITO",
    v.name AS nombre_via
FROM lotes_limpios l
JOIN vias v 
    ON ST_Intersects(l.geom, v.geom)
WHERE v.name ILIKE '%Calle Murillo%';

i) Buscar lotes por distrito y nombre de vía

SELECT 
    l.id_predio,
    l."DISTRITO",
    v.name AS nombre_via
FROM lotes_limpios l
JOIN vias v 
    ON ST_Intersects(l.geom, v.geom)
WHERE l."DISTRITO" = 'Distrito 1' 
  AND v.name ILIKE '%colon%';

Conclusión

Con este patrón, cada capa queda documentada y trazable. Las claves primarias y foráneas aseguran integridad; los joins enriquecen reportes y auditorías; y la combinación con funciones espaciales de PostGIS permite análisis temáticos y geográficos coherentes en un catastro moderno.