15 Oct 2013 Herramientas TIG para el análisis del comportamiento de turistas en la ciudad de Girona

SIGTE

Este post expone la metodología llevada a cabo en el marco de un estudio que pretende analizar el comportamiento de turistas en la ciudad de Girona. El objetivo del estudio es conocer las calles más transitadas y las zonas más visitadas de la ciudad por parte de los turístas que a diario recorren la ciudad. Para llevar a cabo este estudio se ha recogido, gracias a la colaboración de los turistas, más de 1000 tracks de GPS. Estos ficheros (en formato .gpx) contienen, con cierto margen de error, toda la información del itinerario que han realizado los visitantes de la ciudad (recorrido, tiempo impartido a lo largo del recorrido, etc) y son el punto de partida de este proyecto.

Un track es una relación ordenada de puntos, donde para cada punto, se conocen sus coordenadas (latitud y longitud) y la fecha (y hora) en que fue tomada cada coordenada.

La siguiente imagen muestra un fragmento del fichero track en formato gpx. A la izquierda se muestra el contenido del fichero track i a la derecha la representación gráfica del track sobre el callejero de la ciudad de Girona.

HERRAMIENTAS

Tras evaluar varias herramientas de trabajo, tanto herramientas privativas como de código abierto, se ha optado por importar los ficheros en formato .gpx a una base de datos PostgreSQL/PostGIS y utilizar las herramientas espaciales de PostGIS para desgranar, a partir de sentencias SQL, la información contenida en esos ficheros gpx.

METODOLOGÍA

El principal desafío del proyecto consiste, en descomponer cada track en una relación de calles (o arcos de calle) por las que se desliza cada track. De este modo se podrá conocer los tramos de las calles por las que pasa cada track y el tiempo invertido en cada uno de esos tramos.

La zona objeto de estudio se localiza en el casco antiguo de la ciudad de Girona. Un casco antiguo, como no, caracterizado por sus calles estrechas y ombrías que inducen a una mala cobertura satelital. Por este motivo, una vez importados los tracks a la base de datos se procederá a la asignación de cada punto del track a su calle más cercana mediante una operación de snap y una tolerancia de 7 metros.

La siguiente imagen muestra los puntos de track y las calles asignadas a cada punto.

Los pasos seguidos en este proyecto son los que se detallan a continuación:

1. Importación y reproyección de los fichero .gpx (track GPS) a coordenadas proyectadas.

2. Importación de un callejero a la base de datos

3. Asignación de los puntos de track sobre el callejero.

4. Presentación de los resultados.

1.- Importación de los ficheros .gpx (track GPS) a la base de datos

Para la importación de los ficheros .gpx se ha utilizado la herramienta ogr2ogr. Desde una consola de comandos tecleamos el siguiente comando para importar el fichero track.gpx a la tabla track dentro de la base de datos TURISMO.

ogr2ogr -f «PostgreSQL» PG:»host=localhost user=postgres port=5433 dbname=TURISMO schemas=originals password=contraseña» track.gpx -overwrite -lco GEOMETRY_NAME=geom track_points -nln «track»

Este comando, utiliza el usuario postgres y su contraseña para conectarse a la base de datos TURISMO. Una vez conectado genera una nueva tabla track con toda la información contenida en el fichero track.gpx.

La imagen anterior muestra un framento del contenido de la tabla track tras su importación. El campo gid actúa como clave primaria y sirve para identificar inequívocamente cada punto de la tabla. Este atributo será utilizando posteriormente en nuevas sentencias SQL. La geometría de los puntos se encuentra en la columna geom mientras que la columna time contiene tanto la fecha como la hora en que se tomó cada punto del track.

La importación masiva de los más de 1000 ficheros gpx se lleva a cabo mediante un fichero de comandos (.bat). Este fichero de comandos se genera a partir de un script que lee el contenido de una carpeta (donde se encuentran los tracks) y genera una sentencia de importación para cada fichero .gpx.

Una vez importado cada track es necesario reproyectarlo de coordenadas geográficas (GPS) a coordenadas proyectadas (UTM). Este paso es obligado para poder realizar una posterior operación de Snap indicando una tolerancia en unidades métricas (propias de las coordenadas proyectadas).

Con la siguiente sentencia SQL se crea una nueva tabla con todas las columnas iniciales, más el contenido de la geometría reproyectado al sistema de referencia ETRS89 (srid = 25831)

create table track_reproyectado as

select st_transform(geom,25831) as geom_reproyectada, track.* from track;

2.- Importación de un callejero a la base de datos

Para llevar a cabo este ejercicio es necesario importar a la base de datos un callejero de la zona de estudio. La característica principal de este callejero es que, todas y cada una de las calles que lo componen, estan descompuestas en sus distintos tramos. Cada tramo de cada calle se representa por una entidad espacial propia. Es decir, una misma calle se descompone en tantos tramos como intersecciones tenga con otras calles. Este tipo de callejero permite detectar por qué tramo de una calle pasa cada track, pudiendo discriminar los tramos de la misma calle por los que no pasa.

La siguiente imagen muestra, en rojo, los puntos de intersección entre calles. Los distintos tramos de una misma calle aparecen en un mismo color.

Partiendo de un callejero de este tipo, en formato shp, se ha utilizado la herramienta shp2pgsql (incluida en PostGIS) para importarlo a la base de datos.

Desde una consola de comandos ejecutamos:

Shp2pgsql callejero_shape tabla_callejero > callejero.sql

Este comando generará el fichero callejero.sql con el contenido del fichero .shp traducido a expresiones SQL. El siguiente paso consiste en ejecutar el fichero callejero.sql dentro de la base de datos ‘TURISMO’. Para ejecutar el fichero podemos hacerlo desde pgAdmin o bien, desde una consola de comandos, utilizar el cliente interactivo de linea de comandos de PostgreSQL llamado psql tal y como se muestra a continuación.

psql -d TURISMO -U postgres -W -f callejero.sql

Este comando solicitará la contraseña del usuario (-U) correspondiente.

3.- Asignación de los puntos de track sobre el callejero

Cada punto localizado en el track del GPS debe ser asignado a la calle más próxima. De este modo podremos generar un listado con los nombres de las calles por los que se desplaza el track. Únicamente se han asignado los puntos de track que se encuentran a un máximo de 7 metros de alguna calle. Es resto de puntos ha sido descartado.

Para asignar cada punto del track a una calle se ha ejecutado el siguiente comando SQL inspirado en el post de Paul Ramsey (http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html):

create table track_callejero as

select distinct on (punto_id) c.gid as calle_id, tp.gid as punto_id

from track_reproyectado tp

inner join callejero c on st_DWithin(tp.geom, c.geom, 7.0)

order by tp.gid, st_Distance(c.geom, tp.geom);

Este comando calcula, para cada punto del track, qué calles están a menos de 7 metros. Si un punto del track tiene más de una calle a menos de 7m, entonces las ordena para que la calle más cercana aparezca en primer lugar. Finalmente, la cláusula distinct(punto_id) indica que solo queremos obtener una calle para cada punto. El hecho de haber ordenado previamente las calles hace que cada punto del track queda asignado, únicamente, a su calle más cercana.

El resultado de la sentencia anterior es una nueva tabla track_callejero que relaciona cada punto del track con la calle más próxima. La relación entre puntos y calles se realiza a partir de los atributos alfanuméricos (gid) que identifican cada punto y cada calle. Estos atributos ‘gid‘ se renombran, respectivamente, a punto_id y calle_id para facilitar su interpretación.

Las calles del listado anterior que recogen menos de 5 puntos han sido obviadas del resultado final. De este modo obviamos algunos tramos poco representativos del itinerario seguido por el track.

4.- Presentación de los resultados

Después de obtener las calles por las que pasa el track podemos también averiguar el orden en el que se han visitado las calles y cuanto tiempo se ha invertido en cada calle.

Para extraer esta información es necesario crear una función PL/PSQL denominada ‘itinerario‘. El resultado devuelto por esta función es una estructura de datos en forma de array. Cada elemento del array contiene tres variables: calle_id, punto_id y contador. Siendo contador la variable utilizada para contar cuántas veces un mismo track cruza por la misma calle.

CREATE OR REPLACE FUNCTION itinerario(_tbl character varying, _col1 character varying, _col2 character varying)

RETURNS SETOF integer[] AS $$

DECLARE

ultima_calle integer;

contador integer;

fila RECORD;

r boolean;

BEGIN

FOR fila IN EXECUTE ‘SELECT ‘ || quote_ident(_col1) || ‘ as calle_id, ‘ || quote_ident(_col2) || ‘ as punto_id FROM ‘ || quote_ident(_tbl) || ‘ ORDER BY ‘ || quote_ident(_col2)

LOOP

IF ultima_calle IS NULL THEN

ultima_calle:= fila.calle_id;

contador:= 0;

ELSE

IF ultima_calle != fila.calle_id THEN

ultima_calle:= fila.calle_id;

contador:=contador+1;

END IF;

RETURN next ARRAY[ fila.calle_id, fila.punto_id, contador];

END LOOP;

END

$$ LANGUAGE plpgsql VOLATILE;

Como se aprecia la función itinerario recibe tres parámetros. Estos son el nombre de la tabla y los nombres de las columnas que contienen los identificadores de los puntos y de las calles. Estos tres parámetros se corresponden con la tabla track_callejero creada anteriormente.

Con la estructura de datos devuelta por la función itinerario podemos obtener una relación del primer y último punto que pasa por cada calle. Para ello ejecutamos el siguiente comando:

create table itinerario_seguido as

SELECT calle_id, MIN(punto_id) as primer_pt_id, MAX(punto_id) as ultimo_pt_id FROM (

SELECT ar[1] calle_id, ar[2] punto_id, ar[3] contador from (select itinerario(‘track_callejero’,’calle_id’,’punto_id’) ar) as fo

) as foo

GROUP BY calle_id, contador

ORDER BY contador;

Y obtenemos una nueva relación como la que se aprecia en la siguiente imagen.

Con esta nueva tabla podemos, finalmente, calcular el tiempo invertido en cada calle. Para ello utilizamos el tiempo transcurrido entre el primer y último punto de cada calle.