marti.pericay@geomatico.es
CETEMAS
29/04/2022
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
Para qué una base de datos???
Limitaciones archivos
Para qué una base de datos???
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 ?
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)
Tipos de Modelos de datos
Bases de datos relacionales
Bases de datos no-relacionales
Objetivos BDD relacionales
Modelo de datos relacional
Hoja de cálculo?????
Modelo de datos relacional
Modelo de datos relacional
Modelo de datos relacional
Ejemplos de datos
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
Modelización de BDD
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
SELECT *
FROM provincias_espana
devuelve un listado de registros (filas y columnas)
SELECT id, nameunit, codnut2
FROM provincias_espana
WHERE codnut2='ES12'
devuelve un listado de registros (filas y columnas)
SELECT id FROM (SELECT * FROM tabla1) -- no funciona!
SELECT id FROM (SELECT * FROM tabla1) AS foo
Funciones de una BDD
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
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
2. Añadir capa 'bivalvos_mcnb' a QGIS (doble clic o arrastrar)
3. Botón derecho -> Actualizar capa SQL
4. Escribir SQL y 'Ejecutar'
5. Para ver en el mapa: 'Actualizar'
CONSULTAS BIVALVOS_MCNB
RESPUESTAS BIVALVOS_MCNB
CONSULTAS PROVINCIAS
(botón Actualizar)
Base de datos relacional
Extensión geoespacial
C:\Program Files\PostgreSQL\some version\data
1- Instalar PostgreSQL+PostGIS
2- crear Base de datos
3-CREATE EXTENSION POSTGIS;
PostgreSQL
8.x
9.x
10.x 11.x
12.x +
PostGIS
1.x
2.x
2.x y 3.x
3.x
PostgreSQL
12.4
PostGIS
3.0
select version();
select postgis_version();
Desde PostGIS 2.0, incluido (Para PostGIS Raster)
https://trac.osgeo.org/gdal/wiki/frmts_wtkraster.html
Think free as in free speech, not free beer
R.Stallman
PostgreSQL, PostGIS, GDAL son software libre
Clone, pull request: mantenimiento
sysadmin: psql (viene con PostgreSQL)
Técnico GIS: QGIS o ArcMap
Administrador BDD: pgAdmin o dBeaver
#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
\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
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?
-- Enable PostGIS
CREATE EXTENSION postgis;
SELECT version();
SELECT PostGIS_Version();
-- gdb_template
ALTER DATABASE prueba WITH is_template TRUE;
CREATE DATABASE nueva_bd_igual_que_prueba TEMPLATE prueba;
listen_addresses = '*'
#listen_addresses = 'localhost'
sysadmin: psql
Técnico GIS: QGIS o ArcMap
Administrador BDD: pgAdmin o dBeaver
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
(Importador QGIS)
BDD 1
Esquema A
Esquema B
Tabla A1
Tabla A2
Tabla A3
Tabla B1
Tabla B2
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
SELECT * FROM esquema.tabla
excepto esquema public
SELECT * FROM tabla
SELECT * FROM public.tabla
kahoot.it
(PIN juego: 09757954)
https://kahoot.it/challenge/09757954
7 esquemas
cartobase
cartobtn
cartocatastro
cartocetemas
cartopnoa
cartosigpac
dataref
ST_AsText
select ST_AsText(geom) from bivalvos_mcnb;
Esférica????
coordenadas geográficas: grados (lat/lon)
coordenadas proyectadas: metros (proyección UTM)
Sistemas de coordenadas
https://en.wikipedia.org/wiki/List_of_map_projections
https://epsg.io
EPSG:4326
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.
geográficos (lat/lon)
4326
4258
3857
proyectados (UTM)
25829
25830
25831
geográficos (lat/lon)
4326
4258
3857
proyectados (UTM)
25829
25830
25831
CREATE TABLE prueba (
gid serial NOT NULL,
geom geometry(polygon, 4326) NULL
);
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?
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
ogr2ogr --version
/* lo tengo instalado? */
"C:\Program Files\QGIS 3.22\bin\"ogr2ogr.exe --version
ogr2ogr -f GeoJSON archivo_destino.geojson archivo_origen.shp
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
https://plugins.qgis.org/plugins/Spanish_Inspire_Catastral_Downloader/
https://docs.qgis.org/3.22/en/docs/user_manual/plugins/core_plugins/plugins_db_manager.html