Download Introduction to PostGIS Workshop
Document related concepts
Transcript
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 pbg@tragsa.es