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
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 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”
Operador de cajas
Muchas de las operaciones habituales de PostGIS dispone de operador de cajas, por ejemplo:
ST_Intersects VS _ST_Intersects
django-rest-framework-gis
"@postgis en una cáscara de nuez" by Micho García is licensed under CC BY 2.0
Micho García
micho.garcia@geomati.co
@michogar
PostGIS en una cáscara de nuez # 27 / 02 / 2016
@postgis en una cáscara de nuez
By Micho García
@postgis en una cáscara de nuez
Breve introducción a PostGIS para la Hackathon SJ - Vigo - Febrero 2016
- 1,647
