Formación PostGIS para gestión medioambiental
marti.pericay@geomatico.es
CETEMAS
29/04/2022
Presentación
- Sobre la formación
- Quiénes somos
- Temario
Calendario
-
Jornada 1 (29/04/2022): Introducción SQL
-
Jornada 2 (06/05/2022): Uso y gestión de datos
-
Jornada 3 (13/05/2022): Funciones espaciales I
-
Jornada 4 (20/05/2022): Funciones espaciales II
-
Jornada 5 (27/05/2022): Conceptos avanzados
-
Jornada 6 (03/06/2022): Introducción a Geoserver
Bloque 1: introducción a PostGIS y gestión de datos
1.1 Introducción
1.1.1 Teoría de base de datos
1.1.2 Conceptos básicos de SQL
1.1.3 PostgreSQL y PostGIS
Bloque 1: introducción a PostGIS y gestión de datos
1.2 Uso de PostgreSQL/PostGIS
1.2.1 pgAdmin
1.2.2 Otros clientes: QGIS, DBeaver
1.2.3 Tipos y conceptos básicos PostgreSQL
1.2.4 PostGIS: geometrías, WKT, WKB
1.2.5 PostGIS: sistemas de coordenadas
1.3 Gestión de datos
1.3.1 Importación vía GDAL/OGR
1.3.2 Introducción a QGIS
1.3.3 Importación y exportación vía DBManager
¿Quiénes somos?
1.1.1 Teoría de base de datos
Para qué una base de datos???
- Limitaciones en la cantidad de datos que era posible almacenar
- Rendimiento de lectura de estos archivos
- Bloqueo de los archivos con el acceso por usuario
- Imposibilidad de gestionar el versionado de manera sencilla
Limitaciones archivos
- Manejo de grandes volúmenes de datos
- Complejidad en la extracción de estos datos
- Concurrencia en el acceso a datos, accesos simultáneos por varios usuarios
Para qué una base de datos???
- Una gran masa de datos relacionados entre si pertenecientes a un mismo contexto
- Colección estructurada almacenada en un sistema informático
Qué es una BDD?
Aplicación web
BDD se encarga del almacenamiento propiamente dicho y el SGBD de la manipulación de la información contenida en la base de datos.
Pero se suele usar "BDD" para ambos
Qué es un SGBD ?
- Recogida
- Almacenamiento
- Procesamiento
- Recuperación
Funciones de una BDD
La atomicidad o integridad describe la propiedad de “todo o nada” de los SGBD, por la que todas las fases de una transacción deben finalizarse por completo y en el orden correcto para que esta sea válida.
Integridad (ACID)
La consistencia implica que las transacciones completadas no afecten la estabilidad de la base de datos, lo que requiere supervisarlas constantemente.
Consistencia (ACID)
El aislamiento es la propiedad que asegura que las transacciones no obstaculicen a las demás, de lo que, por lo general, se encargan algunas funciones de bloqueo.
Aislamiento (ACID)
La permanencia implica que todos los datos queden almacenados permanentemente en el SGBD, no solo después de una transacción correcta, sino también o especialmente en caso de error o caída del sistema. Los registros de las transacciones, donde quedan anotados todos los procesos del SGBD, son fundamentales para garantizar la permanencia.
Permanencia (ACID)
- Relacionales (SQL)
- No-relacionales (No-SQL)
- Otros (jerárquicos, de red ...)
Tipos de Modelos de datos
Bases de datos relacionales
Bases de datos no-relacionales
Objetivos BDD relacionales
- Abstracción de la información
- Independencia
- Redundancia mínima
- Consistencia
- Seguridad
- Integridad
- Respaldo y recuperación
- Control de la concurrencia, versionado
- Tiempo de respuesta
Modelo de datos relacional
- Tablas
- Campos = Columnas
- Registros = Filas
Hoja de cálculo?????
Modelo de datos relacional
- Tablas
- Columnas
- Registros
- Relaciones
Modelo de datos relacional
Modelo de datos relacional
Ejemplos de datos
- El área de un parque natural
- El nombre de un parque natural
- La dirección de una oficina de correos
- El número de empleados de la oficina de correos
- El nombre de un accidente geográfico
- Las coordenadas de un accidente geográfico
Práctica
Defina la estructura de una tabla para los Parques Naturales. Para ello detecte la información necesaria susceptible de ser almacenada y estructúrela en una tabla definiendo el nombre de los campos.
Normalización de BDD
- Evitar la redundancia de los datos.
- Evitar problemas de actualización de los datos en las tablas.
- Proteger la integridad de los datos.
Modelización de BDD
- Encontrar entidades (conjuntos de entidades)
- Identificar atributos de las entidades
- Buscar identificadores
- Especificar las relaciones y cardinalidades
- Identificar entidades débiles
- Especializar y generalizar entidades donde sea posible
La integridad referencial es un sistema compuesto por reglas que permiten la garantía de las relaciones entre las filas de la tabla relacionadas para que sean válidas y que estas relaciones no se eliminen ni se modifiquen por error.
Integridad referencial
1.1.2 Conceptos básicos de SQL
SQL = Structured Query Language
Ejemplo SQL
SELECT *
FROM provincias_espana
devuelve un listado de registros (filas y columnas)
Ejemplo SQL
Ejemplo SQL
SELECT id, nameunit, codnut2
FROM provincias_espana
WHERE codnut2='ES12'
devuelve un listado de registros (filas y columnas)
SQL Playground
SQL para todos los SGBD relacionales? PostgreSQL, Oracle, MySQL ....?
Oracle vs PostgreSQL
SELECT id FROM (SELECT * FROM tabla1) -- no funciona!
SELECT id FROM (SELECT * FROM tabla1) AS foo
- Recogida
- Almacenamiento
- Procesamiento
- Recuperación
Funciones de una BDD
- DDL Data Definition Language
- DML Data Manipulation Language
-
DCL Data Control Language
Comandos SQL
CREATE | Utilizado para crear nuevas tablas, campos e índices |
DROP | Empleado para eliminar tablas e índices |
ALTER | Utilizado para modificar las tablas agregando campos o cambiando la definición de los campos. |
DDL Data Definition Language
CREACIÓN DE TABLAS
CREATE TABLE empleado
(
nombre VARCHAR,
apellido VARCHAR(50),
direccion VARCHAR(255),
ciudad VARCHAR(60),
telefono VARCHAR(15)
);
CREACIÓN DE TABLAS
CREATE TABLE nombre_tabla
(
campo1 tipo,
campo2 tipo null_not_null,
campo3 tipo
);
CREACIÓN DE TABLAS
CREATE TABLE recinto_sigpac (
gid serial NOT NULL,
geom geometry(polygon, 4258) NULL,
provincia int4 NULL,
municipio int4 NULL,
agregado int4 NULL,
zona int4 NULL,
poligono int4 NULL,
parcela int8 NULL,
recinto int8 NULL,
pendiente_media int4 NULL,
coef_admisibilidad int4 NULL,
coef_regadio int4 NULL
);
TIPOS MÁS HABITUALES
varchar
text
int = int4
int8
float8
geom
ELIMINACIÓN DE TABLAS
DROP TABLE recinto_sigpac;
MANIPULACIÓN DE CAMPOS
ALTER TABLE recinto_sigpac DROP COLUMN coef_regadio;
ALTER TABLE recinto_sigpac ADD anyo int4 NULL;
PRÁCTICA
Definir mediante comandos SQL el modelo de datos creado para los Parques Naturales. Inspirarse en los ddl de los datos de ejemplo.
SELECT | Utilizado para consultar registros de la base de datos que satisfagan un criterio determinado |
INSERT | Utilizado para cargar lotes de datos en la base de datos en una única operación. |
UPDATE | Utilizado para modificar los valores de los campos y registros especificados Utilizado para modificar las tablas agregando campos o cambiando la definición de los campos. |
DELETE | Utilizado para eliminar registros de una tabla |
DML Data Manipulation Language
SELECT
SELECT campo1, campo2
FROM tabla
WHERE columna1 = 'valor1'
INSERT
INSERT INTO agenda_telefonica (nombre, numero)
VALUES ('Roberto Jeldrez', 4886850);
INSERT INTO recinto_sigpac
(dn_oid,provincia,municipio,agregado,zona,poligono,parcela,recinto,dn_surface,dn_perimeter,pendiente_media,coef_admisibilidad,coef_regadio,incidencias,uso_sigpac,region,grupo_cultivo_2013)
VALUES
(718617452,33,15,0,0,1,6,1,2104.5235735514193,220.11372448943828,331,100,0,'140','PS','0103','PT');
UPDATE
UPDATE mi_tabla
SET campo1 = 'nuevo valor campo1'
WHERE campo2 = 'N';
DELETE
DELETE FROM tabla
WHERE columna1 = 'valor1'
NÚMEROS | anyo = 1977 |
TEXTO | provincia = 'Zaragoza' |
COMPARACIONES (=)
"COLUMNAS" | "anyo" = 1977 |
'TEXTO' | "provincia" = 'Zaragoza' |
COMILLAS SIMPLES Y DOBLES
< | Menor que |
> |
Mayor que |
<> != | Distinto de |
<= | Menor o igual que |
>= | Mayor o igual que |
BETWEEN | Intervalo |
LIKE % | Comparación texto |
In | Especificar |
OPERADORES DE COMPARACIÓN
FROM | Utilizada para especificar la tabla de la cual se van a seleccionar los registros |
GROUP BY | Utilizada para separar los registros seleccionados en grupos específicos |
HAVING | Utilizada para expresar condición que debe satisfacer cada grupo |
ORDER BY | Utilizada para ordenar los registros seleccionados de acuerdo con un orden específico |
WHERE | Utilizada para determinar los registros seleccionados en la clausula FROM |
CLÁUSULAS
AND | Es el “y” lógico. Evalúa dos condiciones y devuelve un valor de verdad sólo si ambas son ciertas. |
OR | Es el “o” lógico. Evalúa dos condiciones y devuelve un valor de verdad si alguna de las dos es cierta. |
NOT | Negación lógica. Devuelve el valor contrario de la expresión. |
OPERADORES
kahoot.it
(PIN juego: 05768090)
https://kahoot.it/challenge/05768090
Kahoot!
QGIS básico
Qué nivel de QGIS tenéis?
(de 0 a 10, por el chat)
0: no lo he visto nunca
1: sé añadir capas y navegar
5: sé consultar info y acceder a los atributos
10: sé hacer consultas espaciales, editar y usar toolbox
Aplicación web
Conexiones a BDD
Conectar a PGSQL remoto
- Host/anfitrión: 192.168.20.17
- Database/base de datos: <nombre>
- User/usuario: <nombre>
- Password/contraseña: <pwd>
- Port/Puerto: 5432
QGIS: ejecutar SQL
- Crear conexión PostGIS
- Añadir capa a QGIS (doble clic)
- Botón derecho -> Actualizar capa SQL
- Escribir SQL y 'Ejecutar'
- Para ver en el mapa: 'Actualizar'
QGIS: ejecutar SQL
- Crear conexión PostGIS (botón derecho)
QGIS: ejecutar SQL
- Crear conexión PostGIS (botón derecho)
QGIS: ejecutar SQL
2. Añadir capa 'bivalvos_mcnb' a QGIS (doble clic o arrastrar)
QGIS: ejecutar SQL
3. Botón derecho -> Actualizar capa SQL
QGIS: ejecutar SQL
4. Escribir SQL y 'Ejecutar'
5. Para ver en el mapa: 'Actualizar'
CONSULTAS BIVALVOS_MCNB
- bivalvos_mcnb del stateprovince Barcelona
- bivalvos_mcnb que no son del stateprovince Barcelona
- bivalvos_mcnb de los stateprovince que empiecen por B
- bivalvos_mcnb que no son del stateprovince Barcelona y sí del género Venerupis
- bivalvos_mcnb que tienen un id superior o igual a 100.000
- bivalvos_mcnb ordenados alfabéticamente por género
- actualizar con phylum Mammalia los registros que tienen family Unionidae
RESPUESTAS BIVALVOS_MCNB
- SELECT * FROM "bivalvos_mcnb" WHERE stateprovince='Barcelona'
- SELECT * FROM "bivalvos_mcnb" WHERE stateprovince!='Barcelona'
- SELECT * FROM "bivalvos_mcnb" WHERE stateprovince LIKE 'B%'
- SELECT * FROM "bivalvos_mcnb" WHERE stateprovince!='Barcelona' AND genus='Venerupis'
- SELECT * FROM "bivalvos_mcnb" WHERE id >=100000
- SELECT * FROM "bivalvos_mcnb" ORDER BY genus
- UPDATE "bivalvos_mcnb" SET phylum='Mammalia' where family='Unionidae'
CONSULTAS PROVINCIAS
(botón Actualizar)
- nameunit de provincias_espana de codnut2 ES42 (CastillaLM)
- nameunit de provincias_espana de codnut2 ES42 ordenadas alfabéticamente descendiente
- nameunit de provincias_espana cuyo nameunit contenga la sílaba ca
- listado de todas las autonomías (codnut2) con el número de provincias que contienen
- listado de las autonomías (codnut2) que tienen más de 3 provincias
1.1.3 PostgreSQL
y PostGIS
PostgreSQL y PostGIS
Base de datos relacional
Extensión geoespacial
PostgreSQL: core y extensiones
Sistemas operativos
Windows
Windows
C:\Program Files\PostgreSQL\some version\data
Utilizar PostGIS
1- Instalar PostgreSQL+PostGIS
2- crear Base de datos
3-CREATE EXTENSION POSTGIS;
Documentación
Versiones
PostgreSQL
8.x
9.x
10.x 11.x
12.x +
PostGIS
1.x
2.x
2.x y 3.x
3.x
Versiones
cetemas
PostgreSQL
12.4
PostGIS
3.0
select version();
select postgis_version();
GDAL
Desde PostGIS 2.0, incluido (Para PostGIS Raster)
https://trac.osgeo.org/gdal/wiki/frmts_wtkraster.html
GDAL formatos
Quién usa GDAL?
Software libre
Think free as in free speech, not free beer
R.Stallman
PostgreSQL, PostGIS, GDAL son software libre
Roadmap
Github
Clone, pull request: mantenimiento
Licencias
- PostgreSQL: PostgreSQL License (BSD/MIT)
- PostGIS: GNU / GPL v2
Arquitecturas web
Arquitecturas web
Alta disponibilidad
Cómo conectar a PostgreSQL?
sysadmin: psql (viene con PostgreSQL)
Técnico GIS: QGIS o ArcMap
Administrador BDD: pgAdmin o dBeaver
psql
#desde servidor
ssh root@192.168.20.17
psql -h localhost -U postgres
#desde cliente (necesita puerto abierto!)
psql -h 192.168.20.17 -U postgres
postgres
psql
\q: Quit/Exit
\c __database__: Connect to a database
\d __table__: Show table definition including triggers
\dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
\l: List databases
\dn: List schemas
\df: List functions
\dv: List views
\df+ __function__ : Show function SQL code.
\x: Pretty-format query results instead of the not-so-useful ASCII tables
psql - primera BD
CREATE DATABASE prueba;
\c prueba
CREATE TABLE tabla_prueba (
id SERIAL PRIMARY KEY,
name VARCHAR(128)
);
ALTER TABLE tabla_prueba ADD COLUMN geom geometry(Geometry, 4326);
-- ERROR?
psql - primera BD
-- Enable PostGIS
CREATE EXTENSION postgis;
SELECT version();
SELECT PostGIS_Version();
-- gdb_template
crear template
ALTER DATABASE prueba WITH is_template TRUE;
CREATE DATABASE nueva_bd_igual_que_prueba TEMPLATE prueba;
postgresql.conf
listen_addresses = '*'
#listen_addresses = 'localhost'
pg_hba.conf
1.2 Uso de PostgreSQL/PostGIS
Cómo conectar a PostgreSQL?
sysadmin: psql
Técnico GIS: QGIS o ArcMap
Administrador BDD: pgAdmin o dBeaver
Conectar a PGSQL local
- Host/anfitrión: localhost
- Database/base de datos: postgres
- User/usuario: postgres
- Password/contraseña: <pwd>
- Port/Puerto: 5432
Conectar a PGSQL remoto
- Host/anfitrión: 192.168.20.17
- Database/base de datos: <nombre>
- User/usuario: <nombre>
- Password/contraseña: <pwd>
- Port/Puerto: 5432
- puede almacenar muchas conexiones
- permite consultar las BDD/tablas en las que el usuario tenga permisos
- permite añadir BDD/tablas/campos por interfaz gráfica
- permite asignar roles
- permite hacer consultas SQL
- parecido a pgAdmin
- también software libre
- permite una cierta visualización de geometrías
- permite añadir conexiones a otro tipo de bases de datos (MySQL, Oracle, MongoDB ...)
https://dbeaver.com/databases
- no es para administradores de BDD, sino para técnicos GIS
- las funcionalidades más importantes son visualizar e importar a PostGIS
si QGIS ya permite hacer buffer, unión, intersección, para qué quiero PostGIS?
si QGIS ya permite hacer buffer, unión, intersección, para qué quiero PostGIS?
1) DATOS VIVOS
2) AUTOMATIZACIÓN
TIPOS MÁS HABITUALES
varchar(50)
int int4
int8
float8
serial
geom
Data types
¡No uséis mayúsculas ni en tablas ni en campos!
(Importador QGIS)
BDD, esquema, tabla
BDD 1
Esquema A
Esquema B
Tabla A1
Tabla A2
Tabla A3
Tabla B1
Tabla B2
BDD, esquema, tabla
carto
cartobase
cartosigpac
Tabla MFE25_33_ETR89H30
Tabla Parques_Naturales
Tabla Red_Fluvial
Tabla e_paisaje
Tabla recinto
antolin
Esquema public
Tabla bivalvos_mcnb
Tabla provincias_espana
Tabla cuenca_ebro
esquema.tabla
SELECT * FROM esquema.tabla
excepto esquema public
SELECT * FROM tabla
SELECT * FROM public.tabla
kahoot.it
(PIN juego: 09757954)
https://kahoot.it/challenge/09757954
Kahoot!
7 esquemas
cartobase
cartobtn
cartocatastro
cartocetemas
cartopnoa
cartosigpac
dataref
Base de datos carto
- GEOMETRY (abstract superclass)
- POINT (a single coordinate, usually but not necessarily 2 dimensional)
- LINESTRING (a set of two or more coordinates, with a linear interpretation of the path between the coordinates)
- LINEARRING (a linestring of three or more coordinates in which the start and end points are the same, usually not instantiable, but used to build polygons)
- POLYGON (a set of one or more closed linearrings, one exterior ring that defines a bounded area, and a set of interior rings that define exceptions (holes) to the bounded areas)
- MULTIPOINT (a set of points)
- MULTILINESTRING (a set of linestrings)
- MULTIPOLYGON (a set of polygons)
- GEOMETRYCOLLECTION (a heterogeneous set of geometries)
Spatial types
WKT, WKB
WKT, WKB
WKT, WKB
ST_AsText
WKB
WKT
select ST_AsText(geom) from bivalvos_mcnb;
La tierra no es plana
Esférica????
La tierra no es plana
La tierra no es plana
La tierra no es plana
coordenadas geográficas: grados (lat/lon)
coordenadas proyectadas: metros (proyección UTM)
La tierra no es plana
Sistemas de coordenadas
https://en.wikipedia.org/wiki/List_of_map_projections
https://epsg.io
EPSG:4326
EPSG = SRID
EPSG está directamente relacionado al término SRID (en realidad, es equivalente). De hecho, si empleas cualquier software GIS (como ArcGIS o QGIS), éstos hacen referencia a los Sistemas de Coordenadas aludiendo al término EPSG, sin embargo, si nos movemos en PostGIS, la alusión a los Sistemas de Coordenadas se hace con el término SRID.
EPSG más habituales
geográficos (lat/lon)
4326
4258
3857
proyectados (UTM)
25829
25830
25831
EPSG más habituales
geográficos (lat/lon)
4326
4258
3857
proyectados (UTM)
25829
25830
25831
Definir columna geometría
CREATE TABLE prueba (
gid serial NOT NULL,
geom geometry(polygon, 4326) NULL
);
Práctica parte 1
Crear tabla ocurrencias_jaen con los siguientes campos:
- gid: autonumérico (entero que se rellena "solo")
- id_especie: número entero que identifica la especie
- geom: geometria puntual con proyección UTM de Jaén
Crear tabla especies con los siguientes campos:
- id: número entero que identifica la especie
- nombre: nombre de la especie
CON QUÉ HERRAMIENTA?
Práctica parte 2
Insertar registros en las tablas ocurrencias_jaen y especies con valores inventados. Ej:
ocurrencias_jaen
- gid: 1
- id_especie: 16
- geom: a partir de QGIS o http://bboxfinder.com
especies
- id: 16
- nombre: Aquila pennata
1.3 Gestión de datos
GDAL y ogr2ogr
ogr2ogr --version
/* lo tengo instalado? */
"C:\Program Files\QGIS 3.22\bin\"ogr2ogr.exe --version
ogr2ogr -f GeoJSON archivo_destino.geojson archivo_origen.shp
GDAL y ogr2ogr
GDAL y ogr2ogr
ogr2ogr -f "PostgreSQL"
PG:"dbname=marti host=192.168.20.17 port=5432 user=marti password=----"
"recintos_sigpac_chapineria.shp"
-lco GEOMETRY_NAME=geom -lco FID=gid -progress
QGIS o GDAL: exportar SQL
pgAdmin: ejecutar SQL
OPCION 1 para importar cualquier formato a PG: Administrador de base de datos
Por detrás, usa GDAL
kahoot.it
(PIN juego: 01885721)
https://kahoot.it/challenge/01885721
Kahoot!
Spanish Catastral Downloader
https://plugins.qgis.org/plugins/Spanish_Inspire_Catastral_Downloader/
SIGPAC Downloader
Importar
- recintos_sigpac_chapineria.shp
- direcciones_catastro_chapineria.geojson
https://docs.qgis.org/3.22/en/docs/user_manual/plugins/core_plugins/plugins_db_manager.html
Servicios ATOM
Ejercicio práctico (45 minutos)
- De un mismo municipio, descargar Catastro y Sigpac (en SHP).
- Subir los recintos SIGPAC y las direcciones de catastro a PostGIS en proyección 4326.
- Hacer un mapa QGIS en proyección 3857 con estas dos capas de PostGIS debidamente simbolizadas.
- Filtrar sólo las parcelas de rústica en la capa de QGIS de recintos.
- Filtrar sólo los direcciones que tienen specification parcel en la capa de QGIS de direcciones.
- Añadir una capa de base con el plugin Quick Map Services
CETEMAS - PostGIS
By mpericay
CETEMAS - PostGIS
- 181