在地球表面上寻找距离意味着使用大圆距离,用Haversine 公式计算出来,也称为球面余弦定律公式。
问题是这样的:给定一个带有纬度和经度的位置表,这些位置中哪些位置离给定位置最近?
我有以下查询:
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 ...
如何使用earthdistance
扩展来更改查询中的复杂公式?
是等价的变化吗?
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 ...
您可以通过earthdistance
以下查询充分利用:
位置足够近(即在 1000000.0 米内 - 621.371192 英里)到 (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
最接近 (34.0480698, -118.3589196) 的前 5 个位置:
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;
要使用索引,请将以下内容应用于您的表:
create index idx_doorbots_latlong
on doorbots using gist (earth_box(ll_to_earth(latitude, longitude), 0));
使用索引: 位置足够接近(即在 1000000.0 米内 - 621.371192 英里)到 (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;
使用索引:最接近 (34.0480698, -118.3589196) 的前 5 个位置:
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句