Импорт planet.osm в PostgreSQL

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
там по шагам расписана установка и настройка сервака, попробую)

Ezhick · 2009-10-07T13:11:39.000Z

Не надо пробовать!

Там используется osm2pgsql который режет инфу под мапник, схема будет неполная. Я точно не знаю, что он режет ,но это будет совсем не зеркало родной базы!!!

Aleks_EE · 2009-10-28T16:13:33.000Z

Пытаюсь поднять рабочую копию базы с помощью осмосиса, но столкнулся с проблемой, с которой тут как раз пытаются помочь.

Caused by: org.postgresql.util.PSQLException: ERROR: constraint "pk_users" does not exist

Взял дамп.скл, который предложил Ежик. Проблема осталась
Взял транк из свна, поправил исходники немного (осмосис дропает индексы перед началом импорта), пересобрал, запустил… Свалилось на импорте пользователей

Дальше править исходники не стал - слишком разные схемы базы.

Вопрос - под какую версию АПИ выложен дамп.скл? --write-pgsql-0.5 не пошел, --write-pgsql-0.6 тоже не пошел, что удивительно но и простой write-pgsql не работает.

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