distance between two longitude and latitude

Question

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

Answer 1

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;

Answer 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.

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

Answer 3

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

Answer 4

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

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