Как выбрать и приоритизировать координаты на карте по другим?

Question

[XenK]

Есть таблица запросов пользователей с координатами:

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

И вторая таблица с координатами организаций:

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

Нужно найти координаты наиболее запрашиваемых мест, и соотнести их с организациями по приоритету.
Например для организации "А" соответствует - 4000 запросов, а для "Б" - 100 запросов. В итоге должно получится что-то наподобие heatmap. Как правильно составить для этого запрос, и обойдется ли это только запросом?

Answer 1

[Руслан .]

При такого вида запросах точного попадания в координаты организации не будет и нужно определиться с размером области вокруг координат пользователя и ее формой (квадрат, прямоугольник, круг, что-то еще).
Размер области ограничит список отбираемых организаций.
Задать размер можно например так, пусть нужно показать все вокруг пользователя в радиусе 2 км.
И пусть область для простоты - это квадрат.
Из вики можно найти, что средняя длинна одного градуса широты/долготы примерно 111 км.
Тогда вариация по широте/долготе от текущей позиции пользователя будет плюс/минус 2/111 градуса = 0,018 градуса.

А дальше можно вот таким запросом вывести список подходящих организаций:

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

Чтобы это нормально работало нужны индексы по полям lat, lon таблицы tb_location.
Также лучше ограничить точность координат lat, lon например можно указать место организации с точностью до 10 метров, тогда координаты можно округлить до 0,01/111 = 9*10^-6, т.е. до 5 знаков после запятой.

Можно еще для операции поиска координаты привести к целым числам, т.е. хранить для поиска lat/lon умноженными на 10^5 и без дробной части. При такой схеме нужно 3+5 = 8 значащих цифр и тип int подойдет для хранения. Этот вариант позволить уменьшит размер индекса по полям lat/lon, а это должно ускорить поиск.
При таком вариант в запросе вместо
us.lat - 0.018 AND us.lat + 0.018
нужно указать
us.lat - 1800 AND us.lat + 1800

При использовании в поиске условия вида
us.lat - 1800 AND us.lat + 1800
есть нюанс, если искать в районе 0/360 градуса нужно делить условие на две части, вот пример когда
us.lat - 1800 получается меньше нуля.

BETWEEN 36000000 + (us.lat - 1800) AND 36000000
OR
BETWEEN 0 AND us.lat + 1800

Если us.lat + 1800 получается больше 36000000, то аналогично две части:
BETWEEN us.lat - 1800 AND 36000000
OR
BETWEEN 0 AND (us.lat + 1800)-36000000