Formación PostGIS para gestión medioambiental
-Bloque 2-
marti.pericay@geomatico.es
TRAGSA
31/10/2023
Presentación
- Sobre la formación
- Quiénes somos
- Temario
Descargar data bundle
Calendario
-
Jornada 1 (24/10/2023): Introducción SQL
-
Jornada 2 (26/10/2023): Instalación y uso
-
Jornada 3 (30/10/2023): Gestión de datos
-
Jornada 4 (31/10/2023): Funciones espaciales
-
Jornada 5 (06/11/2023): Conceptos avanzados
-
Jornada 6 (07/11/2023): Administración + práctica
Bloque 2: conceptos avanzados y análisis espacial
2.1 PostgreSQL: funciones
2.1.1 Características
2.1.2 Conversiones de tipo
2.1.3 Funciones para cadenas de texto
2.2 PostGIS: funciones espaciales
2.2.1 Geometry y geography
2.2.2 Ejemplo: ST_MakePoint y ST_SetSRID
2.2.3 Relaciones espaciales
2.2.4 Joins espaciales
2.2.5 Análisis espacial: buffer, intersección, unión
Bloque 2: conceptos avanzados y análisis espacial
2.3 Conceptos avanzados
2.3.1 Esquemas
2.3.2 Funciones temporales
2.3.3 Índices espaciales
2.3.4 Vistas
2.3.5 Disparadores (triggers)
2.3.6 PL/PgSQL
Bloque 2: conceptos avanzados y análisis espacial
2.4 Administración de BDD
2.4.1 Roles de usuario
2.4.2 Privilegios
2.4.3 Backups
2.5 Caso práctico aplicado a gestión medioambiental y forestal
2.1 Funciones PostgreSQL
Comentarios
-- un comentario
SELECT * FROM nyc_streets
/* otro comentario */Strings entre comillas simples
select * from provincias_espana where nameunit="Zaragoza" ---NO!
select * from provincias_espana where nameunit='Zaragoza' ---SILower-case
SELECT * FROM TablaMayusculas
SELECT * FROM "TablaMayusculas"¡No uséis mayúsculas ni en tablas ni en campos!
LIKE %
select * from provincias_espana where nameunit LIKE 'Zara%'CAST
SELECT id_cuenca FROM cuenca_ebro; -- varchar
SELECT CAST(id_cuenca AS int) FROM cuenca_ebro; -- integer
SELECT id_cuenca::int FROM cuenca_ebro; -- integerCAST
En qué casos nos interesa int y en qué casos texto?
Funciones y parámetros
nombre_función(parámetro1, parámetro 2, ...)
Funciones PostgreSQL
Funciones PostgreSQL
replace(text, string1, string2)
SELECT replace(stateprovince, 'Barcelona', 'Barcelona, España'), scientificname FROM bivalvos_mcnb
concatenar ||
SELECT municipality || '(' || county || ')'
FROM bivalvos_mcnb
Práctica
Seleccionar dos campos:
- toda la cadena taxonómica como texto (con los niveles separados por / )
- occurrence_id como texto
Ejemplo:
Animalia/Mollusca/Bivalvia/Veneroida/Sphaeriidae/Sphaerium
17
Repuesta práctica
SELECT occurrence_id::text, kingdom || '/' || phylum || '/' _class || '/' || family || '/' || genus || '/' || specificepithet FROM bivalvos_mcnb
Funciones PostGIS
Funciones PostGIS
Las podéis ver en pgAdmin->NombreBDD->public->Functions
2.2 Funciones espaciales
Definir columna geometría
CREATE TABLE puntos (
gid serial,
geom geometry(point, 4326)
);
ST_MakePoint(x, y)
INSERT INTO puntos(geom)
VALUES (ST_MakePoint(2,42));
ST_Point(x, y)
INSERT INTO puntos(geom)
VALUES (ST_SetSRID(ST_Point(2,42), 4326));
ST_GeometryFromText
ST_GeomFromText
INSERT INTO puntos(geom)
VALUES (ST_GeometryFromText('POINT(1 41)', 4326));
ST_AsText y ST_GeometryFromText
ST_AsText
WKB
WKT
ST_GeometryFromText
Tabla sin geometría
CREATE TABLE puntos2 (
gid serial NOT NULL,
lat int NULL,
lon int NULL
);
Práctica
1- descargar registros GBIF de Spirobranchus giganteus
2- subirla a PostGIS
3- crear una columna de geometría (puntos, 4326)
4- llenar la geometría creando puntos a partir de los datos
ST_SetSRID() vs ST_Transform()
ST_Transform is often confused with ST_SetSRID(). ST_Transform actually changes the coordinates of a geometry from one spatial reference system to another, while ST_SetSRID() simply changes the SRID identifier of the geometry.
ST_SetSRID()
ST_SetSRID(ST_Point(-123.365556, 48.428611),4326)
ALTER TABLE mytable ALTER COLUMN geom TYPE Geometry(Point, 4326) USING ST_Transform(geom, 4326);
Cambiar EPSG de una tabla
Operaciones espaciales: ST_Distance (geometry A, geometry B)
SELECT ST_Distance(
ST_GeometryFromText('POINT(-3.70 40.42)', 4326), -- Madrid
ST_GeometryFromText('POINT(-3.77 37.79)', 4326) -- Jaén
);SELECT ST_Distance(a.geom, b.geom)
FROM bivalvos_mcnb a, bivalvos_mcnb b
WHERE a.id=88414 AND b.id=88412;Operaciones espaciales: ST_Distance
Tipo 'geography'
SELECT ST_Distance(
ST_GeometryFromText('POINT(-118.4079 33.9434)', 4326), -- Los Angeles (LAX)
ST_GeometryFromText('POINT(2.5559 49.0083)', 4326) -- Paris (CDG)
);SELECT ST_Distance(
ST_Transform(ST_GeometryFromText('POINT(-118.4079 33.9434)', 4326), 3857), -- Los Angeles
ST_Transform(ST_GeometryFromText('POINT(2.5559 49.0083)', 4326), 3857) -- Paris
)/1000;SELECT ST_Distance(
ST_GeographyFromText('POINT(-118.4079 33.9434)'), -- Los Angeles (LAX)
ST_GeographyFromText('POINT(2.5559 49.0083)') -- Paris (CDG)
) / 1000;SELECT ST_Distance(
ST_GeographyFromText('LINESTRING(-118.4079 33.9434, 2.5559 49.0083)'), -- LAX-CDG
ST_GeographyFromText('POINT(-22.6056 63.9850)') -- Iceland
) / 1000;
--ejemplo con CAST
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography,
'POINT(-21.96 64.15)':: geography);Tipo geography: su base es una esfera WGS84
Funciones geography
ST_AsText(geography) returns text
ST_GeographyFromText(text) returns geography
ST_AsBinary(geography) returns bytea
ST_GeogFromWKB(bytea) returns geography
ST_AsSVG(geography) returns text
ST_AsGML(geography) returns text
ST_AsKML(geography) returns text
ST_AsGeoJson(geography) returns text
ST_Distance(geography, geography) returns double
ST_DWithin(geography, geography, float8) returns boolean
ST_Area(geography) returns double
ST_Length(geography) returns double
ST_Covers(geography, geography) returns boolean
ST_CoveredBy(geography, geography) returns boolean
ST_Intersects(geography, geography) returns boolean
ST_Buffer(geography, float8) returns geography [1]
ST_Intersection(geography, geography) returns geography [1]
Relaciones espaciales
Operaciones espaciales: ST_Buffer
SELECT ST_Buffer(geom, 2000)
FROM bivalvos_mcnb;
ST_DWithin (geometry A, geometry B, radius):
Elementos de las 'geometry A' a menos de 'radius' unidades de distancia (p.e. metros) de las 'geometry B'.
ST_DWithin (geometry A, geometry B, radius):
SELECT gid, id_especie, geom
FROM ocurrencias_jaen
WHERE ST_DWithin(
geom,
ST_GeomFromText('POINT(-3.77 37.79)', 4326),
1000
);
--error!!!ST_DWithin (geometry A, geometry B, radius):
SELECT gid, id_especie, geom
FROM ocurrencias_jaen
WHERE ST_DWithin(
geom,
ST_Transform(ST_GeomFromText('POINT(-3.77 37.79)', 4326), 25830),
30000
);
--con EPSG iguales, calcula bienCuando ejecuto funciones espaciales sobre dos geometrías:
MISMO EPSG/SRID
kahoot.it
(PIN juego: 02019221)
https://kahoot.it/challenge/02019221
Kahoot!
Ejercicios geometría
- Buscar distancia entre el único Mactra glauca y el único Venerupis geographica en bivalvos_mcnb
- Buscar todos los bivalvos a menos de 50km del punto lon: -6.5, lat: 38.5
Respuesta ejercicios
SELECT ST_Distance(ST_Transform(a.geom, 25830), ST_Transform(b.geom, 4326))
FROM bivalvos_mcnb a, bivalvos_mcnb b
WHERE a.scientificname='Mactra glauca' AND b.scientificname='Venerupis geographica';
Respuesta ejercicios
SELECT id, scientificname
FROM bivalvos_mcnb
WHERE ST_DWithin(
ST_Transform(geom, 25830),
ST_Transform(ST_GeomFromText('POINT(-6.5 38.5)', 4326), 25830),
50000
);
Joins
SELECT *
FROM ocurrencias_jaen o
JOIN especies e ON o.id_especie=e.gid;
Joins espaciales
Joins espaciales
SELECT prov.nameunit, biv.*
FROM provincias_espana AS prov
JOIN bivalvos_mcnb AS biv
ON ST_Contains(prov.geom, biv.geom);
Joins espaciales
SELECT prov.nameunit, biv.stateprovince
FROM provincias_espana AS prov
JOIN bivalvos_mcnb AS biv
ON ST_Contains(prov.geom, biv.geom)
WHERE prov.nameunit='Illes Balears';
- Qué bivalvos quedan dentro de la Cuenca del Ebro?
Ejercicio join
Respuesta ejercicio
SELECT ebro.id AS ebro_id, biv.*
FROM cuenca_ebro AS ebro
JOIN bivalvos_mcnb AS biv
ON ST_Contains(ebro.geom, biv.geom);
Ejercicios relaciones espaciales
ST_Intersects (geometry A, geometry B):
- Qué provincias intersectan con la Cuenca del Ebro?
Respuesta ejercicio
SELECT ebro.id AS ebro_id, prov.nameunit
FROM cuenca_ebro AS ebro
JOIN provincias_espana AS prov
ON ST_Intersects(ebro.geom, prov.geom);
2.3 Funciones avanzadas
2.3.1 PostgreSQL: esquemas
CREATE SCHEMA nuevo;
SELECT * INTO nuevo.cuenca_ebro FROM cuenca_ebro;
-- copiarla
ALTER TABLE cuenca_ebro SET SCHEMA nuevo; -- moverla (ERROR?)
Múltiples esquemas
- Cuidado con permisos (usuarios)
- Posibilidad de varios usuarios sin tocar pg_hba.conf
- Buena práctica separar: backup y restore, upgrade
2.3.2 PostgreSQL: funciones temporales
- Seleccionar campo fecha a cuenca_ebro para sumar 1 año a los id 1 y 2
- Añadir columna anyo (int) a cuenca_ebro a partir de fecha (date)
Ejercicio funciones temporales
Respuestas ejercicio
select (fecha + interval '1 year')::date
from cuenca_ebro
where id=1 or id=2;
alter table ce add column anyo int null;
UPDATE cuenca_ebro
SET anyo = EXTRACT(YEAR FROM fecha);
2.3.3 PostGIS: indexación
R-Tree
Añadir índice
CREATE INDEX <INDEXNAME> ON <TABLENAME> USING GIST (<GEOMETRYCOLUMN>);
Borrar índice
DROP INDEX <INDEXNAME>;
CREATE INDEX sidx_provincias_espana_geom ON public.provincias_espana USING gist (geom)
SELECT i.relname as indname,
i.relowner as indowner,
idx.indrelid::regclass,
am.amname as indam,
idx.indkey,
ARRAY(
SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
FROM generate_subscripts(idx.indkey, 1) as k
ORDER BY k
) as indkey_names,
idx.indexprs IS NOT NULL as indexprs,
idx.indpred IS NOT NULL as indpred
FROM pg_index as idx
JOIN pg_class as i
ON i.oid = idx.indexrelid
JOIN pg_am as am
ON i.relam = am.oid
WHERE i.relname LIKE 'prov%';Todos los índices
2.3.4 PostgreSQL: vistas
Crear vista
CREATE VIEW view_bivalvos_simple AS
SELECT
occurrence_id,
geom,
scientific_name
FROM bivalvos_mcnb
ORDER BY scientific_name;Una vista es como una tabla
CREATE VIEW AS
SELECT
occurrence_id,
geom
FROM view_bivalvos_simple;Crear vista
CREATE VIEW view_bivalvos_family AS
SELECT
ST_Union(geom) AS geom,
family AS id
FROM bivalvos_mcnb
GROUP BY family;Usar vista
SELECT * FROM view_bivalvos_family;
SELECT * FROM view_bivalvos_family
WHERE familyid='Corbulidae';
-- comparar tiempos- Crear una vista con los nombres de las provincias que contengan bivalvos
Ejercicios vistas
Respuesta ejercicios vistas
create VIEW view_provis AS
SELECT
prov.nameunit
FROM provincias_espana prov
JOIN bivalvos_mcnb AS biv
ON ST_Contains(prov.geom, biv.geom)
group by nameunit
Crear vista materializada (>9.3)
CREATE MATERIALIZED VIEW view_bivalvos_phylum AS
SELECT
ST_Union(geom) AS geom,
phylum AS id
FROM bivalvos_mcnb
GROUP BY phylum;Refrescar vista materializada
REFRESH MATERIALIZED VIEW view_bivalvos_phylum;
-- comparar tiemposEn realidad, es una tabla
- Puede tener índices
- Útil para acceder a sistemas remotos
Estrategias con vistas materializadas
PostgreSQL: secuencias
Es crucial tener una PRIMARY KEY!
No nula + única + autoincremental: secuencias
Tabla bivalvos -> primary key (occurrence_id)
Creación secuencia con SERIAL
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR(100) NOT NULL,
primary_author VARCHAR(100) NULL
);
ALTER TABLE bivalvos_buffer ADD COLUMN gid SERIAL PRIMARY KEY;2.3.5 Disparadores (triggers)
¿Qué es un trigger?
PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs.
¿Qué es un trigger?
PostgreSQL trigger can be specified to fire
- Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted)
- After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed)
- Instead of the operation (in the case of inserts, updates or deletes on a view)
Ejemplo trigger: tabla
CREATE TABLE numeros(
numero bigint NOT NULL,
cuadrado bigint,
cubo bigint,
raiz2 real,
raiz3 real,
PRIMARY KEY (numero)
);Ejemplo trigger: función
CREATE OR REPLACE FUNCTION rellenar_datos() RETURNS TRIGGER AS $rellenar_datos$ DECLARE
BEGIN
NEW.cuadrado := power(NEW.numero,2);
NEW.cubo := power(NEW.numero,3);
NEW.raiz2 := sqrt(NEW.numero);
NEW.raiz3 := cbrt(NEW.numero);
RETURN NEW;
END;
$rellenar_datos$ LANGUAGE plpgsql;Ejemplo trigger: disparador
CREATE TRIGGER rellenar_datos BEFORE INSERT OR UPDATE
ON numeros FOR EACH ROW
EXECUTE PROCEDURE rellenar_datos();kahoot.it
(PIN juego: 04041211)
https://kahoot.it/challenge/04041211
Kahoot!
2.3.6 PL/pgSQL
Parecido a PLSQL (Oracle)
Estructura PL/pgSQL
CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
seconds_now integer := extract(second from NOW());
BEGIN
RETURN seconds_now + 50;
END;
$$ LANGUAGE plpgsql;Ejemplo PL/pgSQL
CREATE OR REPLACE FUNCTION
toolbox_intersections_checks(geom geometry)
RETURNS geometry
AS $function$
begin
case
when ST_IsEmpty(geom) then return null;
when GeometryType(geom) = 'GEOMETRYCOLLECTION' then return st_transform(st_setsrid(ST_CollectionExtract(geom, 3), 25829), 4326);
when GeometryType(geom) = 'POLYGON' or GeometryType(geom) = 'MULTIPOLYGON' then return st_transform(st_setsrid(geom, 25829), 4326);
else return null;
end case;
END;
$function$
LANGUAGE plpgsql;2.4 Administración de BDD
Usuarios y roles
CREATE ROLE mrbean LOGIN;
CREATE USER mrbean; --es lo mismo
CREATE ROLE supermrbean LOGIN SUPERUSER;
CREATE ROLE mrbean LOGIN PASSWORD 'securePass1';Usuarios y roles
CREATE ROLE rodriguezdelafuente LOGIN INHERIT;
CREATE ROLE acceso_a_public NOINHERIT;
CREATE ROLE acceso_a_practica NOINHERIT;
GRANT acceso_a_public TO rodriguezdelafuente;
GRANT acceso_a_practica TO acceso_a_public;Usuarios y roles
Usuarios y roles
GRANT CONNECT ON DATABASE marti TO acceso_a_bdd;
GRANT ALL ON SCHEMA public TO acceso_a_public;
GRANT ALL ON ALL TABLES IN SCHEMA public TO acceso_a_public;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO acceso_a_public;
Usuarios read-only
-- A user account for the web app
CREATE USER app_user;
-- Web app needs access to specific data tables
GRANT SELECT ON nombre_tabla TO app_user;
Usuarios y roles
SELECT rolname FROM pg_roles;Grant Wizard (pgAdmin)
Documentación
kahoot.it
(PIN juego: 0729842)
https://kahoot.it/challenge/0729842
Kahoot!
Backup y restore
- Efectuar backup de una BDD
- Restaurarlo
Caso práctico aplicado a gestión medioambiental/forestal
- Descargar bundle de datos https://cloud.geomatico.es/s/kxX3jdDmzmZpzNk
- Importar a vuestra BDD PostGIS las 3 tablas en EPSG:25830 a un nuevo esquema "practica"
- Hacer un mapa QGIS bien simbolizado con estas tablas con origen PostGIS:
- elementos de mfe que tienen tipo Forestal
- capa recintos_azofra
- pois
Caso práctico aplicado a gestión medioambiental/forestal
4. Seleccionar los elementos de la tabla poi que están a 50 metros o menos de distancia a features de la tabla mfe50_26 y que su tipo sea igual a “Pinares de pino carrasco”
5. Seleccionar de la capa "recintos_azofra" los que tienen uso_sigpac=forestal, y saber qué tipo de árboles (Pinares, Choperas) contienen, cruzándolo con Mapa Forestal mfe (nom_forarb)
Respuestas caso práctico
SELECT p.id, mfe.nom_forarb, p.geom
FROM practica.poi AS p, practica.mfe50_26 AS mfe
WHERE ST_DWithin(p.geom, mfe.geom, 50)
AND mfe.id_forarb = 24;
Respuestas caso práctico
SELECT rec.*, mfe.nom_forarb
FROM "practica"."recintos_azofra" rec
JOIN "practica"."mfe50_26" mfe
ON St_Contains(mfe.geom, ST_Centroid(rec.geom))
WHERE uso_sigpac=’FO’
Tragsa - PostGIS bloque 2
By mpericay
