Смысл функциональности пространственных баз данных заключается в
выполнении запросов к базе данных, для которых иначе потребовалась бы настольная ГИС. Эффективное использование PostGIS требует знания доступных
пространственных функций и умения создавать индексы, обеспечивающие эффективную работу.
4.6.1. Преимущества индексов
Конструируя запрос важно помнить, что только операторы работающие с охватами (bounding-box-based),
типа && могут использовать пространственные индексы GiST. Такие функции, как 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 большинство функций геометрических отношений (Geometry Relationship Functions), за
исключением 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.
|
Какова длина улиц, полностью находящихся в пределах своего
муниципалитета?
|
|
Это - пример "пространственного объединения " (spatial 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".
|
|
Это - пример "наложения" (overlay) , которое использует пространственные данные из двух
таблиц и заносит результат в новую таблицу. В отличие от показанного выше
"пространственного объединения ", запрос создает новые геометрии. Оверлей
похож на пространственный 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) |
|
Последнее обновление: September 09 2008
(Наверх)
|