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

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

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

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

km_roads
------------------
70842.1243039643
(1 row)

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

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

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

hectares
------------------
32657.9103824927
(1 row)

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

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

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().

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

Это - пример "пространственного 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 дорог). Для небольших покрытий (от нескольких сотен, до нескольких тысяч записей) ответ может быть очень быстрым.

Создать новую таблицу со всеми улицами в городе "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';

Какова длина в километрах улицы "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)

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

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)