Таблицы изнутри

  • Статья

Визуализация пространственных данных

Боб Бошемин

Microsoft добавила в SQL Server 2008 поддержку пространственных данных, введя два новых встроенных типа данных:геометрический и географический. Хотя данные столбцов пространственных типов можно «видеть» в трех форматах — Well-Known Text, Well-Known Binary и Geographic Markup Language (GML), — единственным встроенным средством визуализации данных на карте является вкладка Spatial Results, добавленная в SQL Server Management Studio. Это удобно для разработчиков, занимающихся визуализацией многоугольников при отображении карт или даже геометрических данных, которые не имеют отношения к картам (например, плана склада), но, если у вас имеется набор точек, задающих координаты городов, вы увидите только точки. Чтобы добавить «основную карту» (например, карту мира, на которой будут показываться местонахождения городов), можно объединить операторы SELECT конструкцией UNION ALL и посмотреть результаты их выполнения на вкладке Spatial Results:

SELECT city_name, geog FROM cities
UNION ALL 
SELECT NULL, geog FROM map_of_the_world_table;

Но SQL Server Management Studio — инструмент не только администратора, но и программиста. Возможно, вам потребуется визуализировать данные в отчетах, предоставляемых руководству. Графическое представление позволяет лучше видеть тенденции.Уолдоу Тоблер (Waldo Tobler) в своем Первом законе географии утверждал, что «все связано со всем, но более близко расположенные объекты связаны более тесно». Сегодня я собираюсь показать три нововведения в визуализации пространственных данных, хранящихся в SQL Server: картографический элемент управления (Map Control) для SQL Server 2008 R2 Reporting Services (SSRS), продукт MapIt компании ESRI и MapPoint Add-in for SQL Server 2008.

Картографический элемент управления для Reporting Services

Визуализация карт основывается на концепции слоев. На фон или основную карту (скажем, карту мира) накладывают один или несколько слоев с пространственной информацией или информацией из базы данных. Пространственная информация может состоять из наборов многоугольников (например, показывающих регионы, в которых ведется торговля), линий (допустим, обозначающих реки и дороги) и точек (например, расположение магазинов). Одним из наиболее распространенных типов карт, применяемых в анализе бизнес-данных, является картограмма (choropleth) — тематическая карта, затенение областей которой пропорционально значению некой статистической переменной, подлежащей анализу. Так, картограмма, показывающая уровень продаж по различным штатам США, может иметь вид, приведенный на рис. 1.. Добавьте на нее второй слой, показывающий местонахождение магазинов вашей компании, и вы получите дополнительный уровень анализа.


Рис. 1.** Картограмма продаж по штатам США**

Как разработчик отчетов, вы можете обращаться к SQL Server 2008 R2 Map Control либо из Report Builder 3.0, либо из проекта Reporting Services в Business Intelligence Development Studio (BIDS). В Report Builder отчет Map (карта) показывается как один из вариантов выбора в основном шаблоне. В проекте BIDS элемент Map Control перетаскивают в отчет из окна инструментария. В обоих случаях задают основные параметры с помощью мастера.

Сначала выберите основную карту в диалоговом окне New Map Layer. Основную карту можно взять из встроенного набора карт Map Gallery, ESRI Shapefile (shape-файл — стандартный формат представления геопространственных данных) или запроса к SQL Server, возвращающего пространственные данные, как показано на рис. 2


Рис. 2. Диалоговое окно New Map Layer в SSRS 2008 R2

При выборе запроса к SQL Server, возвращающего пространственные данные, картографическая информация берется из пространственного столбца таблицы SQL Server. В процессе создания запроса с помощью Query Designer мастер поможет вам определить соединение с данными (Data Connection). Ваш запрос должен возвращать минимум один столбец типа geometry или geography.Иначе вы получите ошибку. В следующем диалоговом окне — Choose spatial data and map view options — можно выбрать пространственное поле (если ваш запрос возвращает несколько пространственных полей) и тип слоя (рис. 3). Обычно пространственный столбец таблицы содержит только точки, линии или многоугольники, а мастер создания карты обнаруживает это и предлагает выбрать соответствующий тип слоя. Тип слоя можно изменить; однако, если столбец содержит только многоугольники (или наборы многоугольников), а вы выбрали тип слоя (Layer Type): Point, то при предварительном просмотре вы ничего не увидите.Одна из приятных возможностей — в качестве фона можно взять карты Bing Maps.Таким образом, при использовании точечных данных вы можете сделать ячейки Bing Maps фоном картографического элемента. В этом случае можно выбрать вид Street Map (карта улицы), Aerial (воздушный) или Hybrid (смешанный). При выборе Bing Maps ваши пространственные данные будут располагаться поверх базового слоя Bing Map.


Рис.3.** Выбор поля с пространственными данными и параметров карты**

Определив основной слой или слои, вы должны выбрать ряд параметров, зависящих от того, из каких объектов состоят ваши пространственные данные — точек, линий или многоугольников. Например, если ваш слой содержит многоугольники, можно выбирать из Basic Map (стандартная карта — просто вывод выбранных вами пространственных данных), Color Analytical Map (цветная аналитическая карта — уже упоминавшиеся картограммы, в которых цвет каждого многоугольника зависит от анализируемой переменной) и Bubble Map (карта с пузырьками — размер символа, расположенного в центре каждой области пропорционален анализируемой переменной). В случае линий можно выбирать между Basic Line Map и Analytical Line Map, а в случае точек — между Basic Marker Map, Analytical Marker Map и Bubble Map. Картограмма на рис. 1 — это Color Analytical Map, сгенерированная мастером.

При выборе цветной аналитической карты или карты с пузырьками показывается панель выбора анализируемого столбца данных. Эти данные могут относиться к тому же набору данных, что и ваши пространственные данные, или к другому набору, связываемому с ним через поле. Например, одна таблица может содержать информацию о штатах, а другая — итоговую сумму продаж для каждого штата (SalesTotals). В то время как геометрические данные для отображения карт могут считываться только из shape-файла, Map Gallery или таблиц SQL Server с пространственными данными, данные для анализа могут поступать из любого источника, в частности из SQL Server Analysis Services.

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

Конечно, как это обычно бывает при разработке с использованием мастеров, мастер позволяет лишь заложить основы того, что требуется сделать.Карта, создаваемая с помощью этого элемента управления, содержится в Viewport — отдельном элементе управления, задающим расположение карты на странице отчета. Свойства карты, отображаемой в отчете, грубо говоря, поделены между тремя элементами:Viewport, Map и Layer. Их можно менять либо с помощью контекстных меню, либо в окнах свойств, содержащих более детальные представления. Набор свойств, показываемый в соответствующих окнах свойств, зависит от того, какая часть элемента Map Control находится в фокусе.

В свойствах Viewport задается, в частности, Map Projection (картографическая проекция). Ее выбор зависит от типа пространственного столбца, используемого в слое.Важно понимать, какое влияние оказывают на этот выбор ваши данные и идентификатор пространственной ссылки (spatial reference ID, SRID). Начнем с простейшего случая:предположим, что требуется нарисовать план склада вашей компании (физического склада, где хранятся товары, а не «склада» данных). Вероятно, придется обмерить склад, составить план помещения и нанести на план помещения местонахождение товаров. В данной задаче расстояния, отображаемые на плане, настолько малы, что тот факт, что Земля круглая, а не плоская, не имеет значения. Вы производите измерения в геометрической системе координат (координаты X и Y) и используете геометрический тип данных SQL Server. В этом случае для свойства Coordinate System элемента Viewport задают значение Planar.

Более типичная ситуация — отображение позиций объектов на Земле, задаваемых широтой и долготой.  Поскольку данные карты содержат широту и долготу, скорее всего будет использоваться географический тип данных. Для построения плоской карты Земли (которая, с технической точки зрения, является сплюснутым сфероидом) картографический элемент управления поддерживает набор картографических проекций.  Чтобы картографические проекции можно было использовать надлежащим образом, экземпляры вашего географического типа данных должны иметь SRID 4326, 4269 или другие типичные SRID, относящиеся к Земле и ожидаемые картографическим элементом управления. В этом случае для свойства Coordinate System элемента Viewport задают значение Geographic и выбирают картографическую проекцию в списке свойства Projection этого элемента.

Последний случай — данные, использующие систему координат проекции (projected coordinate system). В этом случае применяется геометрический тип данных SQL Server, а не географический. Картографический элемент ничего не делает для проецирования данных, поскольку информация о координатах на проекции уже содержится в самих данных. К типичным SRID для системы координат проекции относятся проекция State Plane и British National Grid.

Причина, по которой я обо всем этом рассказываю, — за исключением проецирования географических координат (предполагается, что используется SRID 4326 — координаты WGS84) картографический элемент управления не осуществляет автоматическое проецирование между различными системами координат. Например, нельзя сочетать на одной и той же карте элемент Layer (слой) с системой координат Bing Maps Tile (географическая) и элемент Layer с системой координат British National Grid (плоская) — результаты вас не обрадуют.

Значительным достижением Reporting Services 2008 R2 Map Control является то, что пространственные данные SQL Server автоматически визуализируются в различных форматах карт. С помощью мастера можно без особых усилий определить отношение между бизнес- и пространственными данными, а затем у вас появится все необходимое для дальнейшей настройки отчета с помощью многочисленных свойств. Одна из моих излюбленных возможностей картографического элемента управления (не связанная с пространственными типом данных) — поддержка детализации с помощью стандартного механизма «Action» (действие) SQL Server Reporting Services. Просто выберите вкладку Action в диалоговом окне Map Properties и вы сможете связать между собой отчеты, показывающие на карте продажи по странам, затем перейти к штатам или регионам, а далее — к городам. На уровне городов можно добавить точечный слой с расположением магазинов вашей компании. Дополнительную информацию о подготовке и использовании пространственных данных при работе с картографическим элементом SQL Server 2008 R2 Reporting Services см. в превосходной серии статей в блоге Эда Катиба (Ed Katibah), начинающейся с blogs.msdn.com/edkatibah/archive/2009/05/09/cartographic-adjustment-of-spatial-data-for-sql-server-reporting-services-part-1.aspx.

Продукт MapIt компании ESRI

ESRI — компания, являющаяся мировыми лидером в области ГИС, — представила продукт MapIt на своей конференции пользователей (Users Conference) в 2009 г. MapIt, по сути, представляет собой набор компонентов, облегчающих работу с существующими бизнес-данными, содержащих информацию о местонахождении. Этот продукт напрямую формирует и использует данные пространственных типов SQL Server 2008, поэтому для использования MapIt не требуется дополнительное ПО компании ESRI.

Наиболее типичный пример бизнес-данных с информацией о местонахождении — адреса, поэтому в MapIt входит программа Spatial Data Assistant, выполняющая геокодирование адресных данных в различных форматах. Spatial Data Assistant добавляет в таблицы SQL Server, содержащие адреса, столбец типа Geometry и заполняет его, обращаясь к веб-сервисам. У вас есть выбор между сервисами ESRI Map Service и Bing Maps Geocoding. После обновления существующих данных можно актуализировать данные о местонахождении, выполняя геокодирование в триггере. Пример такого триггера см. на сайте поддержки MapIt.

Для показа ваших бизнес-данных вместе с другими слоями карты (например, адресов учеников вместе со слоем, содержащим границы школьных округов) потребуется импортировать в SQL Server 2008 дополнительную информацию о местонахождении.Spatial Data Assistant позволяет импортировать существующие GIS-данные из формата ESRI Shapefile или из бесплатных картографических данных ESRI, доступных в онлайновом режиме. При импорте можно изменить проекцию данных на проекцию, используемую Bing Maps или ARCGIS Server. Можно даже выбрать SRID по своему усмотрению из списка поддерживаемых SRID. Механизм импорта создает в таблице SQL Server, куда импортируются данные, столбец геометрического типа.

После того как бизнес-данные и другие данные слоев занесены в SQL Server, можно визуализировать их в собственных программах. ESRI предоставляет бесплатный API для Silverlight и Windows Presentation Foundation (WPF), который можно использовать при создании полнофункциональных веб- и Windows-приложений. Чтобы можно было обращаться к вашим данным SQL Server через эти API, в MapIt включен Spatial Data Service, основанный на REST и позволяющий создать одну или несколько конечных точек Web, формирующих JSON- или HTML-вывод в формате, который могут принимать на входе Silverlight API. В этот сервис входят каталог Spatial Data Services, позволяющий просматривать таблицы, представления и запросы к ним, содержащие геометрические и географические столбцы. После того как вы решили, какие данные хотели бы видеть в своей карте, просто скопируйте URL этой таблицы или представления из каталога Spatial Data Services и вставьте его в приложение, использующее API. Ниже приведен пример показа адресов клиентов (Customer) из базы данных AdventureWorks2008 с помощью Spatial Data Service и представления Sales.vIndividualCustomerSpatial, которое я создал, добавив столбец SpatialLocation в существующее представление AdventureWorks2008 (рис. 4). В карте реализована одна из моих любимых функций — добавление компонента кластеризации, который при использовании более низких разрешений создает кластеры точек. По мере перехода к более высоким разрешениям карты кластеры точек распадаются на отдельные точки.

Рис. 4 Использование Spatial Data Service при работе с представлением

<!--UserControl element and namespace declarations elided for clarity -->
<Grid x:Name="LayoutRoot">
    <esri:Map x:Name="MyMap" >
        <esri:Map.Layers>
            <esri:ArcGISTiledMapServiceLayer ID="StreetMapLayer"
                Url="http://server.arcgisonline.com/ArcGIS/rest/services/ESRI_
StreetMap_World_2D/MapServer"/>
                <esri:FeatureLayer ID="MyFeatureLayer"
                    Url="http://zmv10/SDS/databases/AdventureWorks2008/Sales.
vIndividualCustomerSpatial"
                Where="CountryRegionName = 'United States'">
                <esri:FeatureLayer.Clusterer>
                    <esri:FlareClusterer FlareBackground="#99FF0000"
                        FlareForeground="White" MaximumFlareCount="9" />
                    </esri:FeatureLayer.Clusterer>
                </esri:FeatureLayer>
            </esri:Map.Layers>
        </esri:Map>
</Grid>

Результаты работы этого кода приведены на рис. 5. можно смешивать и сопоставлять слои пространственных данных SQL Server 2008 со слоями основных карт ARCGIS или Bing Maps, а также с другими слоями, импортированными с помощью Spatial Data Assistant. Например, можно добавить слой адресов менеджеров по продажам, чтобы сопоставить местонахождения менеджеров по продажам и клиентов. Кроме того, для элемента Graphics доступен тип Layer, а для обеспечения большей интерактивности карты API поддерживает поверхность для рисования (Drawing surface). Наконец, на веб-сайте поддержки ESRI имеются специализированные уровни для GEORSS и KML. Я рассмотрел лишь базовую функциональность этих API. Дополнительную информацию о Silverlight и WPF API см. по ссылке resources.esri.com/arcgisserver/apis/silverlight/index.cfm. 


Рис. 5.Карта местонахождений клиентов, сгенерированная MapIt (используется кластеризация точек)

Возможно, у вас возникнет необходимость создавать карты профессионального качества на основе данных SQL Server и при этом не нести издержек, связанных с разработкой и поддержкой Silverlight- или WPF-приложения. В MapIt входит веб-фрагмент (Web Part), позволяющий добавлять карты в сайты SharePoint вообще без программирования. Пространственные данные можно брать из:

  • списков SharePoint, содержащих адреса или поля с широтой/долготой;
  • пространственных данных SQL Server, формируемых с помощьюSpatial Data Assistant;
  • данных ARCGIS Server.

При редактировании экземпляра ESRI SharePoint Web Part можно импортировать слои пространственных данных, указывая URL или имя списка SharePoint. В качестве основных карт можно использовать карты Bing Maps или ARCGIS Server. С помощью дополнительных диалоговых окон можно задать выражения для фильтрации, создать всплывающие окна, которые отображают дополнительную информацию, когда курсор оказывается над определенными точками (в Silverlight API эта функция называется MapTips), выбрать символику и, щелкнув флажок, добавить кластеризацию точек. При динамическом изменении данных карты будут актуализироваться, показывая самую свежую информацию. Можно настраивать актуализацию и кеширование данных на уровне отдельных веб-фрагментов или глобально для Spatial Data Service.

Надстройка MapPoint 2010 для SQL Server 2008

MapPoint — предложение в области визуализации бизнес-данных и запросов данных о местонахождении от самой Microsoft. Одна из сильных сторон MapPoint в том, что в ней есть обширный набор многоуровневой картографической информации, с которой можно работать в автономном режиме, а также можно добавлять собственные пространственные слои. Самые свежие версии MapPoint даже поддерживают интеграцию с GPS. В августе 2009 г. Microsoft выпустила бесплатную надстройку MapPoint для работы с пространственными данными SQL Server 2008, позволяющую добавлять слои информации, получаемой от SQL Server, выполнять пространственные запросы к данным и слоям и сохранять конечные результаты на диске для дальнейшей доработки. При распространении сохраненной версии карты уже не требуется доступ к SQL Server.

Эта надстройка, основанная на WPF, использует при взаимодействии с SQL Server прямое ADO.NET-соединение. После подсоединения к экземпляру SQL Server и базе данных, содержащий пространственные данные, на вкладке Add Layer (рис. 6), выбирают таблицу с пространственными данными, используемыми в добавляемом слое. Можно сократить количество возвращаемых данных (а значит, быстрее заполнять слой), получая информацию только для текущей области карты, выбирая только подмножество столбцов и группируя возвращаемые данные. Можно также использовать SQL-запросы c разделом WHERE. Интерфейс построения запросов разработан так, чтобы обеспечивать максимальную скорость; гарантировать использование пространственных индексов в более сложных запросах можно с помощью обобщенных табличных выражений. Конечно, это диалоговое окно позволяет полностью управлять символикой.


Рис. 6** Диалоговое окно надстройки MapPoint, в котором добавляется слой**

MapTips, появляющиеся при выборе пространственных объектов, например точек, содержат все данные из указанной вами таблицы. Эти данные являются «живыми» (активными), т. е. их можно редактировать.При этом учитывается, к какой таблице свойств относятся редактируемые данные, а обновленные данные сохраняются в базе данных по нажатию кнопки Save. Можно даже добавить новые свойства (записи, содержащие пространственные данные) или удалить свойства прямо в надстройке.

Наконец, можно уточнять запросы в соответствии с текущим слоем пространственных данных. Например, после считывания набора расположений школ в области карты, примерно соответствующей границам моего города, я могу запросить набор местонахождений школ, расположенных в радиусе мили от моего дома, или запросить какие-либо дополнительные поля. При каждом дополнительном запросе возвращается дополнительный слой, и вы можете показывать, скрывать или корректировать слои перед сохранением карты. При сохранении карты сохраняются не только графические слои, но и SQL-запросы, на основе которых они были созданы, поэтому, если данные изменятся, слои можно обновить. Перед выполнением запросов, формирующих дочерние слои, сгенерированный для них SQL-код показывается в окне. Пользователь может задать значение тайм-аута, а также отменить запрос данных слоя в процессе выполнения. Благодаря прямому соединению с SQL Server рабочая среда становится гораздо более интерактивной. Имеется даже утилита импорта, позволяющая импортировать в SQL Server данные из shape-файлов или MIF-файлов MapInfo.

Рассмотренные в статье продукты и средства — новейшие разработки, расширяющие возможности SQL Server по работе с пространственными данными. Кроме того, компания Safe Software предлагает продукт FME for SQL Server 2008, интегрированный с SQL Server Integration Services (SSIS) и позволяющий использовать ETL-операции над пространственными данными в рабочих процессах SSIS. Вероятно, в следующей версии SQL Server мы увидим еще более тесную интеграцию с SQL Server Analysis Services и даже средствами анализа данных. Средства визуализации — как встроенные, так и от сторонних разработчиков — позволяют расширить область применения данных о местонахождении, не ограничиваясь только анализом адресов, и сделать обычные бизнес-данные более наглядными.

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