ejercicio: An HTML repository from artorresv

PREREQUISITOS

ubuntu > 18.04
docker > 18.06.*
osmctools: sudo apt-get install osmctools
osm2pgrouting: sudo apt-get install osm2pgrouting
psql: apt-get install postgresql-client-10

CLONAR EL REPOSITORIO

Asumiendo que este directorio existe y tiene permisos...

cd /opt/postgis/

git clone https://github.com/artorresv/ejercicio.git

Construir la aplicación

cd ejercicio/pgrouting-test

npm install

Si todo sale bien el directorio tiene un contenido similar a este:

ls -lt

drwxr-xr-x 5 atorres atorres 4096 Feb 28 05:26 public

-rw-rw-r-- 1 atorres atorres 326 Feb 28 05:25 package.json

-rw-rw-r-- 1 atorres atorres 1078 Feb 28 01:21 app.js

-rw-rw-r-- 1 atorres atorres 35749 Feb 27 22:09 package-lock.json

drwxr-xr-x 2 atorres atorres 4096 Feb 27 21:38 routes

drwxr-xr-x 2 atorres atorres 4096 Feb 27 20:54 bin

drwxr-xr-x 2 atorres atorres 4096 Feb 27 20:54 views

INSTALAR POSTGRES/POSTGIS/PGROUTING

Cambiar al directorio con los datos de prueba:

cd /opt/postgis/ejercicio/datos

(La imagen de docker es esta: https://github.com/Starefossen/docker-pgrouting/)

Instalar el contenedor:

docker run --name pgrouting-test --rm -d -P -p 5432:5432 -e POSTGRES_PASSWORD=123456 -d starefossen/pgrouting

Verificar que el contenedor funciona:

docker image ls

REPOSITORY TAG IMAGE ID CREATED SIZE

starefossen/pgrouting latest 780a35ec8668 13 days ago 638MB

...

Para revisar las versiones instaladas:

docker image inspect 780a35ec8668

...

"PG_MAJOR=10",

"PG_VERSION=10.7-1.pgdg90+1",

"PGDATA=/var/lib/postgresql/data",

"POSTGIS_MAJOR=2.5",

"POSTGIS_VERSION=2.5.1+dfsg-1.pgdg90+1",

"PGROUTING_MAJOR=2.5",

"PGROUTING_VERSION=2.5.2"

...

A partir de este punto la base de datos se puede administrar con pgAdmin, con psql desde ubuntu, o bien con psql desde dentro del contenedor:

docker run -it --link pgrouting-test:postgres --rm postgres \ sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

PREPARAR DATOS DE CALLES DE OSM

Descargar OSM para México, la última versión está actualizada al 2019-02-26T21:14:03Z:

wget "https://download.geofabrik.de/north-america/mexico-latest.osm.bz2"

Los datos se descomprimen en formato osm (xml), en el archivo "mexico-latest.osm":

bzip2 -dk mexico-latest.osm.bz2

Limpiar etiquetas no utlizadas y recortar al área de la CDMX:

osmconvert mexico-latest.osm --drop-author --drop-version --out-osm --complete-ways -b=-99.3573,19.1322,-98.9433,19.5927 -o=mexico-latest-cdmx.osm

Crear base de datos para los datos de OSM y de reportes del C5:

psql -h localhost -p 5432 -U postgres -c 'CREATE DATABASE calles_cdmx_osm'

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -c 'CREATE EXTENSION postgis'

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -c 'CREATE EXTENSION pgRouting'

Publicar funciones auxiliares...

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -a -f wrk_dijkstra.sql

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -a -f wrk_fromAtoB.sql

Publicar en "calles_cdmx_osm" los datos de OSM para la CDMX:

osm2pgrouting -f mexico-latest-cdmx.osm -c /usr/share/osm2pgrouting/mapconfig.xml -d calles_cdmx_osm -U postgres --clean

Analizar red de calles para completar información sobre conectividad de los nodos:

psql -h localhost -p 5432 -U postgres -d calles_cdmx_osm -c "SELECT pgr_analyzeGraph('ways',0.000001,'the_geom','gid');"

El resultado debería ser este:

NOTICE:  PROCESSING:
NOTICE:  pgr_analyzeGraph('ways',1e-06,'the_geom','gid','source','target','true')
NOTICE:  Performing checks, please wait ...
NOTICE:  Analyzing for dead ends. Please wait...
NOTICE:  Analyzing for gaps. Please wait...
NOTICE:  Analyzing for isolated edges. Please wait...
NOTICE:  Analyzing for ring geometries. Please wait...
NOTICE:  Analyzing for intersections. Please wait...
NOTICE:              ANALYSIS RESULTS FOR SELECTED EDGES:
NOTICE:                    Isolated segments: 1443
NOTICE:                            Dead ends: 34068
NOTICE:  Potential gaps found near dead ends: 118
NOTICE:               Intersections detected: 4824
NOTICE:                      Ring geometries: 402
 pgr_analyzegraph
------------------
 OK
(1 row)

PREPARAR LOS DATOS DE REPORTES DEL C5

Descarga los datos originales:

wget -O siniestros.all.csv "https://datos.cdmx.gob.mx/explore/dataset/reportes-de-siniestros-viales-c5/download/?format=csv&timezone=America/Mexico_City&use_labels_for_header=true"

Quita la primer fila (los headers):

tail -n +2 siniestros.all.csv > siniestros.csv

Crear la tabla:

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -a -f create.siniestrosviales.sql

Copia los registros del csv a la tabla siniestrosviales:

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres --command "\copy public.siniestrosviales FROM 'siniestros.csv' DELIMITER ';' CSV ENCODING 'UTF8';"

Agrega primary key:

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -a -f create.primarykey.sql

Agregar campo de geometría:

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -c 'ALTER TABLE public.siniestrosviales ADD COLUMN the_geom geometry(Point,4326);'

Genera la geometría con las columnas correspondientes:

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -c "UPDATE public.siniestrosviales SET the_geom = ST_GeomFromText('POINT(' || longitud || ' ' || latitud || ')', 4326);"

Crea el índice espacial:

psql -h localhost -d calles_cdmx_osm -p 5432 -U postgres -c 'CREATE INDEX siniestrosviales_geom_idx ON public.siniestrosviales USING gist (the_geom) TABLESPACE pg_default;'

Respaldar la base de datos:

pg_dump -h localhost -d calles_cdmx_osm -p 5432 -U postgres -W -F t > siniestrosviales.db.tar

APLICACION WEB

Iniciar la aplicación:

Ir al directorio de la aplicación:

cd /opt/postgis/ejercicio/pgrouting-test

Iniciar el servicio:

DEBUG=myapp:* npm start

Si todo sale bien debe salir un aviso como este:

> pgrouting-test@0.0.0 start /opt/postgis/ejercicio/pgrouting-test

> node ./bin/www

Ahora entrar a:

http://localhost:3000/pgrouting-test.html

(Se puede sustituir 'localhost' por la ip, pero hay que cambiar también las ocurrencias de 'localhost' en ./public/pgrouting-test.html)

Uso de la aplicación:

Hacer click en dos ubicaciones distintas (de preferencia no muy retiradas).

Se observaran dos puntos pequeños, con borde rojo y centro blanco:

Después de algunos segundos se dibuja una línea en color rojo que va del punto incial al punto final:

Esta es la ruta más corta para ir en vehículo desde el punto incial al punto final. Ver los detalles en:

(Línea 20) /opt/postgis/ejercicio/pgrouting-test/routes/postgis.js
/opt/postgis/ejercicio/datos/wrk_fromAtoB.sql

El algoritmo para determinar la ruta y en general todos los detalles se pueden consultar en:

http://docs.pgrouting.org/2.6/en/pgr_dijkstra.html

Unos segundos después de que aparezca la ruta, se mostrarán todos los incidentes reportados al C5, en un radio de 50 metros respecto al trazo de la ruta:

Los puntos se agrupan en clusters siempre que la distancia entre ellos sea menor a 20 pixeles. Ver detalle en:

(Línea 83) /opt/postgis/ejercicio/pgrouting-test/public/pgrouting-test.html

Para examinar el código que accede a la base de datos ver:

(Línea 57, Col. 70) /opt/postgis/ejercicio/pgrouting-test/routes/postgis.js

Hacer zoom en el mapa para ver los puntos de los incidentes más desagrupados:

Para elegir dos nuevos puntos de origen/destino hacer click en el botón 'clear':

Volver a dar click en dos ubicaciones distintas

COMENTARIOS FINALES

Los datos para calcular las rutas óptimas se tomaron del proyecto OpenStreetMap para México, son la última versión disponible
Los datos, tanto de OpenStreetMap como del C5 no están "limpios", en general tienen errores e inconsistencias que habría que arreglar para una aplicación real
Naturalmente, la consulta que la base de datos del C5 se puede personalizar tanto como se quiera, por ejemplo para filtrar los incidentes por fecha, hora, tipo de evento, etc.
Del mismo modo, la ruta óptima que cálcula la base de datos de calles es solo una de las opciones disponibles, en general:
- Es posible determinar la ruta óptima para un trayecto en vehículo, caminando, etc.
- El criterio para determinar la ruta puede ser el tiempo, la distancia o cualquier otra medida de "costo"
En general la aplicación y los servicios de datos no están optimizados, hay varias cosas que se pueden hacer para mejorar el rendimiento