Какую СУБД следует использовать для хранения openstreetmap в виде графика?

Справочная информация:

Мне нужно сохранить в базе данных следующие данные:

  • узлы osm с тегами;

  • osm ребра с весами (то есть ребро между двумя узлами, извлеченное из 'way' из файла .osm).

Узлы, образующие рёбра, которые находятся в одном и том же наборе «путей», должны иметь те же теги, что и эти пути, то есть каждый узел в наборе узлов «путь», который представляет собой шоссе, должен иметь тег «шоссе».

Мне нужна эта структура, чтобы легко создавать график на основе различных фильтров, например. граф, состоящий только из узлов и ребер, которые являются шоссе, или граф «пешеходных дорожек» и т. д.

Проблема:

Раньше я не слышал о пространственном индексе, поэтому просто проанализировал файл .osm в базе данных MySQL:

  • все узлы в таблицу 'узлов' (с соответствующими столбцами координат) - ОК, в моем случае около 9000000 строк:

(INSERT INTO nodes VALUES [pseudocode]node_id,lat,lon[/pseudocode];

  • все пути к таблице 'рёбер' (обычно один способ создает несколько рёбер) - ОК, также около 9000000 строк:

(INSERT INTO edges VALUES [pseudocode]edge_id,from_node_id,to_node_id[/pseudocode];

  • добавить теги к узлам, вычислить веса для ребер - Проблема:

Вот проблемный скрипт php:

$query = mysql_query('SELECT * FROM edges');
$i=0;
while ($res = mysql_fetch_object($query))  {
$i++;
echo "$i\n";
$node1 = mysql_query('SELECT * FROM nodes WHERE id='.$res->from);
$node1 = mysql_fetch_object($node1);
$tag1 = $node1->tags;
$node2 = mysql_query('SELECT * FROM nodes WHERE id='.$res->to);
$node2 = mysql_fetch_object($node2);
$tag2 = $node2->tags;

mysql_query('UPDATE nodes SET tags="'.$tag1.$res->tags.'" WHERE nodes.id='.$res->from);
mysql_query('UPDATE nodes SET tags="'.$tag2.$res->tags.'" WHERE nodes.id='.$res->to);`

Nohup показывает вывод для 'echo "$ i \ n"' каждые 55-60 секунд (что может занять более 17 лет, если размер таблицы 'edge' превышает 9 000 000 строк, как в моем случае).

Htop показывает процесс / usr / bin / mysqld, который занимает 40-60% ЦП.

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

Вопрос:

Как я могу ускорить обновление этого SQL? Следует ли мне настроить какие-либо параметры конфигурации MySQL? Или мне следует использовать PostgreSQL с расширением PostGIS? Должен ли я использовать другую структуру для моих данных? Или надо как-то использовать пространственный индекс?


database graph postgis openstreetmap
person Kirill    schedule 24.04.2011    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Если я правильно вас понял, нужно обсудить две вещи.

Во-первых, ваша идея поставить метку шоссе на узлы старта и остановки. К узлу может быть подключено более одного ребра, куда поместить метку со второго ребра? Или третий или четвертый, если это перекресток? Причина, по которой тег шоссе помещается в таблицу краев, в первую очередь, заключается в том, что с реляционной точки зрения он принадлежит именно ему.

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

Я не работал с mysql и полностью согласен с тем, что вы, вероятно, получите гораздо больше удовольствия от перехода на PostGIS, поскольку PostGIS имеет гораздо лучшие пространственные возможности (даже если вам не нужны пространственные возможности для этой конкретной задачи) от того, что Я слышал.

Итак, если мы проигнорируем первую проблему и просто для демонстрации концепции скажем, что есть только два ребра, подключенных к одному узлу, и что каждый узел имеет два поля тегов. tag1 и tag2. Тогда это могло бы выглядеть в PostGIS примерно так:

UPDATE nodes set tag1=edges.tags from edges where nodes.id=edges.from;
UPDATE nodes set tag2=edges.tags from edges where nodes.id=edges.to;

Если вы отключите индексы, это должно быть очень быстро.

Опять же, если я правильно вас понял.

person Nicklas Avén    schedule 29.04.2011

arrow_upward
3
arrow_downward

PostgreSQL
Сама Openstreetmap использует PostgreSQL, поэтому я думаю, что это рекомендуется.
См.: http://wiki.openstreetmap.org/wiki/PostgreSQL

Вы можете увидеть схему базы данных OSM по адресу: http://wiki.openstreetmap.org/wiki/Database_Schema

Таким образом, вы можете использовать те же поля, типы полей и индексы, которые использует OSM для максимальной совместимости.

MySQL
Если вы хотите импортировать файлы .osm в базу данных MySQL, посмотрите:
http://wiki.openstreetmap.org/wiki/OsmDB.pm
Здесь вы найдете код Perl, который создаст таблицы MySQL, проанализирует файл OSM и импортирует его. в вашу базу данных MySQL.

Ускорение
Если вы выполняете массовое обновление, вам не нужно обновлять индексы после каждого обновления.
Вы можете просто отключить индексы, выполнить все обновления и повторно включить индекс.
Я предполагаю, что это должно быть намного быстрее.

Удачи

person Johan    schedule 24.04.2011
comment
Спасибо, Йохан, но я боюсь, что смогу потратить время на реализацию схемы, подобной OSM, с PostgreSQL, и по-прежнему столкнусь с той же проблемой выбора / обновления. Я проверил схему базы данных osm перед тем, как написать свой вопрос, и не нашел никаких ключей к разгадке моей проблемы. - person Kirill; 25.04.2011
comment
@Kirill, не знал, что вы уже проверили схему, но я кое-что придумал и обновил ответ. - person Johan; 25.04.2011
comment
Йохан, спасибо. Вероятно, я обнаружил проблему: выполнение «Выбрать * из краев», когда рёбер больше 9 миллионов, - не лучшая идея. Я написал сценарий, который выбирает * из краев, ограничивающих X, Y, и, похоже, он работает. Тем не менее, я серьезно подумываю о переходе на структуру данных OSM PostgreSQL и выполнении из нее «извлечения магистралей». - person Kirill; 26.04.2011