Bit - loader

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


Netmind - Geolocalización en SQL Server, una imagen vale más que mil palabras    Artículo | Microsoft SQL Server 2016
Xavier Saladié | 10/07/18

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.

 

Resultados en formato tabular
Resultados en formato tabular

 

Resultados en formato espacial
Resultados 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

Geolocalización en SQL Server, una imagen vale más que mil palabras 2
Consulta para comprobar resultados

 

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í.

 

Autor del artículo

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

Xavier Saladié es Lead Expert en SQL Server y Desarrollo en .NET en Netmind. Es instructor acreditado por Microsoft desde el año 2005 y de ITIL desde el año 2013. Dispone de las certificaciones ITIL Expert, MCSE Cloud Platform and Infrastructure, MCSE Data Platform, MCSE Business Intelligence, MCPD Windows Developer, MCPD Web Developer, MCITP Sharepoint Administrator, MS Microsoft Project 2016 y MCSA SQL Server. Dispone de más de 20 años de experiencia en formación y ha dirigido múltiples proyectos de consultoría y optimización para arquitecturas de soluciones para varias organizaciones.

 

 

 


Xavier Saladié


Entradas relacionadas

Cursos relacionados
Nuestro sitio utiliza cookies para análisis. Si no estás seguro de ello, echa un vistazo a nuestra política de privacidad.