PostGis вопросы по запросам к БД

defond · March 6, 2014, 10:26am

Вот эта статья актуальна? http://wiki.openstreetmap.org/wiki/RU:Osm2pgsql

Мне нужно загрузить в БД данные:

допустим, Краснодарского края (шейп-файл скачен).
топографические данные - дороги, реки, поля, леса и т.п. Города (без названий улиц и номеров домов, а так же без самих домов).
POI - нужны только аэродромы+заправки+больницы+туристические достопримечательности.

Как я понял из описания, при помощи Osm2pgsql я могу загрузить в БД только эти данные. Я правильно понял?

Может есть еще какие то интересные статьи или инструкции (очень желательно на русском)?

dkiselev · March 6, 2014, 11:12am

Вроде как osm2pgsql умеет загружать данные сохраняя атрибуты в hstore колонку (если что осмозис точно умеет но он тормоз).
С такой бд уже можно “нарезать” слоев с любыми данными какие хочется.

Выглядит это примерно так https://github.com/kiselev-dv/EasyAtlas/blob/master/load-layers.sql

А поспешил, вам наоборот надо поменьше данных, а не все подряд с последующей фильтрацией.

defond · March 6, 2014, 11:20am

Спасибо!

Посмотрю.

defond · March 6, 2014, 11:23am

Сейчас пытаюсь разобраться с osm2pgsql.

У меня два файла default.style

/usr/share/osm2pgsql
/usr/share/osm2pgsql/osm2pgsql

Как я понял, надо отредактировать файл default.style, оставив только те тэги, которые мне нужны.

Если я правильно понял, то какой файл мне надо редактировать.

И второй, очень глупый вопрос - в папке /usr/share/osm2pgsql/osm2pgsql лежит БД. Её тоже надо было использовать?

defond · March 6, 2014, 1:24pm

Александр, если Вам не тяжело, чирканите мне на мыло, оно у меня подсвечено. У меня есть небольшой вопрос по osm2pgsql, который на форуме задавать не хочется.

Заранее спасибо.

defond · March 10, 2014, 2:27pm

Пытаюсь загрузить данные в БД. Выкидывает непонятную для меня ошибку:


defond@defond-1201T:~$ sudo osm2pgsql -U defond -d RU-KDA.osm.bz2
[sudo] password for defond: 
osm2pgsql SVN version 0.80.0 (64bit id space)

Usage error. For further information see:
	osm2pgsql -h|--help
defond@defond-1201T:~$ sudo osm2pgsql -U defond -d ./home/defond/Загрузки/RU-KDA.osm.bz2
osm2pgsql SVN version 0.80.0 (64bit id space)

Usage error. For further information see:
	osm2pgsql -h|--help
defond@defond-1201T:~$ sudo osm2pgsql -U defond -d '/home/defond/Загрузки/RU-KDA.osm.bz2'
osm2pgsql SVN version 0.80.0 (64bit id space)

Usage error. For further information see:
	osm2pgsql -h|--help
defond@defond-1201T:~$

freeExec · March 10, 2014, 2:40pm

./home/defond/Загрузки/RU-KDA.osm.bz2

Находясь в домашней папке (~) этот путь неверен. Точку в переди надо убрать.

defond · March 10, 2014, 2:49pm

Конечно. Поставил ошибочно.

Поэтому использовал обычный путь:


defond@defond-1201T:~$ sudo osm2pgsql -U defond -d '/home/defond/Загрузки/RU-KDA.osm.bz2'
osm2pgsql SVN version 0.80.0 (64bit id space)

Usage error. For further information see:
	osm2pgsql -h|--help

Не пойму, что за ерунда…

defond · March 10, 2014, 2:52pm

Насколько понимаю, дело не в пути, а в командах. Но команда вроде верная…

Suleyma · March 11, 2014, 4:53am

defond:


defond@defond-1201T:~$ sudo osm2pgsql -U defond -d '/home/defond/Загрузки/RU-KDA.osm.bz2'
osm2pgsql SVN version 0.80.0 (64bit id space)

Usage error. For further information see:
	osm2pgsql -h|--help

Не пойму, что за ерунда…

А имя базы? если уж -d указали.

defond · March 11, 2014, 9:06am

Уже лучше - ругается на проверку пользователя.


defond@defond-1201T:~$ sudo osm2pgsql -U defond -d defond /home/defond/Загрузки/RU-KDA.osm.bz2
osm2pgsql SVN version 0.80.0 (64bit id space)

Error: Connection to database failed: ВАЖНО:  пользователь "defond" не прошёл проверку подлинности (Peer)

Такой пользователь есть + пароли совпадают.

Сейчас попробую поиграться с настройками доступа. Но через pgAdmin доступ есть и без проблем…

defond · March 11, 2014, 9:11am

По поводу наличия пользователя:


defond@defond-1201T:~$ sudo su - postgres
postgres@defond-1201T:~$ psql
psql (9.3.3)
Введите "help", чтобы получить справку.

postgres=# create user defond login;
ОШИБКА:  роль "defond" уже существует
postgres=# create database defond owner defond;
ОШИБКА:  база данных "defond" уже существует
postgres=# \c defond
Вы подключены к базе данных "defond" как пользователь "postgres".
defond=#

Suleyma · March 11, 2014, 9:34am

defond:

Уже лучше - ругается на проверку пользователя.
defond@defond-1201T:~$ sudo osm2pgsql -U defond -d defond /home/defond/Загрузки/RU-KDA.osm.bz2
osm2pgsql SVN version 0.80.0 (64bit id space)

Error: Connection to database failed: ВАЖНО:  пользователь "defond" не прошёл проверку подлинности (Peer)
Такой пользователь есть + пароли совпадают.

Сейчас попробую поиграться с настройками доступа. Но через pgAdmin доступ есть и без проблем…

Тут уже зависит от настройки постгреса. Я вижу используется метод Peer - это значит имя пользователя
постгреса должно совпадать с системным (попробуйте без sudo)

или настроить чтобы был md5 (pg_hba.conf)
local defond defond md5

или osm2pgsql указывать например -h localhost

defond · March 11, 2014, 9:36am

Теперь и через pgAdmin 3 зайти тоже не могу

Вот, что у меня в файле pg_hda.conf.sample:


# TYPE  DATABASE        USER            ADDRESS                 METHOD

@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local   all             all                                     @authmethodlocal@
# IPv4 local connections:
host    all             all             127.0.0.1/32            @authmethodhost@
# IPv6 local connections:
host    all             all             ::1/128                 @authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@#local   replication     @default_username@                                @authmethodlocal@
#host    replication     @default_username@        127.0.0.1/32            @authmethodhost@
#host    replication     @default_username@        ::1/128                 @authmethodhost@

Suleyma · March 11, 2014, 9:58am

Это sample, а в рабочем что? (что-то вроде/etc/postgresql/9.3/main/pg_hba.conf)

defond · March 11, 2014, 10:00am

Переделал. Сделал вот так:


# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
#local   all             postgres                                peer
local   all             defond                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

Так подключается. Неправильно конечно, но для тестов пойдет, наверное.

Спасибо огромное за помощь!

defond · March 11, 2014, 10:05am

Экспорт пошел, но с ошибками, видимо… О_о

Пишет, что таблица planet_osm_polygon не существует и пропускается. С остальными так же. Это нормально?

defond · March 11, 2014, 10:18am

Видимо прошло все удачно…

В конце написал:


Reading in file: /home/defond/Загрузки/RU-KDA.osm.bz2
Processing: Node(7242k 39.6k/s) Way(1036k 6.69k/s) Relation(20829 473.39/s)  parse time: 382s

Node stats: total(7242174), max(2709816665) in 183s
Way stats: total(1036551), max(265312658) in 155s
Relation stats: total(20829), max(3565275) in 44s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Writing way (1036k)
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Writing relation (20827)
node cache: stored: 7242174(100.00%), storage efficiency: 7.20% (dense blocks: 98266, sparse nodes: 0), hit rate: -314827792988556364415920065772644327655763563221306441589669245303251058456859376547800634110346725413175378590393701880668591052769971067490926204871441598399687703508224312681756171480332708610048.00%
Sorting data and creating indexes for planet_osm_roads
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_point
Analyzing planet_osm_roads finished
Analyzing planet_osm_point finished
Analyzing planet_osm_polygon finished
Analyzing planet_osm_line finished
Copying planet_osm_roads to cluster by geometry finished
Copying planet_osm_point to cluster by geometry finished
Creating indexes on  planet_osm_roads finished
All indexes on  planet_osm_roads created  in 20s
Completed planet_osm_roads
Creating indexes on  planet_osm_point finished
All indexes on  planet_osm_point created  in 26s
Completed planet_osm_point
Copying planet_osm_polygon to cluster by geometry finished
Creating indexes on  planet_osm_polygon finished
All indexes on  planet_osm_polygon created  in 58s
Completed planet_osm_polygon
Copying planet_osm_line to cluster by geometry finished
Creating indexes on  planet_osm_line finished
All indexes on  planet_osm_line created  in 78s
Completed planet_osm_line

Osm2pgsql took 566s overall
defond@defond-1201T:~$

Запускать пришлось с sudo.

Suleyma · March 11, 2014, 10:22am

Нет конечно. Скорее всего он не смог ее создать. Возможно нет hstore или расширений постгис.

Попробуйте сначала
psql defond -c “CREATE EXTENSION hstore”
потом
psql defond -f /usr/share/postgresql/9.3/contrib/postgis-1.5/postgis.sql
psql defond -f /usr/share/postgresql/9.3/contrib/postgis-1.5/spatial_ref_sys.sql

И, кстати рекомендую ключик -s для osm2pgsql.

P.S. у Вас постгис может быть где-то в дугом месте.

defond · March 11, 2014, 10:44am

Вроде что-то сформировалось и таблицы создал. Попробую посмотреть, что в них… Если не получится - снесу БД defond и сделаю с учетом Ваших команд.

Теперь, вопрос напрямую к теме - мне нужно сформировать команду, которая вызовет наименования всех аэродромов для данного региона (у меня этот регион Краснодарский край).

Я формирую запрос:


SELECT 'point' AS type,ST_X(way) AS lon,ST_Y(way) AS lat,aeroway,name FROM planet_osm_point 
WHERE aeroway IN ('airport','aerodrome')    
UNION
SELECT 'polygon' AS type,ST_X(ST_Centroid(way)) AS lon,ST_Y(ST_Centroid(way)) AS lat,aeroway,name FROM planet_osm_polygon
WHERE aeroway IN ('airport','aerodrome') LIMIT 3;

Получаю результат:


"point";4329933.5970566;5597791.89076696;"aerodrome";""
"point";4333289.46782143;5611301.14111703;"aerodrome";""
"polygon";4258241.17978227;5892617.43724153;"aerodrome";""

При этом, названий аэродромов нет. Т.е. у меня их нет в таблице, или я неправильно запрос сформировал? Или неправильно сформировались таблицы при импорте данных?

А зачем ключик -s?