Импорт planet.osm в PostgreSQL

Chertov_Maxim · 2009-09-26T16:39:46.000Z

Возникла задача получить базу точно такую же как на сервере OSM’а.
Скачал planet.osm, распаковал, создал базу в PostgreSQL с помощью sql-файла… Нашел его на странице в вики Osmosis

Сам Osmosis я скачал и долго мучился… не работает он под виндой(
Переписал кое-как сам osmosis.bat из папки bin.
Прописал в нем жестко пути, но никакого толку пока не добился… запускается, выдает какие-то ошибки.

Помогите импортнуть базу в PostgreSQL!
Если кто-нибудь это уже делал, то наверняка есть настроенный и рабочий osmosis, которому нужно сунуть планет.осм, хост сервака PostgreSQL, имя базы, логин/пароль.
Выложите, пожалуйста, если у кого есть такая сборка osmosis’а. Если нету, буду его дальше мучать…

Кстати, сама структура базы на сервере именно та что в http://gweb.bretth.com/apidb06-pgsql-latest.sql?
На страничке вики Database schema юзают MySQL, но насколько я понимаю этот вариант уже не используется… просто страничку не изменяли.
MySQL проще конечно)

KekcuHa · 2009-09-26T21:18:45.000Z

Стукнись к Ezhik в личку, он на этом собаку съел.
ЗЫ: Осмозис под виндой нормально работает.

Chertov_Maxim · 2009-09-26T23:35:24.000Z

KekcuHa, спасибо! Если ничего не получится постучусь!
Пока наметились некоторые сдвиги…

Скачал osmosis, закинул в программ файлз, посмотрел либы в lib/default, скопировал сам osmosis.jar в lib/default к остальным jar’ам и написал вот такой вот bat:

@ECHO OFF
set MYAPP_HOME=C:\Program Files\osmosis-0.31
set LIBDIR=%MYAPP_HOME%\lib\default
set LIBS="%LIBDIR%\osmosis.jar";"%LIBDIR%\bzip2-20090327.jar";"%LIBDIR%\commons-logging.jar";"%LIBDIR%\jpf-1.5.jar";"%LIBDIR%\mysql-connector-java-5.1.6.jar";"%LIBDIR%\postgis-1.3.2.jar";"%LIBDIR%\postgresql-8.4-701.jdbc4.jar";"%LIBDIR%\stax2-api-3.0.1.jar";"%LIBDIR%\woodstox-core-lgpl-4.0.3.jar"
SET EXEC=java -cp %LIBS% org.openstreetmap.osmosis.core.Osmosis %*
%EXEC%
@ECHO ON

Тут я в LIBS просто перечислили все jar файлы, которые нашел в lib/default и еще сам osmosis.jar указал.
Этот мой новый бат я закинул в C:\WINDOWS\system32 для удобства. Теперь откуда бы я не вызвал из командной строки osmosis все работает. Если просто osmosis выполнить без параметров, что-то выдает, но явно не ругается… ошибок нет.
Далее я делаю бат, который определяет параметры osmosis’а:

osmosis --read-xml-0.6 file="T:\planet-090916.osm" --write-pgsql-0.6 host="localhost" database="newdb" user="postgres" password="qwerty" validateSchemaVersion=no
pause

База создана, sql-скриптец http://gweb.bretth.com/apidb06-pgsql-latest.sql выполнил через консоль (psql) т.к. в pgAdmin III он не выполняется. Там при добавлении данных в таблицы используют COPY FROM stdin… через psql все отлично выполнилось, структура базы видна, данные в таблицах есть. (например в schema_migrations)

Далее пытаюсь запустить свой бат. Получаю вот такую фигню:

C:\temp>osmosis --read-xml-0.6 file="T:\planet-090916.osm" --write-pgsql-0.6 host="localhost" database="newdb" user="postgres" password="qwerty" validateSchemaVersion=no
27.09.2009 3:20:53 org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31
27.09.2009 3:20:53 org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
27.09.2009 3:20:53 org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
27.09.2009 3:20:53 org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
27.09.2009 3:20:53 org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-xml-0.6 failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to execute statement.
        at org.openstreetmap.osmosis.core.pgsql.common.DatabaseContext.executeStatement(DatabaseContext.java:106)
        at org.openstreetmap.osmosis.core.pgsql.v0_6.PostgreSqlWriter.initialize(PostgreSqlWriter.java:281)
        at org.openstreetmap.osmosis.core.pgsql.v0_6.PostgreSqlWriter.process(PostgreSqlWriter.java:902)
        at org.openstreetmap.osmosis.core.xml.v0_6.impl.BoundElementProcessor.end(BoundElementProcessor.java:85)
        at org.openstreetmap.osmosis.core.xml.v0_6.impl.OsmHandler.endElement(OsmHandler.java:108)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanStartElement(Unknown Source)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
        at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
        at javax.xml.parsers.SAXParser.parse(Unknown Source)
        at javax.xml.parsers.SAXParser.parse(Unknown Source)
        at org.openstreetmap.osmosis.core.xml.v0_6.XmlReader.run(XmlReader.java:108)
        at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: constraint "pk_users" of relation "users" does not exist
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345)
        at org.openstreetmap.osmosis.core.pgsql.common.DatabaseContext.executeStatement(DatabaseContext.java:103)
        ... 19 more
27.09.2009 3:20:53 org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
        at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
        at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:85)
        at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

C:\temp>pause
Для продолжения нажмите любую клавишу . . .

Если запустить без validateSchemaVersion=no, то вообще вот такая фигня:

C:\temp>osmosis --read-xml-0.6 file="T:\planet-090916.osm" --write-pgsql-0.6 host="localhost" database="newdb" user="postgres" password="qwerty"
27.09.2009 3:23:22 org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31
27.09.2009 3:23:22 org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
27.09.2009 3:23:22 org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
27.09.2009 3:23:22 org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
27.09.2009 3:23:22 org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-xml-0.6 failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to read the schema version from the schema info table.
        at org.openstreetmap.osmosis.core.pgsql.common.SchemaVersionValidator.validateDBVersion(SchemaVersionValidator.java:92)
        at org.openstreetmap.osmosis.core.pgsql.common.SchemaVersionValidator.validateVersion(SchemaVersionValidator.java:52)
        at org.openstreetmap.osmosis.core.pgsql.v0_6.PostgreSqlWriter.initialize(PostgreSqlWriter.java:242)
        at org.openstreetmap.osmosis.core.pgsql.v0_6.PostgreSqlWriter.process(PostgreSqlWriter.java:902)
        at org.openstreetmap.osmosis.core.xml.v0_6.impl.BoundElementProcessor.end(BoundElementProcessor.java:85)
        at org.openstreetmap.osmosis.core.xml.v0_6.impl.OsmHandler.endElement(OsmHandler.java:108)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanStartElement(Unknown Source)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source)
        at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
        at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
        at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
        at javax.xml.parsers.SAXParser.parse(Unknown Source)
        at javax.xml.parsers.SAXParser.parse(Unknown Source)
        at org.openstreetmap.osmosis.core.xml.v0_6.XmlReader.run(XmlReader.java:108)
        at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "schema_info" does not exist
  ╧ючшЎш : 21
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
        at org.openstreetmap.osmosis.core.pgsql.common.SchemaVersionValidator.validateDBVersion(SchemaVersionValidator.java:73)
        ... 20 more
27.09.2009 3:23:22 org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
        at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForComple
tion(Pipeline.java:146)
        at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:85)
        at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

C:\temp>pause
Для продолжения нажмите любую клавишу . . .

Почему такое происходит? Чего это за “pk_users” и “schema_info” такие и что с ними не так? Может базу собрал не правильно?

Ezhick · 2009-09-27T08:54:03.000Z

Максим, 2 вопроса:

почему именно постгре? Если умеете работать с мусклом и с ним вам проще - юзайте его, осмосис умеет в него писать
посмотрите в вашей базе, пустой, есть ли там таблица schema_info. Если нет - значит скрипт, которым вы создавали базу устаревший. К сожалению разработчики осмосиса и базы ОСМ очень часто забывают обновлять инфу в вики - она там может отставать на несколько изменений схемы базы

У меня осм под постгре запущен, но не просто под постгре, а под связку постгре+постгис. Не знаю, нужно ли вам такое. ибо это под довльно специфические задачи и существенно сложнее в эксплуатации - двое-трое суток на импорт и потом еще двое-трое на построение индексов.

Что могу предложить - если вы захотите перейти на мускл - я вам могу скинуть актуальную схему базы данных, 100% соответствующую текущей ОСМовской. Ну или такую же актуальну. но для постгре.

Ivan_Komarov · 2009-09-27T09:38:34.000Z

Ezhick:

Что могу предложить - если вы захотите перейти на мускл - я вам могу скинуть актуальную схему базы данных, 100% соответствующую текущей ОСМовской.

Если можно, еще и с общественностью поделитесь, пожалуйста

Chertov_Maxim · 2009-09-27T10:34:23.000Z

Сначала выбрал потому что сам openstreetmap перешел на PostgreSQL, но вобще постгри мне даже больше понравился.
Саму базу я пока поднял под виндой… хотя винда эта в виртуальной машине на убунте крутится. Для тестов пока достаточно.

Если дело в том, что у них обновления выкатываются несвоевременно, то думаю можно и постгри юзать.
Нашел какой-то скриптец… http://trac.openstreetmap.org/browser/sites/rails_port/db/create_database.sql?rev=2778 двухлетней давности, там тоже нет schema_info. Других sql-файлов в svn не нашел, может там как-нибудь из кода базу создают… должно же оно где-то обновляться(

В своей тестовой базе schema_info я не нашел, есть только schema_migrations… в ней содержаться все версии… числа от 1 до 25… я так понял версии базы.

По ссылке http://gweb.bretth.com/apidb06-pgsql-latest.sql должен ведь лежать самое последнее обновление… т.к. “latest”, если это конечно относится к svn как-нибудь. На данный момент apidb06-pgsql-latest.sql это как раз 25 версия.

В комментах в этом файле стоит дата:

--
-- PostgreSQL database dump
--

-- Started on 2009-04-19 13:20:00 EST

Кстати, слышал что есть девелоперский сервер, на который можно залезть и что-нибудь там поделать…
можно ли оттуда снимать дамп базы без данных при необходимости?

Ezhick, выложите, пожалуйста, sql для постгри, если заработает буду сидеть на этой базе)

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

Спасибо!

Ezhick · 2009-09-27T19:48:59.000Z

Правильно обновление базы происходит через ruby-скрипт в составе rails port. Подробности по нему - в вики. Суть - можно поднять у себя сервер, идентичный ОСМовскому. И обновлять его из svn. Оттуда же обновится и схема базы. Именно ее я вам и предлагаю выложить. Учитывая просьбы коллег - выложу сюда. Но только завтра, сейчас очень спать хочется.

Chertov_Maxim · 2009-09-28T05:48:59.000Z

Нашел какой-то 001_create_osm_db.rb в db\migrate…
блин, руби - жуть) раньше с ним не работал…

Ezhick · 2009-09-28T08:51:13.000Z

create еще далеко не все, нужно потом еще 49 (ну или около того) миграций наложить.

По выкладыванию схемы - сегодня только вечером сделаю, на работе позакрывали порты нафиг, не могу удаленной зайти на сервак

Ezhick · 2009-10-07T04:59:27.000Z

Сорри, завозился и забыл про эту тему.

Ловите свежую структуру для PostgreSQL.

http://ifolder.ru/14365295

x10kHz · 2009-10-07T08:08:40.000Z

Ezhick, Спасибо Большое!)

запросы не выполняются… под виндой не хватает какого-то /usr/lib/postgresql/8.3/lib/liblwgeom
ставлю убунту под виртуалку, надоело мучаться с виндой)

Ezhick · 2009-10-07T08:18:43.000Z

Я под виндой завести это все не смог, плюнул и поставил убунту. В ней все ок. Только вот производительности моего компа под полнуй дамп планеты маловато… Видимо придется строить отдельные производные таблицы под интересующие регионы, ибо апгрейд сервера пока не планировался…

Ivan_Komarov · 2009-10-07T08:21:12.000Z

Спасибо!

x10kHz · 2009-10-07T11:26:21.000Z

Ezhick, спасибо еще раз!
Настроил базу под убунтой, постгри настроил, dump.sql залился с первого раза и без ошибок, база создана!
Попробовал импортнуть osmosis’ом кусок Москвы… получаю те же ошибки что и под виндой(((

Осмосис, кстати, заработал после небольших мучений) Ничего в нем руками не допиливал…

Импорт делаю двумя скриптами, лежат они в osmosis-0.31 в папке bin

run.sh

clear all
./osmosis --read-xml-0.6 file="test.osm" --write-pgsql-0.6 host="localhost" database="osmdb" user="postgres" password="postgres"

При запуске в ошибках получаю:

Caused by: org.postgresql.util.PSQLException: ERROR: отношение "schema_info" не существует

runnov.sh

clear all
./osmosis --read-xml-0.6 file="test.osm" --write-pgsql-0.6 host="localhost" database="osmdb" user="postgres" password="postgres" validateSchemaVersion=no

Тут получаю:

Caused by: org.postgresql.util.PSQLException: ERROR: констрейнт "pk_users" не существует

Не понимаю в чем дело… опять что-то с базой? В вашем последнем дампе schema_info нету, да и чего это такое вообще… может ее уже убрали уже, а osmosis 0.31 не последняя версия?

Ezhick:

Я под виндой завести это все не смог, плюнул и поставил убунту. В ней все ок. Только вот производительности моего компа под полнуй дамп планеты маловато… Видимо придется строить отдельные производные таблицы под интересующие регионы, ибо апгрейд сервера пока не планировался…

Кстати, а какое железо сейчас стоит? Если запрашивать небольшие области, то PostgreSQL запрос долго выполняет?
С чистым xml даже пытаться не хочу работать… это же ужас… в десятках гигов искать)

Ezhick · 2009-10-07T11:36:16.000Z

Запрос неправильный. Смотри ключ api-db.

железо - p4 - 2,52, 1.5 Гб памяти. По запросам ничего не скажу - поставил, настроил обновление и на большее просто не было времени. Но импорт .построение индексов и постгисовских колонок занял чуть ли не неделю…

UPD
попробовал простенький запрос
select way_id from way_tags where k = ‘oneway’ and v = ‘no’ limit 1000

3 секунды почти. Индекса по этим колонкам нет.

Ezhick · 2009-10-07T11:38:49.000Z

Кстати, есть ще одна схема базы под посгре - постгре+постгисовская. Строится скриптами из осомсиса (см. папку scripts в осмосисе). Там добавляются постгисовские колонки дополнительные. Вот в нее осмосис у меня импорт делает без проблем!

x10kHz · 2009-10-07T12:59:53.000Z

В папке scripts нашел
pgsql_simple_schema_0.6.sql – Это я так понял структура самой базы, остальные не знаю для чего
pgsql_simple_schema_0.6_action.sql
pgsql_simple_schema_0.6_bbox.sql
pgsql_simple_schema_0.6_linestring.sql

Там есть schema_info, кстати!
Но структура маленькая, нету функций, например… типы какие-то не известны.
Я так понял это накидывается на postgis’овскую базу… в вашем дампе функции были. поэтому я взял отправил на базу запросы из dump.sql, а потом из pgsql_simple_schema_0.6.sql
чего-то там было, ошибки какие-то что мол уже существует…

при импорте словил

Caused by: org.postgresql.util.PSQLException: ERROR: нельзя удалить констрейнт pk_users на таблица users по причине наличия зависимых объектов

на schema_info вроде уже не жалуется.

У девелоперов нету образа виртуальной машины с установленным сервером?
Где-то в вики проскакивало что-то подобное… нийти не могу

Ezhick · 2009-10-07T13:02:16.000Z

pgsql_simple_schema_0.6.sql - это отдельная схема! Ее не надо накатывать на ту, что я дал. Это разные схемы!!!

pgsql_simple_schema_0.6_action.sql
pgsql_simple_schema_0.6_bbox.sql
pgsql_simple_schema_0.6_linestring.sql

Добавляют постгисовские колонки и индексы к базе на основе pgsql_simple_schema_0.6.sql.

Ezhick · 2009-10-07T13:03:04.000Z

x10kHz:

У девелоперов нету образа виртуальной машины с установленным сервером?
Где-то в вики проскакивало что-то подобное… нийти не могу

Сильно сомневаюсь, что там будет что-то свежее

x10kHz · 2009-10-07T13:08:02.000Z

Вот тему нашел… VMWare Image
там по шагам расписана установка и настройка сервака, попробую)