两个经度和纬度之间的距离

How should I write query for this? Consider P1(a, b) and P2(c, d) to be two points on a 2D plane.

我该怎么写这个查询?将P1(a,b)和P2(c,d)视为2D平面上的两个点。

a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).

a恰好等于北纬度的最小值(STATION中的LAT_N)。

b happens to equal the maximum value in Northern Latitude (LAT_N in STATION)

b恰好等于北纬度的最大值(STATION中的LAT_N)

c happens to equal the minimum value in Western Longitude (LONG_W in STATION)

c恰好等于西经的最小值(STATION中的LONG_W)

d happens to equal the maximum value in Western Longitude (LONG_W in STATION)

d碰巧等于西经的最大值(STATION中的LONG_W)

Query the Manhattan Distance between points P1 and P2 and round it to a scale of 4 decimal places.

查询点P1和P2之间的曼哈顿距离并将其四舍五入到小数点后4位。

Table STATION(ID number, CITY varchar2(21), STATE varchar2(2), LAT_N number, LONG_W number)

表STATION(ID号,CITY varchar2(21),STATE varchar2(2),LAT_N号,LONG_W号)

this is my try but returns NULL

这是我的尝试但返回NULL

select
    2 * 3961 * asin(power(sqrt((sin(radians(MAX(LAT_N) - MIN(LAT_N)) / 2) )) , 2 ) + cos(radians(MAX(LAT_N))) * cos(radians(MIN(LAT_N))) * power(sqrt((sin(radians(MAX(LONG_W) - MIN(LONG_W)) / 2) )) , 2 )) as distance from station where city like 'manhattan';

any idea will be appreciated

任何想法将不胜感激

3 个解决方案

#1

you can follow this maths formula to get straight distance between two points Distance =squarerootof((x2−x1)^2+(y2−y1)^2)

你可以按照这个数学公式得到两点之间的直线距离Distance = squarerootof((x2-x1)^ 2 +(y2-y1)^ 2)

#2

Instead of reinventing the wheel, you can make use of the SQL Server geography data types. These data types are present as of version 2008, and there are functions is there to do in exactly what you're trying to do without all of the math involved.

您可以使用SQL Server地理数据类型,而不是重新发明*。这些数据类型在2008版本中出现,并且在没有涉及所有数学的情况下,您正在尝试执行的操作中有一些功能。

Take a look at this example (there's far too much to include here) or look up more information on MSDN.

看一下这个例子(这里包含的内容太多了)或者在MSDN上查找更多信息。

#3

Manhattan Distance (Taxicab Geometry)

曼哈顿距离(出租车几何)

Manhattan Distance between points P1(a,b) and P2(c,d)= |a-c|+|b-d|

曼哈顿点P1(a,b)和P2(c,d)之间的距离= | a-c | + | b-d |

--a= min(LAT_N)
--b= max(LAT_N)
--c= min(LONG_W)
--d= max(LONG_w)
SELECT ROUND(ABS(MIN(LAT_N) - MIN(LONG_W)),4) + ROUND(ABS(MAX(LAT_N) - MAX(LONG_W)),4) FROM STATION;

#1