Приветствую читателей своего блога! Сегодня я подробно рассмотрю GIS библиотеку в MySQL, которую упоминал в статье про выборку точек в круге. Там я упомянул эту технологию вскользь и она оказалась не самым оптимальным решением, однако такие простые операции как приведенные в том примере, это лишь малая часть настоящих возможностей этого модуля. Сегодня я покажу что и как можно сделать с помощью ST_* функций в MySQL.
Сперва стоит рассказать о том, что это вообще за зверь. Данная функциональность является реализацией стандарта OpenGIS для SQL (подробнее тут) – отсюда названия функций в виде диковинной смеси Pascal и Snake case и принципы их работы. Полную документацию по тому как это работает в MySQL можно найти вот тут: (типы данных, функции). Нам же сейчас достаточно просто знать, что с помощью этого модуля можно производить совершенно любые операции с геоточками, полигонами, линиями, маршрутами и т.д.
Когда вообще нам может понадобиться возможность делать подобные операции? Например в ситуациях, когда невозможно или очень сложно описать полигон с помощью математических операторов. Круг или квадрат – удобные фигуры для представления их в виде математических выражений, но попробуйте, например, описать формулой Центральный Административный Округ города Москвы.
Конечно, можно привязать точки к неким заранее определенным регионам (например в реляционной базе просто связать id точки с id ЦАО во вспомогательной таблице), но это не решит всех проблем. Регионы не всегда входят друг в друга – мы не можем быть уверенными что точка входит в более крупный регион на основании того, что она входит в меньший по размеру, если меньший не находится внутри большего целиком. Например Чикаго находится в двух округах: Кук и Ду-Пейдж и точку внутри города нельзя автоматически отнести к одному из них. Временами регионы меняют границы или вообще исчезают. Также для анализа и выборок может потребоваться брать точки исходя не из нарезанных административных границ, а из каких-то других, имеющих смысл только в контексте конкретной задачи – например найдем современные города в границах Московского княжества.
Наконец точки и полигоны могут быть сформированы пользователем произвольно, что не даст нам никакой возможности заранее привязать одно к другому. Да и саму привязку, которую так или иначе делать придется, лучше осуществлять программно, а не в ручном режиме.
Работа с полигонами
Теперь, когда очевидность необходимости делать выборки такого рода ясна (кого я обманываю, она была ясна и так), давайте посмотрим, как это происходит в MySQL. Сегодня я покажу как с помощью геометрических методов осуществить выборку точек в произвольном полигоне – наиболее реальный кейс из тех, где без GIS не обойтись никак. В качестве базы данных будут использоваться уже знакомые по предыдущим статьям 3000 точек сгенерированные поверх Москвы. Чтобы задача была интереснее я сформилирую ее не абстрактно, а более конкретно. Пусть точки будут объектами недвижимости, а мы покупателями, которые ищут жилье в определенном районе.
К слову, подобное уже реализовано у Яндекс-недвижимости, так что можно одним глазком поглядывать туда и сравнивать. На бек, правда, заглянуть не получится 🙂
Наш покупатель – персона с высокими доходами, москвич в третьем поколении, хочет переселиться в центр города. Конкретнее его интересует недвижимость в пределах Садового кольца, исключая Замоскворечье и Заяузье.
Составим запрос, используя получившийся полигон. Я, как уже написал выше, немного схитрил и сгенерировал данный полигон с помощью сайта Яндекс Недвижимость. Полигон у меня получился вот такой:
55.735226,37.59747; 55.73571,37.593864; 55.738712,37.58614; 55.744717,37.583565; 55.75227,37.583565; 55.760788,37.58614; 55.76563,37.590088; 55.76911,37.59558; 55.771626,37.602276; 55.77279,37.609142; 55.77337,37.62562; 55.772205,37.63472; 55.77056,37.64296; 55.768143,37.64931; 55.76553,37.653603; 55.760983,37.656693; 55.754303,37.656864; 55.75062,37.655148; 55.749073,37.643475; 55.74772,37.64004; 55.749752,37.63163; 55.749752,37.62133; 55.748783,37.614807; 55.747234,37.610344; 55.745586,37.608112; 55.738033,37.60382; 55.735226,37.59747Я использую такой же синтаксис, как в прошлый раз – это будет полигон в формате OpenGIS, сформированный из точек выше. Обратите внимание на формат точек – он должен быть именно таким, а также на то, что полигон обязательно должен быть замкнутым.
(ST_WITHIN(
g,
ST_GeomFromText(
"POLYGON(
(
55.735226 37.59747,
...
55.738033 37.60382,
55.735226 37.59747
)
)", 4326
)
))Также существует альтернативный вариант как можно сделать то же самое: можно использовать ST_GeomFromGeoJSON и специальным образом сформированный json.
Что там с результатами запроса? Они очень четко формируют фигуру, которую нарисовал клиент:
Усложняем условие
Предположим, клиент понял, что это не совсем то, чего он хотел. Равно как и я: область, которую я нарисовал, имеет практически правильную форму и не в полной мере демонстрирует мощь геометрической выборки. Давайте испробуем фигуру посложнее.
55.715992,37.576684; 55.718221,37.576512; 55.725294,37.586297; 55.737208,37.597455; 55.741179,37.603634; 55.746892,37.606724; 55.749119,37.611531; 55.751442,37.620114; 55.751733,37.634018; 55.748344,37.642773; 55.740694,37.649468; 55.737499,37.647923; 55.736046,37.642945; 55.739435,37.642087; 55.745246,37.636422; 55.747473,37.62595; 55.745439,37.615136; 55.743019,37.611702; 55.733721,37.606381; 55.730525,37.598313; 55.719577,37.587498; 55.715508,37.578572; 55.715508,37.57634; 55.718027,37.57599На этот раз форма очень неудобная – и здесь “традиционными” методами справиться точно не получится. Давайте смотреть, что сервер выдаст на такой запрос.
Даже в таком случае MySQL вышел из ситуации достойно. Похоже, что все подходящие точки попали в выборку. Скорость также довольно хорошая: она измеряется сотыми долями секунды (правда тут я уже замерял только на 3000 демо-точек). Лучший результат – менее 0.01 сек на выборку “недвижимости” у реки, худший – около 0.05.
Итог
Это короткая статья – в цели сегодняшней заметки не входил детальный разбор тонкостей работы с GIS в SQL, раскрытие подводных камней или хардкорный обзор “подкапотного пространства”. Здесь я всего лишь показал огромную мощь того, что уже сейчас (а на самом деле довольно давно) доступно в коробке MySQL. Библиотека, которую мы сегодня рассмотрели позволяет получать очень точные результаты для геометрических запросов любой сложности в различных координатных системах и все это за считанные мгновения. Так что у хейтеров, которые заявляют, что MySQL ненастоящая СУБД с некоторых пор стало еще меньше доводов, а у пользователей этой системы больше инструментов.
На этом цикл заметок посвященных основам работы с геометрией в MySQL можно считать законченным. Разумеется, я не осветил и десятой части всего, что доступно программисту, однако для простых проектов, где геометрия не mission critical, а просто одна из фич, описанного выше будет более чем достаточно.