Distance formula in SQL

Question

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)

but where is the actual 'distance' column which you are trying to select? :) — andrews, Oct 15 '16 at 11:52
Trying to calculate it based on longitude/latitude and another point. — Ben, Oct 15 '16 at 11:54
`SELECT sighting_id, SQRT(POWER(latitude -(-28),2) + POWER(longitude -(151),2)) as distance FROM sightings GROUP BY sighting_id, distance; SELECT sighting_id, SQRT(POWER(latitude -(-28),2) + POWER(longitude -(151),2)) as distance FROM sightings GROUP BY sighting_id, distance` Tried this but still no luck. — Ben, Oct 15 '16 at 11:54
if distance is a keyword, surround it with double quotes and match the case of the actual column name. ie. "DISTANCE" — Mike, Oct 15 '16 at 12:00
A good overview here: https://docs.oracle.com/cd/E17781_01/appdev.112/e18750/xe_locator.htm#XELOC569 — Mike, Oct 15 '16 at 12:13
Ben, as per the Mike's link, do you also have any spatial indexes defined for sightings table? — andrews, Oct 15 '16 at 12:22
I'm guessing that `sighting_id` is the PK for this table. Is this true? If so, having `sighting_id` in the `GROUP BY` clause makes the `GROUP BY` clause useless as the values will always be distinct and hence never grouped. Exactly what are you trying to get your query to return here? — Luke Woodward, Oct 15 '16 at 13:09
Is `SIGHTING_ID` a key to your table? Then why grouping on it? — Amir Pashazadeh, Oct 15 '16 at 13:20

andrews · Answer 1 · 2016-10-15T12:41:09.997

1

try using some other name for 'distance' column, looks like it is some internal GEO function or synonym already defined in your Oracle DB. Please also check if all latitude and longitude values in the table are valid numbers, not null etc.

You may need to add some coalesce() wrapper for null latitude and longitude occurrences or add the "AND latitude IS NOT NULL AND longitude IS NOT NULL" into WHERE clause.

edited Oct 15 '16 at 12:41

answered Oct 15 '16 at 11:58

andrews

2,173
2
16
29

I doubt that that's the problem. The error message says OGC_DISTANCE, not DISTANCE. Oracle is very precise with those things. Then: A **function** name can be used just fine as a table or column name (although it is a very bad practice); the parser can clearly distinguish between the two, since a function call is ALWAYS followed by an open parenthesis. So even if DISTANCE was a function name, using it as a column name would not throw that error. – Oct 15 '16 at 12:58
@mathguy, your point may be true regarding to functions, but I bet there is a 'distance' synonym already defined in OP's DB which Oracle tries to use in his query, thefore, he needs to pick another name for his distance column. – andrews Oct 15 '16 at 13:00
...Right about synonyms... let's see what the OP finds if he changes the column alias. – Oct 15 '16 at 13:03

Kacper · Accepted Answer · 2016-10-15T13:09:16.533

0

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));

edited Oct 15 '16 at 13:09

answered Oct 15 '16 at 13:05

Kacper

4,798
2
19
34

check comments, the OP has put the updated SQL into comments, the one in the question body really lacks the distance column. But even with the distance column he gets that error. So the issue is different. – andrews Oct 15 '16 at 13:11
Edited. Sorry group by has to also be based on calculations not on alias. That what I meant. @LukeWoodward when I was writing that comment hasn't loaded. Probably it was hidden in see more comment that why I haven't noticed – Kacper Oct 15 '16 at 13:12
@andrews I've replaced distance in group by formula by formula. And wuould give it a try. No reference to distance at all now. Just alias. – Kacper Oct 15 '16 at 13:16
Thanks Kacper and all the contributors. The OGC error had me stumped. Looking forward to improving my skills at SQL and contributing to the community. Thanks. – Ben Oct 16 '16 at 01:01

Distance formula in SQL

2 Answers2