OSM to MS SQL

Всем добрый день.
У нас стоит задача в нашем ПО (С#) отображать карту для размещения на ней объектов. Мы выбрали и отображаем OSM. Для каждого пользователя при начальной загрузке окна карта должна спозицироваться на определенный город и адрес, которые известны. Нужно узнать координаты WGS84. Хотим выгрузить OSM в MSSQL и запросами определять координаты. Подскажите как осуществить такую выгрузку. Если есть более элегантный способ, подскажите. Будем очень благодарны.

Спасибо

Если ради этого, то проще на osm.org.ru один раз геокодировать адрес пользователя и сохранить координаты в его настройки. А так насколько мне известно, полноценная гис-надстройка над базой есть только для postgresql.

Многое зависит от того, на основании чего вы хотите получать координаты. Что вы хотите указывать в параметрах запроса?
Если на основании адреса - это типовая задача геокодирования, есть несколько открытых реализаций на базе OSM, самый известный - Nominatim.
Если же на основании других данных - озвучьте их.

Я хочу получить координаты по известному адресу. Хочется сделать чтобы это не выполнялось каким-то внешним сервисом (хотя если не получится SQL то придется). Если в OSM хранятся координаты каждой точки объекта, то можно извлекать любой (например, угол дома). Получив эти координаты позицировать карту.

Нет, здесь связь один ко многим. У одного пользователя несколько адресов (могут быть тысячи) и в зависимости в какой объект он будет входить нужно отображать определенное место. У места известен полный адрес. Мне нужно получить координаты WGS84.

Ну тогда ваш путь не лёгок. Лопатьте форум, читайте вики, всё это было, курите репозитории nominatium и openstreetmap.ru, благо код на гитхабе открыт. Заливка в базу тут не самое сложное, хотя и mssql тут за гранью обычного.

Задача геокодирования слишком сложна, чтобы её можно было описать на полях этого форума.
Воспользуйтесь более простым набором картографических данных, либо изучайте вики и гитхаб по словам «nominatim» и т.п.
MS SQL — очень необычный выбор, найти хотя бы рабочие примеры с ним близко к невозможному.

Также, повторю вопрос из другой темы: почему вы решили заняться этим сейчас, и как думаете, откуда на нашем форуме в последний месяц столько людей, которые что-то хотят сделать с картой? Обычно один-два в полгода, а тут — прямо толпа.

Учтите, что сейчас далеко не вся адреска внесена, особенно по мелким городам. Поэтому если адрес не нашелся, то надо искать соседние дома и интерполировать, а если вообще домов нет - то искать линию улицы, а если и ее нет - искать город.

https://github.com/kiselev-dv/gazetteer
Можете готовые данные залить в MS SQL можете исходники расковырять.

А mysql 5.6 ?

А что там реализовано?

http://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html
http://dev.mysql.com/doc/refman/5.6/en/spatial-analysis-functions.html

Может я что-то не понимаю, но примерный алгоритм действий:

  1. Скачиваем planet.osm
  2. Далее, парсим и заливаем в MSSQL. Табличка примерно такая: LAT, LON, STREET, HOUSENUMBER и так далее. При конвертации/заливке, если точка имеет адрес, то заносим как есть, если адрес висит на вее, то считаем среднюю координату и заливаем как точку.
  3. ???
  4. Profit!!!

Построить индекс (адрес - координаты) на MS SQL вам будет тяжело. Т.к. в большинстве своем средстав для работы с осмом расчитывались на связку с постгисом. Тоесть просто залить осм в бд построив геометрию на MSSQL средствами з коробки у вас скорее всего не выйдет. Придется писать свой парсер осм или переделывать готовый. Поэтому смотрите где можно взять готовые данные для индекса.

Например вот: https://github.com/kiselev-dv/gazetteer/tree/develop/Gazetteer
Есть уже готовые выгрузки в json http://tr1.nowtaxi.ru/dumps/ где уже посчитана геометрия. Для домов рассчитаны полигоны городов и областей в которые они попадают, рассчитаны улицы по которым адресованы дома либо окресные улицы и т.п. Обработаны пои.

Вам по сути надо будет прочитать json, да загрузить его к себе разложив по табличкам в том виде в котором вам это нужно. Дальше уже морфологию и поиск можете писать на MS SQL (в общем то на чем угодно, привязок к закрытым особенностям той или иной субд там нету).

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

Этот алгоритм не рабочий. Примерно соответствует вот этому:

:roll_eyes:

В каком месте он не рабочий?
Написать конвератцию osm->sql? Или запрос “SELECT * FROM ADDRESSES WHERE STREET = ‘Краснопопская’ AND HOUSENUMBER = 6”?
Понятное дело, что базу желательно нормальзовать. Но это уже совсем никак с оsm не связанно.

Конкретно пункт 2, я бы сделал следующим образом:

  • скачал planet.pbf
  • конвернтул в .o5m
  • далее отфильтровал бы по наличию тегов addr:*
  • дальше простой скрипт, например, на питоне, который собственно бы клал сразу базу данных или, что проще - писал соответствующий .sql файл.

это лучшая тема месяца

ynx, а город? А область? А associatedStreet? А addr:interpolation? А addr:street2?
А собрать мультиполигоны для областей из отфильтрованного o5m? А улицы? Да даже чтобы посчитать центроид домика надо в начале геометрию домика загрузить. (Про --all-to-nodes я знаю, но на больших объемах он запросто ломается).
Что-то скрипт уже не выглядит простеньким.

А что город? Ещё один столбец в моей табличке. Тоже самое с областью. И индексом. И корпусом. И строением. И что там ещё бывает.
associatedStreet - надо просто распарсить ещё и релейшины. Не вижу с этим проблем. Их в любом случай расковыривать, так как адрес может на отношении висеть.
addr:interpolation - с этим дело не имел, выглядит как извращение, но на первый взгляд особых проблем не видно.
addr:street2 - это вообще не проблема, просто учесть в структуре БД что два адреса могут указывать на одну точку. Или, в моей табличке просто будет две записи с разными улицами, но одинаковыми номерами домов и координатами. Как я уже писал - надо будет это всё нормализовать и всё.
Не уверен, что понял про мультиполигоны и улицы - зачем их собирать? Нас же координаты улицы не интересуют.
Про валится или нет, это уже вообще совсем другой вопрос - если скрипт не влезает в память, то можно обрабатывать регионами или увеличить количество памяти.

У меня таким способом выковыриваются города.

#!/bin/sh
/work/dav/ynx/osmfilter/osmconvert /work/Downloads/torrents/planet-141105.osm.pbf -o=/work/dav/ynx/osmfilter/planet.o5m

#!/bin/sh
/work/dav/ynx/osmfilter/osmfilter /work/dav/ynx/osmfilter/planet.o5m --keep="place=town =city" --drop="admin_level=10 =11" --drop-tags="source= created_by=" -o="/work/dav/ynx/osmfilter/cities.osm" --drop-version --drop-author

Тут получается cities.osm размер около 650 мегабайт.
Далее, скрипт на питоне, который складывает в xml все города, отрабатывает примерно за минуту. Для веев считает центр, для релейшенов ищет точку role=label.