Search code examples
mysqlsql

SQL - Agg Func Manhattan Distance

SO Link doesn't answer the question. I can't figure out how to solve this query on Hackerspace. None of the solutions online seem to be working. Is this a bug or am I doing something wrong?

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 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 Manhattan Distance between points and and round it to a scale of decimal places.

Input Format

The STATION table is described as follows:

STATION Table

  1. ID | Number
  2. City | VarChar2(21)
  3. State | VarChar2(2)
  4. LAT_N | Number
  5. LONG_W | Number

Database: MySQL

Source: https://www.hackerrank.com/challenges/weather-observation-station-18/problem

Link: distance between two longitude and latitude (Tried, but none of the answers provided work.)

SELECT ROUND(ABS(MIN(Station.LAT_N) - MIN(Station.LONG_W)) + ABS(MAX(Station.LAT_N) - MAX(Station.Long_W)), 4) 
FROM Station;
Solution

  • The formula for manhattan distance is | a - c| + | b - d| where a and b are min lat and long and c and d are max lat and long respectively. 

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

    I got 25 hakker points! so can I get 25 points from you?