Введение в PostGIS ¶

PostGIS добавляет дополнительную функциональность к СУБД PostgreSQL. PostGIS расширяет возможности PostgreSQL с точки зрения хранения пространственных данных, запросов к ним и управления ими. В данном “введении” будут описаны основные функции PostgreSQL и PostGIS.

Данное «введение» описывает:

Как создать пространственную базу данных и делать запросы к ней из командной строки и из Quantum GIS.

Управлять данными из клиента pgAdmin.

Contents

Введение в PostGIS

Архитектура Клиент-Сервер ¶

PostgreSQL, как и другие СУБД, работает в качестве сервера в системе клиент-сервер. Клиент отправляет серверу запрос и получает отклик. По такому же принципу работает сеть Интернет: ваш браузер является клиентом, посылающим запрос, а веб-сервер возвращает обратно веб-страницу. Запросы PostgreSQL производятся с помощью языка SQL, откликами обычно являются таблицы данных из базы данных.

Ничего не мешает серверу PostgreSQL находится на одном компьютере с клиентом. Ваш клиент подключается к серверу по внутреннему IP-интерфейсу обратной связи, который не виден для других компьютеров (если вы не настроите иначе).

Здесь будут показаны три клиента: клиент командной строки, Quantum GIS и графический клиент базы данных — pgAdmin.

Создание баз данных, поддерживающих пространственную информацию ¶

Клиенты командной строки запускаются из окна эмулятора терминала. Запустите эмулятор терминала из меню Приложения в разделе Дополнительное. Это даст вам доступ к оболочке командной строки. Напечатайте:

psql -V

нажмите Ввод, чтобы увидеть версию PostgreSQL.

Один сервер PostgreSQL позволяет организовать работу, распределив её по отдельным базам данных. Каждая из баз данных будет работать в своем режиме, со своими собственными таблицами, видами, пользователями, и т.п. При установлении соединения к серверу PostgreSQL вы должны указать нужную базу данных.

Чтобы получить список баз данных, хранящихся на сервере, напечатайте команду:

psql -l

Вы увидите несколько баз данных, используемых несколькими проектами системы. В этом введении будет создана новая база данных.

Tip

Список использует стандартный Unix-pager: нажмите пробел, чтобы перейти на следующую страницу, b, чтобы вернуться, q для выхода и b для вызова справки.

PostgreSQL предоставляет служебную программу для создания базы данных createdb. Мы создадим базу данных с помощью расширения PostGIS, указав используемый шаблон. Назовите базу данных demo. У этом случае команда будет выглядеть следующим образом:

createdb -T template_postgis demo

Tip

Вы можете получить справку в командой строке, использую опцию --help.

Если вы запустите команду psql -l, то должны увидеть базу demo в списке баз данных.

Вы можете создать базу данных PostGIS с помощью языка SQL. Сначала мы удалим только что созданную базу данных с помощью команды dropdb, потом используем команду psql, чтобы получить интерпретатор команд SQL:

dropdb demo
psql -d postgres

Эти команды подключат к системной базе данных postgres, которую должны иметь все сервера. Введите запрос SQL, чтобы создать новую базу данных:

postgres=# CREATE DATABASE demo TEMPLATE=template_postgis;

Переключите ваше соединение с базы данных postgres на новую базу данных demo. В дальнейшем вы сможете подключаться непосредственно к базе данных, используя команду psql -d demo. Тут показан способ переключения с помощью psql в командной строке:

postgres=# \c demo

Tip

Нажмите CTRL + C, если подсказка psql появляется после нажатия кнопки возврата. Это приведет к очищению введённой информации. Возможно, вам понадобится закрыть кавычки, двоеточие и тп.

Вы должны увидеть информационное сообщение, в котором будет указано, что вы подключены к базе данных demo.

Далее, добавим PostGIS 2.0:

demo=# create extension postgis;

Чтобы проверить, что всё работает, напечатайте \dt , эта команда отобразит список таблиц в базе данных. Вы должны увидеть подобный этому список:

demo=# \dt
             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+-------
 public | spatial_ref_sys  | table | user
(1 row)

Это таблица, используемая PostGIS. Таблица spatial_ref_sys хранит информацию о системе координат, чтобы быстро просмотреть информацию, используйте команду SQL:

demo=# SELECT srid,auth_name,proj4text FROM spatial_ref_sys LIMIT 10;

 srid | auth_name |          proj4text
------+-----------+--------------------------------------
| EPSG      | +proj=longlat +ellps=bessel +towgs...
| EPSG      | +proj=longlat +ellps=aust_SA +no_d...
| EPSG      | +proj=longlat +ellps=GRS80 +towgs8...
| EPSG      | +proj=longlat +ellps=GRS80 +towgs8...
| EPSG      | +proj=longlat +ellps=bessel +no_de...
| EPSG      | +proj=longlat +ellps=airy +no_defs...
| EPSG      | +proj=longlat +a=6377340.189 +b=63...
| EPSG      | +proj=longlat +ellps=aust_SA +no_d...
| EPSG      | +proj=longlat +ellps=bessel +no_de...
| EPSG      | +proj=longlat +a=6377492.018 +b=63...
(10 строк)

Выведенные таблицы подтверждают, что наша база пространственная. Таблица: geometry_columns служит для передачи информации о том, какая из таблиц содержит пространсвенную информацию.

Создание пространственной таблицы сложным способом ¶

Теперь, имея пространственную базу данных, можно создать несколько пространственных таблиц. Сначала мы создадим обычную таблицу базы данных, чтобы хранить данные о городе. Эта таблица будет содержвать два поля — одно числовое поле-идентификатор, второе — название города:

demo=# CREATE TABLE cities ( id int4 primary key, name varchar(50), the_geom geometry(POINT,4326) );

Далее добавьте колонку геометрии, содержащую данные о местоположении городов. Для удобства назовем это поле geom (более ранние версии PostGIS требуют имени таблицы the_geom). Это поле указывает PostGIS, какой тип геометрии имеет каждый из объектов (точки, линии, полигоны и т.п.), какая размерность (т.к. возможны и 3-4 измерения — POINTZ, POINTM, POINTZM) и какая система координат. Для данных по городам мы будем использовать систему координат EPSG:4326.

Заглянув в таблицу города, вы увидите новую колонку, а также информацию о том, что в таблице отсутствуют строки.

demo=# SELECT * from cities;
 id | name | the_geom
----+------+----------
(0 строк)

Чтобы добавить строки в таблицу, используйте команды SQL. Чтобы добавить данные геометрии в соответствующую колонку, используйте функцию PostGIS ST_GeomFromText, чтобы сконвертировать координаты и идентификатор референсной системы из текстового формата:

demo=# INSERT INTO cities (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');
demo=# INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');
demo=# INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');

Tip

Используйте кнопки-стрелки для отмены и редактирования командной строки.

Как вы могли заметить, это быстро надоедает. К счастью, существуют иные, более лёгкие пути передачи данных в таблицы PostGIS. Сейчас в нашей базе находятся три города, с которыми мы можем начать работать.

Простые запросы ¶

Все самые обычные операторы SQL могут быть использованы для выбора данных из таблицы PostGIS:

demo=# SELECT * FROM cities;
 id |      name       |                      the_geom
----+-----------------+----------------------------------------------------
  1 | London, England | 0101000020E6100000BBB88D06F016C0BF1B2FDD2406C14940
  2 | London, Ontario | 0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D4540
  3 | East London,SA  | 0101000020E610000040AB064060E93B4059FAD005F58140C0
(3 строки)

Это возвращает нам бессмысленные значения координат в шестнадцатеричной системе. Если вы хотите увидеть вашу геометрию в текстовом формате WKT, используйте функцию ST_AsText(the_geom) или ST_AsEwkt(the_geom). Вы также можете использовать функции ST_X(the_geom), ST_Y(the_geom), чтобы получить числовые значения координат.

demo=# SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;
 id |          st_astext           |               st_asewkt                |    st_x     |   st_y
----+------------------------------+----------------------------------------+-------------+-----------
  1 | POINT(-0.1257 51.508)        | SRID=4326;POINT(-0.1257 51.508)        |     -0.1257 |    51.508
  2 | POINT(-81.233 42.983)        | SRID=4326;POINT(-81.233 42.983)        |     -81.233 |    42.983
  3 | POINT(27.91162491 -33.01529) | SRID=4326;POINT(27.91162491 -33.01529) | 27.91162491 | -33.01529
(3 строки)

Пространственные запросы ¶

Мы уже увидели, как получить геометрию из текстовых данных WKT с помощью функции ST_GeomFromText. Большинство таких функций начинаются с ST (“пространственный тип”) и описаны в документации PostGIS. Мы используем одну из них, чтобы ответить на практический вопрос: на каком расстоянии в метрах друг от другах находятся три города с названием Лондон, учитывая сферичность земли?

demo=# SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;
      name       |      name       | st_distance_sphere
-----------------+-----------------+--------------------
 London, Ontario | London, England |   5875766.85191657
 East London,SA  | London, England |   9789646.96784908
 East London,SA  | London, Ontario |   13892160.9525778
 (3 строки)

Этот запрос возвращает расстояние в метрах между каждой парой городов. Обратите внимание как часть ‘WHERE’ предотвращает нас от получения расстояния от города до самого себя (расстояние всегда будет равно нулю) и расстояния в обратном порядке (расстояние от Лондона, Англия до Лондона, Онтарио будет таким же как от Лондона, Онтарио до Лондона, Англия). Попробуйте ещё раз без ‘WHERE’ и посмотрите, что произойдёт.

Мы также можем рассчитать расстояния на сфере, используя различные функции и указывая называния сфероида, параметры главных полуосей и коэффициента обратного сжатия:

demo=# SELECT p1.name,p2.name,ST_Distance_Spheroid(
        p1.the_geom,p2.the_geom, 'SPHEROID["GRS_1980",6378137,298.257222]'
        )
       FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;
      name       |      name       | st_distance_spheroid
-----------------+-----------------+----------------------
 London, Ontario | London, England |     5892413.63776489
 East London,SA  | London, England |     9756842.65711931
 East London,SA  | London, Ontario |     13884149.4140698
(3 строки)

Картирование ¶

Для создания карты из данных PostGIS вам необходим клиент, с помощью которого вы сможете интерпретировать данные. Многие из открытых ГИС могут делать это, например, Quantum GIS, gvSIG, uDig. Далее будет показано, как сделать карту с помощью Quantum GIS.

Запустите Quantum GIS и выберите Add PostGIS layers из меню Слой. Параметры для подключения к данным: Natural Earth в PostGIS уже определены в выпадающем меню Соединения. Там же вы можете создать новое подключение к серверу и хранить настройки для быстрого доступа. Нажмите Edit, если хотите увидеть, какие параметры указаны для данных Natural Earth, или нажмите Connect, чтобы продолжить:

Появится список пространственных таблиц базы данных:

Выберите озёра (“Lakes”) и нажмите Add внизу (не Load вверху, которая подгрузит параметры подключения базы данных), после чего слой откроется в QGIS:

Вы должны увидеть карту с озёрами. QGIS не знает, что это озера, поэтому, возможно, они будут не голубого цвета. Используйте документацию QGIS, чтобы узнать, как изменить цвет. Увеличьте обзор до группы знаменитаых озёр Канады.

Простое создание пространственной таблицы ¶

Большинство приложений OSGeo поддерживают импорт пространственных данных из файлов в базу данных PostGIS. Для демонстрации этого будет использован QGIS.

Импорт шейп-файлов в PostGIS через QGIS можно сделать в с помощью дополнения PostGIS Manager. Чтобы установить его, перейдите в меню Модули, далее Загрузить модули. QGIS загрузит самую новую версию из репозитория (для этого вам потребуется работающее интернет-соединение). Найдите PostGIS Manager и нажмите кнопку Install plugin.

В меню Модули должен появиться пункт PostGIS Manager. Для запуска плагина также можно кликнуть по кнопке с логотипом PostGIS (слон с земным шаром), расположенной на панели.

После этого будет уставновлено соединение с базой данных Natural Earth. Оставьте поле пароль пустым, если будет необходимо его ввести. Вы увидите основное окно управления: слева вы сможете выбрать таблицы из базы данных, в правой части — узнать информацию о них. Вкладка Просмотр покажет небольшую карту. Для примера был выбран слой “Населённые пункты”, карта была приближена к произвольному месту.

Далее PostGIS Manager будет использован для импорта шейп-файла в базу данных.: Мы используем данным о синдроме внезапной смерти у детей в Северной Каролине, которые входят в одно из дополнений статистического пакета R.

Из меню Data выберите опцию Load data from shapefile. Нажмите кнопку ... и загрузите шейп-файл sids.shp в пакет R maptools (находится здесь: /usr/local/lib/R/site-library/):

.. image:: ../../images/screenshots/1024x768/postgis_browsedata.png

scale: 50 %

alt: Find the shapefile

align: center

scale:	50 %
alt:	Find the shapefile
align:	center

Не изменяя настроек в появившемся окне, нажмите Load:

Шейп-файл должен быть импортирован в PostGIS без ошибок. Закройте менеджер PostGIS и вернитесь в основное окно QGIS.

Подгрузите данные SIDS с помощью опции “Добавить слой PostGIS”. Изменив порядок слоёв и заливок, вы сможете создать фоновую картограмму (хороплет), отображающую количество детей, умерших от синдрома внезапной смерти в Северной Каролине.

Знакомство с pgAdmin III ¶

Вы можете использовать графический клиент базы данных pgAdmin III из системного меню “Базы данных”, чтобы сделать запрос или изменить атрибутивную информацию в базе данных. Это официальный клиент для PostgreSQL, позволяющий использовать SQL для управления таблицами данных.

В клиенте имеется опция создания нового соединения с сервером PostgreSQL или подключения с имеющимся соединением. В этом случае необходимо подключиться к локальному серверу (local).

После того, как соединение установлено, вы можете видеть список имеющихся баз данных.

Красный значок “Х” на большей части баз данных указывает, что они до сих пор не подключены (кроме базы данных по умолчанию — postgres). Таким образом, можно видеть только базы данных, имеющиеся в системе. Чтобы подключиться, кликните дважды на имени базы данных. Сделайте это для базы данных natural_earth2. Теперь вы можете видеть, что красный значок “Х” исчез и появился знак “+” слева. Нажатием на дерево раскрывается содержимое базы данных.

Перейдите в подраздел schemas, раскройте его. После этого раскройте схему public. Перемещаясь по структуре и раскрывая пункты (Tables), вы можете видеть все таблицы, которые содержит эта схема.

Выполнение SQL-запроса из pgAdmin III ¶

pgAdmin III имеет возможность выполнения запросов к реляционным базам данных. Чтобы выполнить запрос, нажмите кнопку SQL на главной панели инструментов (с жёлтой лупой).

Мы собираемся найти уровень синдрома внезапной детской смерти (SIDS) в 1974 году для каждого города. Кроме того, мы будем сортировать результат, основываясь на вычисленной величине. Чтобы сделать это, нам нужно выполнить следующий запрос (вставьте его в текстовый редактор в окно SQL):

select name, 1000*sid74/bir74 as rate from sids order by rate.

После этого нажмите на зелёную кнопку со стрелкой, указывающей вправо («выполнить запрос»).

Что стоит попробовать ¶

Несколько дополнительных задач, которые нужно выполнить:

Попробуйте больше пространственных функций, например, st_buffer(the_geom), st_transform(the_geom,25831), x(the_geom) — вы найдёте полную документацию здесь: http://postgis.net/documentation/
Экспортируйте ваши таблицы в шейп-файлы, используя pgsql2shp в командной строке.
Попробуйте утилиту ogr2ogr для экспорта/импорта данных.

Что дальше?¶

Это только первые шаги на пути использования PostGIS. Существует гораздо больше полезных функций, которые вы при желании можете попробовать.

Веб-страница PostGIS — http://postgis.net

Документация PostGIS — http://postgis.net/documentation/