PostGIS en una cáscara de nuez

#

27 / 02 / 2016

Micho García

@michogar

micho.garcia@geomati.co

@postgresql

¿Qué es PostgreSQL?

PostgreSQL es un sistema de gestión de bases de datos objeto-relacional
Distribuido bajo licencia BSD y con su código fuente disponible libremente.
Es el sistema de gestión de bases de datos de código abierto más potente del mercado y en sus últimas versiones no tiene nada que envidiarle a otras bases de datos comerciales.

¿Qué es PostgreSQL?

Utiliza un modelo cliente/servidor

¿Qué es PostgreSQL?

Ficheros de configuración: Los 3 ficheros principales de configuración utilizados por PostgreSQL, postgresql.conf, pg_hba.conf y pg_ident.conf
Disco: Disco físico donde se almacenan los datos y toda la información necesaria para que PostgreSQL funcione

Instalación

Desde repositorios Linux
Docker
Compilando código fuente, WTF!!
Instalación en Windows

$ sudo apt-get install postgresql

Desde repositorios

Docker

https://hub.docker.com/r/mdillon/postgis/

https://hub.docker.com/r/kartoza/postgis/

Estructura de la instalación

/usr/lib/postgresql/9.x→ ejecutables y librerías
/usr/share/postgresql/9.x → archivos sql para creación estructura
/usr/share/postgresql-common → herramientas comunes para administración
/var/lib/postgresql/9.x
/etc/postgresql/9.x/main

manejo del servicio

$ sudo service postgresql [start | stop | restart]

/etc/postgresql/9.x/main

En esta localización se encuentran los archivos necesarios para la configuración:

pg_hba.conf
postgresql.conf

pg_hba.conf es el archivo de configuración de la autentificación de PostgreSQL

postgresql.conf es el archivo de configuración de PostgreSQL

Configuración accesos

Lo primero es configurar el servidor para que acepte conexiones de red.

Para ello modificaremos el archivo pg_hba.conf

[Tipo de conexion][database][usuario][IP][Netmask][Tipo de autentificacion][opciones]

local all postgres trust

CONFIGURACIÓN ACCESOS

Una vez definida la regla de acceso a nuestro servidor, le indicaremos las interfaces en las que puede escuchar el servidor. Para ello modificaremos el parámetro `listen_address` en el archivo ` postgresql.conf ` de la siguiente manera:

listen_address = '*'

En los parámetros de seguridad, activaremos la encriptación de las claves de usuario modificando en el mismo archivo:

password_encryption = on

clientes

psql

psql - PostgreSQL interactive terminal, es un cliente tipo terminal para la gestión de PostgreSQL

Preparar acceso

Disponemos de usuario postgres creado por la instalación en el sistema

La instalación crea en el servidor la base de datos postgres y el usuario superadministrador postgres

preparar acceso

Asignamos una clave al usuario postgres del sistema

$ su passwd postgres

Accedemos mediante psql al servidor y modificamos password de postgres

$ su postgres

$ psql

=# alter role postgres with password '<una_password>';

psql -d nombre_base_de_datos accederá a la base de datos que le indiquemos
psql -f ruta_a_archivo utiliza las sentencias que se encuentren dentro del archivo
psql -h nombre_servidor se conecta al servidor que le indiquemos
psql -p puerto se conecta a la instancia de |PG| a través del puerto indicado
psql -l muestra un listado de las bases de datos de la instancia
psql -U nombre_usuario se conecta usando el usuario indicado
psql -V muestra la versión de psql

Opciones

$ psql --help

algunas funciones

select version(); nos indicará la versión del servidor que tenemos instalada
\l muestra un listado de las bases de datos
select * from pg_user; nos muestra todos los usuarios del sistema
select * from pg_tables; muestra todas las tablas incluidas las del sistema
\c database cambia de base de datos
\dn muestra todos los esquemas de la base de datos

ALGUNAS funciones

\dt muestra las tablas, acepta expresiones para filtrar por ejemplo, \dt p* todas las tablas que empiezan por p
\du listado de usuarios/grupos y roles
\d tabla columnas, y tipos de datos de la tabla
\i ruta_archivo ejecuta las sentencias de un archivo
\o ruta_archivo devuelve los datos a un archivo
\conninfo muestra la información de la conexión
\encoding codificación fija la codificación del sistema, o sin parámetro la muestra
\q sale de la consola ``psql``

algunas funciones

pgAdmin 3

pgAdmin es el más popular software para la administración de PostgreSQL a través de entorno gráfico. Se puede utilizar para el manejo de las versiones de 7.3 en adelante. Soporta todas las funcionalidades de PostgreSQL y permite una administración más sencilla de este. Incluye un editor de SQL desde el que se pueden realizar las consultas.

instalacion

$ sudo apt-get install pgadmin3

arranque

añadir conexión

SQL Editor

@postgis

¿qué es postgis?

PostGIS añade soporte espacial a la base de datos relacional PostgreSQL.

Le da a PostgreSQL la capacidad de almacenar, consultar y manipular datos espaciales

Licenciado con GNU GPL

Desarrollado en lenguaje C

Utiliza la librería GEOS (wrapper JTS)

Implementa OGC Simple Feature Access SFA

Instalación

Desde repositorios:

$ sudo apt-get install postgis

Espacialización de la base de datos

Crear una base de datos. Como usuario postgres:

createdb gis

Espacialización de la base de datos

psql -d gis -c "CREATE EXTENSION postgis;"

Espacialización de la base de datos

Tabla spatial_ref_sys:

contiene una lista con los ¿sistemas de referencia? disponibles

PROJCS["ETRS89 / UTM zone 29N",
    GEOGCS["ETRS89",
        DATUM["European_Terrestrial_Reference_System_1989",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            AUTHORITY["EPSG","6258"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.01745329251994328,
            AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4258"]],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",0],
    PARAMETER["central_meridian",-9],
    PARAMETER["scale_factor",0.9996],
    PARAMETER["false_easting",500000],
    PARAMETER["false_northing",0],
    AUTHORITY["EPSG","25829"],
    AXIS["Easting",EAST],
    AXIS["Northing",NORTH]]

Espacialización de la base de datos

Vista geometry_columns:

catálogo de las columnas espaciales existentes en la base de datos. PostGIS debe buscarse una manera de identificar qué campo contiene geometrías. Esto se hace de manera estándar (OGC) manteniendo un catálogo con la lista de columnas espaciales que existen. Hay que tenerla siempre actualizada

Espacialización de la base de datos

# SELECT postgis_full_version();

Creación de tablas espaciales

Creamos la tabla, bien con create table o desde pgAdmin

Creación de tablas espaciales

Definimos campo geométrico:

Usando geometry (typmod):

# alter table [nombre_tabla] add column geom geometry([tipo_geometria], SRID)

O usando el estandar:

# SELECT AddGeometryColumn ('mi_schema','mi_tabla','geom',SRID,'[tipogeometria]',dimension);

Caracteristicas espaciales postgis

Funciones espaciales

Indices espaciales

Funciones espaciales

Una base de datos ordinaria proporciona funciones para manipular los datos en una consulta como concatenación de cadenas, operaciones matemáticas o la extracción de información de las fechas. Una base de datos espacial proporciona un completo juego de funciones para poder realizar análisis con los objetos espaciales: analizar la composición del objeto, determinar su relación espacial con otros objetos, transformarlo, etc.

Funciones Espaciales

La mayor parte de las funciones espaciales pueden ser agrupadas en una de las siguientes cinco categorías:

Conversión: Funciones que convierten las geometrías a otros formatos externos

Gestión: Tareas administrativas de PostGIS

Recuperación: Obtienen propiedades y medidas de las geometrías.

Comparación: Comparan dos geometrías y obtienen información sobre su relación espacial.

Generación: Generan geometrías a partir de otros tipos de datos.

La indexación para tipos de datos estándar que pueden ser ordenados (alfabéticamente o numéricamente) consiste en esencia en ordenar estos registros de manera que sea fácil localizarlos.

Pero en el caso de la información espacial no existe un orden total ya que un polígono puede contener a un punto, cruzarse con una línea, etc. Se ponen en marcha ciertas estrategias para asociar los registros con determinadas partes del territorio que cubren y así poder obtener los registros que se encuentran cerca de una posición dada.

PostgreSQL implementa un algoritmo de indexación espacial denomimado GiST (Generalized Search Tree). PostGIS extiende los índices GiST para que funcionen adecuadamente con los tipos geometry.

Índices espaciales

Importación y exportación de datos

Objetivos

Conocer las herramientas de importación y exportación de las que dispone PosGIS

shp2pgsql

pgsql2shp

shp2pgsql-gui

ogrinfo

ogr2ogr

osm2psql

Importar datos desde shape a PostGIS y exportar esos datos.

Importar datos OSM

Importar datos mediante ogr

shp2pgsql

Convierte archivos ESRI Shapefile en SQL preparado para la inserción en la base de datos

¿ESRI Shapefile?

shp2psql

$ shp2pgsql [<opciones>] <ruta_shapefile> [<esquema>.]<tabla>

shp2pgsql -s 23029 -I data/057/nucleo_poblacion.shp gis.nucleo_poblacion | psql -d gis -U gis

COMANDOS

-s <srid> Asigna el sistema de coordenadas. Por defecto será -1

(-d|a|c|p)

-d Elimina la tabla, la recrea y la llena con los datos del shape

-a Llena la tabla con los datos del shape. Debe tener el mismo esquema exactamente

-c Crea una nueva tabla y la llena con los datos. opción por defecto.

-p Modo preparar, solo crea la tabla

-g <geocolumn> Especifica el nombre de la columna geometría (usada habitualmente en modo -a)

-G Usa tipo geografía, requiere datos de longitud y latitud

mas comandos

-I Crea un índice spacial en la columna de la geometría

-S Genera geometrías simples en vez de geometrías MULTI

-w Salida en WKT

-W <encoding> Especifica la codificación de los caracteres. (por defecto : “WINDOWS-1252”)

-n Solo importa el archivo DBF

-? Muestra la ayuda

pgsql2shp

Con ella podremos convertir los datos de nuestra base de datos en archivos ESRI Shape

pgsql2shp

$ pgsql2shp [<opciones>] <basedatos> [<esquema>.]<tabla>

$ pgsql2shp [<opciones>] <basedatos> <consulta>

pgsql2shp -u gis -P gis -f parroquia_vigo.shp gis "select * from gis.nucleo_poblacion where parroquia = 'VIGO'"

comandos

-f <nombrearchivo> Especifica el nombre del archivo a crear

-h <host> Indica el servidor donde realizará la conexión

-p <puerto> Permite indicar el puerto de la base de datos

-P <password> Contraseña

-u <user> Usuario

-g <geometry_column> Columna de geometría que será exportada

shp2pgsql-gui

$ shp2pgsql-gui

GDAL/OGR

GDAL/OGR es una librería de lectura y escritura de formatos geoespaciales, tanto Raster con GDAL como Vectorial con OGR. Se trata de una librería de software libre ampliamente utilizada.

ogrinfo

ogrinfo obtiene información de los datos vectoriales. Podremos utilizar esta herramienta para la obtención de esta información de las tablas que tenemos almacenadas en la base de datos

ogrinfo

$ ogrinfo [<opciones>] <ruta fuente datos>

ogrinfo data/057/nucleo_poblacion.shp

comandos

-where muestra los datos de las filas que cumplan la clausula

-sql filtra la información mediante consultas SQL

ogr2ogr

OGR es capaz de convertir a PostGIS todos los formatos que maneja, y será capaz de exportar desde PostGIS todos aquellos en los que tiene permitida la escritura.

ogr2ogr

$ ogrinfo -formats

ogr2ogr

En la página principal de GDAL podremos encontrar un listado de todas las opciones que nos permite manejar el comando. Detallamos a continuación algunas de las principales:

-select <lista de campos> lista separada por comas que indica la lista de campos de la capa de origen que se quiere exportar

-where <condición> consulta a los datos de origen

-sql posibilidad de insertar una consulta más compleja

ogr2ogr

Otras opciones en referencia al formato de destino (las anteriores hacían referencia al de origen):

-f <driver ogr> formato del fichero de salida

-lco VARIABLE=VALOR Variables propias del driver de salida

-a_srs <srid> asigna el SRID especificado a la capa de salida

-t_srs <srid> Reproyecta la capa de salida según el SRID especificado

ogr2ogr -f "KML" nucleo_poblacion.kml PG:"host=localhost user=gis dbname=gis password=gis" -sql "select gid, nom_ine, geom from gis.nucleo_poblacion"

OSM

OpenStreetMap (también conocido como OSM) es un proyecto colaborativo para crear mapas libres y editables.

Los mapas se crean utilizando información geográfica capturada con dispositivos GPS móviles, ortofotografías y otras fuentes libres. Esta cartografía, tanto las imágenes creadas como los datos vectoriales almacenados en su base de datos, se distribuye bajo licencia abierta Open Database Licence (ODbL).

osm

Diferente relaciones entre los datos:

Node: simplemente un punto

Way: una lista ordenada entre 2 y 20000 nodes area = yes

Relation: lista ordenada de nodes o ways relaciondos entre ellos

a diferencia de las geometrías características como:

Punto, Linea, Polígono

osm

Una característica particular es la ausencia de polígonos dentro del modelo, estos se realizan mediante la asignación de una relación a una linea cerrada. Esta particularidad no impide que los datos de OSM puedan ser adaptados al modelo de geometrías normal mediante cargadores de datos OSM.

osm2pgsql

No mantiene las relaciones entre las way y nodes, solo dibuja la linea como una MultiLinestring

osm2pgsql

Mediante el uso de este programa podremos incorporar en nuestra base de datos los datos obtenidos desde OSM. Una vez que hemos realizado la importación, aparecerán en nuestra base de datos las tablas que serán el resultado de esta importación:

planet_osm_point

planet_osm_line

planet_osm_polygon

planet_osm_roads

osm2pgsql

$ osm2pgsql [opciones] ruta_fichero.osm otro_fichero.osm

$ osm2pgsql [opciones] ruta_planet.[gz, bz2]

comandos

-H Servidor PostGIS

-P <puerto> Puerto

-U <usuario> Usuario

-W pregunta la password del usuario

-d <base_de_datos> base de datos de destino

-a añade datos a las tablas importadas anteriormente

-l almacena las coordenadas en latitud/longitug en lugar de Spherical Mercator

-s utiliza tablas secundarias para la importación en lugar de hacerlo en memoria

-S <fichero_de_estilos> ruta al fichero que indica las etiquetas de OSM que se quiere importar

-v modo verborrea, muestra la sali da de las operaciones por consola

osmosis

osmosis [opciones] file=ruta al fichero osm –write-pgsql database=nombre base de datos user=usuario password=contraseña

osmosis

user <usuario> usuario

host <host> servidor

password <contraseña>

database <base de datos>

--write-pgsql Exporta los resultados a PostGIS

--truncate-pgsql Elimina los registros de todas las tablas de osmosis

--read-pgsql Lee datos osmosis en PostGIS

--read-xml Establece como origen el formato xml de OSM

Simple Feature Model, Geometrias, Tipos y Predicados Espaciales

El estándar SFA de OGC (ISO 19125)

Tiene como objetivo conseguir la “Interoperabilidad Semántica”: la reutilización de los datos vectoriales generados por distintos paquetes software, sin que se produzcan pérdidas por la falta de correspondencia entre las implementaciones del modelo vectorial empleadas.

El estándar SFA de OGC (ISO 19125)

OGC no solo definió una especificación abstracta del modelo vectorial, además:

Definió dos modelos de representación externa de la componente espacial del dato geográfico: los estándares WKT y WKB
Un formato de intercambio de GML
Un esquema “genérico” de cómo se debería implementar el modelo vectorial definido por la norma SFA en una base de datos SQL: SFA para SQL.

Dimensión

Una entidad puntual tendrá dimensión 0

Una entidad lineal tendrá dimensión 1

Una entidad superficial tendrá dimensión 2

ST_Dimension(geometría) devolverá el valor de la dimensión

Una geometry collection tendrá la dimensión más alta de las geometrías que la componen

Dimensión de las coordenadas

No confundir la dimensión de una geometría con las dimensiones de las coordenadas que la componen. Esta indica la dimensión de las coordenadas de los vértices que las componen:

X,Y = 2

X,Y,Z = 3

…

ST_CoordDim(geometria) devuelve este valor de las dimensiones

Interior, contorno y exterior

El estándar SFA de OGC (ISO 19125)

La clase clave de toda la jerarquía de clases del modelo es la clase abstracta Geometry.

La clase abstracta Geometry representa una estructura común (propiedades) y un comportamiento común (métodos) a todos los subtipos geométricos de la implementación que del paradigma vectorial hace el estándar ISO/OGC SFA.

clase geometry

Los métodos básicos ofrecen funciones para obtener propiedades básicas de la geometría: si es simple o no, superficie y perímetro, coordenadas de sus vértices (en caso de que su dimensión sea superior a 0), mínimo rectángulo que la envuelve, etc.

Los métodos ST_AsText() y ST_AsBinary(), permiten obtener la representación externa de una instancia u objeto de la clase Geometry según los estándares WKT o WKB.

WKT

WKT es el acrónimo en inglés de Well Known Text, que se puede definir como una codificación o sintaxis diseñada específicamente para describir objetos espaciales expresados de forma vectorial.

A efectos prácticos la sintaxis WKT consta de una descripción de los vértices que componen la geometría. Para que esta forma de especificar las geometrías tengan sentido deben de acompañarse de una indicación de la referencia espacial o proyección cartográfica utilizada en dicho vector.

WKB

WKB acrónimo de Well Known Binary es la variante de este lenguaje, pero expresada de forma binaria, también utilizada por los gestores espaciales, pero con la ventaja de que al ser compilada en forma binaria la velocidad de proceso es muy elevada.

definiciones

Point y Multipoint

definiciones

Linestring y MultiLinestring

definiciones

Polygon y MultiPolygon

métodos básicos

st_issimple(geometria)

st_isvalid(geometria)

st_isvalidreason(geometria)

st_isvaliddetail(geometria)

st_dimension(geometria)

st_isclosed(geometria)

st_numinteriorrings(geometria)

relaciones espaciales

Verifican el cumplimiento de determinados predicados geográficos entre dos geometrías distintas.

Los predicados geográficos toman dos geometrías como argumento, y devuelven un valor booleano que indica si ambas geometrías cumplen o no una determinada relación espacial

Las principales relaciones espaciales contempladas son equals, disjoint, intersects, touches, crosses, within, contains, overlaps.

relaciones espaciales

Métodos para realizar análisis espacial.

Reciben una o más geometrías como parámetro, y devuelven una nueva geometría complemente distinta de las anteriores (o un valor número que mide una determinada propiedad).

Relaciones Espaciales

Matriz DE-9IM

ST_Relate

ST_Relate permite el uso de patrones personalizados

El patrón T******** me indica que los interiores de las geometrías tienen que tener alguna dimensión, por lo tanto deben intersecar

Mediante el uso de ST_Relate y los patrones podemos encontrar las relaciones de las geometrías. Es tedioso el uso de los patrones.

PostGIS define operaciones que son particularizaciones del uso de los patrones.

Estas operaciones deben cumplir la definición de la relación impuesta por los estándares

ST_Equals(geom, geom)
ST_Disjoint(geom, geom)
ST_Intersects(geom, geom)
ST_Touches(geom, geom)
ST_Crosses(geom, geom)
ST_Overlaps(geom, geom)
ST_Within(geom A, geom B)
ST_Contains(geom A, geom B)
ST_Covers(geom A, geom B) = ST_CoveredBy(geom B, geom A)

Carreteras que cruzan Vigo

create table gis.carreteras_cruzan_vigo as (select c.gid, c.geom
from gis.nucleo_poblacion as n, gis.tramo_carretera as c
where ST_Crosses(n.geom, c.geom)
and n.nom_ine = 'VIGO');

Tramos de carreteras de Vigo

create table gis.carreteras_de_vigo as (select c.gid, c.geom
from gis.nucleo_poblacion as n, gis.tramo_carretera as c
where ST_Contains(n.geom, c.geom)
and n.nom_ine = 'VIGO');

Operaciones Espaciales

Estas operaciones toman como argumento una o varias geometrías y devuelven otra geometría.

Toman únicamente las coordenadas X e Y de las geometrías ignorando las Z o M

Funcionan con todas las geometrías básicas excepto con las GEOMETRY COLLECION

Buffer

ST_Buffer(geom,distance,optional)

Es el conjunto de puntos situados a una determinada distancia de la geometría. La función acepta en su primer argumento una geometría y en el segundo la distancia con la que construirá la geometría de salida.

create table gis.buffer_castelao as (select c.gid, st_buffer(c.geom, 50)
from gis.tramo_carretera as c
where c.gid = '121')

Otras funciones

ST_Intersection(geom, geom)
ST_Union(Geometría A, Geometría B)
ST_Diference(geom,geom)
ST_SymDifference(geom,geom)
ST_ConvexHull(geom,geom)

Complejidad en el análisis

Uso intensivo de SQL para definir consultas eficaces y correctas

Estrategias de SQL avanzadas

Definición del problema y planificación de la consulta, diseño previo

¡Uso de índices espaciales!

Tipos de geometrías devueltas

El tipo de geometrías que devuelven estas operaciones no tienen porque ser igual al tipo de geometrías que le son pasadas como argumentos.

Proyecciones

PostGIS soporta varios miles de sistemas de coordenadas.

La gestión de los Sistemas de coordenadas se realiza con las siguientes funciones:

ST_Srid(geom) → devuelve el CRS de la geometría

ST_SetSrid(geom,srid) → devuelve la geometría con el nuevo CRS asignado

ST_Transform(geom, srid) → devuelve la geometría reproyectada

Cambio de CRS a una capa

En caso de que necesitemos cambiar el CRS a una capa por completo no será necesario recorrer todos los registros, podremos utilizar la función:

UpdateGeometrySrid(tabla, columna geom, nuevo SRID)

¡No reproyecta los datos!

Reproyección de una capa

Para reproyectar las geometrías de una capa utilizaremos ST_Transform. Para poder hacer esta operación, hemos de eliminar la restricción sobre el SRID antes de actualizar las geometrías.

create table gis.nucleo_3857 as (select n.gid, n.nom_ine, ST_Transform(n.geom, 3857) as geom from gis.nucleo_poblacion as n)

Indexación Espacial

Índices Espaciales

La caja (box) es el rectángulo definido por las máximas y mínimas coordenadas x e y de una geometría.

Índices Espaciales

Crear un índice espacial

La síntaxis será la siguiente:

CREATE INDEX [Nombre_del_indice] ON [Nombre_de_tabla] USING GIST ([campo_de_geometria]);

Esta operación puede requerir bastante tiempo en tablas de gran tamaño.

Uso del índice

Para usar el índice debemos incluir el operador && en nuestras consultas

Para las geometrías, el operador && significa “la caja que toca (touch) o superpone (overlap)” de la misma manera que para un número el operador = significa “valores iguales”