CODEWITHSUNDEEP
sqlsqlitegeolocation
limpmike
limpmike

Reputation: 33

Distinct returning duplicates with multiple columns

im developing an application in android for showing bus routes from my hometown, i have problems with a query, i have two tables in my app as shown below:

table INFORMATION

ID | NAME     | DIRECTION
--------------------------
 1 | BUS 1    | departure
 2 | BUS 1    | return
 3 | BUS 2    | departure
 4 | BUS 2    | return
 5 | BUS 3    | departure
 6 | BUS 3    | return

table COORDINATES

ID | INFORMATION_ID |     LAT   |     LON       |
-------------------------------------------------
 1 |    1           | 19.171184 | -96.174029    |
 2 |    1           | 19.171021 | -96.173991    |
 3 |    1           | 19.171002 | -96.173979    |
 5 |    2           | 19.193244 | -96.138732    |
 6 |    2           | 19.193368 | -96.138957    |
 7 |    2           | 19.193332 | -96.138741    |

I'm trying to show nearby bus routes within 500 m range, and i'm actually able to do it with this query

SELECT DISTINCT NAME,
((example.lat  - COORDINATES.LAT) * (example.lat - COORDINATES.LAT) + (example.lon - COORDINATES.LON) * (example.lon - COORDINATES.LON)) AS DISTANCE  
FROM COORDINATES
INNER JOIN INFORMATION
ON COORDINATES.INFORMATION_ID=INFORMATION.ID 
WHERE DISTANCE < 0.000023544  //supposedly 500 m, im not sure
ORDER BY DISTANCE

But the problem is that i have repeated values in the NAME field because i have several latlon points from the bus routes that are within the range, i just need to return the minimun distance with the corresponding name like this:

ID | NAME     | DISTANCE
---------------------
 1 | BUS 1    | 1.349834...
 3 | BUS 2    | 1.367033...

I'll aprecciate any guidance towards resolving this issue, thanks.

Upvotes: 0

Views: 42

Answers (1)

Dilberted
Dilberted

Reputation: 1172

See if this helps to get the shortest distance out.

SELECT NAME, MIN(DISTANCE) FROM
(
SELECT NAME, 
((19.171150  - COORDINATES.LAT) * (19.171184 - COORDINATES.LAT) + 
 (-96.173990 - COORDINATES.LON) * (-96.173990 - COORDINATES.LON)) AS DISTANCE  
FROM COORDINATES
INNER JOIN INFORMATION
ON COORDINATES.INFORMATION_ID=INFORMATION.ID 
WHERE DISTANCE < 0.000023544
) 
GROUP BY NAME

http://sqlfiddle.com/#!7/2e1ea/4

Upvotes: 1

Related Questions