mysql SELECT MIN from WHERE result

Question

I have a table with several routes which has severeal points defined by lattitude and longitude.

table name: route_path

 |id_route  |id_point| lat        | lng       |
 |hhVFlBFA0M|     328| 48.90008   | 18.0233   |
 |hhVFlBFA0M|     329| 48.90003   | 18.0268   |
 |hhVFlBFA0M|     330| 48.89997   | 18.02856  |
 |hhVFlBFA0M|     331| 48.89991   | 18.02857  |
 |hhVFlBFA0M|     332| 48.89986   | 18.02862  |
 |hhVFlBFA0M|     333| 48.89982   | 18.02869  |
 |hhVFlBFA0M|     334| 48.89981   | 18.02878  |
 |hhVFlBFA0M|     335| 48.89981   | 18.02886  |
 |hhVFlBFA0M|     336| 48.89956   | 18.02925  |
 |hhVFlBFA0M|     337| 48.89914   | 18.02972  |
 |hhVFlBFA0M|     338| 48.8986177 | 18.0302365|
 |3toCyDGVV2|       1| 48.134166  | 17.1051961|
 |3toCyDGVV2|       2| 48.13417   | 17.1052   |
 |3toCyDGVV2|       3| 48.13344   | 17.10559  |
 |3toCyDGVV2|       4| 48.13298   | 17.10609  |
 |3toCyDGVV2|       5| 48.13221   | 17.10699  |
 |3toCyDGVV2|       6| 48.132     | 17.10806  |
 |3toCyDGVV2|       7| 48.13193   | 17.10997  |
 |3toCyDGVV2|       8| 48.13203   | 17.1109   |  
 |3toCyDGVV2|       9| 48.132     | 17.1 112  |
 |3toCyDGVV2|      10| 48.13181512| 17.1112   |  
 |3toCyDGVV2|      11| 48.13181   | 17.10806  |
 |3toCyDGVV2|      12| 48.13181   | 17.10806  |
 |3toCyDGVV2|      13| 48.13197   | 17.10399  |
 |3toCyDGVV2|      14| 48.13199   | 17.10352  |
 |3toCyDGVV2|      15| 48.1323    | 17.10328  |

So far I can do it to select all rows from one route which are within tolerated distance and then loop to find minimal distance point.

SELECT * FROM route_path 
WHERE 
(((lat < $start_lat + $tolerance) AND 
(lat > $start_lat - $tolerance)) AND 
((lng < $start_lng + $tolerance) AND 
(lng > $start_lng - $tolerance)))

So this will results in several rows (id_points) of each route and then I need to loop with while to find minimal.

How can I found out select one row (one id_point) from each route with minimal distance from start lat and lng considering this distance is not more then some value.

Any suggestion for sql request without looping.

Basically I need something like, but of course it is not possible to use MIN after WHERE

SELECT * FROM route_path WHERE **MIN(**(((lat < $start_lat + $tolerance) AND (lat > $start_lat - $tolerance)) AND ((lng < $start_lng + $tolerance) AND (lng > $start_lng - $tolerance)))**)**

"minimal distance from start lat and lng"? Are you trying to find the nearest point? — Strawberry, Jun 25 '14 at 08:51
You need to calculate the distances from start_lat / start_lng to each points lat / lng, then get the min one of those. This might be far easier if you use spatial fields. If you want the other details, you use this as a sub query and join back against the table (doing the calculation again in the join condition). Or if just for a single row, calculate the distance and the use `ORDER BY distance LIMIT 1` — Kickstart, Jun 25 '14 at 11:32
@Kickstart could you please help to write such kind of sql uqery? Doesnt matter which idea. Thanks — Vilis, Jun 25 '14 at 12:11

score 0 · Accepted Answer · edited May 23 '17 at 12:10

There are a few ways to calculate the distance between 2 points. The most efficient are probably using spatial data types which are designed for this and have indexes for this. I am not yet that experience with these so if you want to alter your database to use these I will just point you at this previous question to get the basics (the accepted answer covers it):-

Fastest Way to Find Distance Between Two Lat/Long Points

If you want to use your table as it currently stands then you can get the distance in km between 2 points with the following calculation:-

111.045 * DEGREES(ACOS(COS(RADIANS(lat_point_1))
                 * COS(RADIANS(lat_point_2))
                 * COS(RADIANS(long_point_1) - RADIANS(long_point_2))
                 + SIN(RADIANS(lat_point_1))
                 * SIN(RADIANS(lat_point_2))))

(taken from here).

Using this if you wanted to know the closest point on a particular route to your starting point you could use this (no need to multiply by 111.045 unless you care about the actual distance rather than it just being the closest one):-

SELECT id_route,
        id_point,
        lat,
        lng,
        DEGREES(ACOS(COS(RADIANS($start_lat))
                 * COS(RADIANS(lat))
                 * COS(RADIANS($start_lng) - RADIANS(lng))
                 + SIN(RADIANS($start_lat))
                 * SIN(RADIANS(lat)))) AS distance_in_km
FROM route_path
WHERE id_route = 'hhVFlBFA0M'
ORDER BY distance_in_km 
LIMIT 1

If you wanted to know the closest point on EACH route to your starting point you would calculate the closest point on each route, then join that to your original table where the distance for that point matches the min distance (this will cause a problem if 2 points on a single route are exactly the same distance from your start point)

SELECT route_path.id_route,
        route_path.id_point,
        route_path.lat,
        route_path.lng
FROM route_path
INNER JOIN
(
    SELECT id_route,
            MIN(DEGREES(ACOS(COS(RADIANS($start_lat))
                     * COS(RADIANS(lat))
                     * COS(RADIANS($start_lng) - RADIANS(lng))
                     + SIN(RADIANS($start_lat))
                     * SIN(RADIANS(lat))))) AS distance_in_km
    FROM route_path
    GROUP BY id_route
) sub0
ON route_path.id_route = sub0.id_route
AND DEGREES(ACOS(COS(RADIANS($start_lat))
                     * COS(RADIANS(lat))
                     * COS(RADIANS($start_lng) - RADIANS(lng))
                     + SIN(RADIANS($start_lat))
                     * SIN(RADIANS(lat)))) = sub0.distance_in_km

thank you for perfect explanation and useful examples. I implemented them. — Vilis, Jun 25 '14 at 20:15

mysql SELECT MIN from WHERE result

1 Answers1