Примеры использования инструментов PostGIS

Эта страница является черновиком статьи.

Еще заготовка шпаргалки

Установка

sudo apt-get install postgis

В Ubuntu 12.04 по-умолчанию все ставится в /usr/share/postgresql/9.1/contrib/postgis-2.0

Создание базы

В шелл (база создатся на пользователя pgsql):

createdb -U pgsql testdb

или

sudo -u pgsql createdb testdb

Начало работы

Заходим в базу:

psql -U pgsql -d testdb

Создание пользователя

CREATE USER admin PASSWORD 'admin';

Назначение его администратором:

ALTER DATABASE testdb OWNER TO admin;

не забудем выйти:

\q

Добавление функций PostGIS и систем координат (нужно знать, куда установился postgis):

psql -U pgsql -d testdb -f /usr/local/share/postgis/postgis.sql
psql -U pgsql -d testdb -f /usr/local/share/postgis/spatial_ref_sys.sql

или

sudo -u postgres psql -d testdb -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis.sql
sudo -u postgres psql -d testdb -f /usr/share/postgresql/9.1/contrib/postgis-2.0/spatial_ref_sys.sql

Создание базы сразу по темплейту:

createdb -U zadmin -T template_postgis zapoved_ngw

Загрузка данных

shp2pgsql

Загрузка данных в базу проводится в 2 этапа, сначала слой выгружается в sql, а затем он грузится в базу

shp2pgsql -i -D -s 4326 -W cp1251 topo250m-rus.shp > topo250-rus.sql
psql -U pgsql -f topo250-rus.sql -d testdb 

Права

Разрешение подключаться к определенной таблице базы пользователю с определенным именем (пользователь уже заведен в базе):

psql -U pgsql -d gen -c 'GRANT SELECT ON TABLE "topo250m-rus" TO guest;'

Разблокирование базы

SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.procpi
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.procpid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT bl.granted;

и, после получения ID (например 64712) заблокированной базы:

SELECT pg_terminate_backend(64712);

Удаление базы

Аналогично созданию, только dropdb, вместо createdb

dropdb -U postgres testdb

Если к базе есть подключения, но ее всё равно нужно удалить, нужно сначала удалить подключения:

Если версия PostgreSQL 9.1 и ниже:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'testdb';

Если версия PostgreSQL 9.2 и выше:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'testdb';