Download Introduction to PostGIS Workshop

Curso de PostGIS
ÍNDICE
•
•
•
•
•
•
•
•
•
Introducción a BD espaciales
Instalación PostgreSQL/PostGIS
Creación de BD
Carga cartográfica
Carga alfanumérica
Cosultas SQL alfanuméricas
Cosultas SQL espaciales
Configuración
Acceso (lectura/edición) desde otros clientes
Introducción a BD
• Los Sistemas Gestores de Bases de Datos Relacionales
(SGBDR, RDBMS en inglés) ofrecen
– Tolerancia a fallos
– Transacciones ACID (Atomicity, Consistency,
Isolation, Durability)
– Acceso multiusuario a grandes volúmenes de datos
– Consultas escalables en grandes bases de datos que no
caben en la memoria de los equipos
– Control de seguridad, acceso y bloqueos
– Lenguajes de programación: API’s
– Lenguaje de consulta SQL y modelado DDL
– Libera al usuario de operaciones complejas
– Tipos de datos no pensados para datos espaciales
Introducción a BD
AÑO DE APARICIÓN APROXIMADA
MySql
1990 (+18)
MS SQL Server 1989 (+19)
PostgreSQL
IBM DB2
Oracle
1986 (+22)
1983 (+25)
1977 (+30)
Introducción a BD
1970
Teoría relacional
Ingres
Oracle
1980
Ingres
Sybase
Postgres
SQL Server
1990
Illustra
Informix (IUS)
2000
Ingres
Postgres95
PostgreSQL
Introducción a BD espaciales
• Las primera versiones de SGBDR espaciales
son relativamente nuevas:
– Oracle Spatial
• 1996 SDO.
• 1998 Spatial.
• 2000 8i Spatial – primera implementación nativa.
–
–
–
–
PostGIS 0.1,mayo 2001
MySql 4.1.0, abril 2003
MS SQL Server. No disponible
IBM DB2 Spatial Extender.
• 1997 – Spatial Extender for IBM DataJoiner.
• 2001 – Spatial Extender for DB2 Distributed
Introducción a BD espaciales
• En las primeras implementaciones SIG, los
datos espaciales y los atributos a ellos
referidos se almacenaban de forma
independiente. Los atributos se solían
almacenar en una base de datos (o fichero), y
la información espacial en formato propietario.
• Las bases de datos espaciales nacieron cuendo
empeezaron a considerarse los objetos
espaciales como el núcleo de la BD
Introducción a BD espaciales
Consultas espaciales utilizando SQL
Uso de expresiones SQL simples para obtener
relaciones espaciales
• Distancia
• Adyacencia
• Contenido
Uso de expresiones SQL simples para obtener
operaciones espaciales
• Área, Longitud, Intersección, Unión, Buffer,
…
Introducción a BD espaciales
Desventajas
•
•
•
•
•
Alto coste de implementación
Poca flexibilidad
Incompatibilidad con algunos programas SIG
Más lento que pequeños ficheros en local
Necesidad de conocimientos de DBA
Introducción a BD espaciales
Oferta actual
•
•
•
•
•
•
•
•
ESRI ArcSDE 9.2(sobre varias DB’s)
Oracle Spatial 11g. Soporte 3D y GeoRaster
IBM DB2 Spatial Extender
Informix Spatial DataBlade (comprada por IBM en
2001)
MS SQL Server (Katmai). Disponible este año
Geomedia 6 (sobre varias DB’s)
PostGIS 1.3 / PostgreSQL 8.3
MySQL Spatial 5.1
Introducción a BD espaciales
OGC
“Much geospatial data is available on the web and in offline archives, but it is complex, heterogeneous, and
incompatible. Users must possess considerable expertise
and special geographic information system (GIS) software
to overlay or otherwise combine different map layers of
the same geographic region. Data conversion is
cumbersome and time-consuming, and the results are
often unsatisfactory. Common interfaces are the only
way to enable overlays and combinations of complex and
essentially different kinds of geographic information to
happen automatically over the Internet, despite
differences in the underlying GIS software systems. OGC
brings together the key players and provides a formal
structure for achieving consensus on the common
interfaces.”
Introducción a BD espaciales
• Open Geospatial Consortium
– 1994 – Fundación
– 1997 – Especificación Simple Features for SQL
• ISO
– SQL/MM – Extensión para Multimedia/SQL
– 19125 – OGC Simple Features + SQL/MM
Introducción a BD espaciales
• Componentes que deben estar
presentes en un SGDBR espacial
– Tipo de dato espacial
– Esquema de indexación espacial
– Operadores espaciales.
Introducción a BD espaciales
Tipo de dato espacial
Introducción a BD espaciales
Tipo de dato espacial (PostGIS)
Introducción a BD espaciales
Tipo de dato espacial (PostGIS)
Introducción a BD espaciales
Tipo de dato espacial (Oracle Spatial)
Objeto SDO_GEOMETRY:
SDO_GTYPE
SDO_SRID
SDO_POINT
SDO_ELEM_INFO
SDO_ORDINATES
NUMBER
NUMBER
SDO_POINT_TYPE
SDO_ELEM_INFO_ARRAY
SDO_ORDINATE_ARRAY
Ejemplo:
SQL> CREATE TABLE us_states (
2
state
VARCHAR2(30),
3
totpop
NUMBER(9),
4
geom
SDO_GEOMETRY);
Introducción a BD espaciales
Tipo de dato espacial (Oracle Spatial)
SDO_POINT_TYPE:
x
y
z
NUMBER
NUMBER
NUMBER
SDO_ELEM_INFO_ARRAY:
VARRAY (1048576) OF NUMBER
SDO_ORDINATE_ARRAY:
VARRAY (1048576) OF NUMBER
Introducción a BD espaciales
Esquema de indexación espacial
Introducción a BD espaciales
Índice R-tree
Introducción a BD espaciales
Índice R-tree
Introducción a BD espaciales
Operadores espaciales
ST_Distance(geometry, geometry)
Devuelve la distancia cartesiana en unidades de
proyección entre 2 geometrías.
ST_DWithin(geometry, geometry, float)
Devuelve verdadero si las geometrías están, una
con respecto a otra, dentro de la distancia
especificada
…
Introducción a BD espaciales
Evolución de la
tecnología
de DBMS
Introducción a BD espaciales
• DBMS Post-relacionales
– Soportan tipos de datos abstractos definidos por el
usuario
– Se pueden añadir tipos de datos espaciales (p.e.
polígonos)
• Elección de DBMS post-relacional
– Orientada a objeto (OO) DBMS
– Objeto-relacional (OR) DBMS - PostgreSQL/PostGIS
Introducción a BD espaciales. Resumen
• SDBMS (Spatial DBMS)
– Trabaja con un DBMS de fondo
– Proporciona ADTs (spatial Abstract Data
Types ) espaciales accesibles desde un
lenguaje de consultas (SQL)
– Proporciona métodos paa un procesamiento
eficiente de consultas espaciales
Introducción a BD espaciales. Resumen
Componentes de un SDBMS
-Modelo de datos espacial, Tipos de datos
espaciales y Operadores espaciales
-Lenguaje de consultas, procesado y
optimización espacial
-Data mining espacial (principio de buscar
en grandes volúmenes de datos para obtener
información relevante )
Introducción a PostgreSQL
¿Por qué elegir PostgreSQL/PostGIS?
–
–
–
–
Confianza probada por múltiples usuarios
¡Sin coste!
Soporta la mayoría de los estándares SQL
Posibilidad de añadir tipos de datos definidos
por el usuario
– TOAST – Sin límite en el tamaño de la
columna
– Índices geográficos GiST
– Fácil añadir funciones propias
Introducción a PostgreSQL
• Añade soporte para objetos geográficos
a la base de datos objeto-relacional
PostgreSQL
• PostgreSQL posee “tipos geométricos”
pero estas geometrías nativas están
demasiado limitadas para las
operaciones espaciales con datos SIG
Introducción a PostgreSQL
• Características
–
–
–
–
–
ACID
SQL 92
Replicación
Triggers, Procedimientos (PL/PgSQL, PL/R)
Backups en caliente, WAL’s / PITR
• Mejor que MySQL
• Tan bueno como el propietario
– Mejor en algunos aspectos
Introducción a PostgreSQL
• ¡ Necesito una base de datos!
• ¿MySQL?
• ¡Necesito transacciones, Triggers,
lenguajes procedurales, integridad!
Introducción a PostgreSQL
• ¿ Y Oracle?, Todo el mundo lo
compra
• ¿Disponemos del dinero?
Introducción a PostgreSQL
• ¿SQL Server?, todos lo soportan.
• No es gratuito. Además, no soporta
operaciones espaciales ( de
momento…?)
Introducción a PostgreSQL
Escalabilidad
“Enterprise”
Oracle
IBM DB2
MS SQL Server
IBM Informix
PostgreSQL
1 Dual-Core
$40,000
$36,400
$25,000
$50,000
$0
2 Quad-Core
$160,000
$145,600
$50,000
$200,000
$0
Introducción a PostGIS
• Junio 2001 –Versión 0.1
• Tipos espaciales relacionados con GiST
• Julio 2001 – Versión 0.5
• Compatible con UMN MapServer
• Mayor capacidad de visualizar datos
• 2003 –Versión 0.8
• Pasa los test de conformidad del OGC
• Mayo 2007 – Versión 1.2
• Soporte completo de OGC SF-SQL
Introducción a PostGIS
• Geometría
– POINT, LINESTRING, POLYGON, MULTIPOINT,
MULTILINETRING, MULTIPOLYGON,
GEOMETRYCOLLECTION
CURVESTRING, CURVEPOLYGON,
COMPOUNDCURVE
• Índices
– R-TREE
• Funciones
– OpenGIS “Simple Features for SQL”
– ISO SQL/MM
– Más de 300 funciones
Introducción a PostGIS
• Integración
–
–
–
–
–
–
–
–
–
–
–
Mapserver
Geotools (Geoserver, uDig)
FDO (Mapguide, Autodesk Map 3D)
JUMP (OpenJUMP, Kosmo)
OGR (QGIS, Mapserver, GRASS)
FME (ArcGIS Data Interoperability Extension)
Cadcorp SIS
Manifold
Ionic Redspider
ESRI ArcSDE 9.3
Python / Perl / PHP
Introducción a PostGIS
• Simplicidad
– Polígono PostGIS
• POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))
– Polígono Oracle
• MDSYS.SDO_GEOMETRY(
2003, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,0, 0,1, 1,1,
1,0, 0,0))
Introducción a PostGIS
• Velocidad
–
–
–
–
Implementación de geometría Lightweight
Índices Lightweight (ahorros de tamaño del 50%)
Linear time R-Tree
Algoritmo de bloqueo a nivel de fila
• Características
– Todas las funciones OGC Simple Features for SQL
– Agregados, ST_Collect(), ST_Union()
– Extras, ST_AsGML(), ST_AsKML(), ST_AsSVG()
ST_BuildArea(), ST_LineMerge, ST_Transform()
Introducción a PostGIS
Precio
Funcionalidad
Introducción a PostGIS
• Las BD son mejores que los ficheros
• Almacenamiento unificado, Gestión,
Acceso
• Todo es SQL
• Integridad transaccional
• Múltiples usuarios y ediciones
Introducción a PostGIS
LAN
Mapserver
uDig
QGIS
GRASS
ArcGIS
gvSIG
PostGIS
GeoServer
MapGuide
Internet
OpenIMF
Cliente
Web
uDig
Introducción a PostGIS
- Mucha gente utiliza PostGIS …
• 1.400 miembros de listas de distribución
• 14.000 visitas/mes
10.000 visitantes/mes
• 100 descargas de código fuente diarias
100 descargas de binarios windows diarias
• 970.000 resultados de búsquedas de Google
• Google trends …
Instalación de PostgreSQL
• Windows Installer
– PostgreSQL 8.2.4
– PgAdmin III
• Se instala como un servicio para que se
inicie de forma automática
• Demostración de instalación …
Instalación de PostgreSQL
Instalación de PostgreSQL
• Directorios creados durante la
instalación:
–
–
–
–
\bin
\include
\lib
\share
Ejecutables
Ficheros para la compilación
Librerías DLL compartidas
Extensiones
Instalación de PostgreSQL
• Herramientas incluidas con la
instalación:
•
PgAdmin III
•
Línea de comandos psql
Instalación de PostGIS
Instalación de PostGIS
• PostGIS viene incluido en PostgreSQL,
pero con cierto retraso de versiones
• La versión actual es 1.3.2
(1.1.x es la que se incluye con
PostgreSQL)
• Vamos a instalar PostGIS y a crear una
base de datos
Instalación de PostGIS
• Una instancia PostgreSQL posee una versión
de software y un puerto de red (5432)
• Una instancia contiene una o varias bases de
datos
• Una base de datos contiene uno o varios
esquemas
– Public es el habitual
• Un esquema contiene una o varias tablas
– public.geometry_columns
• Una tabla contiene varias filas
Conectarse a PostGIS
• Conectarse a la instancia
• Crear una nueva conexión al equipo XXX
• Usario postgres, Contraseña postgres
• Crear un Usuario (siu)
• Crear Tablespace (siu_tbl)
• Crear una nueva base de datos (siu)
• Seleccionar template_postgis como plantilla
• Conectarse a la base de datos
• Comprobar la existencia de la tablas espaciales del
sistema
• spatial_ref_sys
• geometry_columns
2.3 – Spatially Enable PostgreSQL
Sin template_postgis
• Crear una base de datos nueva
• Seleccionar template1 como plantilla
• Conectarse a la base de datos
• Ejecutar la extensión PostGIS
(C:\Archivos de programa\PostgreSQL\
8.2\share\contrib\lwpostgis.sql)
• Ejecutar el sistema de referencia
espacialde PostGIS (spatial_ref_sys.sql)
2.3.1 – Sin template_postgis
• Ejecutar PG Admin III …
Utilizando PostGIS
Crear tablespace nuevo
Utilizando PostGIS
Crear un usuario nuevo
Utilizando PostGIS
Crear Base de Datos nueva
Utilizando PostGIS
EJERCICIOS
D:\Vivienda\Curso_POSTGIS\Curso_PostGIS.txt
¡Conjunto de comandos SQL para
no tener que teclear!
SQL básico
-- CREAR UNA TABLA
-- BORRAR UNA TABLA
-- INSERTAR REGISTROS EN TABLA
-- SELECCION BÁSICA
-- EXPRESIONES
-- CONDICIÓN WHERE
-- ORDENAR
-- QUITAR DUPLICADOS
SQL básico
-- CREAR TABLA PROVINCIAS
-- CARGAR DATOS PROVINCIAS
-- CREAR TABLA POBLACION
-- CARGAR DATOS POBLACION
-- CREAR TABLA MUNICIPIOS
-- CARGAR DATOS POBLACION
-- CREAR TABLA PROV_TOTAL
-- CARGAR DATOS PROV_TOTAL
SQL básico
-- ENLAZAR TABLAS
-- INNER JOIN
-- OUTER JOIN
-- ALIAS DE TABLAS
-- ALIAS DE CAMPOS
-- FUNCIONES DE AGREGACION
-- MODIFICAR DATOS
-- BORRADOS
-- CREAR VISTA
-- SENTENCIA SQL ESPACIAL SENCILLA
SQL espacial sencillo
Crear geometrías “manualmente”
create table puntos (pt geometry, name varchar);
insert into puntos values ('POINT(0 0)', 'Origin');
insert into puntos values ('POINT(5 0)', 'X Axis');
insert into puntos values ('POINT(0 5)', 'Y Axis');
select name, ST_AsText(pt),
ST_Distance(pt, 'POINT(5 5)') from puntos;
SQL espacial sencillo
OGC Tablas de Metadatos
GEOMETRY_COLUMNS
–
–
–
–
–
–
–
F_TABLE_CATALOG = ‘’
F_TABLE_SCHEMA = ‘public’
F_TABLE_NAME = ‘prueba’
F_GEOMETRY_COLUMN = ‘the_geom’
COORD_DIMENSION = 2
SRID = 23030
TYPE = ‘MULTILINESTRING’
OGC Tablas de Metadatos
• Tabla "GEOMETRY_COLUMNS“
– Guarda un índice de tablas que contienen algún
campo con geometría
Nombre completo de la
tabla que contiene la
geometría.
Nombre del campo
que contiene la
geometría en la
tabla en cuestión
Dimensión espacial (2,
3, 4) de la columna de la
geometría.
ID del Sist. Ref. Espacial
usada por la geometría en
dicha tabla.
Tipo del
objeto
espacial
(Punto,
Poligonal…)
OGC Tablas de Metadatos
SPATIAL_REF_SYS
–
–
–
–
–
SRID = 23030
AUTH_NAME = ‘EPSG’
AUTH_SRID = 23030
SRTEXT = ‘PROJCS["ED50 / UTM zone 30N“…’
PROJ4TEXT = ‘+proj=aea …’
OGC Tablas de Metadatos
• Tabla “SPATIAL_REF_SYS“
– Contiene los identificadores numéricos y
descripciones textuales de los Sist. de
Referencia.
Nº entero que identifica
unívocamente a ese
Sist. Ref. espacial en la
Base de Datos
Nombre del estándar
para este Sist. Ref.
ID del Sist. Ref. tal y
como lo define el
estándar que
aparece en
auth_name
La
representación
Well-Known
Text del Sist.
Ref. Espacial
Cargar ficheros SHAPE
• Fichero SHAPE (Compuesto por 3
ficheros)
– .SHP = geometría
– .DBF = atributos
– .SHX = índice
• Tabla PostGIS/PostgreSQL
– Las columnas pueden ser geometría
– Las columnas pueden ser atributos
• Un fichero SHAPE = Una tabla PostGIS
Cargar ficheros SHAPE
• shp2pgsql [opts] shapefile tablename
– shp2pgsql –i –s 23030 ccaa.shp
ccaa > ccaa.sql
• Lee el fichero .shp
• Crea fichero .sql
• Cargar fichero .sql en PostgreSQL
– Utilizando psql
– Utilizando PgAdmin
Cargar ficheros SHAPE– shp2pgsql
Cargar ficheros SHAPE
Ejecutar  cmd.exe
Cargar ficheros SHAPE
notepad ccaa.sql
Opciones de línea de comandos
-i = No utilizar enteros largos
-s <#> = SRID (Referencia Espacial)
-W <encoding> = Encoding de los datos
-a = Añadir
- I = Crea un índice espacial GiST
Cargar ficheros SHAPE
pg_shpsql.bat
Cargar ficheros SHAPE
• Psql
–d SIU
–U siu
–f ccaa.sql
psql -f ccaa.sql -h port-cafc -U siu -d siu
Cargar ficheros SHAPE
Ríos_CEDEX
Urbano
Municipios
Rios
Provincias
Red
Autovías
CCAA
Viaria
Cargar tablas
• Probaremos con MS Access
• Instalación de Driver ODBC
– D:\Vivienda\Curso_PostGIS\Driver_ODBC
• Cargamos la tabla MUNICIPIOS
(D:\Vivienda\Curso_PostGIS\BD\Base_Datos_C
artografia_XP.mdb\Municipios)
• Definimos DRIVER ODBC de PostgreSQL
UNICODE
• Entramos en Access y exportamos a bases de
datos ODBC
Crear índices espaciales
• PostgreSQL soporta 3 clases de índices
por defecto:
– Índices B-Tree
– Índices R-Tree
– Índices GiST
Crear índices espaciales
• B-Trees se utilizan para datos que pueden ser ordenados a lo
largo de un eje: números, letras o fechas. Los datos SIG no
pueden ordenarse de forma racional a lo largo de un eje (¿qué es
mayor, (0,0), (0,1) o (1,0)?)
• R-Trees dividen los datos en rectángulos y subrectángulos. Se
usan por muchas bases de datos espaciales para indexar datos,
pero la implementación del índice R-Trees de PostgreSQL no es
tan robusta como la implementación de GiST.
• GiST (Generalized Search Trees) los datos se dividen en grupos
como “elementos a un lado", “elementos que solapan",
“elementos que están dentro". Pueden utilizarse en multitud de
tipos de datos, incluidos los geográficos. PostGIS utiliza un índice
R-Tree implementado sobre un tipo GiST para indexar sus datos.
Crear índices espaciales
• Los índices GiST tienes 2 ventajas sobre
los R-Tree en PostgreSQL.
• Son "null safe", pueden indexar columnas
con valores nulos.
• Soportan el concepto "lossiness“, o
pequeña pérdida, importante al tratar
con objetos que superanel tamaño de
página de 8K de PostgreSQL 8K.
Crear índices espaciales
- PostGIS implementa índices R-Tree sobre
el sistema de indexado GiST
- Organiza los datos en rectángulos
enlazados para una consulta rápida
- No es necesario crearlos, porque hemos
usado la opción –I de shp2pgsql
CREATE INDEX ccaa_gidx ON ccaa USING
GIST (the_geom);
Utilizar índices espaciales
Los índices entran en juego cuando
PostgreSQL reconoce un operador en la
sentencia SQL. Por ejemplo:
- SELECT * FROM tabla WHERE nombre =
‘Pablo’
= es un operador
- SELECT * FROM tabla WHERE edad < 2
< es un operador
Utilizar índices espaciales
• El operador del índice espacial es “&&”
– “Cajas externas () se tocan”
A && B = TRUE
A && B = FALSE
Utilizar índices espaciales
• ¡MBR (Mean Bounding rectangle) no es
suficiente!
A && B = TRUE
_ST_Intersects(A && B) = FALSE
• Se necesitan dos pasos
– Se utiliza MBR para reducir candidatos
– Para obtener resultados reales se utilizan
relaciones topológicas reales
Utilizar índices espaciales
A && B AND _ST_Intersects(A,B)
ST_Intersects(A,B)
Utilizar índices espaciales
A && B
Utilizar índices espaciales
A && B
Utilizar índices espaciales
_ST_Intersects(A,B)
Utilizar índices espaciales
• Las operaciones de indexación (&&) están
construidas dentro de las funciones más
comunes para automatizar su uso, pero
pueden ser usadas de forma separada.
– ST_Intersects(G1,G2)
• G1 && G2 AND _ST_Intersects(G1,G2)
–
–
–
–
ST_Contains(G1,G2)
ST_Within(G1,G2)
ST_Touches(G1,G2)
ST_DWithin(G1,G2,D)
• G1 && ST_Expand(G2,D) AND
ST_Distance(G1,G2) > D
Prueba índices espaciales
• Ejecución de consulta con una función no indexada
– SELECT gid, code_00_5 from clc_00_5_30
WHERE _ST_Crosses( the_geom,
ST_GeomFromText('LINESTRING(681592
4438183,723459 4464818)‘’, 23030) );
• Ejecución de consulta con una función indexada
– SELECT gid, code_00_5 from clc_00_5_30
WHERE ST_Crosses( the_geom,
ST_GeomFromText('LINESTRING(681592
4438183,723459 4464818)' , 23030) );
• ¿Alguna diferencia?
Índices y Query Plans
• Ejecutar las consultas utilizando el
botón “Explain” en lugar de “Run”
• Fijarse en cómo la base de datos está
utilizando los índices
• Pulsar en los iconos para obtener
información sobre cada paso de la
consulta
Índices y Query Plans
Índices y Query Plans
Cuando Query Plans van mal
• La base de datos construye “planes” basados
en estadísticas sobre la distribución de los
datos muestreados de las tablas
– Siempre intenta ser “selectivo”, para seleccionar
el menor número de registros necesarios para
pasar al siguiente paso.
• La base de datos crear malos planes cuando
tiene malas estadísticas
• Con el comando ANALYZE se actualizan
estas estadísticas
Cuando Query Plans van mal
• EXPLAIN ANALYZE SELECT gid,
texto FROM vias WHERE
_ST_Crosses(the_geom,
ST_GeomFromText('LINESTRING(68
1592 4438183,723459 4464818)',
23030));
Visualizar datos PostGIS
• Programas visores
–
–
–
–
–
–
uDig
QGIS
gvSIG
CadCorp SIS
FME Viewer
Jump
Aplicaciones web
MapGuide
Mapserver
Geoserver
Optimización de PostgreSQL
• Los parámetros de configuración de
PostgreSQL se gestionan en el fichero
postgresql.conf
• Programs =>PostgreSQL 8.2 =>
Configuration Files =>Edit postgresql.conf
• Algunos parámetros exigen reiniciar la base
de datos
• Algunos puede cambiarse en tiempo de
ejecución mediante el comando SET
Optimización de PostgreSQL
• PostgreSQL parte de parámetros muy
conservadores
– Utiliza muy poca memoria
– Se ejecuta en hardware muy limitado
• El acceso a disco siempre es lento, por lo
que puede conseguirse un rendimiento
mayor utilizando más memoria para cachear
datos
– Incrementar shared_buffers 250 MB
– RAM – 25%-50% del total
Optimización de PostgreSQL
• Ordenar es más rápido en memoria
– Incrementar work_mem 128 MB
• El vacioado de disco es más rápido con más
memoria
– Incrementar maintenance_work_mem 128MB
• Asignado por conexión
• También
– Incrementar wal_buffers 1MB
– Incrementar checkpoint_segments 10
– Reducir random_page_cost
Análisis Espacial
• ST_Intersects(A, B)
Análisis Espacial
• ST_Contains(A, B)
• ST_Within(B, A)
Análisis Espacial
• ST_Touches(A, B)
Análisis Espacial
• ST_Crosses(A, B)
Análisis Espacial
• ST_DWithin(A, B, D)
D
Análisis Espacial
¿Cuál es la longitud total en kilómetros
de carreteras?
• SELECT
Sum( ST_Length( the_geom ) ) / 1000
AS vias_km
FROM vias;
Análisis Espacial
¿Qué superficie, en hectáreas, tiene la
provincia de Cuenca?
• SELECT
ST_Area(the_geom)/10000
AS hectares
FROM prov
WHERE
nombre = ‘Cuenca’;
Análisis Espacial
¿Cuál es el municipio de mayor
superficie?
• SELECT
nombre,
ST_Area(the_geom)/10000
AS hectareas
FROM muni
ORDER BY hectareas DESC
LIMIT 1;
Análisis Espacial
¿Cuál es el perímetro del municipio de
Ambite?’
• SELECT ST_Perimeter(the_geom)
FROM muni
WHERE nombre = ‘Ambite’;
Análisis Espacial
¿Cuál es el área total de todos los
municipios de madrid, en hectáreas
• SELECT
Sum(ST_Area(the_geom))/10000
AS hectares
FROM muni;
Análisis Espacial
¿Cuál es el área total (en hectáreas) de
todos los municipios con más de
1.000.000 habitantes?
• SELECT
sum(ST_Area(the_geom))/10000 as
hectares FROM prov WHERE pob2005 >
1000000;
Análisis Espacial
¿Cuál es la longitud total de la
carretera A3?
• SELECT
Sum(ST_Length(the_geom))/1000 AS
kilometers
FROM vias
WHERE texto = ‘A-3’ OR texto = ‘N-III’;
Análisis Espacial
¿Qué población tienen los municipios
que se encuentran a 5 km de una
coordenada?
• SELECT nombre, sum(censo_2001) as
muni_cerca FROM muni where
ST_DWithin(the_geom,
ST_GeomFromText('POINT(500000
4300000)', 23030), 5000) GROUP BY
nombre;
Análisis Espacial
Qué municipios están situados a menos
de 2 km de una mina?
• SELECT nombre FROM muni m, urbano u
WHERE ((u.texto ilike 'Mina%') AND
(ST_DWithin(m.the_geom,u.the_geom,2
000))) GROUP BY nombre;
Análisis Espacial
Núcleos urbanos que está a menos de
250 metros de las autovías
• SELECT h.texto FROM urbano h, autop p
WHERE ST_DWithin(h.the_geom,
p.the_geom, 250);
Análisis Espacial
MUNICIPIOS CRUZADOS POR LA A-3
SELECT nombre FROM muni m, autop u
WHERE ((u.nom_via ilike 'A-3%' OR
u.nom_via ilike 'N-III%') AND
(ST_DWithin(m.the_geom,u.the_geom,2
000))) GROUP BY nombre;
Análisis Espacial
NÚCLEOS URBANOS A MENOS DE 2000
METROS DE LA A-3
– Sin orden optimizado
• SELECT texto FROM urbano m, autop u
WHERE u.nom_via ilike 'A-3%' OR
u.nom_via ilike 'N-III%' and
ST_DWithin(m.the_geom,u.the_geom,2
000) group by texto;
Análisis Espacial
NÚCLEOS URBANOS A MENOS DE 2000
METROS DE LA A-3
– Con orden optimizado
• SELECT texto FROM urbano m, autop u
WHERE((u.nom_via ilike 'A-3%' OR
u.nom_via ilike 'N-III%') AND
(ST_DWithin(m.the_geom,u.the_geom,
2000))) GROUP BY texto;
Solapes
• Las interseciones de capa a capa son
posibles con la función ST_Intersection()
– ST_Intersects(a,b) devuelve BOOLEAN
– ST_Intersection(a,b) devuelve GEOMETRY
ST_Intersects() = TRUE
ST_Intersection() =
Solapes
Crear una nueva tabla que contenga los
municipios intersectados por la
provincia de Madrid
• CREATE
muni_madrid AS
4.4 - TABLE
Overlays
SELECT
ST_Intersection(v.the_geom, m.the_geom)
AS intersection_geom,
ST_Area(v.the_geom) AS va_area,
v.*,
m.nombre
FROM
muni v,
prov m
WHERE
ST_Intersects(v.the_geom, m.the_geom) AND
m.nombre = ‘Madrid’;
• CREATE
muni_madrid2 AS
4.4 - TABLE
Overlays
SELECT
ST_Intersection(v.the_geom, m.the_geom)
AS intersection_geom,
ST_Area(v.the_geom) AS va_area,
v.*,
m.nombre
FROM
muni v,
prov m
WHERE
ST_Within(v.the_geom, m.the_geom) AND
m.nombre = ‘Madrid’;
• CREATE
muni_madrid3 AS
4.4 - TABLE
Overlays
SELECT
ST_Intersection(v.the_geom, m.the_geom)
AS intersection_geom,
ST_Area(v.the_geom) AS va_area,
v.*,
m.nombre
FROM
muni v,
prov m
WHERE
ST_Intersects (ST_Centroid(v.the_geom),
m.the_geom) AND
m.nombre = ‘Madrid’;
Las tras consultas anteriores
crean 3 capas diferentes
Proyección de coordenadas
• c
• SELECT ST_SRID(the_geom)
FROM autop
LIMIT 1;
• ¿Qué significa “23030”?
• SELECT srtext
FROM spatial_ref_sys
WHERE srid = 23030;
Proyección de coordenadas
• "PROJCS["ED50 / UTM zone
30N",GEOGCS["ED50",DATUM["European_Datum_1950",SP
HEROID["International
1924",6378388,297,AUTHORITY["EPSG","7022"]],AUTHORI
TY["EPSG","6230"]],PRIMEM["Greenwich",0,AUTHORITY["E
PSG","8901"]],UNIT["degree",0.01745329251994328,AUTH
ORITY["EPSG","9122"]],AUTHORITY["EPSG","4230"]],PROJE
CTION["Transverse_Mercator"],PARAMETER["latitude_of_o
rigin",0],PARAMETER["central_meridian",3],PARAMETER["scale_factor",0.9996],PARAMETER["false_
easting",500000],PARAMETER["false_northing",0],UNIT["m
etre",1,AUTHORITY["EPSG","9001"]],AUTHORITY["EPSG","2
3030"]]"
Proyección de coordenadas
• ¿Qué es “23030” de nuevo?
• SELECT proj4text
FROM spatial_ref_sys
WHERE srid = 23030;
• "+proj=utm +zone=30 +ellps=intl
+units=m +no_defs "
• PROJ4 es una librería de reproyección de
coordenadas utilizada por PostGIS
Proyección de coordenadas
• La reproyección de coordenadas se realiza
utilizando la función ST_Transform()
• SELECT ST_AsText(the_geom)
FROM vias
LIMIT 1;
• SELECT
ST_AsText(
ST_Transform(the_geom, 4326) )
FROM vias
LIMIT 1;
Proyección de coordenadas
"MULTILINESTRING((487251 4745186,487000
4745526,…))"
ST_Transform(the_geom)
"MULTILINESTRING((-3.15604832809332
42.8575654465415,-3.1591284297366
42.8606229123234,…))"
Ejercicios avanzados
¿Cuál es la longitud de carreteras en Madrid?
• SELECT
Sum(ST_Length(r.the_geom))/1000
AS kilometers
FROM vias r, prov m
WHERE
ST_Contains(m.the_geom, r.the_geom) AND
r.texto = ‘A-3’ OR r.texto = ‘N-III’ AND
m.nombre = ‘Madrid’;
Ejercicios avanzados
¿Qué núcleos urbanos están situados a 500
metros del municipio de Madrid?
• SELECT
p.texto,
Sum(v.censo_2001) AS Pob_2001
FROM urbano p, muni v
WHERE
ST_DWithin(v.the_geom, p.the_geom, 500)
GROUP BY p.texto, p.city
ORDER BY pob_2001 DESC LIMIT 2;
Ejercicios avanzados
¿Cuál es la latitud del núcleo urbano situado
más al norte?
– Pista – El SRID de lat/lon es 4326
• SELECT
ST_Y(ST_Transform(the_geom,4326))
AS latitude
FROM urbano
ORDER BY latitude DESC
LIMIT 1;
Ejercicios avanzados
¿Cuál es el municipio de mayor tamaño que
contiene islas en su interior?
– Pista – Una isla implica más de un anillo
• SELECT
gid,
provmun,
nombre,
ST_Area(the_geom) AS area
FROM muni
WHERE ST_NRings(the_geom) > 1
ORDER BY area DESC
LIMIT 1;
Ejercicios avanzados
DISSOLVE
Ejercicios avanzados
Creamos la tabla origen
BEGIN;
CREATE TABLE "polygon1" (gid serial
PRIMARY KEY, "code" int4);
SELECT
AddGeometryColumn('','polygon1','the_geo
m','-1','MULTIPOLYGON',2);
INSERT INTO "polygon1" ("code",the_geom)
VALUES (‘…');
END;
Ejercicios avanzados
Creamos la destino (vacía)
"polygon1_union" (gid
serial PRIMARY KEY, "code" int4);
CREATE TABLE
Ejercicios avanzados
Añadimos a tabla destino columna de
geometría
SELECT
AddGeometryColumn('','polygon1_union
','the_geom','-1','MULTIPOLYGON'
Ejercicios avanzados
Hacemos la operación
INSERT INTO polygon1_union
(the_geom,code) SELECT
astext(multi(geomunion(the_geom)))
AS the_geom,code FROM polygon1
GROUP BY code
Carga, consulta y edición
• Vamos a utilizar dos programas:
• Uno gratuito y de código abierto, que
accede de forma nativa: gvSIG 1.1.1
• Otro comercial, que es un desarrollo no
soportado: ArcGIS Desktop 9.2
Visualización de datos
Otros …
uDig
Visualización de datos
Kosmo
Visualización de datos
qGIS
Carga, consulta y edición
• CARGA con gvSIG 1.1.1
Carga, consulta y edición
EDICIÓN con gvSIG 1.1.1
Carga, consulta y edición gvSIG
• Capacidad de análisis sobre datos ráster y
vectoriales en archivos locales (gran variedad
de formatos)
• Cliente WMS + Consultas “simples”.
• Cliente WCS + capacidades de análisis ráster.
• Cliente WFS.
• Cliente BBDD JDBC (PostGIS, MySQL, Oracle
Spatial, ArcSDE)
• Clientes de catálogo:Z39.50, CSW, SRW
• Cliente Gazetteer: WFS-G, ADL
• Primeras herramientas de edición
• Primeras herramientas de georreferenciación.
Carga, consulta y edición gvSIG
• EXPORTAR a PostGIS
Carga, consulta y edición gvSIG
CARGA con ArcGIS Desktop 9.2 (ZigGIS)
Carga, consulta y edición
EDICIÓN con ArcGIS Desktop 9.2
ESRI tiene previsto en su próxima
versión 9.3 acceder de forma nativa a
PostGIS, y que ArcSDE se pueda
instalar sobre PostgreSQL.
De momento los datos de PostGIS son
accesibles mediante la extensión de
pago ESRI Interoperability Extensión.
Gracias por vuestra atención …
Pedro Briones García
[email protected]