'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

sql-server-2008phplaravelredisqueuelumen


Solution 1:[1]

For MySQL,

SELECT ROUND(ABS(MIN(LAT_N) - MAX(LAT_N)) + ABS(MAX(LONG_W) - MIN(LONG_W)),4) FROM STATION

Solution 2:[2]

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

Solution 3:[3]

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.

Solution 4:[4]

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;

Solution 5:[5]

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

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

Solution 6:[6]

select round(abs(min(lat_n)- max(lat_n)) + abs(min(long_w)- max(long_w)),4) from  station;

Solution 7:[7]

Declare @a float, @b float, @c float, @d float;
      SET @a =(select LAT_N from station where LAT_N = (select min(LAT_N) from station) )
      SET @b =(select LONG_W from station where LONG_W =(Select min(LONG_W) from station) )
      SET @c= (select LAT_N from station where LAT_N = (select max(LAT_N) from station) )
      SET @d= (select LONG_W from station where LONG_W = (select max(LONG_W) from station) ) 
select cast(ROUND((abs(@a -@c)+ abs(@b - @d)),4) as decimal(12,4))

Solution 8:[8]

Easiest way of doing this problem is
Using mathematics function [ Dis. = sqrt((x2?x1)^2+(y2?y1)^2) ], try:
for sql server:

select format(abs(min(LAT_N)-max(LAT_N))+abs(min(long_w)-max(long_w)),'.####') from station;

or

select format(sqrt(square(min(LAT_N)-max(LAT_N))+square(min(long_w)-max(long_w))),'.####') from station;

for SQL:

SELECT ROUND(ABS(MIN(LAT_N)-MAX(LAT_N)) + ABS(MIN(LONG_W)-MAX(LONG_W)), 4) FROM STATION;

for simplicity to understand:

select 
  round(
    abs(
      min(lat_n)- max(lat_n)
    ) + abs(
      min(long_w)- max(long_w)
    ), 4
  ) 
from 
  station;

Solution 9:[9]

Here: a=MIN(LAT_N) c=MAX(LAT_N) b=MIN(LONG_W) d=MAX(LONG_W)

#Query for obtaining Manhattan distance is:

    SELECT ROUND(ABS(MIN(LAT_N)-MAX(LAT_N))+ABS(MIN(LONG_W)-MAX(LONG_W)),4) FROM STATION;

Solution 10:[10]

You can use the code below using Oracle.

SELECT 
    ROUND(ABS(MIN(LAT_N) - MAX(LAT_N)) + ABS(MIN(LONG_W) - MAX(LONG_W)) , 4)
FROM STATION;

Solution 11:[11]

According to the question, they are asking to find Manhattan Distance but you are trying to find Euclidean distance between two points. Both of them are different

Manhattan Distance: |X1 - X2| + |Y1 - Y2|

Euclidean Distance: sqrt((X2?X1)^2+(Y2 ?Y1)^2)

select Format(ABS(MIN(LAT_N)-MAX(LAT_N)) + ABS(MIN(LONG_W)-MAX(LONG_W)),'.####') from STATION;

Solution 12:[12]

Euclidean distance between two points. Both of them are different.

Manhattan Distance:

|X1 - X2| + |Y1 - Y2|

select round((max(lat_n)-min(lat_n))+(max(long_w)-min(long_w)),4)
from station

Solution 13:[13]

For whatever reason, the compiler seems to ignore the ROUND() function and still output all the decimal places. I used the SUBSTR() function to work around this bug. So the query would look like:

select substr((ABS(MAX(LAT_N) - MIN(LAT_N)) + ABS(MAX(LONG_W) - MIN(LONG_W))), 1, 8) FROM STATION;

Solution 14:[14]

I tried this one for MYSQL:

CREATE TABLE points (
a DECIMAL(10,4),
b DECIMAL(10,4),
c DECIMAL(10,4),
d DECIMAL(10,4));

INSERT INTO points VALUE ((SELECT MIN(lat_n) FROM station),
  (SELECT MIN(long_w) FROM station),
    (SELECT MAX(lat_n) FROM station),
    (SELECT MAX(long_w) FROM station));
  

SELECT ROUND((c - a) + (d - b),4) FROM points;

Solution 15:[15]

select round(abs(max(lat_n )-min(lat_n)) + abs(max(long_w)-min(long_w)),4)
    from station

Solution 16:[16]

I tried this one,

SELECT ROUND(MAX(LAT_N) - MIN(LAT_N) + MAX(LONG_W) - MIN(LONG_W), 4) FROM STATION

Solution 17:[17]

select cast((round((max(lat_n)-min(lat_n)),4)) as decimal (10,4))+ cast(round((max(long_w)-min(long_w)),4) as decimal(10,4)) from station;

Solution 18:[18]

make sure the port is allowed on the droplet's firewall.

Related Questions