marti.pericay@geomatico.es
CETEMAS
13/05/2022
Descargar data bundle
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
-- 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:
Animalia/Mollusca/Bivalvia/Veneroida/Sphaeriidae/Sphaerium
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 NOT NULL,
geom geometry(point, 4326) NULL
);
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));
CREATE TABLE puntos2 (
gid serial NOT NULL,
lat int NULL,
lon int NULL
);
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
select ST_AsText(geom) from bivalvos_mcnb;
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(
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;
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;
-- 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!!!
kahoot.it
(PIN juego: 0817629)
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';
CREATE SCHEMA nuevo;
SELECT * INTO nuevo.cuenca_ebro FROM cuenca_ebro;
-- copiarla
ALTER TABLE cuenca_ebro SET SCHEMA nuevo; -- moverla (ERROR?)
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)
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 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();
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;
kahoot.it
(PIN juego: 01923855)
https://kahoot.it/challenge/01923855
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 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;
-- 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;
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)