Weather Observation Station 19 | Hackerrank

Problem Statement:

Consider P1(a,b) and P2(c,d) 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 minimum value in Western Longitude (LONG_W in STATION).

•  ‘c’ happens to equal the maximum value in Northern Latitude (LAT_N in STATION).

•  ‘d’ happens to equal the maximum value in Western Longitude (LONG_W in STATION).

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

Euclidean Distance: 

Euclidean distance formula is given by:

d =√[(x2 – x1)2 + (y2 – y1)2]

Where,

“d” is the Euclidean distance

(x1, y1) is the coordinate of the first point

(x2, y2) is the coordinate of the second point.

Input Format:

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

Sample Input:

Assume the STATION table has the following data:

 STATION table:

+------+----------+---------+

| CITY |   LAT_N  | LONG_W  |

+------+----------+---------+

|  A   |  20.0000 | 10.0000 |

|  B   |  25.0000 | 12.0000 |

|  C   |  30.0000 | 14.0000 |

|  D   |  35.0000 | 16.0000 |

+------+----------+---------+

Sample Output:

The expected output for the Euclidean Distance between points P1(a,b) and P2(c,d) would be:

+------------------+

| EUCLIDEAN_DISTANCE |

+------------------+

|            29.1548 |

+------------------+

Explanation:

The problem statement requires us to calculate the Euclidean Distance between two points P1(a,b) and P2(c,d) on a 2D plane, where the coordinates of the points are derived from the LAT_N and LONG_W columns of the STATION table.

The values of a, b, c, and d are derived from the STATION table

We can then apply the Euclidean Distance formula to calculate the distance between these two points:

Euclidean Distance = sqrt((c-a)^2 + (d-b)^2)

The SQL queries for the solution are slightly different across different SQL platforms, but the underlying logic is the same. We use the MIN and MAX functions to get the minimum and maximum values of LAT_N and LONG_W from the STATION table, and calculate the Euclidean Distance between these two points using the above formula. The POW or POWER function is used to calculate the square of the difference between MAX(LAT_N) and MIN(LAT_N) and MAX(LONG_W) and MIN(LONG_W). The SQRT function is used to calculate the square root of the sum of the squares of the differences between the latitude and longitude values. Finally, the ROUND function is used to round the calculated Euclidean Distance to 4 decimal places.

Solution:

MySQL Query:

 SELECT ROUND(SQRT(POW(MAX(LAT_N)-MIN(LAT_N),2) + POW(MAX(LONG_W)-MIN(LONG_W),2)), 4) AS EUCLIDEAN_DISTANCE

The output of the query is the Euclidean Distance between the two points, rounded to 4 decimal places.

Execution:

I hope this works for you. Let me know if you face any issue in the comment section.