Формула расстояния для MariaDB ближайших 200 мест без радиуса
У меня есть MariaDB, версия сервера: 10.0.23-MariaDB, со столбцами широты и долготы (с плавающей запятой 10,6) плюс столбец geo_location (геометрия), который был рассчитан по столбцам широты и долготы.
Я хотел бы найти ближайших 200 человек от человека. Человек в центре имеет широту и долготу, которые передаются на запрос. Есть ли способ сделать это без радиуса? Таким образом, если плотность населения высока, радиус будет небольшим. Если плотность населения низкая, то радиус будет большим.
Есть около 4 миллионов строк, и это должно быть как можно быстрее. Строки могут быть отфильтрованы в первую очередь на основе округа, в котором они находятся. Некоторые графства очень большие с низкой плотностью населения, а другие — маленькие графства с высокой плотностью населения. Мне нужен самый быстрый способ найти ближайшие 200 человек.
Решение
SELECT *, ST_DISTANCE(geo_location, POINT(lon, lat)) AS distance
FROM geotable
ORDER by distance DESC
LIMIT 200;
Плохая новость в том, что это будет очень медленно, потому что st_distance () не использует пространственные индексы. Вы должны попытаться ограничить ваш запрос, используя максимальный радиус, чтобы выбрать меньше записей:
set @dist = 100;
set @rlon1 = lon-@dist/abs(cos(radians(lat))*69);
set @rlon2 = lon+@dist/abs(cos(radians(lat))*69);
set @rlat1 = lat-(@dist/69);
set @rlat2 = lat+(@dist/69);
SELECT *, ST_DISTANCE(geo_location, POINT(lon, lat)) AS distance
FROM geotable
WHERE ST_WITHIN(geo_location,ENVELOPE(LINESTRING(point(@rlon1, @rlat1), point(@rlon2, @rlat2))))
ORDER by distance DESC
LIMIT 200;
Или, если у вас есть координаты POLYGON для каждой страны, вы можете использовать их вместо максимального радиуса.
Другие решения
6 десятичных знаков достаточно хороши (16 см / 0,5 фута), но FLOAT (1,7 м / 5,6 фута) теряет часть этой точности. По сути, это никогда не хорошо (M,N) на FLOAT или же DOUBLE; Вы подвергаетесь 2 округлениям, одно из которых является пустой тратой.
Нет простого способа «найти ближайшего» на земном шаре, потому что нет «двумерных» индексов. Однако, используя разделение для одного измерения и кластеризованный PRIMARY KEY с другой стороны, вы можете сделать довольно хорошую работу.
Реальная проблема большинства решений — большое количество дисковых блоков, которые нужно ударить, не найдя правильных элементов. На самом деле, обычно более 90% затронутых рядов не нужны.
Все это «решено» в Мой лат / лнг блог. Это может коснуться, возможно, 800 строк, чтобы получить 200, которые вы хотите, и они будут хорошо сгруппированы, поэтому нужно коснуться лишь нескольких блоков. Он не нуждается в предварительной фильтрации по стране, но нуждается в некоторой радикальной реструктуризации таблицы. И, если вы хотите различить двух людей, обнимающих друг друга, я предлагаю масштабировать INT (16 мм / 5/8 дюйма) — градусы * 10000000. Также FLOAT не будет работать с PARTITIONing; INT будут. Код в этой ссылке использует MEDIUMINT масштабируется (2,7 м / 8/8 футов), но это можно изменить.