mercredi 14 octobre 2015

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.

Aucun commentaire:

Enregistrer un commentaire