11 Dic 2014 Nubes de puntos. Análisis de densidades con PostGIS y visualización

SIGTE

INTRODUCCIÓN.

En este ejercicio vamos a utilizar datos libres procedentes de OpenStreetMap y Flickr para detectar cuáles son las zonas más fotografiadas de la ciudad de Barcelona. La representación final de los datos se va a llevar a cabo utilizando el SIG de escritorio OpenJUMP.

La delimitación de la ciudad de Barcelona la vamos a obtener siguiendo el post Importando una geometría concreta de OpenStreetMap, mientras que la información relativa a las fotografías de FLICKR ya se encuentra disponible en el fichero flickr.xls que ha sido generado, previamente, a partir de la API de Flickr.

Para saber más sobre la API de flickr podéis dirigiros a este enlace https://www.flickr.com/services/api/

El fichero flickr.xls, contiene para cada fotografía, los siguientes atributos:

Usuario: Identificador del usuario que ha tomado la fotografía
Foto_id: Identificador único de la foto
Latitud: Latitud de la localización en la que fue tomada la fotografía
Longitud: Longitud de la localización en la que fue tomada la fotografía
Fecha: Fecha en la que fue tomada la fotografía.

EJERCICIO.

1.- Base de datos con capacidad Espacial.

En primer lugar vamos a crear una nueva base de datos con capacidad espacial a la que vamos a llamar fotos_barcelona. Desde pgAdmin podemos crear una base de datos utilizando la plantilla template1. Para añadir las funcionalidades espaciales, accedemos a una ventana de comandos SQL y ejecutamos la siguiente sentencia:

CREATE EXTENSION postgis;

Esto creará la tabla spatial_ref_sys en nuestra base de datos y añadirá todas las funcionalidades espaciales de PostGIS.

2.- Importación datos tabulados.

Una vez creada la base de datos, vamos a crear una tabla llamada flickr donde posteriormente importaremos los datos contenidos en el fichero flickr.xls

CREATE TABLE flickr(
usuario varchar(15),
foto_id varchar(15),
latitud float,
longitud float,
fecha timestamp);

Donde los campos usuario, foto_id se definen como columnas de tipo VARCHAR y una longitud de 15 caracteres. Las columna latitud y longitud se definen como valores numéricos de tipo float (números reales que pueden incluir decimales). Finalmente la columna fecha se define como un tipo de datos timestamp para permitir almacenar valores de tiempo que incluyen tanto la fecha como la hora.

Antes de importar los datos deberemos renombrar el fichero flickr.xls a flickr.txt. Para la importación vamos a utilizar el comando COPY. En nuestro caso, el fichero (ahora) flickr.txt se localiza en la carpeta C:datosflickr.txt

COPY flickr FROM ‘C:datosflickr.txt‘;

y para comprobar que la tabla flickr contiene esos datos, ejecutamos el comando:

SELECT * FROM flickr;

3.-Importación de la delimitación geográfica de la ciudad de Barcelona.

Todos los pasos a seguir se encuentran el el post Importar una geometría concreta de OpenStreetMap a una Base de Datos PostgreSQL/PostGIS.

4.- Visualizar el contenido de la tabla ‘barcelona’ en OpenJump.

Desde OpenJUMP accedemos al menú File/Run Datastore Query para abrir la ventana emergente que aparece en la siguiente imagen. Siguiendo los pasos indicados, establecemos una conexión a la base de datos indicando los parámetros de la conexión (nombre, servidor, bases de datos, usuario, etc).

El siguiente paso consiste en definir la consulta para extraer la geometría de Barcelona de nuestra base de datos. Desde el mismo menú File/Run Datastore Query de OpenJUMP tecleamos la consulta y pulsamos en aceptar.

SELECT * FROM barcelona;

5.- Visualizar el contenido de la tabla flickr.

Llegados a este punto, es necesario crear una nueva columna en la tabla flickr de tipo geometry ya que, hasta el momento, esta tabla no contiene ninguna columna que se pueda mostrar gráficamente en un SIG de escritorio como OpenJUMP . En esta nueva columna vamos a almacenar un objeto espacial que represente la localización en que fue tomada cada una de las fotos. Esta geometría de tipo POINT se creará a partir de los valores almacenados en las columnas latitud y longitud.

Desde pgAdmin, vamos a utilizar el siguiente comando para crear una nueva columna de tipo geometry:

ALTER TABLE flickr ADD COLUMN geom geometry;

Podemos comprobar que se ha añadido una nueva columna mediante el comando. Todos los comandos SELECT que retornan alguna geometría pueden ser visualizados desde OpenJUMP. En caso contrario, los comandos deben ejecutarse SIEMPRE desde pgAdmin.

SELECT * FROM flickr;

Para crear la geometría de tipo POINT vamos a utilizar el comando UPDATE para modificar el contenido de la columna que acabamos de generar y la función espacial St_MakePoint(x,y) para crear la geometría de tipo POINT a partir de las columnas latitud y longitud

UPDATE flickr SET geom=St_MakePoint(longitud, latitud);

Donde la longitud se corresponde con el eje de las X y la latitud con el eje de las Y.

Llegados a este punto, acabamos de generar unas geometrías de tipo POINT pero todavía no hemos indicado cuál es el sistema de referencia (SRID) de esas geometrías. Por lo tanto vamos a ejecutar de nuevo el comando UPDATE con la función St_SetSRID(geometria, SRID) para indicar el sistema de referencia de las geometrías que acabamos de crear.

UPDATE flickr SET geom=St_SetSRID(geom, 4326);

Lógicamente podríamos ejecutar las dos sentencias de una sola vez mediante el comando:

UPDATE flickr SET geom=St_SetSRID(St_MakePoint(longitud, latitud), 4326);

Ahora sí, podemos visualizar el contenido de la tabla flickr desde OpenJUMP

6.- Fotografías tomadas en Barcelona.

En esta ocasión vamos a hacer un JOIN espacial entre la geometría de tipo POLYGON de la tabla Barcelona y las geometrías de tipo POINT de la tabla flickr. De este modo vamos a seleccionar únicamente las fotografías tomadas dentro de la ciudad. Tenemos, pues, distintas posibilidades espaciales:

St_Intersects

SELECT flickr.* FROM flickr , barcelona WHERE St_Intersects(flickr.geom, barcelona.geom);

St_WithIn

SELECT flickr.* FROM flickr , barcelona WHERE St_WithIn(flickr.geom, barcelona.geom);

St_Contains

SELECT flickr.* FROM flickr , barcelona WHERE St_Contains(barcelona.geom, flickr.geom);

Donde St_WithIn y St_Contains son equivalentes con el orden de los parámetros invertido. ‘A’ está dentro de ‘B’, si ‘B’ contiene ‘A’. St_Within(A,B)=St_Contains(B,A)

Con el objetivo de simplificar los comandos SQL que vendrán a continuación, vamos a crear una nueva tabla fotos_barcelona.

CREATE TABLE fotos_barcelona AS
SELECT flickr.* FROM flickr , barcelona WHERE St_Contains(barcelona.geom, flickr.geom);

7.- Cálculo de densidades de fotografías tomadas en Barcelona.

Para el cálculo de densidades vamos a simular una malla sobre el territorio de Barcelona y vamos a contar cuántas fotografías han sido tomadas en cada una de las celdas que definen la malla. El siguiente paso será colorear cada celda de la malla, en función del número de fotografías que se hayan tomado dentro de ella. Se trata pues de dos pasos bien diferenciados. Para el primero, nos viene muy bien la función St_SnapToGrid. Esta función nos permite agrupar geometrías dentro una malla determinada.

Veamos la función St_SnapToGrid en detalle.

St_SnapToGrid(geom, origenX, origenY, tamañoX, tamañoY).

Donde:

geom es la geometría, cuyas coordenadas, se van a ajustar a una malla determinada.
origenX: Coordenada de origen de la malla para el eje de las X
origenY: Coordenada de origen de la malla para el eje de las Y
tamañoX:Tamaño, sobre el eje de las X, de las celdas que definen la malla
tamañoY:Tamaño, sobre el eje de las Y, de las celdas que definen la malla

Gráficamente:

Como se aprecia en la imagen anterior, la función St_SnapToGrid ajusta las coordenadas de una geometría dada (en la imagen geometrías de tipo POINT) a una malla definida por un punto de origen (0, 0) y un tamaño de celda concretos (X,Y).

Aplicando la función St_SnapToGrid a las fotografías de la tabla fotos_barcelona obtenemos la siguiente sentencia SQL:

SELECT St_SnapToGrid(geom, 0, 0, 0.003, 0.003) FROM fotos_barcelona;

Dado que estamos utilizando coordenadas geográficas para latitudes alrededor de los 41 grados, un tamaño de celda de 0.003 grados se corresponde con una distancia del orden de 100 metros.

Puedes comprobar el efecto de la función St_SnapToGrid para tamaños de celda distintos.

8.-Generar las geometrías de las celdas.

Antes de colorear las celdas de la malla, hay que crearlas. La función St_SnapToGrid, utiliza una malla generada (y destruida) al vuelo, por lo que después de utilizar la función St_SnapToGrid, la malla utilizada no está disponible. El próximo paso consiste, pues, en generar las geometrías de cada celda.

La siguiente imagen muestra el resultado obtenido con la función St_SnapToGrid. Como se aprecia en la imagen las geometrías de tipo POINT (puntos azules) se ajustan perfectamente a una malla. Todos los puntos que se encuentran dentro de cada celda de la malla (representada sobre fondo gris), serán ajustados a la misma coordenada de tipo POINT localizada en el centro de la celda y representada como un punto de color azul.

Para generar todas las geometrías de las celdas, vamos primero a utilizar la función distinct para seleccionar las geometrías, sin repeticiones, retornadas por la función St_SnapToGrid. Si obtenemos las geometrías sin repeticiones podremos luego obtener las geometrías de celda, también sin repeticiones.

Utilizando el siguiente comando, vamos a crear una nueva tabla puntos_snap (sin repeticiones).

CREATE TABLE puntos_snap AS
SELECT distinct St_SnapToGrid(geom,0,0,0.003,0.003) AS geom FROM fotos_Barcelona ;

Con la geometrías obtenidas anteriormente, vamos a utilizar la función St_Expand para expandir esas geometrías. De modo gráfico y volviendo a la imagen anterior, vamos a expandir las geometrías de los puntos azules para obtener la superficie de influencia representada en fondo de color gris.

Igual que hemos hecho con anterioridad, y para mayor comodidad, vamos a crear una nueva tabla puntos_expand.

CREATE TABLE puntos_expand AS
SELECT St_Expand(geom, 0.0015) AS geom FROM puntos_snap;

El tamaño de la expansión (0.0015) se corresponde con la mitad del tamaño de la malla (0.003) ya que dicha extensión se lleva a cabo en todas direcciones (Norte, Sur, Este y Oeste).

9.- Recuento de las fotografías tomadas en cada celda de la malla.

Veamos primero el comando necesario:

SELECT count(*) AS n, puntos_expand.geom
FROM fotos_barcelona, puntos_expand
WHERE St_Within(st_snaptogrid(fotos_Barcelona.geom,0,0,0.003, 0.003), puntos_expand.geom)
GROUP BY puntos_Expand.geom
ORDER BY count(*) DESC;

En la cláusula WHERE aparece un JOIN espacial entre dos geométricas mediante la función St_WithIn.

La primera de esas geometrías es el resultado de la función St_SnapToGrid. Como hemos comentado anteriormente, primero ajustamos las fotografías de barcelona a una malla determinada, y con esas geometrías y las geometrías de las celdas (tabla puntos_expand) efectuamos un JOIN espacial. Este JOIN espacial nos permitirá, junto con la función de agregado count y con la cláusula GROUP BY, proceder al recuento de cuántas fotos hay en cada celda.

Lo último será representar el resultado de la consulta asignando, desde OpenJUMP, colores únicos para cada valor del recuento tal y como se aprecia en la siguiente imagen:

Opcional.

La consulta anterior devuelve 57 valores distintos para la columna ‘n’. Esto significa que debemos asignar una paleta de 57 colores para poder representar gráficamente el resultado de la consulta. Como te puedes imaginar no se trata de una tarea fácil por lo que vamos a ver cómo podemos simplificar la representación agrupando los valores de ‘n’. Es decir, vamos a establecer rangos de valores (0-5, 5-10, 10-30, 30-60, etc) para poder utilizar una paleta mucho más sencilla.

En esta ocasión vamos a utilizar cláusula CASE que incluye PostgreSQL y cuya sintaxis es:

CASE WHEN condicion THEN resultado
[WHEN condicion THEN resultado] …
[ELSE resultado] END

Esta cláusula nos permite asignar distintos valores a una misma columna en función de expresiones condicionales. El valor asignado será el que cumpla la primera condición.

Para el caso que nos ocupa vamos a crear una nueva columna ‘rango’ dependiente de los valores de la columna ‘count(*)’. Concretamente vamos a definir los siguientes rangos de valores: 0-5, 6-10, 11-40, 41-80, 81-120, 121-Infinito

CASE
WHEN count(*)<6 THEN 1
WHEN count(*)<11 THEN 2
WHEN count(*)<41 THEN 3
WHEN count(*)<81 THEN 4
WHEN count(*)<121 THEN 5
ELSE 6
END AS rango

Introduciendo el código anterior en nuestra consulta, obtenemos:

SELECT CASE
WHEN count(*)<6 THEN 1
WHEN count(*)<11 THEN 2
WHEN count(*)<41 THEN 3
WHEN count(*)<81 THEN 4
WHEN count(*)<121 THEN 5
ELSE 6
END AS rango, count(*) AS n, puntos_expand.geom
FROM fotos_barcelona, puntos_expand
WHERE St_Within(st_snaptogrid(fotos_Barcelona.geom,0,0,0.003, 0.003), puntos_expand.geom)
GROUP BY puntos_Expand.geom
ORDER BY count(*) DESC;