PostGIS, PostgreSQL y triggers: Georreferenciar es fácil

  • Google Plus
  • Facebook
  • Twitter
  • Email
  • RSS
  • Add to favorites

Los triggers o disparadores de PostgreSQL  son una de sus funcionalidades más tractivas.Ante una determinada operación (INSERT, UPDATE O DELETE) provocan la ejecución de una función en nuestra base de datos. Si esto lo usamos en conjunción con PostGIS, nos permite tener tablas en las que el manejo de datos espaciales sea sencillo. La documentación sobre los triggers es prolija y yo, sobre todo por falta de conocimiento profundo, no quisiera extenderme demasiado e ir al grano.

¿Y para qué quiero Postgis, PostgreSQL y los triggers?

A menudo, en nuestro trabajo, tenemos que elaborar mapas y por lo tanto precisamos de herramientas que nos permitan posicionar correctamente nuestra información. O nos puede interesar establecer relaciones entre nuestros datos teniendo en cuenta su distribución o posición geográfica. Estas herramientas no tienen que ser necesariamente Sistemas de Información Geográfica. Hay personas que elaboran mapas de primera con herramientas no necesariamente pensadas para esto (por ejemplo, con Excel). En todo caso, tendremos que proporcionar de una manera u otra nuestros datos a la aplicación que empleemos.

Para ese fin algunos Sistemas de Gestión de Bases de Datos proporcionan, de alguna manera, soporte para almacenar y manipular datos espaciales. En el caso de PostgreSQL esta funcionalidad se logra mediante la extensión PostGIS, que no hace sino incorporar a nuestra base de datos un conjunto de funciones, tablas y tipos de datos que nos permitirán almacenar y manipular datos espaciales. La instalación de PostGIS es bastante sencilla, aunque no tanto la actualización de las bases de datos que incorporen PostGIS, pero eso será materia de otro post. Otras bases de datos cuentan con funcionalidades similares (OracleSpatial, SpatiaLite) pero yo me centraré en PostgreSQL + PostGIS porque es la que uso y más o menos entiendo y porque quizá es el Sistema de Gestión de Bases de Datos que más se  entiende con el software SIG disponible y sobre todo, con QuantumGIS que es software GIS que yo utilizo.

Una ventaja adicional de emplear un sistema tipo PostgreSQL + PostGIS es que no es necesario el uso de software GIS para realizar algunas tareas en las que estos softwares osn de aplicación. Así, por ejemplo, es posible consultar si un elemento espacial está dentro de otro, o qué otros elementos gráficos están contenidos en uno determinado. Una aplicación de esto último puede ser, por ejemplo, obtener los lances de pesca ubicados dentro de un determinado lugar de una cuadrícula, o en una determinada zona delimitada por unas coordenadas o, utilizando las funciones de creación de buffers espaciales, conocer qué CTD se han efectuado a una distancia determinada de un punto dado, etc. En otras palabras, nos permiten prescindir, hasta cierto punto, de un software dedicado para realizar las consultas espaciales.

Ahora bien, existe un problema y es que que PostgreSQL + PostGIS no almacenan la información espacial de una manera directamente interpretable por el usuario, como unos valores numéricos de latitud y longitud, por ejemplo, ni los programas leen la información de bases de datos PostGIS de esa manera, sino que para ello utilizan un formato descrito por el Open Geospatial Consortium denominado WKB (Well-Known Binary) que es el formato en el que las bases de datos almacenan la información espacial.

El WKB tiene un hermano mellizo que es el WKT (Well-Known Text) que es la representación digamos… humanamente entendible del objeto que almacenamos como WKB. He hecho mención al objeto porque hay diferentes tipos de objetos espaciales. Los más habituales y que en los que pensaremos inmediatamente son el punto y la línea… pero tenemos también multilíneas, polígonos, multipolígonos, multipuntos… y cada uno de ellos con su SRID o sistema de coordenadas asociado. Por eso, una tabla que almacene un objeto de tipo punto no tiene una columna para latitud y otra para longitud, sino que tiene una única columna que almacena el objeto. Para almacenarlo, PostGIS proporciona unas funciones que permiten la creación de los objetos a partir de sus coordenadas, tipo de objeto, SRID, etc.

Pero es probable que a nosotros nos interese recuperar sin mayor problema las coordenadas numéricas de nuestros puntos o líneas o, simplemente, que podamos interactuar con mayor facilidad con nuestra base de datos. Es más fácil introducir los datos numéricamente, tal y como los podemos tomar en el barco que teniéndonos que acordar cada vez de pasar la función con los parámetros adecuados.Y nos puede interesar, para facilitar luego medidas de distancia, etc o la representación de los lances en un mapa, disponer de nuestro lance como un objeto de tipo línea en lugar de de puntos separados. Y aquí es donde aparecen los triggers.

¿Qué queremos hacer con el trigger?

Lo que queremos hacer es sencillo. Simplemente, que cuando demos de alta un registro o cambiemos sus coordenadas el objeto espacial correspondiente almacenado en la base de datos se cree o se actualice convenientemente.

Lo que ocurre es que esto requiere de unos pasos previos. Lo primero que tenemos que hacer es tener una tabla con columnas espaciales del tipo de dato que nos convenga. En el ejemplo que os describo, voy a suponer que queremos disponer de una tabla donde almacenamos los lances de pesca de una campaña. Esta tabla constará de un nombre de campaña, un número de lance, una posición de largado, otra de firmes, otra de virado y otra de arte a bordo. Además, queremos almacenar, por las razones que hemos comentado antes, una línea que una los puntos de firme y virado.

Creando la tabla en PostgreSQL

Para trabajar con datos espaciales en PostgreSQL + PostGIS no es posible crear mediante una sola sentencia CREATE TABLE la tabla con soporte espacial. Es preciso hacerlo en dos fases, primero creando la tabla normal sin las columnas espaciales y posteriormente añadiéndoles éstas mediante una función de PostGIS llamada (muy original no es, pero sí práctico y lógico, como debe ser)  AddGeometryColumn(),

La creación de la tabla es trivial, ahora bien… hemos de tener en cuenta que necesitaremos, para cada uno de los puntos involucrados en la creación de nuestro objeto espacial (uno si es un punto, dos si es una línea…) , dos columnas; una almacenará la longitud y la otra la latitud.

Añadiendo las columnas espaciales

Ahora tendremos que hacer uso de las funciones que PostGIS pone a nuestra disposición para dotar a una tabla de capacidades de almacenamiento de datos espaciales.

Esta función se llama AddGeometryColumn y su sintaxis es:

Donde:

  • tabla es el nombre de la tabla a la que vamos a añadir la columna,
  • columna es el nombre de la columna a añadir,
  • srid es el sistema de referecia de coordenadas del elemento que vamos a añadir. Para los que usamos el sistema WGS84 (que es el SRID de las coordenadas que proporcionan los GPS habitualmente, o al menos las sentencias NMEA a las que tenemos acceso), este valor es 4326. Para otros SRID es buena idea buscar en www.spatialreference.org.
  • tipo es el tipo de elemento a insertar (linea, punto…)
  • dimension es el numero de dimensiones del mismo (en nuestro caso, 2)

Con esto y un poquito de ganas, creamos nuestras columnas para los puntos y la línea:

Ahora ya viene la parte más delicada y es la creación del trigger.

Creando el trigger en PostgreSQL

La creación del trigger es un proceso que se realiza en dos fases. Puedes obtener información detallada sobre triggers y funciones en las páginas de documentación de PostgreSQL sobre programación del servidor.

En la primera, programaremos la función que dará, valga la redundancia, funcionalidad a nuestro trigger. Las funciones para los trigger son especiales, en tanto en cuanto no tienen valor de retorno, sino que devuelven un objeto de tipo trigger. Lo que hará nuestra función es tomar cada uno de los pares de coordenadas que introdujimos en nuestra tabla (un par por cada uno de los puntos que vamos a almacenar) y construir un dato geométrico que almacenará en la columna correspondiente. Además, una vez construidos los puntos, creará un objeto de tipo línea que almacenará la línea del lance de pesca.

En la segunda fase, asociaremos ese trigger a nuestra tabla en la base de datos. Esta es, al menos léxicamente, la verdadera creación del trigger.

El código para crear la función es el siguiente:

Aparte de las líneas que crean formalmente la sentencia, las que nos interesa conocer cómo funcionan son las del tipo

Básicamente, lo que le decimos a nuestro servidor es que cuando al ejecutar el trigger se le pase a la función que vamos a crear el registro que se inserta o actualiza, el NUEVO valor de la columna XXX_punto será el resultado de:

  • Crear un punto mediante la función ST_MakePoint() de PostGIS.
  • Asignar un SRID a ese punto, en nuestro caso el 4326 (WGS84)

Fijaos en lo de NEW, porque volveremos sobre ello más adelante.

Una vez creado los puntos y aprovechando que éstos tienen ya asignado su SRID, mediante la sentencia

Creamos la línea que unirá los puntos de largado y virada.

Ahora, lo que hace el resto de la función

es retornar el registro NUEVO , indicar que la función termina y cerrar la declaración de la función.

Ahora, como dijimos antes, viene la segunda fase y es la declaración del trigger. Esto lo hacemos mediante las siguientes sentencias:

¿ Os acordáis de lo que os dije antes acerca de los registros NUEVOS? Pues bien, si os fijáis en la parte de BEFORE INSERT OR UPDATE IN lances , lo que decimos aquí es que antes de que se inserte o actualice el nuevo registro ejecute la función que hemos creado sobre cada tupla que se inserte. Por tanto, las variables sobre las que operamos son las columnas de cada tupla, y nos referimos a ellas con NEW.nombre_de_columna, con lo cual, para que quede insertada correctamente la columna de geometría nos referimos a ella en la función como NEW igualmente.

Ahora ya tenemos nuestra tabla lances habilitada para los datos espaciales y, lo que es mejor aún, podemos introducir las coordenadas de los mismos sin necesidad de emplear funciones de PostGIS en cada una de nuestras consultas de inserción o actualización de datos.

¡¡¡Espero que el artículo os sea de utilidad!!!

 

¡¡Compártelo y disfruta!!

  • Google Plus
  • Facebook
  • Twitter
  • Email
  • RSS
  • Add to favorites