distance between two longitude and latitude

问题:

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

• a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
• b happens to equal the maximum value in Northern Latitude (LAT_N in STATION)
• c happens to equal the minimum value in Western Longitude (LONG_W in STATION)
• d happens to equal the maximum value in Western Longitude (LONG_W in STATION)

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

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

this is my try but returns 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

回答1:

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.

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

回答2:

Manhattan Distance (Taxicab Geometry)

Manhattan Distance between points P1(a,b) and 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;

回答3:

You can use this maths formula to get the straight distance between two points:

Distance = squarerootof((x2−x1)^2+(y2−y1)^2) 

回答4:

For SQL server you can use the following query:

SELECT convert(decimal(10,4),ABS(MIN(LAT_N)-MAX(LAT_N)) + ABS(MIN(LONG_W)-MAX(LONG_W))) FROM STATION