ПРИМЕРЫ ЗАПРОСОВ, СОДЕРЖАЩИХ ПРОСТРАНСТВЕННЫЕ ОПЕРАЦИИ
Таблица 3.10. Основные таблицы
| CREATE TABLE Country( Name varchar(30 ), Cont varchar(30 ), Pop Integer, GDP Number, Shape Polygon); (а) | |
| CREATE TABLE River( Name varchar(30 ), Origin varchar(30 ), Length Number, Shape LineString ); (б) | |
| CREATE TABLE City ( Name varchar(30 ), Country varchar(30 ), Pop integer, Shape Point ); (в) |
Используя типы данных и операции стандарта OGIS, мы сформулируем SQL-запросы к базе данных World, иллюстрирующие пространственные соотношения между тремя сущностями: странами (Country), городами (City) и реками (River). Для начала переопределим реляционную схему, предполагая доступность в языке SQL типов данных и операций OGIS. Пересмотренная схема показана в таблице 3.10.
1. Запрос. Отыскать в таблице Country названия всех стран, которые являются соседями Соединенных Штатов. SELECT C1.Name AS "Соседи США" FROM Country C1, Country C2 WHERE Touch(C1.Shape, C2.Shape) = 1 AND C2.Name = ‘США’
Примечания. Предикат Touch проверяет, являются ли два любых геометрических объекта смежными без наложения. Эта операция полезна для отыскания соседних объектов. Операция Touch – один из восьми топологических предикатов, описанных в стандарте OGIS. Приятно отметить одно из свойств топологических операций – их инвариантность по отношению ко многим геометрическим преобразованиям. В частности, на результаты топологических операций не влияет выбор координатной системы базы данных World.
Топологические операции применимы к большому числу различных сочетаний геометрических типов данных. Следовательно, в идеальной ситуации эти операции должны быть определены в стиле «перегрузки». К сожалению, многие объектно-реляционные СУБД не поддерживают таких понятий объектно-ориентированной парадигмы, как наследование классов и перегрузка операций. Поэтому в практических целях для каждой комбинации применяемых геометрических типов эти операции можно определить отдельно.
2. Запрос. Для всех рек, перечисленных в таблице River, определить страны, по которым они протекают.
SELECT R.Name C. Name FROM River R, Country C WHERE Cross(R.Shape, C.Shape) = 1
Примечания. Cross – еще один топологический предикат. Чаще всего он используется для проверки наличия пересечения между объектами LineString и Polygon, как в этом примере, или между парой объектов LineString. 3. Запрос. Какой город, содержащийся в таблице City, ближе других расположен ко всем рекам, перечисленным в таблице River? SELECT C1.Name, R1.Name FROM City C1, River R1 WHERE Distance (C1.Shape, R1.Shape) < ALL (SELECT Distance(C2.Shape, R1.Shape) FROM City C2 WHERE C1.Name <> C2.Name )
Примечания. Distance – двухместная операция вещественного типа. Используется в предложении WHERE и еще раз – в предложении SELECT подзапроса. Функция Distance описана для любых комбинаций геометрических объектов. 4. Запрос. Река Св. Лаврентия может снабжать водой города, удаленные от нее не далее чем на 300 км. Составить список городов, которые могут получать воду из реки Св. Лаврентия. SELECT Ci.Name FROM City Ci, River R WHERE Overlap(Ci.Shape, Buffer(R.Shape,300)) = 1 AND R.Name = ‘Св. Лаврентия’
Примечания. Буфером (Buffer) геометрического объекта называется центрированная относительно объекта геометрическая область, размер которой определяется параметром операции Buffer. В данном примере размер буферной области определяется запросом. Операция нахождения буфера используется во многих приложениях ГИС, включая противопаводочные мероприятия, а также муниципальные правила городского и сельского районирования. Графическое изображение операции нахождения буфера показано на рис. 3.2. Города A и B здесь, вероятно, будут затронуты наводнением, тогда как город C останется за пределами зоны подтопления.
Рис. 3.2. Буфер реки и точки внутри и вне буфера 5. Запрос. Составить список названий, численности населения и площади стран, содержащихся в таблице Country. SELECT C.Name, C.Pop, Area(C.Shape) AS "Площадь" FROM Country C
Примечания. Этот запрос иллюстрирует применение функции Area.
Данная функция применяется только для геометрических типов Polygon и MultiPolygon. Очевидно, что вычисление результата Area зависит от базовой системы координат базы данных World. Например, если очертания в кортежах таблицы Country заданы широтой и долготой, то расчету значения Area должно предшествовать промежуточное преобразование координат. То же справедливо для функций Distance и Length.
6. Запрос. Составить список длин рек в пределах каждой страны, по которой они протекают. SELECT R.Name, C.Name, Length( Intersection(R.Shape, C.Shape)) AS "Length" FROM River R, Country C WHERE Cross(R.Shape, C.Shape) = 1
Примечания. Значение, возвращаемое двухместной операцией Intersection, имеет геометрический тип. Операция Intersection отличается от функции Intersects, которая играет роль топологического предиката, определяющего, пересекаются ли две геометрии. Результат операции Intersection над объектами типа LineString и Polygon может иметь тип Point или LineString. Если река пересекает территорию страны, то результат будет иметь тип LineString. В этом случае функция Length возвратит ненулевую длину реки в каждой стране, по которой та протекает. 7. Запрос. Для всех стран составить список ВВП и расстояний от столичных городов до экватора.
SELECT Co.GDP, Distance(Point(0,Ci.Shape.y),Ci.Shape) AS "Расстояние" FROM Country Co, City Ci WHERE Co.Name = Ci.Country AND Ci.Capital = ‘Д’
Примечания. Поиск неявных соотношений между наборами данных, хранящимися в базе данных, не входит в функции стандартных СУБД. Современные системы баз данных развиваются в направлении оперативной обработки транзакций (OLTP), в то время как приведенный запрос, в том виде, как он сформулирован, относится к сфере аналитической обработки (OLAP). Саму технологию OLAP относят к технологиям добычи данных, нам предстоит рассмотреть эту тему в главе 8. Лучшее, что мы можем сделать на данный момент, – перечислить все столицы и их удаленность от экватора.
Point(0,Ci.Shape.y) – это точка на экваторе, имеющая ту же долготу, что и текущая столица, представленная атрибутом Ci.Name.
Результаты показаны в таблице 3.11.
8. Запрос. Составить список всех стран, упорядоченный по количеству государств-соседей. SELECT Co.Name, Count(Co1.Name) FROM Country Co, Country Co1 WHERE Touch(Co.Shape, Co1.Shape) GROUP BY Co.Name ORDER BY Count(Co1.Name)
Примечания. В этом запросе все страны, имеющие хотя бы одного соседа, сортируются по количеству пограничных государств.
9. Запрос. Перечислить страны, имеющие только одного соседа. Страна является соседней по отношению к другой стране, если их территории имеют общую сухопутную границу. В соответствии с этим определением островные государства, скажем Исландия, соседей не имеют. SELECT Co.Name FROM Country Co, Country Co1 WHERE Touch(Co.Shape, Co1.Shape)) GROUP BY Co.Name HAVING Count(Co1.Name) = 1 SELECT Co.Name FROM Country Co WHERE Co.Name IN (SELECT Co.Name FROM Country Co, Country Co1 WHERE Touch(Co.Shape, Co1.Shape) GROUP BY Co.Name HAVING Count(*) = 1)
Примечания. Здесь мы имеем вложенный запрос, находящийся в предложении FROM. Результатом запроса в конструкции FROM является таблица, состоящая из пар стран, которые являются соседями. Предложение GROUP BY делит новую таблицу на части в соответствии с названиями государств. Наконец, HAVING инициирует принудительное объединение результатов выборки в пары с теми странами, которые имеют только одного соседа. Ключевое слово HAVING играет ту же роль, что и WHERE, за исключением одного: HAVING должно включать такие функции агрегирования, как count, sum, max и min.
10. Запрос. Какая страна имеет наибольшее число соседей? CREATE VIEW Neighbor AS SELECT Co.Name, Count(Co1.Name) AS num_neighbors FROM Country Co, Country Co1 WHERE Touch(Co.Shape, Co1.Shape) GROUP BY Co.Name SELECT Co.Name, num_neighbors FROM Neighbor WHERE num_neighbor = (SELECT Max(num_neighbors) FROM Neighbor)
Примечания. Этот запрос демонстрирует применение представлений (views) с целью упрощения сложных запросов. Первый запрос (представление) вычисляет количество соседей каждой страны. Представление создает виртуальную таблицу, которая в последующих запросах может использоваться как обычная таблица.Второй запрос производит выборку страны с наибольшим количеством соседей из представления Neighbor (Соседи).
Таблица 3.11. Результаты запроса 7
| Co.Name | Co.GDP | Расст-до-экватора (в км) |
| Гавана | 16,9 | 2562 |
| Вашингтон | 8003 | 4324 |
| Бразилиа | 1004 | 1756 |
| Оттава | 658 | 5005 |
| Мехико | 694,3 | 2161 |
| Буэнос-Айрес | 348,2 | 3854 |
