SQLite -> finding two closest points to two different points

Question

Hi everybody.

So I have 2 tables :

route(#id)
step(#id,route_id,lat,lng,sequence_order)

And 2 GPS coordinates : M1(lat1, lng1) M2(lat2, lng2).

So I want to find all the routes which have at least one step closer than 1.0 mile from each of the 2 GPS coordinates with a ascending sequence order.

So if I find Route#25 which has a step S1 at 0.4 miles from M1, and a step S2 at 0.2 miles from M2, it should match only if the sequence number of S1 is inferior to the sequence number of S2. Does it make sense ?

Assuming I added a function to calculate the distance, I can do it quite easily with this request :

SELECT r.id,s1.lat,s1.lng,s2.lat,s2.lng,distance(s1.lat,s1.lng,lat1,lng1) as d1,distance(s2.lat,s2.lng,lat2,lng2) as d2 FROM route r
INNER JOIN step s1 ON r.id = s1.route_id
INNER JOIN step s2 ON r.id = s2.route_id AND s1.sequence_order < s2.sequence_order
WHERE d1<1.0
AND d2<1.0 

The behavior I would like to have, is to keep only the closest coordinates (s1.lat,s1.lng) and (s2.lat,s2.lng) to my to points. So I thought I could do something like this :

ORDER BY d1
ORDER BY d2
GROUP BY r.id

but it just crashes.

Any idea ?

Answer 1

You probably want

ORDER BY d1, d2

as your order by clause. What's the crash, anyway?