Geocoding reverso de comunas/regiones con SQL Server

Un problema típico cuando estás creando un backend es determinar en qué comuna o región está un punto (latitud/longitud). Esto te puede servir por ejemplo para segmentar notificaciones para tus usuarios de acuerdo a su ubicación.

Pensemos que tenemos una base de datos SQL Server en la cual tenemos tabla User con campos Latitud y Longitud. ¿De dónde obtendremos la comuna o región de estas coordenadas?

Por suerte la Biblioteca del Congreso Nacional tiene una página con una serie de shapefiles, que contienen los polígonos o límites de cada comuna. El formato shapefile es un estándar para los software de información geográfica (GIS), y la gracia es que los puedes visualizar con una serie de programas gratuitos. El más sencillo es QGis, con el cual puedes visualizar rápidamente los shapes y añadir otras capas, por ejemplo OpenStreetMap, para tener las calles y otros elementos.

Para este caso, bajamos el shapefile de la división comunal. Al descomprimirlo y abrirlo con QGis, obtendremos algo como lo siguiente:

Perfecto! Con esto podremos determinar a qué comuna corresponde un punto determinado. Y ahora, ¿cómo cargamos esto en SQL Server?

Preparando el archivo

Acá es donde se pone un poco más complicado. Los elementos (polígonos, puntos) de un shapefile existen en un plano que tiene un sistema de referencia, como las coordenadas X e Y en los planos de geometría del liceo. El problema es que existen muchos sistemas de referencia. Uno de ellos es el WGS84, que para estos fines se llama EPSG:4326. Hay una lista larga de todos, pero para este caso lo único que nos interesa es identificar el sistema de referencia del archivo original, y convertirlo a EPSG:4326, para poder usarlo con la latitud/longitud que conocemos de los GPS.

Para esto QGis nos ayuda sin tener que hacer mucho más esfuerzo. En la parte inferior derecha de la pantalla aparece el SRID o sistema de referencia:

Ese EPSG:32719 es lo que necesitamos. Para poder convertir el shape a EPSG:4326 usaremos una herramienta que se llama ogr2ogr, es opensource y existe para Windows y Linux. Puedes descargar la versión de Windows, puedes usar Linux o en mi caso usaré WSL (Windows Subsystem for Linux) para poder instalarlo directo en mi máquina Windows.

La línea de comando es la siguiente:

ogr2ogr -f "ESRI Shapefile" -s_srs EPSG:32719 -t_srs EPSG:4326 output.shp division_comunal.shp -simplify 100

Con esto, crearemos un archivo “output.shp” que estará en el sistema de referencia que nos sirve. El “-simplify 100” al final es para que las geometrías se simplifiquen y quede un archivo más liviano.

Importación a SQL Server

Y finalmente, tenemos que cargarlo en nuestro SQL Server. Para esto usaremos una herramienta llamada SQL Server 2008 Spatial Tools, que funciona perfectamente (la estoy usando con SQL Azure).

Esto nos creará una tabla con el nombre indicado (Comunas), creando un índice espacial para que las consultas sean rápidas, y ya podremos hacer queries espaciales usando las funciones optimizadas para este fin.

Resultado

Acá un ejemplo, en el cual tenemos un historial de ubicaciones del usuario en la tabla Location, y la tabla User contiene la última ubicación.

select c.NOM_COM, c.NOM_PROV, c.NOM_REG, u.Phone, l.Latitude, l.Longitude from dbo.[User] u, dbo.Location l, dbo.Comunas c where u.LastKnowLocationID=l.ID and c.geom.STContains(geography::Point(l.Latitude, l.Longitude, 4326)) = 1