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';

sql-server-2008

Reputation: 393

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';

Upvotes: 0

Answers (23)

kha

Reputation: 1

For MySQL:

select round((max(LAT_N) - min(LAT_N)) + (max(LONG_W) - min(LONG_W)),4) from station

Upvotes: 0

AMEY DESHPRABHU

Reputation: 1

select round( ABS(max(LAT_N)-min(LAT_N)) +
ABS(max(LONG_W)-min(LONG_W)),4 ) FROM STATION

Upvotes: 0

Binny

Reputation: 1

This code is for MS SQL Server.

select cast(ABS(min(LAT_N) - max(LAT_N)) + 
ABS(min(LONG_W) - max(LONG_W)) as decimal (30,4))
from STATION enter code here

Upvotes: 0

Prajwal Bisht

Reputation: 1

select cast(round(abs(Min(LAT_N) - Max(LAT_N)),4) + round(abs(Min(LONG_W) - Max(LONG_W)),4) as decimal(10,4)) from station;

try this query ,it worked for me

Upvotes: 0

Jigar Dabhi

Reputation: 1

For MS SQL server logic

To find Manhattan distance between 2 points: P1(A,B) AND P2(C,D)

Formula : (C-A) + (D-B)

SELECT CAST(MAX(LAT_N) - MIN(LAT_N) + MAX(LONG_W) - MIN(LONG_W) AS DECIMAL(10,4)) 
FROM STATION

If you use the Round function, it will show you the wrong answer because the question asks for 4 decimal places only.

Upvotes: -1

Lastoption

Reputation: 1

Manhattan distance

Definition: The distance between two points measured along axes at right angles. In a plane with p1 at (x1, y1) and p2 at (x2, y2), it is |x1 - x2| + |y1 - y2|.

In the problem x1 is min(lat_n) and x2 is max(lat_n), similarly y1 will be min(long_w) and y2 will be max(long_w). Hence the query to perform this in MySQL is;

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

Upvotes: -1

reshma ramesh

Reputation: 1

-- a = min(lat_n); b = min(long_w) ; c= max(lat_n) ; d= max(long_w) -- Manhattan Distance between points P1(a,b) and P2(c,d)= |c-a|+|d-b|

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

Upvotes: 0

NoSkillzninja

Reputation: 1

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;

Upvotes: 0

Fatih

Reputation: 1

I tried this one,

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

Upvotes: 0

Monirrad

Reputation: 483

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

Upvotes: 0

Ricardo Giacomelli

Reputation: 1

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;

Upvotes: 0

Chigo Nwakanma

Reputation: 1

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;

Upvotes: 0

Mohamed

Reputation: 43

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

Upvotes: 0

Chaitanya

Reputation: 81

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;

Upvotes: 0

MerveK

Reputation: 61

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;

Upvotes: 0

Rahul Saw

Reputation: 29

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;

Upvotes: 0

Muskan Pandey

Reputation: 73

For MySQL,

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

Upvotes: 4

Rishav Singh

Reputation: 343

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;

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;

Upvotes: 0

anil

Reputation: 1

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))

Upvotes: 0

programandoconro

Reputation: 2729

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

Upvotes: 0

suresh kumar

Reputation: 25

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

Upvotes: 1

abhi

Reputation: 106

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

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

Upvotes: 0

GOU7HAM

Reputation: 13

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;

Upvotes: 0

distance between two longitude and latitude

Answers (23)

Related Questions