Встала задача организовать административные центры в чёткую иерархию по принципу матрёшки, например, Украина — Крым — ЮБК — Ялта, и исправить имеющиеся ошибки в текущей базе данных.
В этой статье я расскажу, как я решил эту проблему с помощью KML-файлов обрамляющих границ и Postgres+Postgis.
Дело в том, что база, которой мы пользуемся для нашего проекта SunnyRentals, не коммерческая (user generated, open source) и в ней есть ошибки. Например, самый частый случай — множесто городов приписаны к стране, но не относятся ни к одному из её регионов и областей, мы называем их orphaned cities.
Плюс к этому, бизнес у нас туристический, так что административно-политическое дробление стран не всегда подходит, иногда нет-нет да и приходится добавлять туристические регионы вручную. Например, такого административного региона как «Южный берег Крыма» нет, но есть такой туристический район, по которому туристы выбирают, куда ехать — ищут «дома на ЮБК», а не «дома в ялте, гаспре, гурзуфе и вообще где-то там».
Вопрос в том, как автоматически найти такому административному региону родителя (Крым) и задать всех входящих в него детей (города вроде Ялты и Судака).
К слову: у нас страна состоит из регионов, регионы состоят из областей, области из подобластей. Прямо как смерть Кощеева…
Для решения этой задачи было решено использовать данные с границами регионов, и по ним сопоставить родителей и детей.
Готовим данные
Базу данных границ регионов интересующей вас части мира вполне можно найти в интернете. Я использовал файлы для Google Earth — либо KML (XML c координатами), либо KMZ (KML в zip-архиве), их удобно смотреть прямо в Google Earth.
KMZ автоматически превратить в KML не получилось — ломаются Unicode-символы в названиях топонимов, так что я открыл KMZ-файлы в Google Earth и сохранил как KML. Конечно, «недостойно талантливому человеку тратить, подобно рабу, часы на вычисления, которые, безусловно, можно было бы доверить любому лицу, если бы при этом применить машину», но если бы файлов было больше, точно искал бы средство автоматизации.
Второй путь, если нужны кастомные области вроде ЮБК — отрисовать руками в Google Earth и сохранить в KML-файл.
В файлах данных, которые были у меня, были отличные регионы каждой области, но не было взаимосвязей между родителями и детьми. Например, отдельный файл «все регионы страны», а отдельно — «все области страны». В принципе, это и есть задача, которую нам надо решить, но для этого у нас теперь есть все данные.
Готовим платформу
Поначалу я хотел использовать MySQL, благо у них есть поддержка spatial-типов данных и операций над ними, но потом оказалось, что эти самые операции реализованы в сильно упрощённом виде и для реальных задач непригодны. Упрощённый вид означает, что MySQL работает только с MBR — прямоугольником, описывающим полигон. Получается, вместо красивых границ Крыма операции будут производиться с тупым прямоугольником вокруг него, а подсчёт реальной площади заменился бы просто площадью этого прямоугольника (что будет использоваться для решения данной задачи). Это делает MySQL непригодным для этой задачи.
Используем Postgres и устанавливаем на него библиотеку PostGis для работы с географическими типами данных.
Загружаем данные
Я подготовил несколько таблиц — country, region, area, subarea. Для каждой из них также были сделаны таблицы с суффиксом "_boundary", поскольку один и тот же регион может иметь больше чем одну границу (например, острова).
Сами данные можно загружать 2 способами:
- будучи незнакомым с арсеналом PostGis, я написал свой обработчик KML — по сути, XML-ноды с координатами парсились и превращались в SQL-полигоны,
- альтернатива ручной работе — встроенная в PostGis функция
ST_GeomFromKML
Чистим данные
Полученные границы вышли слишком уж детализированными, с большим числом точек. Это означает более долгую обработку, а также больше данных для отправки клиенту в браузер через AJAX, если эти полигоны надо отрисовать на карте. Забегая вперёд, скажу, что таким образом размер данных удалось уменьшить в 10 раз, и ajax-запросы стали «летать».
Поэтому я решил упростить полигоны, благо для этого в PostGis есть функция ST_Simplify — ей на вход надо дать полигон и значение сглаживания.
Поигравшись с тестовыми данными, я выбрал параметр сглаживания равным 0.001.
Запрос получился такой:
UPDATE "subarea_boundary"
SET "path" = ST_Simplify("path", 0.001);
Сравните сами «до» и «после»:
Подбираем родителя
В PostGis есть разные функции для выяснения отношений между полигонами, но я пришёл к выводу, что лучше всего использовать следующую идею: территория считается дочерней в том случае, если дочерняя территория входит в родительскую более, чем наполовину.
В формульном виде это выглядит так: [площадь пересечения] / [площадь дочерней территории] > 0.5
В терминах функций PostGis это выглядит так:
ST_Area(ST_Intersection(region_path, area_path)) / ST_Area(area_path) > 0.5
Далее, я написал несколько похожих хранимых процедур для нахождения нужного родителя заданной территории.
Вот пример нахождения региона-родителя для области:
CREATE OR REPLACE FUNCTION "SuggestRegion" ("area_path" geometry) RETURNS integer AS
'DECLARE
admId integer := 0;
BEGIN
SELECT INTO admId "parent_id"
FROM "region_boundary"
WHERE ST_Area(ST_Intersection(region_path, area_path))/ST_Area(area_path) > 0.5
LIMIT 1;
RETURN admId;
END;'
LANGUAGE "plpgsql" COST 100
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
Чтобы получить ID региона, в который входит данная область, надо прогнать полигоны данной области через эту функцию:
SELECT "SuggestRegion" ("path") AS parent_id
FROM area_boundary
WHERE area_id = XXX
LIMIT 1;
Если полученное ID больше нуля, то можно сохранить полученный регион как регион-родитель данной области.
Для городов ещё проще, так как город задан только координатой центра — применяем функцию проверки полного вхождения ST_Within.
Надеюсь, данная статья окажется полезной для других любителей геодезии.
Автор: alex4
