Как вывести маршруты из базы MySQL, которые совпадают с координатами искомого объекта?

Question

[freeeeez]

У меня есть база маршрутов. Каждая запись имеет пункт отправления, пункт назначения и их координаты lat lon. Необходимо вывести маршруты, которые пролегают через координаты искомой точки.

Допустим ищем Тверь (56.8, 35.9) и из базы должны быть выведены маршруты:
- Москва(55.7, 37.6) - Питер (59.9, 30.3)
- Клин (56.3, 36.7) - Торжок (57.0, 35.0)
, так как они проходят через Тверь.

У меня есть массив точек каждого маршрута полученный из Google.Maps. Я не знаю как его вставить в БД: в отдельную таблицу или в виде JSON в дополнительное поле к маршруту. Как потом искать среди массива точек искомую точку? Как искать на определенном удалении от точки допустим (10км) я представляю, а как сопоставить попадает ли она в пределы маршрута нет. Кто нибудь сталкивался с такой задачей?

Comments

[hOtRush]

Москва(55.7, 37.6) - Питер (59.9, 30.3) - это маршрут из двух точек, здесь никак не указано что он идет через тверь.
Москва(55.7, 37.6) - Тверь (56.8, 35.9) - Питер (59.9, 30.3) - вот здесь указано.

В общем я думаю все промежуточные точки должны быть предопределены, потому что mysql - это база данных, а не сервис геокодирования.

[hOtRush]

Ну и вообще я бы использовал например elasticsearch, где есть GeoShape тип данных https://www.elastic.co/guide/en/elasticsearch/refe... Есть Point и LineString, и можно не очень хитрыми манипуляциями определить попадает ли Point в LineString, например вот этим https://www.elastic.co/guide/en/elasticsearch/refe...

[freeeeez]

hOtRush, я указал в вопросе, что массив точке каждого маршрута есть, т.е. там не только Тверь в промежутке, а еще 100-150 точек включая Тверь, только в каком виде их разместить и как потом среди них производить поиск? Наверное elasticsearch более правильный вариант, но разбираться с этим времени нет, можно сделать это как то на MySQL чтобы хоть криво и косо, но работало?

[hOtRush]

freeeeez, ну надо будет немного заморочиться с расчетами. Но очень надеюсь что ваши точки имеют больше знаков после запятой, иначе врядли что-то получится.

К примеру взять две таблицы, routes (id, name, ...) и route_points (id, route_id, lat, lon, order, ...) (где order - порядковый номер точки в маршруте, нужен для построения на карте). Когда нужно выбрать маршруты которые проходят возле нужной точки используй SELECT BETWEEN.
К примеру есть точка 55.755831,37.617673
SELECT * FROM route_points WHERE lat BETWEEN 55.755831-X AND 55.755831+X AND lon BETWEEN 37.617673-X AND 37.617673+X, где X собственно нужно посчитать. Если нужен к примеру разброс 10км - сколько это будет в градусах (какое значение и сколько знаков после запятой). Причем для долготы и широты значение X по сути должно быть разным

[freeeeez]

hOtRush, да конечно, точки имеют больше знаков после запятой, я все таки реализовал через Linestring и sql-функцию поиска, работает довольно быстро

Answer 1

[freeeeez]

https://stackoverflow.com/questions/12684148/find-...

Answer 2

[Dimonchik]

такое проще графовой базой решать

ну или https://dev.mysql.com/doc/refman/8.0/en/spatial-ty...

Comments

[freeeeez]

это точно не мой вариант

Answer 3

[rPman]

Для того чтобы сравнивать данные, вам нужно привести их к общему виду.

В вашем случае это не точки а связный двунаправленный граф, где вершины - города, а ветви - дороги. Соответственно вы ваши загружаемые точки должны привести к этому же вижу - подграфу, сначала поиском точек (вычисляя расстояние между искомой точкой и точкой города, определив минимальное).

Если это критично, так же можете попытаться искать попадание точек на дороги (но это в общем случае невозможно, дороги ведь в реальности не прямые, а в базе у вас только точки городов).

И само собой, точки в вашем запросе как то должны быть связаны, направление движения или хотя бы через ближайшие, чтобы вы могли различать из какого города и куда вы двигаетесь.

получив граф, вы легко определите, совпадает ли он с подграфом из базы, и конечно продумайте как будете разруливать ситуации, когда точек в базе нет.