CODEWITHSUNDEEP
sqloracle-databaserounding
Ankit Sharma
Ankit Sharma

Reputation: 1

Euclidean Distance

Hacker Rank - Weather Observation Station 19

Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+

Consider P1(a, b) and P2(c, d) be two points on 2D plane, where (a, b) be minimum and maximum values of Northern Latitude and (c, d) be minimum and maximum values of Western Longitude. Write a query to print the Euclidean Distance between points P1 and P2 up to 4 decimal digits.

my solution for oracle is :

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

but it is giving answer to the 3 decimal points but i need 4 decimal points.

Upvotes: 0

Views: 31858

Answers (19)

Venkatesh Dhanasekaran
Venkatesh Dhanasekaran

Reputation: 344

Solution in MySQL: select ROUND(ABS(MAX(LAT_N) - MIN(LAT_N)) + ABS(MAX(LONG_W) - MIN(LONG_W)), 4) FROM STATION;

Upvotes: 0

Mohamed
Mohamed

Reputation: 43

In Euclidean plane

Distance

dist((x, y), (a, b)) = sqrt ((x - a)² + (y - b)²)

This query do this operation

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

Upvotes: 2

Harsha
Harsha

Reputation: 1

SELECT  LTRIM(
          TO_CHAR(
            SQRT(
              POWER(MAX(LAT_N) - MIN(LAT_N), 2) +
              POWER(MAX(LONG_W) - MIN(LONG_W), 2)
            ),
            '999.9999'
          )
        ) 
FROM    STATION;

Upvotes: -1

pythonista
pythonista

Reputation: 1

select round(ltrim(sqrt(power(max(LAT_N) - min(LAT_N), 2) + power(max(LONG_W) - min(LONG_W), 2)),'999.9999'), 4) FROM STATION;

This is the code that worked for me on the DB2 editor on hackerank

select round(sqrt(power(max(LAT_N) - min(LAT_N), 2) + power(max(LONG_W) - min(LONG_W), 2)), 4) FROM STATION;

This is the code that worked for me on the MySQL editor on hackerank

Upvotes: 0

Anshika Singh
Anshika Singh

Reputation: 1

SELECT ROUND(POWER(POWER(MAX(Station.LAT_N) -MIN(Station.LAT_N),2) + POWER(MAX(Station.LONG_W) - MIN(Station.Long_W),2),0.5), 4) 
FROM Station;

Upvotes: -1

Dakshitha96
Dakshitha96

Reputation: 57

This is the answer... in MySQL

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

Upvotes: 1

Roshni Miranda
Roshni Miranda

Reputation: 11

MySQL soln:

select round( power( power((min(lat_n)-max(lat_n)),2) + power((min(long_w)-max(long_w)),2),1/2),4) as distance from station;

in this code I have used power(x,1/2) instead of sqrt(x) ..so that when needed to find nth root in future we can use power(x, 1/y) operation ( where y = 0,1,2,..,n).

Upvotes: 1

MerveK
MerveK

Reputation: 61

You can run the following code using Oracle.

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

Upvotes: 0

akshay suddalwar
akshay suddalwar

Reputation: 71

correct your SQL Query -- (min(lat_n) - max(lat_n))+abs(min(long_w) - max(long_w)) --

Euclidean distance

Definition: The straight line distance between two points. In a plane with p1 at (x1, y1) and p2 at (x2, y2), it is √((x1 - x2)² + (y1 - y2)²).

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

Upvotes: 0

Adrita Sharma
Adrita Sharma

Reputation: 22213

For SQL Server, try like this:

SELECT 
cast(
    round(
        SQRT(
             SQUARE((Abs ((MIN(LAT_N ) - MAX(LAT_N )))) )
           + SQUARE( (Abs((MIN(LONG_W  ) - MAX(LONG_W  )))))
            )
        ,4)
as decimal(18,4)
)
    
FROM  STATION

Upvotes: 1

Rahul Saw
Rahul Saw

Reputation: 29

For MySql it works fine:

    SELECT ROUND(SQRT(POW(MIN(LAT_N)-MAX(LAT_N),2)+POW(MIN(LONG_W)-MAX(LONG_W),2)),4) FROM STATION;

Upvotes: 2

Vamshi
Vamshi

Reputation: 11

This worked for me:

Select round(sqrt(power((min(lat_n)-max(lat_n)),2) + power((min(long_w)-max(long_w)),2)),4) From station

Upvotes: 1

Sharan V
Sharan V

Reputation: 1

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

This should work.

Upvotes: 0

Anil Dubey
Anil Dubey

Reputation: 31


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

Upvotes: 0

codersaty
codersaty

Reputation: 43

select round(sqrt(((MAX(LAT_N) - MIN(LAT_N))*(MAX(LAT_N) - MIN(LAT_N))) 
+ ((MAX(LONG_W) - MIN(LONG_W))*(MAX(LONG_W) - MIN(LONG_W)))),4)

from station;

Try this in ORACLE SQL.

Upvotes: 0

vasantharaj
vasantharaj

Reputation: 1

Try this SQL:

select
    cast(sqrt((power(max(lat_n)-min(lat_n),2))+(power(max(long_w)-in(long_w),2))) as decimal(16,4))
from station;

Upvotes: 0

Anil Gupta
Anil Gupta

Reputation: 1

Try this:

select round(sqrt(POWER((MAX(LAT_N) - MIN(LAT_N)),2) 
    + POWER((MAX(LONG_W) - MIN(LONG_W)),2)),4)
from station;

Upvotes: -1

Tanusree Roy
Tanusree Roy

Reputation: 64

It will work for sure.

DECLARE @a numeric(36,4),@b numeric(36,4),@c numeric(36,4),@d numeric(36,4)
SET @a=(SELECT MIN(CAST(ROUND(LAT_N,4) as numeric(36,4))) FROM STATION);
SET @b=(SELECT MIN(CAST(ROUND(LONG_W ,4) as numeric(36,4))) FROM STATION);
SET @c=(SELECT MAX(CAST(ROUND(LAT_N,4) as numeric(36,4))) FROM STATION);
SET @d=(SELECT MAX(CAST(ROUND(LONG_W ,4) as numeric(36,4))) FROM STATION);
SELECT DISTINCT CAST(ROUND((POWER(((POWER((@c-@a),2))+(POWER((@d-@b),2))),0.5)),4) as numeric(36,4)) FROM STATION

Upvotes: -1

Konstantin Sorokin
Konstantin Sorokin

Reputation: 405

Try this ltrim(to_char(x, '999.9999'))

Number Format Models

Upvotes: 0

Related Questions