Geolocalización en SQL Server, una imagen vale más que mil palabras

Convertir información de valor en componentes visuales

En un mundo hiperconectado y en el que tenemos tantísima información al alcance de la mano, necesitamos utilizar eficientemente herramientas que nos permitan convertir esa información en componentes visuales y que sean sencillos y rápidos de interpretar.

Siempre se ha dicho, y con razón, que una imagen vale más que mil palabras, así que si fuésemos capaces de convertir esa información geográfica textual (ciudades, códigos postales, países, …) en un mapa, la información sería mucho más completa. Si además pudiéramos cruzarla con información numérica calculada como, por ejemplo, el volumen de ventas o el número de pedidos, los resultados serían sencillamente espectaculares.

A continuación, podemos ver un ejemplo en detalle, utilizando para ello una base de datos de mapas con las coordenadas de latitud y longitud de la mayor parte de aeropuertos del mundo de la que dispongo en mi SQL Server.

La siguiente consulta devuelve todos los aeropuertos en Estados Unidos:

select * from airports where geom.STWithin
((select geom from world where name='United States'))=1

El detalle interesante es que, además de ver los resultados en el formato tradicional, una pestaña extra permite visualizar la información procedente de campos geográficos y geométricos en formato espacial.

La función STWithin

La función STWithin utiliza en la consulta anterior permite evaluar si un punto está contenido dentro de un polígono (o una colección de ellos) de modo que en la captura anterior es perfectamente visible el mapa de Estados Unidos incluídos Hawai y Alaska.

El uso de funciones sobre campos espaciales permite poder calcular áreas, distancias, perímetros, intersecciones y una larga lista de operaciones frecuentes que pueden resultar de una tremenda utilidad.

Para, por ejemplo, calcular distancias podríamos utilizar el siguiente escenario:

Disponemos de una tabla de localizaciones en la que tenemos almacenado el punto en el que se encuentran un conjunto de usuarios y queremos calcular cuál es el aeropuerto más cercano a cada uno de ellos.

• Empezamos creamos la tabla:

create table loc (locname varchar(50), geom geometry)

En el uso de un sistema de coordenadas 2D el tipo de datos geometry es el más adecuado mientras que para un sistema 3D resulta más preciso el tipo geography. En mi caso no dispongo de la información en tridimensional así que (aún sacrificando precisión en los cálculos) utilizaré el tipo geometry.

• Una vez creada la tabla insertaré unos pocos datos de ejemplo:

insert into loc values ('Inca', geometry::STGeomFromText('POINT(2.91 39.72)', 0))
insert into loc values ('Carmona', geometry::STGeomFromText('POINT(-5.64 37.47)', 0))
insert into loc values ('Borobudur', geometry::STGeomFromText('POINT(110.2 -7.61)', 0))
insert into loc values ('San Antonio', geometry::STGeomFromText('POINT(-98.48 29.26)', 0))
insert into loc values ('Zarautz', geometry::STGeomFromText('POINT(-2.17 43.28)', 0))

Los nombres de las ubicaciones nos servirán para poder comprobar que el resultado es el esperado mientras que la función STGeomFromText nos permite convertir un punto con sus coordenadas longitud y latitud en un tipo de datos espacial (el valor 0 indica la ausencia de SRID en nuestro sistema de coordenadas).

• Para terminar con los pre-requisitos crearemos una función que devuelva el aeropuerto más cercano a partir de un campo geometry

create function NearestAirport(@loc geometry) returns varchar(50) as
begin
return (select top 1 name from dbo.airports where [use] in (22,49)
order by geom.STDistance(@loc) )
       -- omitir aeropuertos de uso no civil
end

Para comprobar los resultados solo hay que escribir una consulta que invoque a nuestra función.

select locname, dbo.nearestairport(geom) as Aeropuerto_mas_proximo from loc

En el caso de que queramos integrar esos datos en una aplicación a medida, podemos utilizar algunas de las API de mapas (Google Maps o Bing) para mostrarlas en la web o integrar los mapas en nuestros informes con Reporting Services.

El resultado final vale la pena, ¿verdad?, por cierto, si estás interesado, puedes encontrar mapas similares aquí.