Dada una tabla de ubicaciones con latitudes y longitudes, ¿cuáles de esas ubicaciones son las más cercanas a una ubicación determinada?
Por supuesto, encontrar distancias en la superficie de la tierra significa usar las distancias del Gran Círculo, calculadas con la fórmula de Haversine, también llamada fórmula de la Ley del Coseno Esférico.
Tengo el siguiente código:
SELECT zip, latitude, longitude, distance FROM ( SELECT z.zip, z.latitude, z.longitude, p.radius, p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.latitude)) * COS(RADIANS(p.longpoint - z.longitude)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.latitude)))) AS distance FROM zip AS z JOIN ( /* these are the query parameters */ SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS radius, 111.045 AS distance_unit ) AS p ON 1=1 WHERE z.latitude BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit) AND z.longitude BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) ) AS d WHERE distance <= radius¿Hay alguna forma de mejorar el rendimiento de esta consulta?
¿Es necesario usar PostGIS para mejorarlo o es solo un envoltorio para mi fórmula haversine?
Esta consulta nunca será especialmente rápida. Sin embargo, hay algunas formas en que se puede mejorar.
Primero : la fórmula de Haversine no es necesaria aquí. Las correcciones que aplica sólo son necesarias cuando la curvatura de la tierra es un factor significativo, o muy cerca de los polos. Ninguno de estos es el caso aquí: la distancia más grande que debe calcularse con precisión es de 12 millas, que apenas se encuentra sobre el horizonte. En esta escala, la Tierra es efectivamente plana, por lo que el Teorema de Pitágoras es lo suficientemente bueno para calcular distancias.
Un grado de latitud es aproximadamente 69 millas, y a 52 ° N (alrededor de donde se encuentran los Países Bajos), un grado de longitud es cos (52 °) x 69 = 42,5 millas, por lo que la fórmula se convierte en:
sqrt(pow(69*(lat - $latitud), 2) + pow(42.5*(lng - $longitud), 2))
Segundo : podemos usar una "prueba de tijera" para latitud y longitud. Si un punto está a más de 12 millas en cualquier dirección cardinal desde su punto objetivo, ciertamente no puede estar dentro de un círculo de 12 millas de ese punto. Podemos usar este hecho para realizar una comparación rápida de la latitud y la longitud, omitiendo por completo el cálculo de la distancia. Usando las cifras de un grado de latitud/longitud que obtuvimos arriba, tenemos:
DONDE (lat ENTRE ($latitud - 12/69.0) Y ($latitud + 12/69.0)) Y (lng ENTRE ($longitud - 12/42.5) Y ($longitud + 12/42.5))
¡Tenga en cuenta que esto no reemplaza la verificación de distancia completa! Es simplemente un primer paso para descartar rápidamente puntos que posiblemente no puedan estar dentro del radio correcto. Con un índice en lat o lng, esto permitirá que el servidor de la base de datos evite examinar muchas de las filas de la base de datos.
Supongo que el planificador resolverá esta consulta reescribiéndola por sí mismo, pero vale la pena intentarlo. Al menos es más ordenado.
select zip, latitude, longitude, distance from ( select z.zip, z.latitude, z.longitude, p.radius, p.distance_unit * p.degrees_acos_cos_radians_latpoint * cos(radians(z.latitude)) * cos(radians(p.longpoint - z.longitude)) + p.sin_radians_latpoint * sin(radians(z.latitude)))) as distance from zip z cross join ( select latpoint, longpoint, radius, distance_unit, latpoint - radius / distance_unit as lat0, latpoint + radius / distance_unit as lat1, longpoint - radius / distance_unit * cos(radians(latpoint)) as long0, longpoint + radius / distance_unit * cos(radians(latpoint)) as long1, sin(radians(latpoint)) as sin_radians_latpoint, degrees(acos(cos(radians(latpoint)) as degrees_acos_cos_radians_latpoint from ( values (42.81, -70.81, 50.0, 111.045) ) v (latpoint, longpoint, radius, distance_unit) ) p where z.latitude between lat0 and lat1 and z.longitude between long0 and long1 ) d where distance <= radiusLas expresiones no son la parte lenta. El problema con "encontrar el más cercano" es la dificultad de usar un índice para limitar cuántas filas mirar.
Si aún no tiene estos en z , le ayudarán:
INDEX(latitude), INDEX(longitude)Si ya los tenía, asegúrese de que la subconsulta haya utilizado uno de ellos.
El próximo paso será más drástico (y más fructífero): http://mysql.rjweb.org/doc.php/latlng