Недавно в этом блоге вы могли наблюдать заметку о публикации данных в формате PostGIS, в том числе и данных OSM. Конечно, это не отменяет публикуемых shape-файлов, однако скачивать их при каждом обновлении может быть не всегда удобно, особенно если вам нужен какой-либо специфический срез этих данных. Поэтому хотелось бы рассказать о одном, как мне кажется, довольно интересном способе получения этих данных встроенными средствами PostgreSQL и PostGIS.
Самый очевидный способ, получить копию таблицы – это воспользоваться pg_dump -t <имя_таблицы>, а затем pg_restore и зарегистрировать колонку геометрий – однако это скучно и не интересно, да и фильтры там наложить негде.
Для PostgreSQL есть расширение dblink, которое позволяет выполнять запросы к удаленным базам данных непосредственно из SQL, не прибегая к помощи внешних скриптов.
Чтобы включить это расширение нужно посмотреть папку, в которую у вас были установлены расширения (точный путь может зависеть от дистрибутива), и выполнить dblink.sql из этой папки на вашей БД. Например, так:
psql -U <имя_пользователя> -h <хост_бд> <имя_бд> < /usr/share/postgresql-8.4/contrib/dblink.sql
То есть процедура установки, очень похожа на установку PostGIS. После этого, можно напрямую обращаться к удаленным базам из своих SQL-скриптов, например, так:
SELECT *
FROM dblink('dbname=osm host=gis-lab.info user=guest password=guest',
'SELECT osm_id, waterway, name, way FROM osm_line WHERE waterway IN (''river'', ''stream'')'
) AS rivers(osm_id int, tag varchar(50), name varchar(200), way geometry);
Где:
'dbname=osm host=gis-lab.info user=guest password=guest'– это строка подключения к удаленной базе'SELECT osm_id, waterway, name, way FROM osm_line WHERE ...'– запрос, который вы хотите выполнить (escape-последовательность для одинарных кавычек –'')rivers(osm_id int, tag varchar(50), name varchar(200), way geometry)– псевдоним в текущем запросе и описание полей с их типами
При необходимости выбрать данные по охвату, можно поступить например так:
SELECT *
FROM dblink('dbname=osm host=gis-lab.info user=guest password=guest',
'SELECT osm_id, name, way FROM osm_point WHERE place IN (''city'',''town'')
AND way && ST_SetSRID(ST_MakeBox2D(ST_Point(37.32, 56.53), ST_Point(41.20, 58.96)), 4326)'
) AS rivers(osm_id int, name varchar(50), geom geometry);
Где, ST_SetSRID(ST_MakeBox2D(ST_Point(37.32, 56.53), ST_Point(41.20, 58.96)), 4326) – охват ярославской области, а && – оператор пересечения. Таким образом можно выбрать все крупные населенный пункты ярославской области, обозначенные как place=city|town.
Если по каким-то причинам вам не удобно указывать строку соединения каждый раз, то можно воспользоваться dblink_connect для того чтобы создать подключение и в дальнейшем его использовать.
Спасибо за информацию, будем знать. Как я понял данная процедура полезна при создании скриптов. Но таким же образом ведь можно подключаться и работать с базами средствами самого языка, использующего данные SQL скрипты?
Конечно всегда можно скопировать данные в рабочую базу тем или иным способом, но иногда никакого языка то и нет – просто SQL-скрипты запускаются через psql. В этой ситуации такой подход может быть несколько удобней. По крайней мере мне dblink пригодился именно в такой ситуации.
Может я что-то не понимаю, но почему бы просто не писать psql -h gis-lab.info -d osm -f smth.sql. ?ли dblink позволяет делать запросы (например, содержащиеся в файле smth.sql) к другим базам, расположенным на других хостах, нежели, как -h gis-lab.info -d osm?
Да, в этом и фишка, – хост и база могут быть любыми независимо от текущей базы и хоста.
Спасибо, теперь понятно.
[…] Довольно распространена ситуация, когда данные не консолидированы, содержатся в независимых хранилищах или появляются в результате выполнения запроса (процесса). В такой ситуации можно применить агрегацию. Например, в п. 1а включить БД PostgreSQL с применением расширения dblink. […]