Distancias, coordenadas, SQL y Coldfusion
Si han construido aplicaciones para rastrear personas, ubicaciones, vehiculos, envios o similares, saben que es fundamental lograr calcular la distancia entre 2 puntos. Ya hay una mejor forma de hacerlo que la típica "Harvesine".
La formula Harvesine siempre se uso para conectar dos puntos, pero admitamoslo, requiere mucho cálculo en un momento donde requieres respuestas rápidas.
https://en.wikipedia.org/wiki/Haversine_formula
Durante mucho tiempo esta formula se uso directamente desde SQL o como función en cualquier lenguaje de aplicaciones para calcular la distancia entre dos coordenadas con Latitud y Longitud. Es bastante precisa porque considera la curvatura de la tierra, y es por ella que sabemos que una linea recta no siempre es la distancia más corta.
Pero ya hay una mejor forma de hacerlo. Una que además permite indexación y con ello generar resultados espectacularmente rápidos, precisos y fáciles, y todo directo con SQL. Usaremos MSSQL con Coldfusion para este ejemplo.
Lo primero... Dejemos de guardar coordenadas como valores en un Json (varchar){"lat": xxx, "lng" : yyy}, no es la forma más eficiente. Para esto tenemos un nuevo tipo de datos llamado GEOGRAPHY.
CREATE TABLE [dbo].[sites](
[siteId] [int] identity(1,1) NOT NULL,
[siteName] [char](50) NOT NULL,
[location] [geography] NULL) ON [PRIMARY]
ALTER TABLE [dbo].[sites] ADD CONSTRAINT [PK__sites] PRIMARY KEY CLUSTERED
( [siteId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE SPATIAL INDEX [SpatialIndex1] ON [dbo].[sites]
( [location] ) USING GEOGRAPHY_AUTO_GRID
WITH ( CELLS_PER_OBJECT = 12, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
ejemplo de tabla "sites" con indices
Ahora viene lo divertido...
1) ¿Cómo insertamos las Coordenadas?
<cfset loc = {"lat" : 19.24, "lng" : -99.09} <!--- CDMX --->
<cfquery name="actualiza">
update sites
set location =
geography::STGeomFromText('POINT(#loc.lng# #loc.lat#)', 4326)
where siteId = 1
</cfquery>
ejemplo de como actualizar las coordenadas para el sitio 1
2) ¿Cómo recuperamos las Coordenadas?
<cfquery name="local.qry">
select *, convert(varchar(max),location) as Coordinates
from sites
where siteID = 1
</cfquery> ejemplo de como recuperar las coordenadas para el sitio 1
Nota que aunque en la base de datos se guarda algo "encriptado" como:
"0xE6100000010C3D0AD7A3703D3340F6285C8FC2C558C0"
Al recuperar con el SQL, se convierte a un formato WKT (Well Known Text)
"POINT (-99.09 19.24)", por lo que recomendamos aplicar alguna funcion para convertirlo de vuelta a un JSON tipo {"lat" : 19.24, "lng" : -99.09}
<cffunction name="parseGEOPoint">
<cfargument name="gPoint" type="string">
<cfset local.str = trim(listfirst(listlast(gPoint,'('),')'))>
<cfset local.item =
{"lng" : listfirst(local.str,' '),
"lat" : listlast(local.str,' ')}>
<cfreturn local.item>
</cffunction>ejemplo de como convertir de WKT a JSON
Yo se que esto parece mas trabajo que aplicar una simple formula de Harvesine a los campos de lat, lng directamente, pero veran que cuando hacen consultas directas es mucho mas rápido, preciso y fácil.
Por ejemplo, Si quisieran ver la distancia entre dos puntos, suponiendo entre todos usuarios y un sitio especifico (1), el SQL se vuelve algo tan sencillo como:
<cfquery name="local.qry">
SELECT s.geoHome.STDistance(u.location) AS DistanceInMeters FROM Users u, sites s
WHERE s.site = 1
</cfquery>ejemplo de como obtener las distancias
Y esto es solo el comienzo... Con el tipo de dato de "Geografía" pueden hacer:
- Polígonos para definir zonas (GeoFences)
- Detectar si un punto esta dentro o fuera de una zona
- Buscar por cercanía
y mucho más...
Para aquellos curiosos que no estén familiarizados, les anexo ejemplos (generados por AI, de cómo era antes el cálculo manual con Harvesine)...
// Function to calculate the Haversine distance between two coordinates
function calculateHaversineDistance(lat1, lon1, lat2, lon2)
{ // Radius of the Earth in kilometers earthRadius = 6371;
// Convert latitude and longitude from degrees to radians
lat1Rad = DE2RA(lat1);
lon1Rad = DE2RA(lon1);
lat2Rad = DE2RA(lat2);
lon2Rad = DE2RA(lon2);
// Differences in latitude and longitude
dLat = lat2Rad - lat1Rad;
dLon = lon2Rad - lon1Rad;
// Haversine formula
a = SIN(dLat / 2) * SIN(dLat / 2) + COS(lat1Rad) * COS(lat2Rad) * SIN(dLon / 2) * SIN(dLon / 2);
c = 2 * ATN(SQRT(a) / SQRT(1 - a));
// Calculate the distance
distance = earthRadius * c;
return distance;
}
// Example coordinates (latitude, longitude)
lat1 = 34.0522; // Los Angeles, CA
lon1 = -118.2437;
lat2 = 40.7128; // New York, NY
lon2 = -74.0060;
// Calculate the distance
distanceInKm = calculateHaversineDistance(lat1, lon1, lat2, lon2);
distanceInMiles = distanceInKm * 0.621371; // Convert to miles
// Output the results
WriteOutput("Distance between Los Angeles and New York:");
WriteOutput("<br>Distance in kilometers: " & round(distanceInKm,2));
WriteOutput("<br>Distance in miles: " & round(distanceInMiles,2));
Función para calcular distnacia entre puntos usando Harvesine con Coldfusion
SELECT u.UserName, s.SiteName, (6371 * 2 * ASIN(SQRT( SIN(RADIANS(s.Latitude - u.Latitude) / 2) * SIN(RADIANS(s.Latitude - u.Latitude) / 2) + COS(RADIANS(u.Latitude)) * COS(RADIANS(s.Latitude)) * SIN(RADIANS(s.Longitude - u.Longitude) / 2) * SIN(RADIANS(s.Longitude - u.Longitude) / 2) ))) AS DistanceInKm FROM Users u CROSS JOIN Sites s;
Función para calcular distnacia entre puntos usando Harvesine con MSSQL
¿Qué les pareció? En lo personal, ya estoy cambiando todas mis viejas practicas de guardar latitude y longitud por separado y hacer calculos manuales con Harvesine por funciones y validaciones integradas dentro de SQL con geography.