Formación Intergraph Spain en PostGIS - 2
marti.pericay@geomati.co
Descargar data bundle
Calendario
-
Jornada 1 (10/05/2017): introducción PostGIS (Martí Pericay)
-
Jornada 2 (18/05/2017): PostGIS avanzado (Martí Pericay)
-
Jornada 3 (29/05/2017): alta disponibilidad (Jose Vicente Higón) - 10:00 !!!
Postgres/PostGIS
1 Introducción
1.1 PostgreSQL
1.2 PostGIS
1.3 GDAL/OGR
1.4 Open Source: ventajas y desventajas
1.5 Licencias OSS
1.6 Arquitecturas
2 Instalación y configuración
2.1 Instalación
2.2 Crear BDD: command line, GUI
2.3 pg_hba.conf y postgresql.conf
2.4 pgAdmin
2.5 Importar datos
2.6 Otros clientes: QGIS+DBManager, Navicat
3 PostGIS vs Oracle
3.1 Tipos y conceptos básicos PostgreSQL
3.2 PostGIS: geometrías, WKT, WKB
3.3 PostGIS: funciones
3.4 PostGIS: sistemas de coordenadas
3.5 Vistas
3.6 Funciones temporales
3.7 PostgreSQL: índices
3.8 Triggers
3.9 PostgreSQL: esquemas
4. Tuning for Spatial
4.1 Configuración avanzada
4.2 Seguridad, encriptación
4.3 Definición de datos: restricciones
4.4 Definición de datos: privilegios, Row Security y herencia
4.5 Vistas y vistas materializadas
4.6 Indexación
4.7 Secuencias
5. Stored Procedures
5.1 Funciones
5.2 PL/PgSQL
5.3 Upgrades
5.4 Triggers
6. Backup y restore
6.1 Copias de seguridad lógicas (pg_dump y pg_restore)
6.2 Copias de seguridad físicas
6.3 WAL y archivado continuo
6.4 Copias de seguridad online
6.5 Restauración en un punto del tiempo (PITR)
7. Conexión con otras base de datos
7.1 DB links
7.2 Foreign data wrapper (Oracle, MySQL, ...)
8. Clustering y alta disponibilidad
8.1 Sistema de réplica standby (Slony)
8.2 Sistema de réplica hot Stand-by (Streaming Replication)
8.3 Balanceo de carga con pgPool-II
¿Quiénes somos?
3.3 Funciones PostGIS
Operaciones espaciales
Operaciones espaciales: ST_Buffer
SELECT ST_Buffer(geom, 500)
INTO nyc_subway_stations_buffer
FROM nyc_subway_stations;
Ejercicio geometría
- Crear una tabla de los condados de NYC (nyc_census_counties) a partir de nyc_census_blocks sabiendo que los cinco primeros dígitos de "blkid" determinan el condado.
Ejercicio geometría
- Crear una tabla de los condados de NYC (nyc_census_counties) a partir de nyc_census_blocks sabiendo que los cinco primeros dígitos de "blkid" determinan el condado.
SubStr(blkid,1,5)
Ejercicio geometría
CREATE TABLE nyc_census_counties AS SELECT ST_Union(geom) AS the_geom, SubStr(blkid,1,5) AS countyid FROM nyc_census_blocks GROUP BY countyid;
Relaciones espaciales
Ejercicios geometría
- Buscar distancia entre Broad St y Christopher St en nyc_subway_stations
- Buscar distancia entre Chambers St y Christopher St en nyc_subway_stations
- Buscar todas las estaciones a menos de 1km del punto -74.0, 40.75
Ejercicios geometría
SELECT ST_Distance(
SELECT geom FROM nyc_subway_stations WHERE name='Chambers St' LIMIT 1,
SELECT geom FROM nyc_subway_stations WHERE name='Christopher St' LIMIT 1
);
Ejercicios geometría
SELECT name
FROM nyc_subway_stations
WHERE ST_DWithin(
geom,
ST_Transform(ST_GeomFromText('POINT(-73.85 40.75)', 4326), 26918),
1000
);
Joins espaciales
- “Qué estación(es) hay en ‘Little Italy’?”
- “Cuál es la densidad (hab / km^2) de ‘Upper West Side’ y el ‘Upper East Side’?”
Ejercicios joins
3.4 PostGIS: sistemas de coordenadas
Proj4
- Puede emplearse el modelo de 3 parámetros o de 7 parámetros
- La configuración se realiza en la tabla spatial_ref_sys
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.
ALTER TABLE mytable ALTER COLUMN geom TYPE Geometry(Point, 4326) USING ST_Transform(geom, 4326);
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_Transform(ST_GeometryFromText('POINT(2.136841 41.455079)', 4326), 3857), -- BCN
ST_Transform(ST_GeometryFromText('POINT(2.817993 41.971743)', 4326), 3857) -- Girona
)/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
CREATE TABLE nyc_subway_stations_geog AS
SELECT
Geography(ST_Transform(geom,4326)) AS geog,
name,
routes
FROM nyc_subway_stations;
Tipo geography
- Las columnas del tipo “geography” se registran en la vista “geography_columns”
- Para usar el tipo geography empleamos opción -G de shp2pgsql
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]
3.6 PostgreSQL: funciones temporales
3.9 PostgreSQL: esquemas
CREATE SCHEMA newschema;
SELECT * INTO newschema.nyc_subway_stations FROM nyc_subway_stations;
-- copiarla
ALTER TABLE nyc_subway_stations SET SCHEMA newschema; -- moverla
SELECT name
FROM newschema.nyc_subway_stations
WHERE ST_DWithin(
geom,
ST_Transform(ST_GeomFromText('POINT(-73.85 40.75)', 4326), 26918),
1000
);
Múltiples esquemas
- Cuidado con permisos (usuarios)
- Posibilidad de varios usuarios sin tocar pg_hba.conf
- Buena práctica separar: backup y restore, upgrade
4. Tuning for Spatial
4.1 PostgreSQL: configuración avanzada para producción
Editar postgresql.conf o vía pgAdmin o directamente:
C:\Program Files\PostgreSQL\9.5\data
SHOW config_file;
shared_buffers
"Memoria caché" de PostgreSQL
Default value: typically 32MB
Recommended value: 75% of database memory (servidor dedicado: aprox. 1/4 RAM)
work_mem
"RAM" de PostgreSQL
Default value: 1-4MB
Recommended value: 16MB
maintenance_work_mem
"RAM" para VACUUM y ANALYZE de PostgreSQL
Default value: 16MB
Recommended value: 128MB
SET maintenance_work_mem TO '128MB';
VACUUM ANALYZE;
SET maintenance_work_mem TO '16MB';
Vacuum analyze
wal_buffers
Memoria temporal para escribir una transacción
Default value: 64kB
Recommended value: 1MB (lo que necesite la mayor transacción)
Reiniciar PostgreSQL
Testear y buscar parámetros óptimos para nuestros datos
select * from pg_settings
Más parámetros (max_connections ...)
4.2 Seguridad, encriptación
C:\Program Files\PostgreSQL\9.5\data
postgresql.conf
listen_addresses = '*'
#listen_addresses = 'localhost'
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local all postgres ident
local all all md5
#solo localhost
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
#todo abierto
host all all 0.0.0.0/0 md5
host all all ::0/0 md5
#abierto para una BD
host bd_abierta all 0.0.0.0/0 md5
host bd_abierta all ::0/0 md5
Más posibilidades
Acceso remoto vía túnel
# TYPE DATABASE USER ADDRESS METHOD
local all postgres ident
local all all md5
host all all 127.0.0.1/32 md5
listen_addresses = 'localhost'
Ejercicio seguridad
Dar acceso sólo a:
- Usuario 'user1' a la BD 'prueba', sin identificarse, desde localhost
- Usuario 'user2' a todas las BD si se identifica con pwd encriptado desde la red interna 192.168.x.x
- Todas las peticiones provenientes de 192.168.173.133 deben rechazarse
Usuarios y roles
CREATE ROLE mrbean LOGIN;
CREATE USER mrbean;
CREATE ROLE acceso_a_contabilidad; -- GRUPO DE USUARIOS
Usuarios y roles
SELECT rolname FROM pg_roles;
Usuarios read-only
-- A user account for the web app
CREATE USER app1;
-- Web app needs access to specific data tables
GRANT SELECT ON nyc_streets TO app1;
-- A generic role for access to PostGIS functionality
CREATE ROLE postgis_reader INHERIT;
-- Give that role to the web app
GRANT postgis_reader TO app1;
Ejercicio: usuario writer
- Via pgAdmin o consola.
- Puede escribir en la BD nyc
- Puede leer las otras BD
Encriptación
Se suele usar md5 (cifrado en un solo sentido)
SELECT md5('password a encriptar');
-- Ver usuario creado --
4.3 Definición de datos: restricciones
Modificar tablas
Restricciones (constraints)
Check constraints
Ejercicio: campo blkid de la tabla nyc_census_blocks empiece por '36'
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
Not-null constraints
Ver id cualquier tabla
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
Unique constraints
Se usa más PRIMARY KEY
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);
Primary key constraints
- Implica UNIQUE + NOT NULL
- Además crea un índice automático
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
Foreign key constraints
Ejercicio: crear una tabla de boroughs (Brooklyn, Manhattan, Queens, Staten Island y The Bronx) y poner restricción a nyc_census_blocks
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
Foreign key constraints
Ejercicio: crear una tabla de boroughs (Brooklyn, Manhattan, Queens, Staten Island y The Bronx) y poner restricción a nyc_census_blocks
SELECT DISTINCT boroname FROM nyc_census_blocks;
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
Conformidad de geometrías
Podemos usar cualquier función definida por nosotros
ALTER TABLE mytable
ADD CONSTRAINT geometry_valid_check
CHECK (ST_IsValid(the_geom));
4.4 Definición de datos: RLS y herencia
Row level security (RLS)
Las tablas pueden tener reglas de seguridad para filas que restringen, para diferentes usuarios, qué filas son retornadas por consultas normales o insertadas, modificadas y borradas.
Row level security
ALTER TABLE ... ENABLE ROW LEVEL SECURITY
Ejemplo Row level security
Herencia
CREATE TABLE cities (
name text,
population float,
altitude int -- in feet
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
Ejercicio herencia
Una tabla nueva que debe añadir a nyc_subway_stations una nueva línea de metro a las existentes, con un campo extra fecha_de_inauguracion (date)
Herencia: ONLY
SELECT name, altitude
FROM cities
WHERE altitude > 500;
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
4.5. PostgreSQL: vistas
Crear vista
CREATE VIEW county_view AS
SELECT
ST_Union(geom) AS geom,
SubStr(blkid,1,5) AS countyid
FROM nyc_census_blocks
GROUP BY countyid;
Usar vista
SELECT * FROM county_view;
SELECT * FROM county_view WHERE countyid='36005';
-- comparar tiempos
Crear vista materializada (>9.3)
CREATE MATERIALIZED VIEW county_view_mat AS
SELECT
ST_Union(geom) AS geom,
SubStr(blkid,1,5) AS countyid
FROM nyc_census_blocks
GROUP BY countyid;
Refrescar vista materializada
REFRESH MATERIALIZED VIEW county_view_mat;
-- comparar tiempos
En realidad, es una tabla
- Puede tener índices
- Útil para acceder a sistemas remotos
Estrategias con vistas materializadas
4.6 PostGIS: indexación
R-Tree
Añadir índice
CREATE INDEX <INDEXNAME> ON <TABLENAME> USING GIST (<GEOMETRYCOLUMN>);
Borrar índice
DROP INDEX nyc_census_blocks_blkid_idx;
SELECT blocks.blkid
FROM nyc_census_blocks blocks
JOIN nyc_subway_stations subways
ON ST_Contains(blocks.geom, subways.geom)
WHERE subways.express = 'express';
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 'nyc%';
Todos los índices
4.7 PostgreSQL: secuencias
Es crucial tener una PRIMARY KEY! (no OID)
No nula + única + autoincremental: secuencias
Tablas ejemplo nyc
-- primary key sin secuencia
id numeric NOT NULL,
(...)
CONSTRAINT nyc_subway_stations_pkey PRIMARY KEY (id)
-- primary key con secuencia
id integer NOT NULL DEFAULT nextval('nyc_neighborhoods_id_seq'::regclass),
(..)
CONSTRAINT nyc_neighborhoods_pkey PRIMARY KEY (id)
-- probar inserciones
Creación secuencia con SERIAL
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR(100) NOT NULL,
primary_author VARCHAR(100) NULL
);
ALTER TABLE nyc_census_counties ADD COLUMN id SERIAL PRIMARY KEY;
Creación secuencia manual
CREATE SEQUENCE books_sequence
start 2
increment 2;
INSERT INTO books
(id, title, primary_author)
VALUES
(nextval('books_sequence'), 'The Hobbit', 'Tolkien');
5. Stored Procedures
5.1 User-defined functions
Lenguajes soportados
PostgreSQL soporta 3 procedural languages:
- SQL
- PL/pgSQL
- C
- Se pueden cargar más procedural languages ej.,Perl, Python, y TCL en PostgreSQL usando extensiones
¿Cuándo conviene usar funciones?
Funciones vs. Stored Procedures
En la mayoría de casos, una stored procedure sirve para:
-
Realizar acciones sin retornar ningún resultado (INSERT, UPDATE operations i.e.)
-
Retornar uno o más valores como parámetros OUT
-
Retornar uno o más result sets
Ejemplo función
-- Function increments the input value by 1
CREATE OR REPLACE FUNCTION increment(i INT) RETURNS INT AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
-- An example how to use the function (Returns: 11)
SELECT increment(10);
Ejemplo Stored Proc
-- Procedure to insert a new city
CREATE OR REPLACE FUNCTION add_city(city VARCHAR(70), state CHAR(2))
RETURNS void AS $$
BEGIN
INSERT INTO cities VALUES (city, state);
END;
$$ LANGUAGE plpgsql;
Ejercicio: hacer una función similar que añada una estación de metro en nyc_subway_stations a partir de nombre, X e Y (en lat/lon)
Ejemplo Stored Proc 2
CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT city, state FROM cities;
RETURN ref;
END;
$$ LANGUAGE plpgsql;
5.2 PL/pgSQL
Parecido a PLSQL (Oracle)
Estructura PL/pgSQL
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
quantity := 50;
--
-- Create a subblock
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
END;
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
Tutorial
5.3 Upgrades
Major o minor upgrade?
- Minor: pgsql 9.5.0 a 9.5.7
PostGIS 2.1.2 a 2.2.0 - Major: pgsql 9.5.7 a 9.6.0
PostGIS 1.x a 2.x
Minor upgrade
- PostgreSQL: stop the server, reinstall, start the server
- PostGIS
ALTER EXTENSION postgis UPDATE TO '2.1.2';
Major upgrade
- pg_dump y pg_restore
- pg_upgrade
Documentación upgrades
5.4 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();
Ejemplo trigger geometría
Las manzanas contienen parcelas. Las parcelas contienen construcciones. Queremos comprobar que una construcción se crea dentro de la parcela y manzana correctos.
Ejemplo trigger geometría
CREATE TABLE parcelas (
gid serial NOT NULL,
the_geom geometry(Multipolygon,4326),
"CODIGO" text, --CODIGO DE LA PARCELA
"COD_MANZ" text --CODIGO DE LA MANZANA
);
CREATE TABLE construcciones (
gid serial NOT NULL,
the_geom geometry(Multipolygon,4326),
"CODIGO" text, --CODIGO DE LA CONSTRUCCION
"COD_PAR" text,--CODIGO DE LA PARCELA
"COD_MANZ" text--CODIGO DE LA MANZANA
);
Ejemplo trigger geometría
CREATE OR REPLACE FUNCTION practica() RETURNS TRIGGER AS
$$
DECLARE
g_par geometry ;
BEGIN
IF (TG_OP = 'INSERT ' OR TG_OP = 'UPDATE ' ) THEN
-- VERIFICA EL TIPO DE GEOMETRIA DE CONSTRUCCIONES SEA MULTIPOLYGON
IF ST_GeometryType(NEW.the_geom ) != 'ST_MultiPolygon ' THEN RAISE NOTICE 'LA GEOMETRIA NO ES MultiPolygon '; RETURN NULL ;
END IF ;
-- SE BUSCA LA GEOMETRIA DE LA PARCELA QUE CONTIENE LA CONSTRUCCION
SELECT p.the_geom INTO g_par FROM parcelas p WHERE p."CODIGO" = NEW."COD_PAR" AND p."COD_MANZ" = NEW."COD_MANZ" ;
IF NOT FOUND THEN -- SI NO TRAE NADA
RAISE EXCEPTION 'NO HAY PARCELA PARA ESTE CODIGO % MANZANA % ',
NEW."COD_PAR" , NEW."COD_MANZ" ;
RETURN NULL ;
ELSE
-- SI TIENE PARCELA SE EVALUA SI LA GEOMETRIA DE LA CONSTRUCCION ESTA CONTENIDA EN LA PARCELA CORRESPONDIENTE
IF NOT (ST_Contains(g_par , NEW.the_geom )) THEN
RAISE NOTICE 'LA GEOMETRIA NO ESTA CONTENIDA POR LA PARCELA ';
RETURN NULL ;
END IF ; END IF ;
RETURN NEW ;
END IF ;
END ;
$$ LANGUAGE plpgsql ;
Historial de ediciones con triggers
Presentación Hexagon - dia2
By mpericay
Presentación Hexagon - dia2
- 515