How to select and prioritize coordinates on the map over others?

Question

MySQL

There is a table of user requests with coordinates:

+----+-----+-----+
| id | lat | lon |
+----+-----+-----+
|  1 | ... | ... |
+----+-----+-----+

And the second table with the coordinates of organizations:

+----+-----+-----+------+---------+-----+
| id | lat | lon | name | address | ... |
+----+-----+-----+------+---------+-----+
|  1 | ... | ... |  ... |     ... |     |
+----+-----+-----+------+---------+-----+

It is necessary to find the coordinates of the most requested places, and correlate them with organizations by priority.
For example, for organization "A" corresponds to - 4000 requests, and for "B" - 100 requests. The end result should be something like a heatmap. How to correctly compose a request for this, and will it cost only a request?

Answer 1

With this type of request, there will be no exact hit in the organization's coordinates and you need to determine the size of the area around the user's coordinates and its shape (square, rectangle, circle, something else).
The size of the area will limit the list of selected organizations.
You can set the size, for example, like this, let's show everything around the user within a radius of 2 km.
And let the area for simplicity be a square.
From the wiki, you can find that the average length of one degree of latitude / longitude is approximately 111 km.
Then the variation in latitude/longitude from the user's current position would be plus/minus 2/111 degrees = 0.018 degrees.
And then you can use this query to display a list of suitable organizations:

SELECT us.id,
       lc.name,
       lc.address 
  FROM tb_user us
  JOIN tb_location lc
    ON lc.lat BETWEEN us.lat - 0.018 AND us.lat + 0.018
   AND lc.lon BETWEEN us.lon - 0.018 AND us.lon + 0.018

For this to work properly, we need indexes on the lat, lon fields of the tb_location table.
It is also better to limit the accuracy of lat, lon coordinates. For example, you can specify the place of organization with an accuracy of up to 10 meters, then the coordinates can be rounded up to 0.01/111 = 9*10^-6, i.e. up to 5 decimal places.
You can also bring the coordinates to integers for the search operation, i.e. store for searching lat/lon multiplied by 10^5 and without the fractional part. With this scheme, 3 + 5 = 8 significant digits are needed and the int type is suitable for storage. This option will allow to reduce the size of the index on the lat/lon fields, and this should speed up the search.
With this option in the query, instead
of us.lat - 0.018 AND us.lat + 0.018
, you need to specify
us.lat - 1800 AND us.lat +
1800
us.lat - 1800 AND us.lat + 1800
There is a nuance, if you search in the region of 0/360 degrees, you need to divide the condition into two parts, here is an example when
us.lat - 1800 turns out to be less than zero.
BETWEEN 36000000 + (us.lat - 1800) AND 36000000 OR
BETWEEN
0 AND us.lat
+ 1800 .lat+1800)-36000000