Finding distances on the surface of the earth means using Great Circle distances, worked out with the Haversine formula, also called the Spherical Cosine Law formula.
The problem is this: Given a table of locations with latitudes and longitudes, which of those locations are nearest to a given location?
I have the following query:
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 ...
How to use earthdistance extension to change my complicated formula in the query?
Is it equivalent change?
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 ...
You can get the most out of earthdistance with the following queries:
Locations close enough (i.e. within 1000000.0 meters -- 621.371192 miles) to (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 miles
Top 5 locations closest to (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;
To use indexes, apply the following one to your table:
create index idx_doorbots_latlong
on doorbots using gist (earth_box(ll_to_earth(latitude, longitude), 0));
Use index for: locations close enough (i.e. within 1000000.0 meters -- 621.371192 miles) to (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 index for: top 5 locations closest to (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;