plsql - 两点之间的PLSQL距离
问题描述
不胜感激,我需要找到 PLSQL 中经度和纬度点之间的距离。是否有一个公式可以插入 Long 和 Lat 的变量?谢谢加夫
解决方案
很久以前,我一直在寻找用坐标计算距离的方法,它帮助很大:Oracle spatial search within distance
我的测试查询:
SELECT a.LATITUDE, a.LONGITUDE, a.distance FROM
(
SELECT i.LATITUDE, i.LONGITUDE, calc_distance(i.LATITUDE, i.LONGITUDE, b.mylat, b.mylng) AS distance
--https://www.google.com/maps/place/Parque+Nacional+da+Chapada+Diamantina/@-12.9838169,-42.6544937,8.13z/data=!4m5!3m4!1s0x74221bc4b6315bf:0x5b6f59531dd4b9bd!8m2!3d-12.5800309!4d-41.701355
FROM (SELECT -12.9838169 LATITUDE,
-42.6544937 LONGITUDE
FROM dual
) i
JOIN (
--https://www.google.com/maps/@-25.0814262,-48.7200207,10z
SELECT -25.0814262 AS mylat,
-48.7200207 AS mylng,
3.1415926 AS pi,
6371.4 earthradius
FROM DUAL
)b ON (1 = 1)
)a
我不知道它是否适合任何坐标,但在我的测试中距离是精确的。验证:https ://www.sunearthtools.com/pt/tools/distance.php
链接中的功能:
CREATE OR REPLACE FUNCTION calc_distance(
pLat1 NUMBER,
pLon1 NUMBER,
pLat2 NUMBER,
pLon2 NUMBER)
RETURN NUMBER
IS
-- r is the spherical radius of earth in Kilometers
cSpherRad CONSTANT NUMBER := 6367;
-- The spherical radius of earth in miles is 3956
a NUMBER;
vLat NUMBER;
vLat1Rad NUMBER;
vLat2Rad NUMBER;
vLon NUMBER;
vLon1Rad NUMBER;
vLon2Rad NUMBER;
BEGIN
/*
Most computers require the arguments of trigonometric functions to be
expressed in radians. To convert lon1, lat1 and lon2,lat2 from
degrees,minutes, seconds to radians, first convert them to decimal
degrees. To convert decimal degrees to radians, multiply the number
of degrees by pi/180 = 0.017453293 radians/degrees.
*/
vLat1Rad := pLat1 * 0.017453293;
vLat2Rad := pLat2 * 0.017453293;
vLon1Rad := pLon1 * 0.017453293;
vLon2Rad := pLon2 * 0.017453293;
vLon := vLon2Rad - vLon1Rad;
vLat := vLat2Rad - vLat1Rad;
a := POWER(SIN(vLat/2),2) + COS(vLat1Rad) * COS(vLat2Rad) *
POWER(SIN(vLon/2),2);
/*
The intermediate result c is the great circle distance in radians.
Inverse trigonometric functions return results expressed in radians.
To express c in decimal degrees, multiply the number of radians by
180/pi = 57.295780 degrees/radian.
The great circle distance d will be in the same units as r.
*/
RETURN ROUND(cSpherRad * 2 * ATAN2(SQRT(a), SQRT(1-a)),1);
EXCEPTION
WHEN OTHERS THEN
RETURN 999;
END calc_distance;
推荐阅读
- java - 从其他类访问方法到 Activity
- git - 无法使用 `git checkout` 命令忽略本地更改
- ruby-on-rails - 使用 Devise 创建第二个用户后,优点确实停止工作
- c++ - CMake对类的未定义引用
- html - eclipse中的http 404错误
- angular - 一个组件中的多个材料分页在 Angular 中不起作用
- machine-learning - 如何进行 ANOVA 测试以比较不同聚类算法的性能
- python - 在 python 中替换 lpc 以估计共振峰频率
- android - 添加 admob 广告库后混合版本可能会导致运行时崩溃
- logging - Fluentd 匹配标签