de siose a postgis en cuatro sesiones

Presentacion Geodatabases De SIOSE a PostGIS Consulta y gestion

¡¡De SIOSE a PostGIS en 4 sesiones!!

Benito M. Zaragozı

Laboratorio de GeomaticaInstituto Interuniversitario de Geografıa

Universidad de Alicante

Marzo de 2014

¡¡De SIOSE a PostGIS en 4 sesiones!! Benito M. Zaragozı


El pequeno gran mundo de las TIG

En los proximos dıas vereis que...

1 Analista SIG + skills = trabajo (ver indeed.com).

2 No se pueden abarcar todos los “skills” en tan poco tiempo.

3 Hay un nivel por debajo de los SIG y por que merecerıa lapena aprender sobre el.

4 Existen elementos comunes en los SIG que, si los entendeis,hacen que os resulte sencillo trabajar con cualquier SIG.

5 Acceder a servicios de IG puede resultar esencial. Por ejemplo,para no tener que clasificar los usos del suelo de un municipio,con vuestros propios medios.

6 Empezar a trabajar con geodatabases y SQL no es nadamıstico.


http://www.indeed.com/jobs?q=postgis&l=



Objetivos

1 Adquirir vocabulario basico del mundo de las TIG(geodatabase, SGBD, SQL, OGC, estandares einteroperabilidad, etc).

2 Evaluar el SIOSE como origen de datos para analisis SIG.

3 Importar, procesar y consultar informacion geografica conPostGIS.

4 Elaborar una reclasificacion de los usos del suelo SIOSE queutilizaremos en el proyecto de la asignatura.




Knowledge Discovery in Databases (KDD)

• Bases de datos enormes

• Multiples orıgenes y formatos

• Datos → Informacion → Conocimiento → Sabidurıa

• La IG es mas compleja




Infraestructuras de Datos Espaciales (IDE)

Para analizar distintas posibilidades podeis echarle un vistazo aeste White Paper de Opengeo.org


http://opengeo.org/publications/opengeo-architecture/opengeo-architecture.pdf



Open Geospatial Consortium (OGC)

Miembros OGC• Empresas distribuidoras de software: ESRI, Autodesk, Bentley

Systems, Cadcorp, Intergraph, NAVTEQ, etc.

• Universidades: Delft University of Technology, HarvardUniversity, ITC University of Twente, MIT, etc.

• Agencias gubernamentales: Norwegian Building Authority, USGeological Survey (USGS), etc.

http://www.opengeospatial.org/

Estandar OGC

Documento establecido por consenso y aprobado por los miembrosdel OGC. Este documento provee de reglas y directrices quebuscan la interoperabilidad del software geoespacial.


http://www.opengeospatial.org/



WKT y WKB

Well-known text (WKT)

WKT es uno de los estandares OGC mas conocidos. WKT es unlenguaje de etiquetado para representar geometrıas vectoriales,sistemas de referencia espacial y transformaciones. En su versionbinaria, el well-known binary (WKB) se utiliza para transferir dichainformacion rapidamente entre bases de datos y distintos tipos desoftware geoespacial.

POINT(53 105)MULTIPOINT(100 0, 100 100)LINESTRING(0 0, 25 100, 50 0, 75 100, 100 0)MULTILINESTRING((100 100, 75 0, 50 100, 25 0, 0 100), (0 0, 25 100, 50 0, 75 100, 100 0))POLYGON((0 0, 100 0, 100 100, 0 100, 0 0))POLYGON((0 0, 100 0, 100 100, 0 100, 0 0), (25 25, 25 75, 75 75, 75 25, 25 25))MULTIPOLYGON(((125 125, 125 175, 175 175, 175 125, 125 125)), ((0 175, 0 100, 100 175, 0 175)))GEOMETRYCOLLECTION(POINT(53 105), LINESTRING(0 0, 25 100, 50 0, 75 100, 100 0),POLYGON((0 0, 100 0, 100 100, 0 100, 0 0)))

Practica

Crea geometrıas en OpenJump y familiarızate con el WKT.¡¡De SIOSE a PostGIS en 4 sesiones!! Benito M. Zaragozı

http://en.wikipedia.org/wiki/Well-known_text



Open Source Geospatial Foundation (OSGeo)

OSGeo apoya, financia ycomercializaherramientas FOSS GIS.Ademas, fomenta elacceso a datosespaciales libres.

http://live.osgeo.org/en/index.html/


http://live.osgeo.org/en/index.html/



Portable GIS

De un modo similar a PortableApps,se distribuyen los FOSS GIS maspopulares. Funciona sobreWindows...

http://www.archaeogeek.com/blog/2012/09/12/

portable-gis-v3-released/


http://portableapps.com/apps

http://www.archaeogeek.com/blog/2012/09/12/portable-gis-v3-released/

http://www.archaeogeek.com/blog/2012/09/12/portable-gis-v3-released/



La importancia del SQL. El primer paso



Introduccion a las geodatabases

Que es una geodatabase?

Definicion de geodatabase

Una geodatabase es una base de datos con tipos especiales paralos objetos geometricos. Permite almacenar datos geometricos yproporciona funciones especiales, ındices espaciales y lenguajes deconsulta (SQL). Una base de datos espacial es a menudo utilizadacomo un simple contenedor de almacenamiento para los datosespaciales, pero puede ser mucho mas que eso.

Geodatabases relacionales

• Comerciales: Oracle Spatial, SQL Server (2008), IBM DB2Spatial, IBM Informix Spatial, MapInfo SpatialWare y ESRIArcSDE.

• Libres: MySQL Spatial, SpatiaLite y PostGIS.


http://en.wikipedia.org/wiki/Oracle_Spatial

http://help.arcgis.com/es/arcgisserver/10.0/help/arcgis_server_dotnet_help/index.html#//009300000115000000

http://help.arcgis.com/es/arcgisserver/10.0/help/arcgis_server_dotnet_help/index.html#//009300000115000000

http://en.wikipedia.org/wiki/SpatiaLite



SGBD espaciales vs SIG orientados a la aplicacion

La relacion entre SIG y Sistemas Gestores de Bases de Datos(SGBD) es cada vez mas cercana y esta tomando un interescreciente. Existen varios motivos que explican dicho interes:

Ventajas

• Reduccion de la redundanciade datos.

• Aumento de la integridad dedatos.

• Independencia de datos.

• Seguridad de los datos.

• Mantenimiento eficiente.

• Vision Corporativa.

Inconvenientes• Costes mas elevados.

• Personal adicional.

• Aumento del tiempo dedesarrollo.

• Riesgo centralizado.

• Anonimato de los datos.

• Racionalidad excesiva.




PostgreSQL

Que es PostgreSQL?

PostgreSQL es un SGBD relacional orientado a objetos y libre.Entre sus principales ventajas podemos encontrar:

• Alta concurrencia y seguridad.

• Amplia variedad de tipos nativos (Numeros de precisionarbitraria, texto de largo ilimitado, direcciones IP, direccionesMAC, arrays, geometrıas, etc).

• Otras caracterısticas (foreign keys, triggers, vistas, integridadtransaccional, herencia de tablas, soporte para transaccionesdistribuidas, etc).

• Funciones en varios lenguajes procedurales (PL/PgSQL, C,C++, Java PL/Java web, PL/Perl, plPHP, PL/Python,PL/Ruby, PL/sh, PL/Tcl, PL/R, etc).


http://es.wikipedia.org/wiki/PostgreSQL

http://www.gisandchips.org/2010/09/08/la-ciencia-de-los-suelos-i-creacion-de-triangulos-texturales-directamente-desde-una-geodatabase/#more-1629



PostGIS

Que es PostGIS?

PostGIS es la extension espacial, FOSS y OGC, para el Sistema deGestion de Base de Datos PostgreSQL. PostGIS anade funcionesespaciales (distancia, area, union, interseccion, etc) y tipos dedatos espaciales a la base de datos. Esta extension es similar aESRI ArcSDE, Oracle Spatial, MySQL-spatial, y DB2 SpatialExtender. La version 2.0.0 tiene otras mejoras notables:

• 3D support.

• Raster/vector support.

• SQL/MM topology support.

• Tiger geocoder with loader for 2010 Tiger data.


http://es.wikipedia.org/wiki/PostGIS

http://www.oslandia.com/tech/?p=1235

http://trac.osgeo.org/postgis/wiki/WKTRaster

http://strk.keybit.net/blog/2011/10/14/postgis-topology-iso-sqlmm-complete/



PostgreSQL y PostGIS con pgAdmin III

Query 1: Hello world!!1 SELECT version ();

2 -- SELECT postgis_full_version ();

3 -- SELECT * FROM pg_tables WHERE

schemaname = ’public ’;


http://www.guia-ubuntu.com/index.php?title=PgAdmin_III


Importando datos a PostGIS

Presentacion

¿Que es el SIOSE?

SIOSE es el Sistema de Informacion sobre Ocupacion del Suelo deEspana, cuyo objetivo es integrar la informacion de las Bases deDatos de coberturas y usos del suelo de las ComunidadesAutonomas y de la Administracion General del Estado (ver Web).

Antecedentes• Protocolo firmado entre los Ministerios de Defensa, Fomento

y Medio Ambiente para la obtencion de coberturas delterritorio espanol con imagenes de satelite de alta y mediaresolucion en octubre de 2005 dentro del marco del PlanNacional de Teledeteccion (PNT).

• CLC1990, CLC2000 y tambien CLC2006

• Directiva INSPIRE (2007/2/EC)


http://www.siose.es/siose/presentacion.html



Objetivos

¿Que pretende el SIOSE?

• Evitar las duplicidades.

• Reducir costes de produccion y mantenimiento.

• Integrar la informacion procedente de las ComunidadesAutonomas.

• Cumplir con los requerimientos de la UE.

• Definir metodologıas consensuadas y armonizadas.

• Obtener un modelo de datos normalizado (OGC, ISO)orientado a objetos, en lenguaje UML.




Caracterısticas basicas

Una descripcion breve

• SIG con una unica capa de polıgonos.

• Escala de Referencia: 1: 25.000.

• Sistema Geodesico de Referencia: ETRS 89.

• Proyeccion UTM: husos 28, 29, 30 y 31.

• Unidad mınima de superficie a representar, dependiendo de lasclases (0,5-2 ha).

• Periodicidad de actualizacion: 5 anos.

• Metadatos estandarizados (NEM, CSG e ISO).

• Imagenes de referencia SPOT5, Landsat5 TM y ortofotosPNOA.

• Varios usos, una cobertura (ver documentacion).


http://www.siose.es/siose/documentacion.jsp



Descarga del SIOSE

Desde el CNIG• SIOSE Comunidad Valenciana 2005

• Shapefile + Tabla plana o *.mdb

• Descarga libre con usuario y contrasena

• Link al Centro de Descargas

Desde el ICV• SIOSE por municipios 2009

• Shapefile + *.mdb

• Descarga libre con usuario y contrasena

• Link al Terrasit


http://centrodedescargas.cnig.es/CentroDescargas/catalogo.do

http://terrasit.gva.es/es/descargas



Resolviendo problemas de la vida real (I)

La decision crıtica

Preferimos trabajar con los usos del suelo SIOSE que ofrece elTerrasit. La cartografıa es mas reciente (2009) y nos permitedescargar solamente la informacion que necesitamos. A prioriahorraremos tiempo si trabajamos con la cartografıa de los 6municipios que nos interesan y no con toda la comunidadvalenciana.




Importando la zona de estudio

Podrıamos importar datos geometricos desde cualquier origenmediante herramientas basicas de PostGIS. Al fin y al cabo, todoson INSERTs (SELECT, UPDATE y DELETE). Sin embargo, hayherramientas que nos facilitan esto: shp2pgsql

Query 2: Uso de shp2pgsql (I)1 sudo shp2pgsql -s 25830 /home/benizar/Dropbox/Curso_Geodatabases_SIOSE/

datos_y_practicas/limite_etrs89/Limite_BUFFER_ETRS89.shp limite public.

siose2postgis | psql -h localhost -d siose2postgis -U postgres




Plugin SPIT en QGIS




PostGIS manager en QGIS




Importando cartografıa del SIOSE

Query 3: Uso de shp2pgsql (II)1 sudo shp2pgsql -s 25830 /home/benizar/Dropbox/Curso_Geodatabases_SIOSE/

datos_y_practicas/SIOSE_terrasit /03014. shp alicante public.siose2postgis |

psql -h localhost -d siose2postgis -U postgres

Y lo mismo para los otros shapefiles/municipios:

• Aspe (03019)

• Crevillente (03059)

• Hondon de los Frailes (03078)

• Monforte del Cid (03088)

• Novelda (03093)



Primera aproximacion a los skills de PostGIS

Union de varias capas/tablas

Query 4: Copia de una tabla1 CREATE TABLE usos AS(SELECT * FROM alicante);

Query 5: Anadir los elementos de otra tabla (Alicante + Aspe)1 INSERT INTO usos (

2 gid , id_2009_or , id_2009 ,

3 code_2009 ,

4 sup_ha_200 ,

5 est_cambio , est_revisi ,

6 aux_char , aux_int , aux_leyend ,

7 obs_2009 ,

8 codblq ,

9 shape_leng , shape_area , geom) SELECT * FROM aspe;

Y ahora... + Crevillente + Elche + Hondon + Monforte + Novelda




Tipos de datos y funciones geometricas

Los polıgonos de usos del suelo podrıan cambiar. Entonces, ¿porque almacenar la superficie y el perımetro varias veces?

Query 6: Uso de una funcion espacial1 SELECT id_2009 , geom As wkb , sup_ha_200 as ha_siose , shape_area as m2_siose ,

st_area(geom) FROM usos;

Visto esto, ahora probaremos otras funciones de PostGIS...




Resolviendo problemas de la vida real (II)

Zas!! Polıgonos duplicados

Si leemos el fichero leeme.txt que hay dentro de cada carpetadescargada, veremos que hay un problema. La descarga no ha sidoexactamente “por municipio”, sino que hay solapes. ¿Comosolucionar esto con las herramientas que conoceis?




Detectar duplicados y filtrar datos

Hay muchos modos de localizar los polıgonos repetidos. Entreotras, utilizando operadores de agregacion:

Query 7: Contar registros repetidos1 SELECT id_2009 , COUNT (*) TotalCount

2 FROM usos

3 GROUP BY id_2009

4 HAVING COUNT (*) > 1

5 ORDER BY COUNT (*) DESC;

Sobran campos innecesarios y debemos eliminar todos lospolıgonos duplicados. ¿Sera difıcil?

Query 8: Obtener la tabla final1 CREATE TABLE usos2

2 AS (SELECT DISTINCT id_2009 , code_2009 , geom FROM usos);




Usos del suelo de la zona de estudio

Seguimos teniendo los usos del suelo para los municipios quecomparten la zona de estudio (y un poco mas). Ahora debemosrealizar una consulta para limitarnos a la zona de estudio...

Query 9: Interseccion SIOSE-lımite1 CREATE TABLE usos3

2 AS (SELECT a.id_2009 , a.code_2009 , a.geom

3 FROM usos2 AS a, limite AS b

4 WHERE ST_Intersects(b.geom , a.geom));




Resolviendo problemas de la vida real (III)

Zas!! Clasificacion poco flexible

La clasificacion de los usos del suelo (Orientada a Objetos) aplicadapor el SIOSE es solo valida para los polıgonos definidos por elSIOSE. Esto significa que, si modificamos las geometrıas/polıgonosdel SIOSE, la clasificacion de usos en porcentajes ya no sera validay necesitaremos revisar todos los polıgonos afectados.




¿Que polıgonos deberıamos revisar?

Query 10: Polıgonos SIOSE que intersectan con el lımite1 CREATE TABLE usos4

2 AS (SELECT a.id_2009 , a.code_2009 , ST_Intersection(a.geom ,b.geom),

34 CASE WHEN ST_Intersects(ST_Boundary(b.geom), a.geom)

5 THEN true

6 ELSE false

7 END AS revisar

89 FROM usos3 AS a, limite AS b

10 WHERE ST_Intersects(b.geom , a.geom)

1112 ORDER BY revisar DESC);




Resolviendo problemas de la vida real (IV)

Zas!! Codigos difıciles de interpretar

En la documentacion del SIOSE en PDF y en las tablas adjuntas(TC SIOSE ATRIBUTOS.dbf y TC SIOSE COBERTURAS.dbf)podemos averiguar como interpretar exactamente los codigos declasificacion que utiliza el SIOSE (code 2009) para describircoberturas y usos del suelo. Sin embargo, estos codigos mezclansiglas, porcentajes y caracteres especiales en un unico campo detexto y esto no es facil de utilizar. Ademas del codigo de texto(code 2009) en el Shapefile, el SIOSE proporciona una clasificacionmas completa en un fichero MDB aparte. ¿Habra algun modo deconsultar estos codigos para que no se conviertan en una pesadilla?




Consultas a la BD

El SQL es un lenguaje estandar de consulta de bases de datos conun gran numero de alternativas a la hora de explotar una base dedatos. Existen muchos metodos y palabras clave para filtrar,ordenar y combinar los datos. Vamos a practicar un poco:

Query 11: Ejemplos de operadores1 SELECT * FROM usos4;

2 -- SELECT * FROM usos4 WHERE id_2009 = ’0349490d-6b0c -40c6 -88cc-cd597dc81a80 ’;

3 -- SELECT * FROM usos4 WHERE st_area(geom) > 10000;

4 -- SELECT * FROM usos4 WHERE code_2009 LIKE ’ %PST %’;

5 -- SELECT DISTINCT code_2009 FROM usos4 WHERE code_2009 LIKE ’ %PST %’;

6 -- SELECT code_2009 , length(code_2009) as longitud FROM usos4 ORDER BY longitud;

7 -- Primera aproximacion a trabajar con los codigos de usos del suelo

8 -- SELECT code_2009 , regexp_split_to_array(code_2009 , E’([()_]) ’) FROM usos4;

Manejar el codigo de clasificacion SIOSE como un texto puede serun dolor de cabeza, por lo que vamos a importar los datos enformato XML que se encuentran en el fichero MDB.




Union con los datos provenientes de los *.mdbExisten varios metodos predefinidos para importar datos aPostgres. Por ejemplo, podrıamos utilizar el comando COPY paraimportar tablas con formato CSV extraıdas de los MDBs que elSIOSE proporciona para cada municipio. No obstante, para ahorraralgo de tiempo os he preparado un backup del resultado de unirtodas las MDBs en una sola tabla y eliminar los registros repetidos.Importad el fichero fullmdb.sql.A continuacion, podeis ver lo sencillo que es realizar un Join comolos que hacemos en los SIG de escritorio (shp+tabla con unarelacion 1:1).

Query 12: Join de las dos tablas1 CREATE TABLE usos5 AS (SELECT a.id_2009 , code_2009 , st_intersection AS geom ,

revisar , xml_2009

2 FROM usos4 AS a, fullmdb AS b

3 WHERE a.id_2009=b.id_2009);




Consultando XMLs almacenados en PostgreSQL

Consultar el codigo SIOSE a partir de un campo de texto es pocopractico, trabajoso y nos puede llevar a cometer errores, peroutilizando el comando Xpath de Postgres todo deberıa ser massencillo. Ahorraremos tiempo si no necesitamos “trocear” el codigoy tampoco necesitaremos convertir los caracteres alfanumericos anumeros que podamos comparar.

Query 13: Ejemplo basico de Xpath1 SELECT code_2009 ,

2 xpath(’/POLIGONO/COBERTURA/COBERTURA/@Desc’, xml_2009 ::xml) AS Coberturas ,

3 xpath(’/POLIGONO/COBERTURA/COBERTURA/@Sup’, xml_2009 ::xml) AS Superficies

4 FROM usos5

5 WHERE id_2009=’1df01074 -fb60 -40b7-a021 -c201242d12c6 ’;




Consultas espaciales

Query 14: Reclasificacion del SIOSE con Xpath1 CREATE TABLE usos6 AS(

2 SELECT *, true AS resid FROM usos5 WHERE usos5.id_2009 IN (SELECT id_2009 FROM

usos5

3 WHERE (xpath(’// COBERTURA[@ID="EDF" and @Sup >20]/ @ID’, xml_2009 ::xml))[1]:: text=

’EDF’

4 OR (xpath(’// COBERTURA[@ID="UCS" and @Sup >20]/ @ID’, xml_2009 ::xml))[1]:: text=’

UCS’

5 OR (xpath(’// COBERTURA[@ID="UEN" and @Sup >20]/ @ID’, xml_2009 ::xml))[1]:: text=’

UEN’

6 OR (xpath(’// COBERTURA[@ID="UDS" and @Sup >20]/ @ID’, xml_2009 ::xml))[1]:: text=’

UDS’)

7 UNION

8 SELECT *, false AS resid FROM usos5 WHERE usos5.id_2009 NOT IN (SELECT id_2009

FROM usos5

9 WHERE (xpath(’// COBERTURA[@ID="EDF" and @Sup >20]/ @ID’, xml_2009 ::xml))[1]:: text=

’EDF’

10 OR (xpath(’// COBERTURA[@ID="UCS" and @Sup >20]/ @ID’, xml_2009 ::xml))[1]:: text=’

UCS’

11 OR (xpath(’// COBERTURA[@ID="UEN" and @Sup >20]/ @ID’, xml_2009 ::xml))[1]:: text=’

UEN’

12 OR (xpath(’// COBERTURA[@ID="UDS" and @Sup >20]/ @ID’, xml_2009 ::xml))[1]:: text=’

UDS’)

13 ORDER BY resid DESC);




Y aun queda lo mejor...

• Modelo Entidad-Relacion

• Consultas complejas

• Vistas y tablas dinamicas

• Automatismos (reglas y disparadores)

• Numerosas funciones espaciales

• Alta concurrencia y rendimiento




Gracias por vuestra atencion!!


de siose a postgis en cuatro sesiones

Education