Use this file to discover all available pages before exploring further.
GeoViable uses PostgreSQL 15+ with PostGIS 3.4+, running inside the geoviable-db container (image: postgis/postgis:15-3.4). All geometries are stored in EPSG:25830 (ETRS89 / UTM zone 30N) regardless of the coordinate reference system of the upstream source data. The schema is initialized automatically on first container creation by the script at backend/initdb/01_init.sql.
CREATE EXTENSION IF NOT EXISTS postgis;CREATE EXTENSION IF NOT EXISTS postgis_topology;
Both extensions must be present before any table DDL is executed. The initialization script creates them with IF NOT EXISTS so the command is safe to re-run.
Every attempt to refresh a layer — whether successful, failed, or skipped — is recorded in this table. It provides a full audit trail and is used by the update script to retrieve the SHA-256 hash of the last successful download, enabling the pipeline to skip redundant re-downloads of unchanged data.
CREATE TABLE IF NOT EXISTS layer_update_log ( id SERIAL PRIMARY KEY, layer_name VARCHAR(100) NOT NULL, -- Target table name (e.g. 'red_natura_2000') status VARCHAR(20) NOT NULL, -- 'success' | 'failed' | 'skipped' started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), finished_at TIMESTAMPTZ, records_loaded INTEGER, -- NULL if update failed or was skipped source_url TEXT, -- Download URL used error_message TEXT, -- NULL on success file_hash VARCHAR(64) -- SHA-256 hex digest of the downloaded ZIP);CREATE INDEX IF NOT EXISTS idx_lul_layer_name ON layer_update_log(layer_name);CREATE INDEX IF NOT EXISTS idx_lul_started_at ON layer_update_log(started_at DESC);
Stores Red Natura 2000 ZEPA and LIC/ZEC protected areas. Source data arrives in EPSG:4258 and is reprojected to EPSG:25830 before insertion.
CREATE TABLE IF NOT EXISTS red_natura_2000 ( id SERIAL PRIMARY KEY, codigo VARCHAR(20) NOT NULL, -- Site code, e.g. 'ES1110001' nombre VARCHAR(255) NOT NULL, -- Official site name tipo VARCHAR(10) NOT NULL, -- 'ZEPA' | 'LIC' | 'ZEC' superficie_ha NUMERIC(12, 2), -- Official area in hectares geom GEOMETRY(MultiPolygon, 25830) NOT NULL);CREATE INDEX IF NOT EXISTS idx_rn2000_geom ON red_natura_2000 USING GIST (geom);CREATE INDEX IF NOT EXISTS idx_rn2000_tipo ON red_natura_2000(tipo);
Flood zone polygons for T100 (100-year) and T500 (500-year) return periods. Both periods share the same table, distinguished by periodo_retorno. A dedicated index on this column supports fast period-specific queries.
CREATE TABLE IF NOT EXISTS zonas_inundables ( id SERIAL PRIMARY KEY, periodo_retorno VARCHAR(10) NOT NULL, -- 'T100' | 'T500' nivel_peligrosidad VARCHAR(50), -- Hazard level if available in source demarcacion VARCHAR(100), -- Hydrographic demarcation geom GEOMETRY(MultiPolygon, 25830) NOT NULL);CREATE INDEX IF NOT EXISTS idx_zi_geom ON zonas_inundables USING GIST (geom);CREATE INDEX IF NOT EXISTS idx_zi_periodo ON zonas_inundables(periodo_retorno);
Hydraulic Public Domain polygons covering river channels (cauce), banks (ribera), and margins (margen). The demarcacion column is used during the load pipeline to filter records to the Galicia-Costa and Miño-Sil hydrographic demarcations.
CREATE TABLE IF NOT EXISTS dominio_publico_hidraulico ( id SERIAL PRIMARY KEY, tipo VARCHAR(50), -- 'cauce' | 'ribera' | 'margen' nombre_cauce VARCHAR(255), -- River or stream name categoria VARCHAR(100), -- Segment classification demarcacion VARCHAR(100), -- Hydrographic demarcation geom GEOMETRY(MultiPolygon, 25830) NOT NULL);CREATE INDEX IF NOT EXISTS idx_dph_geom ON dominio_publico_hidraulico USING GIST (geom);
Traditional livestock routes stored as MultiLineString features. Because the legal extent of a vía pecuaria is determined by its anchura_legal_m (legal width), the spatial analysis service buffers the geometry at query time rather than storing a polygon.
CREATE TABLE IF NOT EXISTS vias_pecuarias ( id SERIAL PRIMARY KEY, nombre VARCHAR(255), -- Route name tipo_via VARCHAR(100), -- 'cañada' | 'cordel' | 'vereda' | 'colada' anchura_legal_m NUMERIC(6, 2), -- Legal width in metres longitud_m NUMERIC(12, 2), -- Total route length in metres estado_deslinde VARCHAR(50), -- 'deslindada' | 'sin_deslindar' | 'parcial' municipio VARCHAR(100), provincia VARCHAR(50), geom GEOMETRY(MultiLineString, 25830) NOT NULL);CREATE INDEX IF NOT EXISTS idx_vp_geom ON vias_pecuarias USING GIST (geom);
vias_pecuarias is the only table that stores linear geometries (MultiLineString). When computing intersection with a parcel, the analysis service applies ST_Buffer(vp.geom, vp.anchura_legal_m / 2) to generate a virtual corridor polygon whose width equals the legal width of the route. Overlap percentage and intersection area are then computed against this buffered polygon.
National and regional protected natural spaces. An index on categoria supports filtering by protection type (e.g. returning only national parks).
CREATE TABLE IF NOT EXISTS espacios_naturales_protegidos ( id SERIAL PRIMARY KEY, codigo VARCHAR(20), -- Official ENP code nombre VARCHAR(255) NOT NULL, -- Space name categoria VARCHAR(100) NOT NULL, -- 'Parque Nacional' | 'Parque Natural' | 'Reserva' | etc. subcategoria VARCHAR(100), -- Sub-category if applicable superficie_ha NUMERIC(12, 2), -- Official area in hectares geom GEOMETRY(MultiPolygon, 25830) NOT NULL);CREATE INDEX IF NOT EXISTS idx_enp_geom ON espacios_naturales_protegidos USING GIST (geom);CREATE INDEX IF NOT EXISTS idx_enp_categoria ON espacios_naturales_protegidos(categoria);
Surface water bodies from PHC 2022-2027. Rivers are stored as LineString and lakes/reservoirs as Polygon in the source Shapefile, so this table uses the generic Geometry type to accommodate both.
CREATE TABLE IF NOT EXISTS masas_agua_superficial ( id SERIAL PRIMARY KEY, codigo_masa VARCHAR(100), -- Water body code nombre VARCHAR(255), -- Water body name tipo VARCHAR(100), -- 'río' | 'lago' | 'embalse' | 'costera' | 'transición' categoria VARCHAR(100), -- PHC category estado_ecologico VARCHAR(50), -- 'bueno' | 'moderado' | 'deficiente' | 'malo' estado_quimico VARCHAR(50), -- 'bueno' | 'no alcanza buen estado' demarcacion VARCHAR(100), -- Hydrographic demarcation geom GEOMETRY(Geometry, 25830) NOT NULL -- Generic: rivers=LineString, lakes=Polygon);CREATE INDEX IF NOT EXISTS idx_mas_geom ON masas_agua_superficial USING GIST (geom);
CREATE TABLE IF NOT EXISTS masas_agua_subterranea ( id SERIAL PRIMARY KEY, codigo_masa VARCHAR(100), -- Groundwater body code nombre VARCHAR(255), -- Body name estado_cuantitativo VARCHAR(50), -- 'bueno' | 'malo' estado_quimico VARCHAR(50), -- 'bueno' | 'malo' superficie_km2 NUMERIC(10, 2), -- Area in km² demarcacion VARCHAR(100), geom GEOMETRY(MultiPolygon, 25830) NOT NULL);CREATE INDEX IF NOT EXISTS idx_masub_geom ON masas_agua_subterranea USING GIST (geom);
The core of GeoViable’s analysis is a PostGIS cross-reference query. The user’s parcel arrives as GeoJSON in EPSG:4326 and is reprojected inline to EPSG:25830 before being compared against each layer. The example below shows the full query used for the Red Natura 2000 layer:
The WHERE ST_Intersects(...) clause uses the GIST spatial index for fast candidate filtering, while ST_Area(ST_Intersection(...)) computes the exact overlap area only for matched rows. The production implementation in spatial_analysis.py uses a CTE (WITH user_parcel AS (...)) so the parcel geometry is reprojected only once per query.
After each bulk data load the update pipeline runs VACUUM ANALYZE automatically. If you need to trigger it manually (for example, after using load_initial_data.py to bulk-load all layers), run:
VACUUM ANALYZE cannot run inside a transaction block. The update scripts handle this by opening a separate connection with isolation_level="AUTOCOMMIT" after the main TRUNCATE + INSERT transaction completes.