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.
El problema es este: dada una tabla de ubicaciones con latitudes y longitudes, ¿cuáles de esas ubicaciones están más cerca de una ubicación dada?
tengo la siguiente consulta:
SELECT z.id, 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 doorbots as z JOIN ( /* these are the query parameters */ SELECT 34.0480698 AS latpoint, -118.3589196 AS longpoint, 2 AS radius, 111.045 AS distance_unit ) AS p ON 1=1 WHERE z.latitude between ... and z.longitude between ... ¿Cómo usar la extensión de earthdistance para cambiar mi fórmula complicada en la consulta?
¿Es un cambio equivalente?
SELECT z.id, z.latitude, z.longitude, p.radius, round(earth_distance(ll_to_earth(p.latpoint, p.longpoint), ll_to_earth(z.latitude, z.longitude))::NUMERIC,0) AS distance FROM doorbots as z JOIN ( /* these are the query parameters */ SELECT 34.0480698 AS latpoint, -118.3589196 AS longpoint, 2 AS radius, 111.045 AS distance_unit ) AS p ON 1=1 WHERE z.latitude between ... and z.longitude between ...Puedes sacarle el máximo partido a earthdistance con las siguientes consultas:
Ubicaciones lo suficientemente cercanas (es decir, dentro de 1000000,0 metros -- 621,371192 millas) a (34,0480698, -118,3589196) :
select * from doorbots z where earth_distance(ll_to_earth(z.latitude, z.longitude), ll_to_earth(34.0480698, -118.3589196)) < 1000000.0; -- in meters select * from doorbots z where point(z.longitude, z.latitude) <@> point(-118.3589196, 34.0480698) < 621.371192; -- in milesLas 5 ubicaciones más cercanas a (34.0480698, -118.3589196) :
select * from doorbots z order by earth_distance(ll_to_earth(z.latitude, z.longitude), ll_to_earth(34.0480698, -118.3589196)) limit 5; select * from doorbots z order by point(z.longitude, z.latitude) <@> point(-118.3589196, 34.0480698) limit 5;Para usar índices, aplique el siguiente a su tabla:
create index idx_doorbots_latlong on doorbots using gist (earth_box(ll_to_earth(latitude, longitude), 0));Utilice el índice para: ubicaciones lo suficientemente cercanas (es decir, dentro de 1000000,0 metros -- 621,371192 millas) a (34,0480698, -118,3589196) :
with p as ( select 34.0480698 as latitude, -118.3589196 as longitude, 1000000.0 as max_distance_in_meters ) select z.* from p, doorbots z where earth_box(ll_to_earth(z.latitude, z.longitude), 0) <@ earth_box(ll_to_earth(p.latitude, p.longitude), p.max_distance_in_meters) and earth_distance(ll_to_earth(z.latitude, z.longitude), ll_to_earth(p.latitude, p.longitude)) < p.max_distance_in_meters;Use el índice para: las 5 ubicaciones más cercanas a (34.0480698, -118.3589196) :
select z.* from doorbots z order by earth_box(ll_to_earth(z.latitude, z.longitude), 0) <-> earth_box(ll_to_earth(34.0480698, -118.3589196), 0) limit 5;