Oracle代码计算点/质心/多边形之间的距离 [英] Oracle code to calculate distance between point/centroids/polygons

问题描述

我为客户A和客户B建立了质心.现在，我需要使用质心将客户A和客户B之间的距离计算为英里.如何在Oracle中做到这一点?

I've centroids for customer A and customer B. Now I need to calculate distance between customer A and B into miles using centroids. How can I do it in Oracle?

现在，我正在使用alteryx距离"空间工具进行质心距离计算，但需要将其转换为oracle查询.

Right now, I'm using alteryx "distance" spatial tool for centroid distance calculation but need to convert it into oracle query.

谢谢！

推荐答案

如果您具有笛卡尔坐标，则由马修已经提供的勾股定理给出的近似距离.

If you have Cartesian coordinates then the approximate distance if given by the Pythagorean theorem as already provided by Matthew.

对于纬度/经度值，应使用 SDO_GEOM.SDO_DISTANCE 函数(如果可用).

For Lat/Lon values you should use the Oracle build in SDO_GEOM.SDO_DISTANCE function, if available.

如果您的Oracle数据库未安装Oracle Spatial(需要额外付费)，则可以使用 Haversine公式来获取近似距离:

If your Oracle DB does not have Oracle Spatial installed (it cost extra) then you can use the Haversine formula to get approximate distance like this:

CREATE OR REPLACE FUNCTION p2p_distance(
        p_latitude1 NUMBER,
        p_longitude1 NUMBER,
        p_latitude2 NUMBER,
        p_longitude2 NUMBER) 
    RETURN NUMBER DETERMINISTIC IS

    earth_radius NUMBER := 6371;
    pi NUMBER := ACOS(-1)/180; 

    lat_delta NUMBER;
    lon_delta NUMBER;
    arc NUMBER;

BEGIN

    lat_delta := (p_latitude2-p_latitude1)*pi;
    lon_delta := (p_longitude2-p_longitude1)*pi;
    arc := SIN(lat_delta/2) * SIN(lat_delta/2) + SIN(lon_delta/2) * SIN(lon_delta/2) * COS(p_latitude1*pi) * COS(p_latitude2*pi);
    return earth_radius * 2 * atan2(sqrt(arc), sqrt(1-arc));
END;

结果以公里为单位，如果您想获得里程，请用英里值代替earth_radius.

Result is given in kilometers, if you like to get miles then replace earth_radius by according value in miles.

积分: https://connor-mcdonald.com/2017/01/17/haversine-plsql/

这篇关于Oracle代码计算点/质心/多边形之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！