0

This question is different from regular mysql lat/long, radius based data fetching

I want to search a mysql database table which contains following columns for example-

ID  Items   lat       long      serving_radius(in km)

1   Item1  26.120888  85.364723    2
2   Item2  26.120888  85.364723    5
3   Item3  25.859800  85.786598    4
4   Item4  26.594900  85.504799    8

Now if a user has lat/long (29.941095/77.812424) wants to know which of these items can be served at his location. Then how i will fetch the result using php & mysql.

James Z
  • 12,209
  • 10
  • 24
  • 44
Pranav Kumar
  • 33
  • 9
  • Post your code please – Simone Rossaini Apr 22 '20 at 07:47
  • i was using the following code to fetch results with a predefined serving radius of say 10km – Pranav Kumar Apr 22 '20 at 07:56
  • $query = mysqli_query($conn, "SELECT *, (6371 * acos(cos(radians('$lat')) * cos(radians(lat)) * cos( radians(lng) - radians('$long')) + sin(radians('$lat')) * sin(radians(lat)))) AS distance FROM products WHERE status = '$status' HAVING distance < 10000 ORDER BY distance"); – Pranav Kumar Apr 22 '20 at 07:56
  • But now i dont have any clue on how to write a query when serving radius is different for different items – Pranav Kumar Apr 22 '20 at 07:58
  • If the table is large, then the straightforward solutions mentioned will be slow. This is because every row must be tested. How many rows will you eventually have? – Rick James May 01 '20 at 20:01
  • @RickJames number of rows can reach very high, please suggest me a better solution if you have. – Pranav Kumar May 01 '20 at 20:04
  • @RickJames here is the extended version of this question https://stackoverflow.com/questions/61387232/ .... i also want to limit the query to 20 rows per request (bcoz i want to add load more button to fetch the next 20 rows per ajax request ). – Pranav Kumar May 01 '20 at 20:13
  • @PranavKumar - My Answer covers "limit 20", too. Getting the 'next' 20 is a minor extension. – Rick James May 01 '20 at 20:46

4 Answers4

1

I guess the SQL you are looking for is this one:

SELECT id, items, lat, long, serving_radius
FROM table 
WHERE lat = 29.941095 
AND long = 77.812424;

In your PHP code, you should use a DB driver (I recommend you PDO) in order to connect to the DB and execute the query.

There are plenty of tutorials about how to using PDO (creating the connection, selecting, inserting, etc).

I just found this tutorial which could help you: https://parzibyte.me/blog/en/2019/10/14/php-mysql-tutorial-using-pdo/

Edit: Not sure if you mean about how to build a query with those exact lat and long values or closer to them. If so, you can take a look at https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql

JesusValera
  • 629
  • 6
  • 14
0

This question seems to be similar to MySQL - Find points within radius from database

  1. Find the radius from your serving_radius and lat/lon at the database.
  2. use the function MBRContains to find the polygon that contains the user's point. 
    SELECT
        *
    FROM 
        items
    WHERE
        MBRContains(
            GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))'),
            GeomFromText('Point(77.812424 29.941095)')) = 1;

hope it helps

Oscar Gallardo
  • 2,240
  • 3
  • 27
  • 47
0

Is "serving_radius" already stored in your table ? What you can do is fetch distance and check if its under serving_radius

SELECT serving_radius, 
(6371 * acos(cos(radians(" . $userLatitude . ")) * cos(radians(`latitude`)) * cos(radians(`longitude`) - radians(" . $userLongitude . ")) + sin(radians(" . $userLatitude . ")) * sin(radians(`latitude`)))) as distance 
having distance < serving_radius
Akhzar Javed
  • 616
  • 6
  • 12
0

I think your problem is tougher than the traditional problem due to the radius being variable.

See http://mysql.rjweb.org/doc.php/find_nearest_in_mysql for discussion.

That lists 5 ways to "find the nearest X". The simplest, but slowest, is check every item for its distance. All the rest are faster because of using a "bounding-box" to limit how many items need to be checked. However, the coding gets complex.

A bounding-box, in your case, could be limited to the maximum radius in the table. Your sample data show radii of up to 8, so building a square of side 2*8 around the starting point would let you feed into the various techniques I describe.

The simplest involves INDEX(lat, lng), INDEX(lng, lat) to let the Optimizer pick which is better. The advantage is that it checks only items in a 16-wide band of latitude (or longitude), not the entire globe. The disadvantage is that it does not do much to optimize the other direction.

I suggest you use that technique as a first cut. If you need more performance then read further in my document and, optimally, come back for more help,

The techniques discussed there also allow for limiting the number of returned values.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Dear Rick James, i went to your website and read the page but honestly speaking, all of your hard work bounced off my head. Could you please write me a proper solution that would work for me. Please also suggest me the table structure which i should use to achieve it. the number of rows in the table can go up to 1 million or higher and the value of serving_radius will never be greater than 10-15 km. Thanks – Pranav Kumar May 01 '20 at 21:01
  • @PranavKumar - First implement the `SELECT` given by other Answers. Your table structure is probably fine, with the addition of the two indexes I list in this Answer. Then focus only on the "bounding box" solution in my article (http://mysql.rjweb.org/doc.php/find_nearest_in_mysql#bounding_box ). Ignore the Z-Order and Partitioning; they are complex. – Rick James May 01 '20 at 21:40