• Статья

Взгляд изнутри

Поддержка пространственных данных в SQL Server 2008

Боб Бошемен (Bob Beauchemin)

Загружаемый файл с кодом доступен в коллекции кода MSDN
Обзор кода в интерактивном режиме

Содержание

Использование типа данных geography
Пространственные индексы
Визуализаторы
Тип данных geometry
Пространственная визуализация
Библиотека пространственных данных SQL Server
Заключение

SQL Server 2008 содержит ряд улучшений, включая новые типы данных, новые выражения T-SQL и дополнительные изменения поддержки Microsoft .NET Framework, поддержки XML и функции Service Broker. Несомненно, самое значительное и интересное для разработчиков изменение — поддержка пространственных данных в SQL Server 2008, новое мощное средство в инструментарии программиста SQL Server. В этой статье я вкратце расскажу о пространственных данных и покажу несколько интересных примеров их использования.

Пространственные данные определяются как данные, представляемые двумерными или трехмерными изображениями. SQL Server 2008 поддерживает двумерные изображения, а трехмерные могут быть добавлены в следующем выпуске. Пространственные данные подразделяются на геометрические данные (данные, которые могут использовать расчеты в евклидовой геометрии) и географические данные (данные, определяющие географические положения и границы на поверхности земли).

Использование типа данных geography (географические данные)

Начнем с простого приложения, которое работает с пространственными данными и может пригодиться многим обычным, работающим с клиентами, системам обработки транзакций в Интернете (OLTP). В этом случае пространственные данные полезны, потому что у всех клиентов есть адреса. Обычно мы вкладываем в понятие «адрес» улицу, город, штат, страну и почтовый индекс, но адрес — это еще и точка на земле, которую можно представить в виде пары «широта и долгота». Технически адрес может представлять собой участок земли, заключенный в многоугольник, но для простоты мы ограничимся простой точкой. Если мы знаем, что клиент живет на данной широте и долготе, мы можем отвечать на вопросы, подобные следующим.

  • Где три ближайших отделения банка для конкретного клиента?
  • Какой продавец живет ближе всего к клиенту?
  • Сколько клиентов у компании в пределах 10 миль от, например, Сиэтла, штат Вашингтон?
  • Сколько клиентов проживает дальше, чем в двух милях от ближайшего отделения фирмы?

Процесс преобразования обычного адреса в пару широты и долготы называется геокодированием адреса. Есть несколько служб в Интернете, занимающихся геокодированием (в том числе MapPoint, Virtual Earth и Google Earth). Для преобразования почтового адреса в США в точку можно заключить вызов к веб-службе геокодирования MapPoint в функцию SQLCLR. Это показано в примере кода. Но где хранить широту и долготу в базе данных SQL Server?

SQL Server 2008 включает в себя два типа данных, необходимых для хранения пространственных данных: geometry и geography. Оба типа данных реализованы на архитектуре .NET для типов, определяемых пользователем; это значит, что у них есть свойства и методы. Остановимся пока на типе данных geography, потому что он больше соответствует текущей задаче.

Экземпляр типа geography можно определить с помощью простого объявления переменной SQL (DECLARE @g geography) или как столбец в таблице; инициализировать этот тип данных можно несколькими способами. Для типа geography, представляющего экземпляр точки, простейший способ — использовать статический метод STPointFromText типа geography. Метод STPointFromText требует не только текстового представления точки в известном текстовом формате открытого геопространственного консорциума (WKT), то есть POINT(x,y), но и идентификатора пространственной ссылки (SRID). SRID определяет пространственную систему отсчета, используемую для моделей круглой или плоской Земли; достаточно знать, что веб-служба геокодирования MapPoint использует координаты GPS, соответствующие SRID 4326. Это мировая геодезическая система 1984 (WGS 84). (Все поддерживаемые SQL Server 2008 SRID нетрудно найти, выполнив запрос к таблице метаданных sys.spatial_reference_systems.)

Итак, код инициализации geography адреса будет выглядеть следующим образом.

DECLARE @addr nvarchar(256) = 'Some sample address, City, State, Zip';
DECLARE @addr_as_xy nvarchar(30);
DECLARE @g geography;
SET @addr_as_xy = dbo.Geocoder(@addr);
SET @g = geography::STPointFromText(@addr_as_xy, 4326);

Так как тип geography реализован как тип данных на основе .NET, следует отметить несколько интересных моментов в коде. Во-первых, STPointFromText — статический метод, поэтому его нужно вызывать с использованием синтаксиса тип::метод. Во-вторых, названия методов типов данных на основе .NET учитывают регистр, поэтому нужно использовать заглавные буквы в точности так: STPointFromText.

Важно отметить еще одну вещь: функция геокодирования должна возвращать положение как "xy", а не как широту и долготу. Открытый геопространственный консорциум определяет форматы WKB и известный бинарный формат (WKB), используя пары координат x,y. Поэтому в STPointFromText следует передавать POINT(долгота/широта), а не POINT(широта/долгота). Если вы предпочитаете широту/долготу, SQL Server создает экземпляры geography с помощью языка географической разметки (GML), словаря XML, использующего широту/долготу. А если у вас точка, в типе данных geometry есть специальный статический метод, принимающий три параметра: широту, долготу и SRID.

После геокодирования сведений о клиентах можно продолжить геокодировать расположения отделений банка (или другие сведения об отделениях, магазинах или о чем угодно, связанном с расположением) и места проживания продавцов. Допустим, в итоге у нас есть таблицы под названием customer (клиент), salesperson (агент) и branch (отделение). В каждой будет столбец (назовем его для простоты «geog») типа geography, указывающий на местоположение.

Этими сведениями можно воспользоваться для ответа на вопросы, упомянутые ранее. Для этого нужно использовать методы типа данных geography. Метод расчета расстояния между двумя географическими объектами называется, разумеется, STDistance. Методы, дающие ответ на третий вопрос (сколько клиентов у нас в радиусе 10 миль от Сиэтла) — STBuffer и STIntersects. SQL Server также содержит метод для расчета приблизительного буфера, который называется BufferWithTolerance (работает несколько быстрее, чем вычисление точного буфера).

Для ответа на первый вопрос можно использовать такой запрос:

SELECT TOP(3) b.name,
 c.geog.STDistance(b.geog)/1000 [Distance in km]
FROM customer c, branch b
WHERE c.customerid = '12345' -- this query looks at customer 12345
ORDER BY c.geog.STDistance(b.geog)

Запрос для поиска ближайшего продавца выглядит примерно так же.

А как узнать, кто живет в радиусе 10 миль от Сиэтла? Для этого запроса нужно создать буфер вокруг границ города (в виде многоугольника границ города или точки, представляющей собой центр Сиэтла) и выбрать все точки (клиентов), пересекающиеся с этим буфером. В коде это выглядит так.

-- or declare POINT for "downtown Seattle"
-- 1609.344 meters per mile
DECLARE @Seattle geography = 'POLYGON(....)'; SELECT c.customerid FROM
 customer c WHERE c.geog.STIntersects(@Seattle.STBuffer(10 * 1609.344));

Эти примеры иллюстрируют более интересные способы взаимодействия с типом geography. Например, используя STBuffer, метод экземпляра типа данных geography, нужно использовать синтаксис экземпляр.метод, а не синтаксис тип::метод, как для статических методов вроде STPointFromText. Кроме того, заметьте, что при расчете расстояния между двумя точками или при определении, пересекается ли один экземпляр geography с другим, используется синтаксис Instance1.method(Instance2).

Для ответа на вопрос о числе клиентов, живущих более чем в двух милях от ближайшего отделения банка, нужно собрать географические данные всех отделений банка с буферами в две мили вокруг каждого, а затем найти клиентов, не пересекающихся с этим набором данных типа geography. Тип данных geography содержит метод под названием STUnion, но он определяется как метод экземпляра типа geography, возвращающий объединение этого экземпляра с другим экземпляром такого же типа.

Это не агрегация на основе набора, которую я имел в виду. К счастью, в SQL Server 2005 введены определяемые пользователем агрегаты, которые можно писать на.NET; для получения объединения набора значений здесь можно использовать именно их. Если не углубляться в подробности создания определяемых пользователем агрегатов, они требуют инициализации четырех методов: Init (инициализация ответа), Terminate (возврат ответа), Accumulate (вызывается для каждой строки) и Merge (вызывается при объединении обработки из нескольких потоков).

Метод Accumulate агрегата может просто объединять данные geography текущего ряда с данными geography ответа при каждом вызове. На самом деле не нужно самому писать этот агрегат; он входит в проект пространственных инструментов SQL Server (Spatial Tools) на CodePlex. Этот агрегат позволит даже передавать буфер для каждой строки, таким образом решая проблему двухмильного буфера. Затем запрос можно написать так:

SELECT COUNT(*)
FROM customer c
WHERE
c.geog.STIntersects(
SELECT dbo.GeographyUnionAggregate(b.geog,2*1609.344) FROM branch b)=0

Из этих примеров видно, что геокодирование адресов и хранение их вместе с почтовыми адресами может повысить полезность приложения для фирмы.

Пространственные индексы

По мере роста фирмы расчет расстояния между каждым клиентом и каждым продавцом или между каждым продавцом и каждым отделением банка может стать очень медленным. Поддержка пространственных данных в SQL Server 2008 включает в себя пространственные индексы. Пространственные индексы — это обычные индексы в сбалансированных деревьях, которые нужны для ускорения выполнения пространственных запросов так же, как реляционные индексы в SQL Server нужны для ускорения реляционных запросов.

Сопоставление двумерных пространственных данных с одномерным сбалансированным деревом выполняется с помощью составления мозаики, то есть разделения области на маленькие подобласти, и записи подобластей, пересекающихся с каждым экземпляром пространственных данных. Для типа данных geography это означает разделение земного шара на полушария и проецирование полушарий на плоскость. Затем каждый экземпляр geography покрывает одну или несколько частей (плиток) на этой плоскости; пространственный индекс содержит строку для каждой плитки, входящей в экземпляр. Для типа geometry можно указывать границы (ограничивающий прямоугольник), которые покрываются пространственным индексом, потому что вы сами определяете собственную декартову систему координат.

Есть несколько правил сопоставления пространственных экземпляров, пересекающихся с плитками, со строками в пространственном индексе; можно определять, насколько подробной будет сетка пространственного индекса на различных уровнях. Для получения дополнительных сведений о пространственных индексах лучше всего обратиться к электронной документации по SQL Server.

Возвращаясь к системе клиентов, можно определить пространственный индекс по столбцу geog таблицы customer с помощью следующего языка определения данных (DDL).

CREATE SPATIAL INDEX cust_geog_idx
ON dbo.customer(geog)
GRIDS =(LEVEL1=HIGH,LEVEL2=HIGH,LEVEL3=HIGH,LEVEL4=HIGH));

Лучше всего определять этот индекс, используя сетку крупного масштаба, потому что каждый адрес клиента — это точка, попадающая только в одну плитку, в отличие от линий или многоугольников, которые могут пересекаться с несколькими плитками.

Следует отметить, что пространственные индексы используются только с некоторыми пространственными методами. В настоящее время они используются со следующими предикатами geography:

instance1.STIntersects(instance2) = 1
instance1.STEquals(instance2) = 1
instance1.STDistance(instance2) < number
instance1.STDistance(instance2) <= number

При работе с geometry, в отличие от geography, пространственный индекс работает также с методами STContains, STOverlaps, STTouches и STWithin, но только при проверке на 1 (True). На основании того, как работают пространственные индексы, стоит изменить пространственный запрос о том, «сколько клиентов живет более, чем в двух милях от отделения банка» на подсчет клиентов, для которых STIntersects возвращает True и вычитание этого числа из общего числа клиентов.

Визуализаторы

Стоит отметить, что в паре примеров, с которыми мы пока что работаем, было бы полезно видеть пространственные данные в каком-то еще виде, помимо строк и столбцов. Вы могли заметить, что сам по себе выбор пространственного типа данных возвращает его двоичное представление. Формат WKB возвращается методами ToString или STAsText. Это несколько лучше, но все равно не позволяет представить географических положений, если, конечно, вы не можете проецировать пары долготы и широты на карту в уме. Получение пользы от пространственных данных практически всегда связано с графическим визуализатором, а если мы говорим о географических данных, такой визуализатор обычно включает карту.

SQL Server Management Studio 2008 содержит простой визуализатор в виде вкладки «Пространственные результаты» в окне «Результаты запроса». Этот визуализатор работает со столбцом geography или geometry в результатах запроса и переносит пространственные типы данных на сетку. Если в результатах запроса несколько столбцов с пространственными данными, можно выбрать один для визуализации. Столбец для отображения должен иметь двоичный формат SQL Server, с визуализатором не будут работать ToString() или STAsText().

Для типа данных geography можно даже выбирать одну из проекций карт, например Меркатора или равнопромежуточную, но по умолчанию данные на карту не наносятся. Простой способ сделать наложение на карту, если у вас есть таблица с данными контуров карты, — выполнить UNION ALL между своим набором строк и набором строк, содержащим данные geography карты. На рис. 1 показаны точки, представляющие набор более чем 700 городов из базы данных Mondial в визуализаторе SQL Server Management Studio. Создавший это запрос SQL Server выглядел так.

SELECT geog, name 
FROM Mondial.dbo.city
WHERE geog IS NOT NULL

fig01.gif

Рис. 1 Набор строк точек из таблицы городов базы данных Mondial

Еще лучше использовать коммерческий или условно-бесплатный визуализатор, предоставляющий наложение на карту по умолчанию, как на рис. 2. Вы заметите, что вкладка «Пространственные результаты» в SQL Server Management Studio показывает набор строк из более чем 700 точек, наложенных на карту. Создавший это запрос SQL Server выглядит так.

SELECT geog, name 
FROM Mondial.dbo.city
WHERE geog IS NOT NULL
UNION ALL
SELECT geog, cntry_name 
FROM SpatialSamples.dbo.cntry00

fig02.gif

Рис. 2 Набор рядов точек с наложенной картой

Один из удобных визуализаторов — программа Крега Дана (Craig Dunn) Geoquery 2008. Это бесплатный визуализатор для результатов запросов SQL Server 2008, содержащий проекции карт и позволяющий выбирать цвет и толщину линий для объектов пространственных данных. Можно также написать код для отображения данных в Microsoft Virtual Earth или Google Планета Земля, но об этом мы здесь говорить уже не будем.

Тип данных geometry

Вернемся к другому типу пространственных данных, который я упоминал, — к типу данных geometry – и поговорим о том, где лучше использовать его вместо geography. Тип данных geometry представляет собой плоскость с координатами x и y, в отличие от geography, представляющего широту и долготу, то есть углы от центра к поверхности земли (эллипсоидальные координаты). Тип данных geometry можно использовать для решения задач, для которых форма Земли не имеет значения, то есть сравнительно небольших плоских поверхностей, например разделенных на комнаты офисов или магазинов. Если вы пытаетесь найти в офисе комнату коллеги, кривизна земной поверхности не имеет значения, поэтому вполне достаточно использовать тип данных geometry и линейные расчеты. При использовании geometry устанавливается единица измерения и исходная точка (например, пусть 0,0 у нас будет нижний левый угол магазина).

Хотя тип geometry иногда можно использовать для пространственных положений на Земле, этот процесс несколько сложнее. Так как есть несколько способов проецировать Землю на систему координат, которые влияют на такие методы расчетов, как STArea и STDistance, в экземплярах пространственных данных должен быть указан SRID.

SRID нужен при использовании и geometry, и geography. Для типа данных geometry при выборе собственной системы координат и единицы измерения (например, если вы размечаете офис) нужно выбрать SRID 0, определяющий неизвестную или локальную пространственную систему отчета. Для типа данных geometry в SQL Server по умолчанию используется SRID 0. Для geography важно выбрать SRID, который может использовать SQL Server. По умолчанию используется SRID 4326, который применяется в системах GPS.

Географические методы, встроенные в SQL Server, могут использовать любую из 390 пространственных систем отсчета, перечисленных в таблице метаданных системы sys.spatial_reference_systems. Эти пространственные системы отсчета взяты из реестра геодезических параметров Европейской группы разведки нефти (EPSG). EPSG больше нет, ее функции выполняются теперь в рамках комитета геологоразведки производителей газа и нефти. Можно быть уверенным в том, что комитета геологоразведки производителей газа и нефти при выполнении разведки нуждается в точнейших положениях на поверхности Земли.

Каждый SRID не только объявляет единицу измерения (обычно метры), но также включает строку, определяющую датум (набор опорных точек на поверхности Земли), геоид, систему координат и проекцию карты. Дополнительные сведения о системах координат можно найти в статье Исаака Кунена (Isaac Kunen) «Введение в пространственные системы координат: плоские карты для круглой планеты».

Так как расчеты типа данных geography в SQL Server чувствительны к используемому SRID, попытка использовать пространственные методы (например, Distance и Intersects) между экземплярами пространственных типов с разными SRID вернет значение NULL. Ниже я подробнее расскажу о преобразованиях между разными SRID.

Пространственная визуализация

Я рассказал о некоторых основных приложениях пространственных данных и о том, что многие из них используют визуализаторы для зрительного представления данных. Допустим, что я хочу не просто использовать карту стран мира или штатов США, а визуализировать свои данные, спроецировав их относительно границ страны или связав их с избирательными округами или демографическими данными. В этом случае мне нужно получить то, что называется «данными пространственных ориентиров». Данные пространственных ориентиров можно загрузить с общедоступных веб-узлов или купить у таких компаний, как Институт Исследования Систем Окружающей Среды (ESRI). Заметьте, что в некоторых местах достать бесплатные данные пространственных ориентиров может быть трудно. В США большинство данных, о которых я говорил, например границы округов и демографические данные, более доступны, чем во многих других странах.

Итак, зайдем на понравившийся общедоступный веб-узел, например веб-узел Tiger бюро переписи населения США, и загрузим файлы географических отношений. Но файлы находятся в формате ESRI Shapefile, а не в формате столбцов типа geography в таблицах SQL Server.

Как импортировать эти файлы в SQL Server? Кроме того, многие общественно-доступные файлы используют SRID 4269, а не SRID 4326, использованный этим геокодером. Как преобразовать один SRID в другой, чтобы пространственные методы не возвращали NULL? Проблема, которую придется решить, относится скорее к разряду проблем извлечения, преобразования и загрузки (ETL), нежели к простому преобразованию. Нужно включить и непространственные данные, например номера районов, по которым производится перепимь или количество населения в определенных районах. И хотя теоретически можно было бы преобразовывать данные между разными SRID на лету, это бы значительно замедлило выполнение запросов. Определенно это задача для ETL.

В SQL Server 2008 не входит никакого готового решения этой задачи. Есть программы, например Shape2SQL Мортена Нилсена (Morten Nielsen), загружающие файлы Shapefile в таблицы SQL Server. Но файлы Shapefile — не единственный тип сторонних файлов пространственных данных, а кроме того, вам может понадобиться проводить преобразования между разными SRID или выполнять более сложные преобразования. Есть несколько коммерческих продуктов от сторонних производителей для преобразования и массовой загрузки пространственных данных. Сюда входят FME для SQL Server фирмы SAFE Software, Spatial Extender для SQL Server фримы Manifold и, конечно, линейка продуктов для работы с геоинформационными системами (GIS) института ESRI. Они могут импортировать данные в SQL Server и экспортировать их в полноценные GIS. Я успешно использовал продукт фирмы SAFE Software под названием «FME для SQL Server», который включает библиотеку преобразований, поддерживает почти все форматы пространственных данных и даже предоставляет набор компонентов для системы SQL Server ETL: службы интеграции SQL Server.

Библиотека пространственных данных SQL Server

Формат для перемещения данных в и из SQL Server зависит от специфических нужд вашей организации и конкретного приложения. Типы данных SQL Server изначально поддерживают форматы WKB и WKT, а также формат языка географической разметки (GML). SQL Server предоставляет типы geometry и geography и связанные с ними методы с помощью библиотеке на основе .NET, которая называется Microsoft.SqlServer.Types.dll. Эта библиотека не только входит в SQL Server, но так как она основана на .NET, ее можно загрузить вместе с пакетом компонентов SQL Server 2008 и перенести расчеты и преобразования на средний уровень или на уровень клиента. Помните, что только серверная часть предоставляет пространственные индексы, оптимизирующие запросы пространственных данных.

SQL Server хранит экземпляры классов .NET SqlGeometry и SqlGeography; сами классы и методы входят в библиотеку. Можно использовать пространственные методы в запросах SQL Server или заключать операции в сохраненные процедуры, определяемые пользователем функции и триггеры T-SQL или SQLCLR. Можно расширять основные функции, используя SqlGeometry и SqlGeography как члены в определяемых пользователем типах и агрегатах .NET, как GeographyUnionAggregate, который я использовал выше.

Библиотека пространственных данных включает также API для сборки, который можно использовать для оптимизации загрузки и для пользовательских преобразований и агрегаций. Он состоит из SqlGeometryBuilder и SqlGeographyBuilder и интерфейсов-приемников (IGeometrySink и IGeographySink), которые можно использовать для оптимизации сборки экземпляров пространственных данных. После реализации одного из интерфейсов-приемников вы сможете начинать и заканчивать геометрические фигуры, добавлять линии и устанавливать подходящие SRID. Собрав все данные, просто вызовите метод Populate для создания подготовленных экземпляров.

Заключение

Описанные мною элементы представляют лишь малую долю функций, появившихся в SQL Server после включения пространственных типов данных и пространственной библиотеки профессионального уровня. Данные, собираемые с помощью геокодирования, общедоступные данные ориентиров и ваши собственные пространственные данные для магазинов и офисов теперь можно хранить в SQL Server, управлять ими с помощью тех же административных средств, которые вы используете для других типов деловых данных, а также индексировать, опрашивать и анализировать, добавляя в свой бизнес новое измерение.

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

Боб Бокемен (Bob Beauchemin) — разработчик и архитектор приложений, построенных вокруг баз данных, автор курсов и преподаватель, писатель и партнер в области расширения навыков разработчиков в SQLskills. Он написал ряд книг и статей по SQL Server, технологиям доступа к данным и интеграции и безопасности баз данных. Связаться с ним можно по адресу bobb@sqlskills.com.