MySQL - How to get nearest locations with multiple latitudes and longitudes?

Question

I found some good suggestions here to find nearest locations with a pair of single latitude and longitude. But i am looking for an efficient way to find the nearest locations with a set of (multiple) pairs of latitudes and longitudes. And then group the results. I can use UNION in mysql with the query i have written below. But i am looking for way to do it without UNION. My table (user_locations)

+----+--------+-----------+-----------+-----------------------------+
| id | userid | lat       | lng       | point                       |
+----+--------+-----------+-----------+-----------------------------+
| 1  | 18     | 48.204784 | 16.369640 | POINT(16.369640, 48.204784) |
+----+--------+-----------+-----------+-----------------------------+
| 2  | 21     | 48.205872 | 16.368946 | POINT(16.368946, 48.205872) |
+----+--------+-----------+-----------+-----------------------------+
| 3  | 11     | 48.205914 | 16.367867 | POINT(16.367867, 48.205914) |
+----+--------+-----------+-----------+-----------------------------+

The query with single lat and lng pair: Find the users who are near [48.205546, 16.368667]

SELECT 
  name, 
   ( 3959 * acos( cos( radians(48.205546) ) * cos( radians( locations.lat ) ) 
   * cos( radians(locations.lng) - radians(16.368667)) + sin(radians(48.205546)) 
   * sin( radians(locations.lat)))) AS distance 
FROM user_locations 
WHERE active = 1 
HAVING distance < 10 
ORDER BY distance;

I am looking to use the above query with a set of given lat longs: Find the users who are near: [[48.205546, 16.368667], [48.205084, 16.369712], [48.205660, 16.367947]]

SELECT 
      name, 
       ( 3959 * acos( cos( radians($lat1,$lat2,$lat3,$lat4) ) * cos( radians( locations.lat ) ) 
       * cos( radians(locations.lng) - radians($lng1,$lng2,$lng3,$lng4)) + sin(radians($lat)) 
       * sin( radians(locations.lat)))) AS distance 
    FROM user_locations 
    WHERE active = 1 
    HAVING distance < 10 
    ORDER BY distance;

I am not really looking for the specific output because the results will be grouped so distance is not really that important. User 18 could be near to multiple lat lng pairs. Grouping the results with userid will retrieve only one row of userid: 18.

    +----+--------+-----------+-----------+-----------------------------+--+
| id | userid | lat       | lng       | point                       |  |
+----+--------+-----------+-----------+-----------------------------+--+
| 2  | 21     | 48.205872 | 16.368946 | POINT(16.368946, 48.205872) |  |
+----+--------+-----------+-----------+-----------------------------+--+
| 3  | 11     | 48.205914 | 16.367867 | POINT(16.367867, 48.205914) |  |
+----+--------+-----------+-----------+-----------------------------+--+

what does "nearest lat long with multiple latitudes and longitudes" mean? give some examples. show the full point values and say what output you want for that sample data? — ysth, Sep 01 '20 at 06:33
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Sep 01 '20 at 06:34
@Strawberry i am not really looking for specific output because if multiple lat and long pairs are provided, then result will also have multiple rows of users. Lets say: find the users who are near [[48.205546, 16.368667], [48.205084, 16.369712], [48.205660, 16.367947]], this will return several rows of userid 18 because he is near [48.205546, 16.368667], [48.205084, 16.369712]. I am looking for a way to feed multiple lat lng pairs instead of single pair of lat and lng. I have edited the question again. Thanks — Noobish, Sep 01 '20 at 07:12
It's your choice not to provide the information requested in the format requested. But if you choose not to, then *I* can't help you. — Strawberry, Sep 01 '20 at 07:15
by near a list of points, you mean the user's minimum distance from any of them? or distance from the area with those points as vertices? — ysth, Sep 01 '20 at 07:43
@ysth I have added a solution that is fast but hacky. I had to use OR with MBRContains — Noobish, Sep 01 '20 at 09:37

Noobish · Accepted Answer · 2020-09-01T09:39:47.323

CollectIon: [[48.205546, 16.368667], [48.205084, 16.369712], [48.205660, 16.367947]]

Hacky SOLUTION:

SELECT  locations.*  FROM    user_locations AS locations
WHERE   (MBRContains
                (
                LineString
                        (
                        Point (
                                16.368667 + 10 / ( 111.320 / COS(RADIANS(48.205546))),
                                48.205546 + 10 / 111.133
                              ),
                        Point (
                                16.368667 - 10 / ( 111.320 / COS(RADIANS(48.205546))),
                                48.205546 - 10 / 111.133
                              ) 
                        ),
                locations.point
                )
                OR MBRContains
                (
                LineString
                        (
                        Point (
                                16.369712 + 10 / ( 111.320 / COS(RADIANS(48.205084))),
                                48.205084 + 10 / 111.133
                              ),
                        Point (
                                16.369712 - 10 / ( 111.320 / COS(RADIANS(48.205084))),
                                48.205084 - 10 / 111.133
                              ) 
                        ),
                locations.point
                )
                      OR MBRContains
                (
                LineString
                        (
                        Point (
                                16.367947 + 10 / ( 111.320 / COS(RADIANS(48.205660))),
                                48.205660 + 10 / 111.133
                              ),
                        Point (
                                16.367947 - 10 / ( 111.320 / COS(RADIANS(48.205660))),
                                48.205660 - 10 / 111.133
                              ) 
                        ),
                locations.point
                ))

Query Duration: 0.109 sec (fine).
Note: The point field has SPATIAL index

10 (distance within km)

16.368667 (long)

48.205546 (lat)

MySQL - How to get nearest locations with multiple latitudes and longitudes?

1 Answers1