Интернет, компьютеры, софт и прочий Hi-Tech

Подписаться через RSS2Email.ru
Руководство по PostGIS. Глава 4. Использование PostGIS. 4.6. Сложные запросы
Назад Вперед

4.6. Сложные запросы

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

4.6.1. Полезность индексов

Конструируя запрос важно помнить, что пространственные индексы GiST могут использовать только операторы ограниченных-пространств (bounding-box-based), типа &&. Такие функции, как distance() не могут использовать индекс для оптимизации своих операций. Например, следующий запрос был бы очень медленным на большой таблице:

SELECT the_geom
FROM geom_table
WHERE ST_Distance(the_geom, GeomFromText('POINT(100000 200000)', -1)) < 100

Этот запрос выбирает все геометрии из geom_table, которые находятся в пределах 100 единиц от точки (100000, 200000). Он будет выполняться медленно, так как влечет вычисления дистанций между каждой точкой в таблице и нашей данной точкой, т.е. одно вычисление ST_Distance() для каждой строки таблицы. Мы можем избежать этого с помощью оператора &&, уменьшающего число необходимых вычислений:

SELECT the_geom
FROM geom_table
WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
  AND
ST_Distance(the_geom, GeomFromText('POINT(100000 200000)', -1)) < 100

Этот запрос выбирает те-же геометрии, но делает это более эффективно. При условии, что на the_geom существует индекс GiST, планировщик будет считать, что использование этого индекса уменьшит число строк, на которых неоходимо вычислять функцию distance(). Заметим, что геометрия BOX3D, которая используется с оператором &&, является прямоугольником со стороной в 200 единиц, центрированным в нужной точке, - это наш "прямоугольник запроса" ("query box"). Оператор && использует индекс, чтобы быстро уменьшить число рассматриваемых записей, выбирая только те геометрии, которые пересекаются с "прямоугольником запроса". Если предположить, что наш прямоугольник запросов намного меньше, чем все геометрии в таблице, то это позволит резко сократить число вычислений расстояний, которые должны быть сделаны.

Изменение в поведении

Начиная с PostGIS 1.3.0 большинство функций геометрических отношений, за исключением ST_Disjoint и ST_Relate, скрыто включают операторы наложения границ.

4.6.2. Примеры пространственного SQL

Примеры этого раздела будут использовать две таблицы: таблицу линейных улиц и таблицу полигональных муниципальных границ. Таблица bc_roads будет определена так:

Column      | Type              | Description
------------+-------------------+-------------------
gid         | integer           | Уникальный ID
name        | character varying | Имя улицы
the_geom    | geometry          | Геометрия расположения (Linestring)

Таблица bc_municipality будет определена так:

Column     | Type              | Description
-----------+-------------------+-------------------
gid        | integer           | Уникальный ID
code       | integer           | Уникальный ID
name       | character varying | Название муниципалитета / города
the_geom   | geometry          | Геометрия расположения (Polygon)
4.6.2.1. Какова общая длина всех улиц в километрах?
4.6.2.2. На скольких гектарах расположен город "Prince George"?
4.6.2.3. Какой муниципалитет является самым большим в провинции по площади?
4.6.2.4. Какова длина улиц, полностью находящихся в пределах своего муниципалитета?
4.6.2.5. Создать новую таблицу со всеми улицами в городе "Prince George".
4.6.2.6. Какова длина в километрах улицы "Douglas St" в городе "Victoria"?
4.6.2.7. Какой муниципалитет является наибольшим из тех, чей полигон содержит дырку?
4.6.2.1.

Какова общая длина всех улиц в километрах?

Ответ на этот вопрос может дать очень простой SQL-запрос:

SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads;

km_roads
------------------
70842.1243039643
(1 row)
4.6.2.2.

На скольких гектарах расположен город "Prince George"?

В этом запросе скомбинированы атрибутивное условие (имя города) и пространственное вычисление (площадь):

SELECT
  ST_Area(the_geom)/10000 AS hectares
FROM bc_municipality
WHERE name = 'PRINCE GEORGE';

hectares
------------------
32657.9103824927
(1 row)
4.6.2.3.

Какой муниципалитет является самым большим в провинции по площади?

Этот запрос производит пространственные измерения согласно условиям вопроса. Есть несколько путей решения этой проблемы, но наиболее эффективным является следующее:

SELECT
  name,
  ST_Area(the_geom)/10000 AS hectares
FROM
  bc_municipality
ORDER BY hectares DESC
LIMIT 1;

name           | hectares
---------------+-----------------
TUMBLER RIDGE  | 155020.02556131
(1 row)

Заметим, что для ответа на данный вопрос, мы должны вычислить площадь каждого полигона. Чтобы не делать этого постоянно, имеет смысл создать в таблице стольбец площадей с отдельным индексом для повышения производительности. Отсортировав результаты по убыванию и использовав команду PostgreSQL "LIMIT" мы смолжем легко выбрать наибольшее значение без помощи таких агрегирующих функций, как max().

4.6.2.4.

Какова длина улиц, полностью находящихся в пределах своего муниципалитета?

Это - пример "пространственного join-а", в котором объединяются данные из двух таблиц (делаем join), но вместо обычного реляционного подхода к объединению (по внешнему ключу), в качестве условия объединения используется пространственное условие взаимоположения ("содержится в"):

SELECT
  m.name,
  sum(ST_Length(r.the_geom))/1000 as roads_km
FROM
  bc_roads AS r,
  bc_municipality AS m
WHERE
  ST_Contains(m.the_geom,r.the_geom)
GROUP BY m.name
ORDER BY roads_km;

name                        | roads_km
----------------------------+------------------
SURREY                      | 1539.47553551242
VANCOUVER                   | 1450.33093486576
LANGLEY DISTRICT            | 833.793392535662
BURNABY                     | 773.769091404338
PRINCE GEORGE               | 694.37554369147
...

Этот запрос выполняется заметное время, так как все дороги в таблице влияют на конечный результат (для нашего конкретного примера таблицы, - это около 250K дорог). Для небольших покрытий (от нескольких сотен, до нескольких тысяч записей) ответ может быть очень быстрым.

4.6.2.5.

Создать новую таблицу со всеми улицами в городе "Prince George".

Это - пример "наложения", который преобразует пространственные данные из двух таблиц и заносит результат в новую таблицу. В отличие от показанного выше "пространственного join-а", запрос создает фактически новые геометрии. Оверлей похож на пространственный join с турбонаддувом, и полезен для более точной аналитической работы:

CREATE TABLE pg_roads as
SELECT
  ST_Intersection(r.the_geom, m.the_geom) AS intersection_geom,
  ST_Length(r.the_geom) AS rd_orig_length,
  r.*
FROM
  bc_roads AS r,
  bc_municipality AS m
WHERE ST_Intersects(r.the_geom, m.the_geom)
  AND m.name = 'PRINCE GEORGE';
4.6.2.6.

Какова длина в километрах улицы "Douglas St" в городе "Victoria"?

SELECT
  sum(ST_Length(r.the_geom))/1000 AS kilometers
FROM
  bc_roads r,
  bc_municipality m
WHERE ST_Contains(m.the_geom, r.the_geom)
  AND r.name = 'Douglas St'
  AND m.name = 'VICTORIA';

kilometers
------------------
4.89151904172838
(1 row)
4.6.2.7.

Какой муниципалитет является наибольшим из тех, чей полигон содержит дырку?

SELECT gid, name, ST_Area(the_geom) AS area
FROM bc_municipality
WHERE ST_NRings(the_geom) > 1
ORDER BY area DESC LIMIT 1;

gid  | name         | area
-----+--------------+------------------
12   | SPALLUMCHEEN | 257374619.430216
(1 row)

Назад Выше Вперед
4.5. Разработка индексов Начало 4.7. Использование Mapserver
Биржа долевых инвестиций SIMEX.

Последнее редактирование: 2008-04-09 19:31:57

Метки материала: PostGIS, запросы, SQL, индексы, базы данных, руководство, ГИС, PostgreSQL

Оставьте, пожалуйста, свой комментарий к публикации

Представиться как     Антибот:
   

Просьба не постить мусор. Если вы хотите потестить xBB, воспользуйтесь кнопкой предварительного просмотра на панели инструментов xBBEditor-а.


© 2007-2017, Дмитрий Скоробогатов.
Разрешается воспроизводить, распространять и/или изменять материалы сайта
в соответствии с условиями GNU Free Documentation License,
версии 1.2 или любой более поздней версии, опубликованной FSF,
если только иное не указано в самих материалах.