Ben Ben - 1 month ago 26
SQL Question

Distance formula in SQL

SQL> SELECT sighting_id, distance
FROM sightings
WHERE distance = SQRT(POWER(latitude -(-28),2) + POWER(longitude -(151),2))
GROUP BY sighting_id, distance;


Receiving the error PLS-306: wrong number or types of arguments in call to 'OGC_DISTANCE'. Any ideas?

Name Null? Type
-------------- -------- --------------------------------
SIGHTING_ID NOT NULL NUMBER
SPOTTER_ID NUMBER
BIRD_ID NUMBER
LATITUDE NUMBER
LONGITUDE NUMBER
SIGHTING_DATE DATE
DESCRIPTION VARCHAR2(255)

Answer

Problem is in your table you have no column distance. As far as I understand you want to do:

SELECT sighting_id, SQRT(POWER(latitude -(-28),2) + POWER(longitude -(151),2)) as distance 
FROM sightings 
GROUP BY sighting_id, SQRT(POWER(latitude -(-28),2) + POWER(longitude -(151),2));
Comments