marti.pericay@geomatico.es
TRAGSA
31/10/2023
Descargar data bundle
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
-- un comentario
SELECT * FROM nyc_streets
/* otro comentario */
select * from provincias_espana where nameunit="Zaragoza" ---NO!
select * from provincias_espana where nameunit='Zaragoza' ---SI
SELECT * FROM TablaMayusculas
SELECT * FROM "TablaMayusculas"
select * from provincias_espana where nameunit LIKE 'Zara%'
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
En qué casos nos interesa int y en qué casos texto?
nombre_función(parámetro1, parámetro 2, ...)
SELECT replace(stateprovince, 'Barcelona', 'Barcelona, España'), scientificname FROM bivalvos_mcnb
SELECT municipality || '(' || county || ')'
FROM bivalvos_mcnb
Seleccionar dos campos:
Ejemplo:
Animalia/Mollusca/Bivalvia/Veneroida/Sphaeriidae/Sphaerium
17
SELECT occurrence_id::text, kingdom || '/' || phylum || '/' _class || '/' || family || '/' || genus || '/' || specificepithet FROM bivalvos_mcnb
Las podéis ver en pgAdmin->NombreBDD->public->Functions
CREATE TABLE puntos (
gid serial,
geom geometry(point, 4326)
);
INSERT INTO puntos(geom)
VALUES (ST_MakePoint(2,42));
INSERT INTO puntos(geom)
VALUES (ST_SetSRID(ST_Point(2,42), 4326));
INSERT INTO puntos(geom)
VALUES (ST_GeometryFromText('POINT(1 41)', 4326));
ST_AsText
ST_GeometryFromText
CREATE TABLE puntos2 (
gid serial NOT NULL,
lat int NULL,
lon int NULL
);
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_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_Point(-123.365556, 48.428611),4326)
ALTER TABLE mytable ALTER COLUMN geom TYPE Geometry(Point, 4326) USING ST_Transform(geom, 4326);
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;
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);
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]
SELECT ST_Buffer(geom, 2000)
FROM bivalvos_mcnb;
Elementos de las 'geometry A' a menos de 'radius' unidades de distancia (p.e. metros) de las 'geometry B'.
SELECT gid, id_especie, geom
FROM ocurrencias_jaen
WHERE ST_DWithin(
geom,
ST_GeomFromText('POINT(-3.77 37.79)', 4326),
1000
);
--error!!!
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 bien
kahoot.it
(PIN juego: 02019221)
https://kahoot.it/challenge/02019221
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';
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
);
SELECT *
FROM ocurrencias_jaen o
JOIN especies e ON o.id_especie=e.gid;
SELECT prov.nameunit, biv.*
FROM provincias_espana AS prov
JOIN bivalvos_mcnb AS biv
ON ST_Contains(prov.geom, biv.geom);
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';
SELECT ebro.id AS ebro_id, biv.*
FROM cuenca_ebro AS ebro
JOIN bivalvos_mcnb AS biv
ON ST_Contains(ebro.geom, biv.geom);
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);
CREATE SCHEMA nuevo;
SELECT * INTO nuevo.cuenca_ebro FROM cuenca_ebro;
-- copiarla
ALTER TABLE cuenca_ebro SET SCHEMA nuevo; -- moverla (ERROR?)
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);
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
CREATE VIEW view_bivalvos_simple AS
SELECT
occurrence_id,
geom,
scientific_name
FROM bivalvos_mcnb
ORDER BY scientific_name;
CREATE VIEW AS
SELECT
occurrence_id,
geom
FROM view_bivalvos_simple;
CREATE VIEW view_bivalvos_family AS
SELECT
ST_Union(geom) AS geom,
family AS id
FROM bivalvos_mcnb
GROUP BY family;
SELECT * FROM view_bivalvos_family;
SELECT * FROM view_bivalvos_family
WHERE familyid='Corbulidae';
-- comparar tiempos
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
CREATE MATERIALIZED VIEW view_bivalvos_phylum AS
SELECT
ST_Union(geom) AS geom,
phylum AS id
FROM bivalvos_mcnb
GROUP BY phylum;
REFRESH MATERIALIZED VIEW view_bivalvos_phylum;
-- comparar tiempos
No nula + única + autoincremental: secuencias
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;
PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs.
PostgreSQL trigger can be specified to fire
CREATE TABLE numeros(
numero bigint NOT NULL,
cuadrado bigint,
cubo bigint,
raiz2 real,
raiz3 real,
PRIMARY KEY (numero)
);
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;
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
CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
seconds_now integer := extract(second from NOW());
BEGIN
RETURN seconds_now + 50;
END;
$$ LANGUAGE plpgsql;
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;
CREATE ROLE mrbean LOGIN;
CREATE USER mrbean; --es lo mismo
CREATE ROLE supermrbean LOGIN SUPERUSER;
CREATE ROLE mrbean LOGIN PASSWORD 'securePass1';
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;
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;
-- 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;
SELECT rolname FROM pg_roles;
kahoot.it
(PIN juego: 0729842)
https://kahoot.it/challenge/0729842
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)
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;
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’