CODEWITHSUNDEEP
sqloracle-databaseeuclidean-distance
101ldaniels
101ldaniels

Reputation: 301

trying to get the Euclidean Distance through a query

i keep getting missing experssion error, but i cant see what i have done wrong?

    DECLARE
    lat NUMBER := -28;
    lon NUMBER := 151;
    BEGIN
    SELECT
      sighting_id 
    FROM
      sightings
    ORDER BY 
      sqrt(power(lat - latitude, 2) + power(lon - longitude, 2))

    END;

Upvotes: 1

Views: 6507

Answers (2)

gaddam
gaddam

Reputation: 21

Hacker rank solution:

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You have several errors in your query.

  • A comma before from.
  • The use of long, which is a reserved word (see here).
  • limit (not supported by Oracle).
  • The DESC keyword should go after the key expression, not before. And for distances, usually ASC is used, not DESC.
  • And a PL/SQL block that has a query, but not an INTO clause or some other place to put the results.

Are you sure you want to use Oracle?

EDIT:

Start with a query that looks more like this:

WITH params as (
      SELECT -28 as lat, 151 as lon
      FROM dual
     )
SELECT s.sighting_id 
FROM params CROSS JOIN
     sightings s
ORDER BY sqrt(power(lat - latitude, 2) + power(lon - longitude, 2))
FETCH FIRST 1 ROW ONLY;  -- Note:  this is in Oracle 12c+

Upvotes: 3

Related Questions