presentacion_postgis
TRANSCRIPT
-
8/17/2019 Presentacion_PostGIS
1/147
Curso de PostGIS
-
8/17/2019 Presentacion_PostGIS
2/147
Í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
-
8/17/2019 Presentacion_PostGIS
3/147
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,
I solation, 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
-
8/17/2019 Presentacion_PostGIS
4/147
MySql 1990 (+18)AÑO DE APARICIÓN APROXIMADA
MS SQL Server 1989 (+19)
PostgreSQL 1986 (+22)
IBM DB2 1983 (+25)
Oracle 1977 (+30)
Introducción a BD
-
8/17/2019 Presentacion_PostGIS
5/147
-
8/17/2019 Presentacion_PostGIS
6/147
• Las primera versiones de SGBDR espacialesson 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
-
8/17/2019 Presentacion_PostGIS
7/147
Introducción a BD espaciales
• En las primeras implementaciones SIG, losdatos espaciales y los atributos a ellos
referidos se almacenaban de formaindependiente. Los atributos se solíanalmacenar en una base de datos (o fichero), yla información espacial en formato propietario.
• Las bases de datos espaciales nacieron cuendoempeezaron a considerarse los objetos
espaciales como el núcleo de la BD
-
8/17/2019 Presentacion_PostGIS
8/147
Introducción a BD espaciales
Consultas espaciales utilizando SQL
Uso de expresiones SQL simples para obtenerrelaciones espaciales
• Distancia• Adyacencia• Contenido
Uso de expresiones SQL simples para obteneroperaciones espaciales
• Área, Longitud, Intersección, Unión, Buffer,…
-
8/17/2019 Presentacion_PostGIS
9/147
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
-
8/17/2019 Presentacion_PostGIS
10/147
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
-
8/17/2019 Presentacion_PostGIS
11/147
OGC“Much geospatial data is available on the web and in off -
line archives, but it is complex, heterogeneous, andincompatible. Users must possess considerable expertise
and special geographic information system (GIS) softwareto overlay or otherwise combine different map layers ofthe same geographic region. Data conversion iscumbersome and time-consuming, and the results areoften unsatisfactory. Common interfaces are the onlyway to enable overlays and combinations of complex andessentially different kinds of geographic information tohappen automatically over the Internet, despitedifferences in the underlying GIS software systems. OGCbrings together the key players and provides a formalstructure for achieving consensus on the common
interfaces.”
Introducción a BD espaciales
-
8/17/2019 Presentacion_PostGIS
12/147
• 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
-
8/17/2019 Presentacion_PostGIS
13/147
• Componentes que deben estarpresentes en un SGDBR espacial
– Tipo de dato espacial– Esquema de indexación espacial
– Operadores espaciales.
Introducción a BD espaciales
-
8/17/2019 Presentacion_PostGIS
14/147
Introducción a BD espaciales
Tipo de dato espacial
-
8/17/2019 Presentacion_PostGIS
15/147
Introducción a BD espaciales
Tipo de dato espacial (PostGIS)
-
8/17/2019 Presentacion_PostGIS
16/147
Introducción a BD espaciales
Tipo de dato espacial (PostGIS)
-
8/17/2019 Presentacion_PostGIS
17/147
Introducción a BD espaciales
Objeto SDO_GEOMETRY:
Ejemplo:SQL> CREATE TABLE us_states (
2 state VARCHAR2(30),
3 totpop NUMBER(9),
4 geom SDO_GEOMETRY);
SDO_GTYPE NUMBER
SDO_SRID NUMBER
SDO_POINT SDO_POINT_TYPE
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY
SDO_ORDINATES SDO_ORDINATE_ARRAY
Tipo de dato espacial (Oracle Spatial)
-
8/17/2019 Presentacion_PostGIS
18/147
Introducción a BD espaciales
SDO_POINT_TYPE:
SDO_ELEM_INFO_ARRAY:
SDO_ORDINATE_ARRAY:
x NUMBERy NUMBERz NUMBER
VARRAY (1048576) OF NUMBER
VARRAY (1048576) OF NUMBER
Tipo de dato espacial (Oracle Spatial)
-
8/17/2019 Presentacion_PostGIS
19/147
Introducción a BD espaciales
Esquema de indexación espacial
-
8/17/2019 Presentacion_PostGIS
20/147
Índice R-tree
Introducción a BD espaciales
-
8/17/2019 Presentacion_PostGIS
21/147
Introducción a BD espaciales
Índice R-tree
-
8/17/2019 Presentacion_PostGIS
22/147
Introducción a BD espaciales
ST_Distance(geometry, geometry)
Devuelve la distancia cartesiana en unidades deproyección entre 2 geometrías.
ST_DWithin(geometry, geometry, float)
Devuelve verdadero si las geometrías están, unacon respecto a otra, dentro de la distanciaespecificada
…
Operadores espaciales
-
8/17/2019 Presentacion_PostGIS
23/147
Evolución de latecnologíade DBMS
Introducción a BD espaciales
-
8/17/2019 Presentacion_PostGIS
24/147
• 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
-
8/17/2019 Presentacion_PostGIS
25/147
Introducción a BD espaciales. Resumen
• SDBMS (Spatial DBMS)
– Trabaja con un DBMS de fondo
– Proporciona ADTs (spatial Abstract DataTypes ) espaciales accesibles desde unlenguaje de consultas (SQL)
– Proporciona métodos paa un procesamientoeficiente de consultas espaciales
-
8/17/2019 Presentacion_PostGIS
26/147
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 yoptimización espacial-Data mining espacial (principio de buscar
en grandes volúmenes de datos para obtenerinformación relevante )
-
8/17/2019 Presentacion_PostGIS
27/147
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 definidospor el usuario
– TOAST – Sin límite en el tamaño de lacolumna– Índices geográficos GiST
– Fácil añadir funciones propias
-
8/17/2019 Presentacion_PostGIS
28/147
• Añade soporte para objetos geográficosa la base de datos objeto-relacionalPostgreSQL
• PostgreSQL posee “tipos geométricos”pero estas geometrías nativas estándemasiado limitadas para las
operaciones espaciales con datos SIG
Introducción a PostgreSQL
-
8/17/2019 Presentacion_PostGIS
29/147
• 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
-
8/17/2019 Presentacion_PostGIS
30/147
• ¡ Necesito una base de datos!
• ¿MySQL?
• ¡Necesito transacciones, Triggers,lenguajes procedurales, integridad!
Introducción a PostgreSQL
-
8/17/2019 Presentacion_PostGIS
31/147
• ¿ Y Oracle?, Todo el mundo locompra
• ¿Disponemos del dinero?
Introducción a PostgreSQL
-
8/17/2019 Presentacion_PostGIS
32/147
• ¿SQL Server?, todos lo soportan.
• No es gratuito. Además, no soportaoperaciones espaciales ( demomento…?)
Introducción a PostgreSQL
-
8/17/2019 Presentacion_PostGIS
33/147
“Enterprise” 1 Dual-Core 2 Quad-CoreOracle $40,000 $160,000
IBM DB2 $36,400 $145,600
MS SQL Server $25,000 $50,000IBM Informix $50,000 $200,000
PostgreSQL $0 $0
Escalabilidad
Introducción a PostgreSQL
-
8/17/2019 Presentacion_PostGIS
34/147
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
-
8/17/2019 Presentacion_PostGIS
35/147
• 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
-
8/17/2019 Presentacion_PostGIS
36/147
• 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
-
8/17/2019 Presentacion_PostGIS
37/147
• 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
-
8/17/2019 Presentacion_PostGIS
38/147
• 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
-
8/17/2019 Presentacion_PostGIS
39/147
P r e c i o
Funcionalidad
Introducción a PostGIS
-
8/17/2019 Presentacion_PostGIS
40/147
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
-
8/17/2019 Presentacion_PostGIS
41/147
ClienteWeb
Internet
uDig
OpenIMF
Mapserver
GeoServerMapGuide
Introducción a PostGIS
LAN
uDig
QGISGRASS
ArcGIS
PostGIS
gvSIG
-
8/17/2019 Presentacion_PostGIS
42/147
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 …
-
8/17/2019 Presentacion_PostGIS
43/147
Instalación de PostgreSQL
• Windows Installer– PostgreSQL 8.2.4– PgAdmin III
• Se instala como un servicio para que seinicie de forma automática
• Demostración de instalación …
-
8/17/2019 Presentacion_PostGIS
44/147
Instalación de PostgreSQL
-
8/17/2019 Presentacion_PostGIS
45/147
Instalación de PostgreSQL
• Directorios creados durante lainstalación:
– \bin Ejecutables– \include Ficheros para la compilación
– \lib Librerías DLL compartidas– \share Extensiones
-
8/17/2019 Presentacion_PostGIS
46/147
Instalación de PostgreSQL
• Herramientas incluidas con lainstalación:
• PgAdmin III
• Línea de comandos psql
-
8/17/2019 Presentacion_PostGIS
47/147
Instalación de PostGIS
-
8/17/2019 Presentacion_PostGIS
48/147
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 conPostgreSQL)
• Vamos a instalar PostGIS y a crear unabase de datos
-
8/17/2019 Presentacion_PostGIS
49/147
Instalación de PostGIS
• Una instancia PostgreSQL posee una versiónde software y un puerto de red (5432)
• Una instancia contiene una o varias bases de
datos• Una base de datos contiene uno o variosesquemas– Public es el habitual
• Un esquema contiene una o varias tablas– public.geometry_columns
• Una tabla contiene varias filas
-
8/17/2019 Presentacion_PostGIS
50/147
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 delsistema• spatial_ref_sys• geometry_columns
-
8/17/2019 Presentacion_PostGIS
51/147
2.3 – Spatially Enable PostgreSQL
-
8/17/2019 Presentacion_PostGIS
52/147
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 referenciaespacialde PostGIS (spatial_ref_sys.sql)
-
8/17/2019 Presentacion_PostGIS
53/147
2.3.1 – Sin template_postgis
• Ejecutar PG Admin III …
-
8/17/2019 Presentacion_PostGIS
54/147
Utilizando PostGIS
Crear tablespace nuevo
-
8/17/2019 Presentacion_PostGIS
55/147
Utilizando PostGIS
Crear un usuario nuevo
-
8/17/2019 Presentacion_PostGIS
56/147
Utilizando PostGIS
Crear Base de Datos nueva
-
8/17/2019 Presentacion_PostGIS
57/147
Utilizando PostGIS
D:\Vivienda\Curso_POSTGIS\Curso_PostGIS.txt
¡Conjunto de comandos SQL para
no tener que teclear!
EJERCICIOS
-
8/17/2019 Presentacion_PostGIS
58/147
-- CREAR UNA TABLA
-- BORRAR UNA TABLA
-- INSERTAR REGISTROS EN TABLA-- SELECCION BÁSICA
-- EXPRESIONES
-- CONDICIÓN WHERE-- ORDENAR
-- QUITAR DUPLICADOS
SQL básico
-
8/17/2019 Presentacion_PostGIS
59/147
-- 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
-
8/17/2019 Presentacion_PostGIS
60/147
-- 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 básico
-
8/17/2019 Presentacion_PostGIS
61/147
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;
-
8/17/2019 Presentacion_PostGIS
62/147
SQL espacial sencillo
-
8/17/2019 Presentacion_PostGIS
63/147
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’
-
8/17/2019 Presentacion_PostGIS
64/147
• 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
ID del Sist. Ref. Espacial
usada por la geometría en
dicha tabla.
Tipo del
objeto
espacial(Punto,
Poligonal…)
Dimensión espacial (2,
3, 4) de la columna de la
geometría.
OGC Tablas de Metadatos
-
8/17/2019 Presentacion_PostGIS
65/147
OGC Tablas de Metadatos
SPATIAL_REF_SYS– SRID = 23030
– AUTH_NAME = ‘EPSG’ – AUTH_SRID = 23030– SRTEXT = ‘PROJCS["ED50 / UTM zone 30N“…’
– PROJ4TEXT = ‘+proj=aea …’
-
8/17/2019 Presentacion_PostGIS
66/147
• Tabla “SPATIAL_REF_SYS“
– Contiene los identificadores numéricos y
descripciones textuales de los Sist. deReferencia.
Nº entero que identifica
unívocamente a eseSist. Ref. espacial en la
Base de Datos
Nombre del estándar
para este Sist. Ref.
La
representación
Well-Known
Text del Sist.
Ref. Espacial
ID del Sist. Ref. tal ycomo lo define el
estándar que
aparece en
auth_name
OGC Tablas de Metadatos
-
8/17/2019 Presentacion_PostGIS
67/147
Cargar ficheros SHAPE
• Fichero SHAPE (Compuesto por 3ficheros)– .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
-
8/17/2019 Presentacion_PostGIS
68/147
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
-
8/17/2019 Presentacion_PostGIS
69/147
Cargar ficheros SHAPE– shp2pgsql
-
8/17/2019 Presentacion_PostGIS
70/147
Cargar ficheros SHAPE
Ejecutar cmd.exe
-
8/17/2019 Presentacion_PostGIS
71/147
Cargar ficheros SHAPE
notepad ccaa.sql
-
8/17/2019 Presentacion_PostGIS
72/147
Opciones de línea de comandos
-i = No utilizar enteros largos-s = SRID (Referencia Espacial)
-W = Encoding de los datos-a = Añadir
- I = Crea un índice espacial GiST
-
8/17/2019 Presentacion_PostGIS
73/147
Cargar ficheros SHAPE
pg_shpsql.bat
-
8/17/2019 Presentacion_PostGIS
74/147
Cargar ficheros SHAPE
• Psql
–d SIU–U siu–f ccaa.sql
psql -f ccaa.sql -h port-cafc -U siu -d siu
-
8/17/2019 Presentacion_PostGIS
75/147
Urbano
Cargar ficheros SHAPE
CCAAProvinciasMunicipiosRed ViariaAutovíasRiosRíos_CEDEXUrbano
bl
-
8/17/2019 Presentacion_PostGIS
76/147
• 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_Cartografia_XP.mdb\Municipios)
• Definimos DRIVER ODBC de PostgreSQLUNICODE• Entramos en Access y exportamos a bases de
datos ODBC
Cargar tablas
C í di i l
-
8/17/2019 Presentacion_PostGIS
77/147
Crear índices espaciales
• PostgreSQL soporta 3 clases de índicespor defecto:
– Índices B-Tree– Índices R-Tree – Índices GiST
C í di i l
-
8/17/2019 Presentacion_PostGIS
78/147
Crear índices espaciales
• B-Trees se utilizan para datos que pueden ser ordenados a lolargo de un eje: números, letras o fechas. Los datos SIG nopueden ordenarse de forma racional a lo largo de un eje (¿qué esmayor, (0,0), (0,1) o (1,0)?)
• R-Trees dividen los datos en rectángulos y subrectángulos. Seusan por muchas bases de datos espaciales para indexar datos,pero la implementación del índice R-Trees de PostgreSQL no estan robusta como la implementación de GiST.
• GiST (Generalized Search Trees) los datos se dividen en gruposcomo “elementos a un lado", “elementos que solapan",“elementos que están dentro". Pueden utilizarse en multitud detipos de datos, incluidos los geográficos. PostGIS utiliza un índiceR-Tree implementado sobre un tipo GiST para indexar sus datos.
C í di i l
-
8/17/2019 Presentacion_PostGIS
79/147
Crear índices espaciales
• Los índices GiST tienes 2 ventajas sobrelos R-Tree en PostgreSQL.
• Son "null safe", pueden indexar columnascon valores nulos.
• Soportan el concepto "lossiness“, opequeña pérdida, importante al tratarcon objetos que superanel tamaño depágina de 8K de PostgreSQL 8K.
C í di i l
-
8/17/2019 Presentacion_PostGIS
80/147
Crear índices espaciales
- PostGIS implementa índices R-Tree sobreel sistema de indexado GiST
- Organiza los datos en rectángulosenlazados para una consulta rápida
- No es necesario crearlos, porque hemosusado la opción –I de shp2pgsql
CREATE INDEX ccaa_gidx ON ccaa USINGGIST (the_geom);
Utili í di i l
-
8/17/2019 Presentacion_PostGIS
81/147
Utilizar índices espaciales
Los índices entran en juego cuandoPostgreSQL reconoce un operador en lasentencia SQL. Por ejemplo:
- SELECT * FROM tabla WHERE nombre =‘Pablo’ = es un operador
- SELECT * FROM tabla WHERE edad < 2
-
8/17/2019 Presentacion_PostGIS
82/147
Utilizar índices espaciales
• El operador del índice espacial es “&&” – “Cajas externas () se tocan”
A && B = TRUEA && B = FALSE
í
-
8/17/2019 Presentacion_PostGIS
83/147
Utilizar índices espaciales
• ¡MBR (Mean Bounding rectangle) no essuficiente!
• Se necesitan dos pasos– Se utiliza MBR para reducir candidatos– Para obtener resultados reales se utilizan
relaciones topológicas reales
A && B = TRUE _ST_Intersects(A && B) = FALSE
í
-
8/17/2019 Presentacion_PostGIS
84/147
Utilizar índices espaciales
ST Intersects(A,B)
A && B AND _ST_Intersects(A,B)
l í d l
-
8/17/2019 Presentacion_PostGIS
85/147
Utilizar índices espaciales
A && B
U ili í di i l
-
8/17/2019 Presentacion_PostGIS
86/147
Utilizar índices espaciales
A && B
U ili í di i l
-
8/17/2019 Presentacion_PostGIS
87/147
Utilizar índices espaciales
_ST_Intersects(A,B)
U ili í di i l
-
8/17/2019 Presentacion_PostGIS
88/147
Utilizar índices espaciales
• Las operaciones de indexación (&&) estánconstruidas dentro de las funciones máscomunes para automatizar su uso, peropueden 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) ANDST_Distance(G1,G2) > D
P b í di i l
-
8/17/2019 Presentacion_PostGIS
89/147
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(6815924438183,723459 4464818)' , 23030) );
• ¿Alguna diferencia?
Í di Q Pl
-
8/17/2019 Presentacion_PostGIS
90/147
Índices y Query Plans
• Ejecutar las consultas utilizando elbotó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
Í di Q Pl
-
8/17/2019 Presentacion_PostGIS
91/147
Índices y Query Plans
Í di Q Pl
-
8/17/2019 Presentacion_PostGIS
92/147
Índices y Query Plans
C d Q Pl l
-
8/17/2019 Presentacion_PostGIS
93/147
Cuando Query Plans van mal
• La base de datos construye “planes” basadosen estadísticas sobre la distribución de losdatos muestreados de las tablas
– Siempre intenta ser “selectivo”, para seleccionarel menor número de registros necesarios parapasar al siguiente paso.
• La base de datos crear malos planes cuando
tiene malas estadísticas• Con el comando ANALYZE se actualizan
estas estadísticas
-
8/17/2019 Presentacion_PostGIS
94/147
Vi li d t P tGIS
-
8/17/2019 Presentacion_PostGIS
95/147
Visualizar datos PostGIS
• Programas visores– uDig
– QGIS
– gvSIG– CadCorp SIS
– FME Viewer
– Jump
Aplicaciones webMapGuideMapserver
Geoserver
Optimización de PostgreSQL
-
8/17/2019 Presentacion_PostGIS
96/147
Optimización de PostgreSQL
• Los parámetros de configuración dePostgreSQL se gestionan en el ficheropostgresql.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
-
8/17/2019 Presentacion_PostGIS
97/147
Optimización de PostgreSQL
• PostgreSQL parte de parámetros muyconservadores– Utiliza muy poca memoria
– Se ejecuta en hardware muy limitado• El acceso a disco siempre es lento, por lo
que puede conseguirse un rendimientomayor utilizando más memoria para cachear
datos– Incrementar shared_buffers 250 MB
– RAM – 25%-50% del total
Optimización de PostgreSQL
-
8/17/2019 Presentacion_PostGIS
98/147
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ásmemoria– 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
-
8/17/2019 Presentacion_PostGIS
99/147
Análisis Espacial
• ST_Intersects(A, B)
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
100/147
Análisis Espacial
• ST_Contains(A, B)• ST_Within(B, A)
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
101/147
Análisis Espacial
• ST_Touches(A, B)
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
102/147
Análisis Espacial
• ST_Crosses(A, B)
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
103/147
Análisis Espacial
• ST_DWithin(A, B, D)
D
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
104/147
Análisis Espacial
¿Cuál es la longitud total en kilómetrosde carreteras?
• SELECT Sum( ST_Length( the_geom ) ) / 1000 AS vias_km
FROM vias;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
105/147
Análisis Espacial
¿Qué superficie, en hectáreas, tiene laprovincia de Cuenca?
• SELECT ST_Area(the_geom)/10000 AS hectares
FROM prov WHERE nombre = ‘Cuenca’;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
106/147
Análisis Espacial
¿Cuál es el municipio de mayorsuperficie?
• SELECT nombre,ST_Area(the_geom)/10000
AS hectareasFROM muni ORDER BY hectareas DESC LIMIT 1;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
107/147
Análisis Espacial
¿Cuál es el perímetro del municipio deAmbite?’
• SELECT ST_Perimeter(the_geom) FROM muniWHERE nombre = ‘Ambite’;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
108/147
Análisis Espacial
¿Cuál es el área total de todos losmunicipios de madrid, en hectáreas
• SELECT Sum(ST_Area(the_geom))/10000AS hectares
FROM muni;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
109/147
Análisis Espacial
¿Cuál es el área total (en hectáreas) detodos los municipios con más de1.000.000 habitantes?
• SELECTsum(ST_Area(the_geom))/10000 as
hectares FROM prov WHERE pob2005 >1000000;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
110/147
Análisis Espacial
¿Cuál es la longitud total de lacarretera A3?
• SELECT Sum(ST_Length(the_geom))/1000 AS kilometers
FROM vias WHERE texto = ‘A-3’ OR texto = ‘N-III’;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
111/147
Análisis Espacial
¿Qué población tienen los municipiosque se encuentran a 5 km de unacoordenada?
• SELECT nombre, sum(censo_2001) asmuni_cerca FROM muni whereST_DWithin(the_geom,
ST_GeomFromText('POINT(5000004300000)', 23030), 5000) GROUP BY nombre;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
112/147
Análisis Espacial
Qué municipios están situados a menosde 2 km de una mina?
• SELECT nombre FROM muni m, urbano uWHERE ((u.texto ilike 'Mina%') AND (ST_DWithin(m.the_geom,u.the_geom,2
000))) GROUP BY nombre;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
113/147
Análisis Espacial
Núcleos urbanos que está a menos de250 metros de las autovías
• SELECT h.texto FROM urbano h, autop pWHERE ST_DWithin(h.the_geom,p.the_geom, 250);
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
114/147
Análisis Espacial
MUNICIPIOS CRUZADOS POR LA A-3
SELECT nombre FROM muni m, autop u
WHERE ((u.nom_via ilike 'A-3%' ORu.nom_via ilike 'N-III%') AND(ST_DWithin(m.the_geom,u.the_geom,2
000))) GROUP BY nombre;
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
115/147
NÚCLEOS URBANOS A MENOS DE 2000METROS DE LA A-3– Sin orden optimizado
• SELECT texto FROM urbano m, autop u WHERE u.nom_via ilike 'A-3%' ORu.nom_via ilike 'N-III%' and
ST_DWithin(m.the_geom,u.the_geom,2000) group by texto;
ál s s spac al
Análisis Espacial
-
8/17/2019 Presentacion_PostGIS
116/147
NÚCLEOS URBANOS A MENOS DE 2000METROS DE LA A-3– Con orden optimizado
• SELECT texto FROM urbano m, autop u WHERE((u.nom_via ilike 'A-3%' ORu.nom_via ilike 'N-III%') AND
(ST_DWithin(m.the_geom,u.the_geom,2000))) GROUP BY texto;
p
Solapes
-
8/17/2019 Presentacion_PostGIS
117/147
p
• Las interseciones de capa a capa sonposibles con la función ST_Intersection()– ST_Intersects(a,b) devuelve BOOLEAN
– ST_Intersection(a,b) devuelve GEOMETRY
ST_Intersects() = TRUE
ST_Intersection() =
Solapes
-
8/17/2019 Presentacion_PostGIS
118/147
p
Crear una nueva tabla que contenga losmunicipios intersectados por laprovincia de Madrid
4.4 - Overlays
• CREATE TABLE muni_madrid AS
-
8/17/2019 Presentacion_PostGIS
119/147
ySELECT 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’;
4.4 - Overlays
• CREATE TABLE muni_madrid2 AS
-
8/17/2019 Presentacion_PostGIS
120/147
ySELECT 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’;
4.4 - Overlays
• CREATE TABLE muni_madrid3 AS SELECT
-
8/17/2019 Presentacion_PostGIS
121/147
ySELECT 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 anteriores3 dif t
-
8/17/2019 Presentacion_PostGIS
122/147
crean 3 capas diferentes
Proyección de coordenadas
-
8/17/2019 Presentacion_PostGIS
123/147
Proyección de coordenadas
• c• SELECT ST_SRID(the_geom)
FROM autop
LIMIT 1;• ¿Qué significa “23030”?
• SELECT srtextFROM spatial_ref_sysWHERE srid = 23030;
Proyección de coordenadas
-
8/17/2019 Presentacion_PostGIS
124/147
oyecc ó de coo de adas
• "PROJCS["ED50 / UTM zone30N",GEOGCS["ED50",DATUM["European_Datum_1950",SPHEROID["International1924",6378388,297,AUTHORITY["EPSG","7022"]],AUTHORITY["EPSG","6230"]],PRIMEM["Greenwich",0,AUTHORITY["E
PSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4230"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-3],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AUTHORITY["EPSG","23030"]]"
Proyección de coordenadas
-
8/17/2019 Presentacion_PostGIS
125/147
y
• ¿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 decoordenadas utilizada por PostGIS
Proyección de coordenadas
-
8/17/2019 Presentacion_PostGIS
126/147
y
• La reproyección de coordenadas se realizautilizando la función ST_Transform()
• SELECT ST_AsText(the_geom)
FROM vias LIMIT 1;
• SELECT ST_AsText(ST_Transform(the_geom, 4326) )
FROM viasLIMIT 1;
Proyección de coordenadas
-
8/17/2019 Presentacion_PostGIS
127/147
y
"MULTILINESTRING((487251 4745186,4870004745526,…))"
"MULTILINESTRING((-3.1560483280933242.8575654465415,-3.159128429736642.8606229123234,…))"
ST_Transform(the_geom)
Ejercicios avanzados
-
8/17/2019 Presentacion_PostGIS
128/147
j
¿Cuál es la longitud de carreteras en Madrid?
• SELECT
Sum(ST_Length(r.the_geom))/1000AS kilometersFROM 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
-
8/17/2019 Presentacion_PostGIS
129/147
j
¿Qué núcleos urbanos están situados a 500metros 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.cityORDER BY pob_2001 DESC LIMIT 2;
Ejercicios avanzados
-
8/17/2019 Presentacion_PostGIS
130/147
j
¿Cuál es la latitud del núcleo urbano situadomás al norte? – Pista – El SRID de lat/lon es 4326
• SELECT ST_Y(ST_Transform(the_geom,4326)) AS latitude
FROM urbanoORDER BY latitude DESC LIMIT 1;
Ejercicios avanzados
-
8/17/2019 Presentacion_PostGIS
131/147
j
¿Cuál es el municipio de mayor tamaño quecontiene 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) > 1ORDER BY area DESC LIMIT 1;
Ejercicios avanzados
-
8/17/2019 Presentacion_PostGIS
132/147
j
DISSOLVE
Ejercicios avanzados
-
8/17/2019 Presentacion_PostGIS
133/147
j
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;
Creamos la tabla origen
Ejercicios avanzados
-
8/17/2019 Presentacion_PostGIS
134/147
j
CREATE TABLE "polygon1_union" (gid
serial PRIMARY KEY, "code" int4);
Creamos la destino (vacía)
Ejercicios avanzados
-
8/17/2019 Presentacion_PostGIS
135/147
SELECT AddGeometryColumn('','polygon1_union','the_geom','-1','MULTIPOLYGON'
Añadimos a tabla destino columna degeometría
Ejercicios avanzados
-
8/17/2019 Presentacion_PostGIS
136/147
INSERT INTO polygon1_union (the_geom,code) SELECT astext(multi(geomunion(the_geom)))
AS the_geom,code FROM polygon1 GROUP BY code
Hacemos la operación
Carga, consulta y edición
-
8/17/2019 Presentacion_PostGIS
137/147
• 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 nosoportado: ArcGIS Desktop 9.2
Visualización de datos
-
8/17/2019 Presentacion_PostGIS
138/147
Otros … uDig
Visualización de datos
-
8/17/2019 Presentacion_PostGIS
139/147
Kosmo
Visualización de datos
-
8/17/2019 Presentacion_PostGIS
140/147
qGIS
Carga, consulta y edición
-
8/17/2019 Presentacion_PostGIS
141/147
• CARGA con gvSIG 1.1.1
Carga, consulta y edición
-
8/17/2019 Presentacion_PostGIS
142/147
EDICIÓN con gvSIG 1.1.1
Carga, consulta y edición gvSIG
-
8/17/2019 Presentacion_PostGIS
143/147
• Capacidad de análisis sobre datos ráster y
vectoriales en archivos locales (gran variedadde 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.
-
8/17/2019 Presentacion_PostGIS
144/147
Carga, consulta y edición gvSIG
-
8/17/2019 Presentacion_PostGIS
145/147
CARGA con ArcGIS Desktop 9.2 (ZigGIS)
Carga, consulta y edición
-
8/17/2019 Presentacion_PostGIS
146/147
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 puedainstalar sobre PostgreSQL.
De momento los datos de PostGIS sonaccesibles mediante la extensión depago ESRI Interoperability Extensión.
Gracias por vuestra atención …
-
8/17/2019 Presentacion_PostGIS
147/147
Pedro Briones Garcí[email protected]