Formación PostGIS  para gestión medioambiental
-Bloque 2-

marti.pericay@geomatico.es

www.geomatico.es

CETEMAS
13/05/2022

https://cloud.geomatico.es/s/TnckZysfBRf6od8 

 

https://slides.com/mpericay/cetemas2

Descargar data bundle

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 2: conceptos avanzados y análisis espacial


2.1 PostGIS: funciones espaciales
2.1.1 Geometry y geography
2.1.2 Ejemplo: ST_MakePoint y ST_SetSRID
2.1.3 Relaciones espaciales
2.1.4 Joins espaciales
2.1.5 Análisis espacial: buffer, intersección, unión

 

Bloque 2: conceptos avanzados y análisis espacial


2.2 Conceptos avanzados

2.2.1 Esquemas
2.2.2 Funciones temporales
2.2.3 Índices espaciales
2.2.4 Vistas
2.2.5 Disparadores (triggers)
2.2.6 PL/PgSQL

 

2.3 Caso práctico aplicado a gestión  medioambiental y forestal

Comandos SQL

para 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' ---SI

Lower-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           -- integer

CAST

En qué casos nos interesa int y en qué casos texto?

Función PostgreSQL 

nombre_función(parámetro1, parámetro 2, ...)

Funciones PostgreSQL 

https://www.postgresql.org/docs/current/functions.html​

Funciones PostgreSQL 

https://www.postgresql.org/docs/current/functions-string.html

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 string (con los niveles separados por / )
  • occurrence_id como texto

 

Animalia/Mollusca/Bivalvia/Veneroida/Sphaeriidae/Sphaerium

 

Repuesta práctica

SELECT occurrence_id::text, kingdom || '/' || phylum || '/' _class || '/' || family || '/' || genus || '/' || specificepithet FROM bivalvos_mcnb

 

 

Funciones PostGIS 

https://postgis.net/docs/reference.html​

Funciones PostGIS 

Las podéis ver en pgAdmin->NombreBDD->public->Functions

2.1 Funciones espaciales

Definir columna geometría

CREATE TABLE puntos (

    gid serial NOT NULL,

    geom geometry(point, 4326) NULL

 );

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

INSERT INTO puntos(geom)

VALUES (ST_GeometryFromText('POINT(1 41)', 4326));

Práctica: tabla sin geometría

CREATE TABLE puntos2 (

    gid serial NOT NULL,

    lat int NULL,

    lon int NULL

 );

Práctica

1- insertar algunos datos numéricos de lat y lon
INSERT INTO puntos2(lat, lon) VALUES(41,1);
INSERT INTO puntos2(lat, lon) VALUES(40,2);

2- crear una columna de geometría (puntos, 4326)

3- llenar la geometria a partir de las columnas lat y lon

ST_AsText

ST_AsText

WKB

WKT

select ST_AsText(geom) from bivalvos_mcnb;

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);

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
);

Operaciones espaciales: ST_Distance (geometry A, geometry B) 

SELECT ST_Distance(
  ST_Transform(ST_GeometryFromText('POINT(-3.70 40.42)', 4326), 25830),    -- Madrid
  ST_Transform(ST_GeometryFromText('POINT(-3.77 37.79)', 4326), 25830)     -- Jaén
);
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;

Tipo 'geography'

https://postgis.net/docs/manual-3.2/using_postgis_dbmanagement.html#PostGIS_Geography

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

http://postgis.net/workshops/postgis-intro/geography.html

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

http://postgis.net/workshops/postgis-intro/spatial_relationships.html

Operaciones espaciales: ST_Buffer 

SELECT ST_Buffer(geom, 2000)
FROM bivalvos_mcnb;

ST_DWithin (geometry A, geometry B, radius): 

-- ocurrencias a 10kms de Jaén
SELECT gid, id_especie, geom
FROM ocurrencias_jaen
WHERE ST_DWithin(
    geom,
    ST_GeomFromText('POINT(-3.77 37.79)', 4326),
    10000
);

--error!!!

ST_DWithin

=

ST_Buffer + ST_Within

kahoot.it
(PIN juego: 0817629)


https://kahoot.it/challenge/0817629

Kahoot!

Ejercicios geometría

  • Buscar todos los bivalvos a menos de 50km del punto lon: -6.5, lat: 38.5

Joins espaciales

http://postgis.net/workshops/postgis-intro/joins.html

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?
  • Qué bivalvos quedan dentro de un radio de 100 kilómetros alrededor de Cáceres?

Ejercicios joins

Ejercicios relaciones espaciales

http://postgis.net/workshops/postgis-intro/spatial_relationships_exercises.html

ST_Intersects (geometry A, geometry B):

  • Qué provincias intersectan con la Cuenca del Ebro?

Ejercicios Carto (carto_user):

  • Seleccionar los Aerogeneradores que pertenecen a la Cuenca Hidrográfica del Navia
  • Seleccionar el tendido eléctrico que cruza algun ZEPA  y con la información de qué ZEPA cruza

2.3 Funciones avanzadas

2.3.1 PostgreSQL: esquemas

https://www.tutorialspoint.com/postgresql/postgresql_schema.htm

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

https://www.postgresql.org/docs/current/datatype-datetime.html​

https://www.postgresql.org/docs/current/functions-datetime.html

  • Modificar campo fecha 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 (cuenca_ebro)

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)

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

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 tiempos

En realidad, es una tabla

  • Puede tener índices
  • Útil para acceder a sistemas remotos

Estrategias con vistas materializadas

https://hashrocket.com/blog/posts/materialized-view-strategies-using-postgresql

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();

2.3.6 PL/pgSQL

Parecido a PLSQL (Oracle)

https://www.postgresql.org/docs/current/plpgsql.html

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;

kahoot.it
(PIN juego: 01923855)

https://kahoot.it/challenge/01923855

Kahoot!

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 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;

GRANT USAGE, SELECT ON ALL SEQUENCES 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;

Documentación

https://www.postgresql.org/docs/current/user-manag.html

Ejercicio: usuario writer

  • Via pgAdmin o consola
  • Puede escribir en el esquema public
  • Puede leer los otros esquemas

Caso práctico aplicado a gestión medioambiental/forestal

  1. Descargar bundle de datos https://cloud.geomatico.es/s/9x4Dbx6mFK2crjg
  2. Importar 3 tablas en EPSG:25830 a un nuevo esquema "practica"
  3. Hacer un mapa QGIS bien simbolizado con estas tablas con origen PostGIS:
    - elementos de mfe con usos del suelo 'Arbolado'
    - 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)

CETEMAS - PostGIS bloque 2

By mpericay

Made with Slides.com

CETEMAS - PostGIS bloque 2

  • 152

More from mpericay