Начиная с версии 2008  MS SQL Server имеет встроенную поддержку пространственных данных. Прекрасно! На данный момент времени уже существует несколько СУБД, предлагающих индексированное хранение пространственных данных. Наверное, самые популярные из них, это: "народная" MySql и PostGIS.

Программируя на c#, естественно, в очень многих случаях, отдаёшь предпочтение продуктам и решениям Microsoft. Причины просты: полнее поддержка одних технологий другими, хорошая документация, более полная реализация, например провайдеров данных, и гораздо меньшая глючность. Я выбрал SQL Server. Заодно захотелось освоить LINQ в общем и LINQ to SQL в. частности.

Поначалу всё было хорошо. Для меня хороший старт сделала, обнаруженная на msdn, статья "LINQ to SQL: .NETLanguage-Integrated Query for Relational Data".
Но я не сильно удивился, когда "всё хорошо" закончилось.

Для хранения геометрических данных в SQL Server были введены два дополнительных типа: geometry и geography. Первый используется для хранения геометрических объектов, описанных в декартовой системе координат, а второй - для геометрических объектов заданных географическими координатами (широта/долгота).
Такое разделение, по всей видимости, пришлось сделать из-за того, что пространственный индекс реализован в SQL Server на основе B-деревьев. При использовании этого индекса пространство шаблонно разбивается сеткой несколько раз и в "ячейки" этой сетки сохраняются ссылки на геометрические объекты. И оказалось невозможно строить универсальное разбиение и для прямоугольной системы координат и для эллипсоидальной. В MySql, например, выбран другой алгоритм индексирования, основанный на R-деревьях, работающий на совершенно другом принципе, и используется один тип данных. Какой способ индексирования лучше, а какой хуже -не очевидно, так что пока не понятно на кого ругаться и стоит ли.

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

Я не смог найти в интернет решения, обходящего эту проблему, поэтому пришлось изобрести его самому. Здесь нет никаких удивительных ходов, но есть детали, которые, думаю, будут интересны. Также в этой большой заметке, для вашего интереса, я чуть-чуть опишу работу с LINQ to SQL.

База данных

Для примера будем использовать следующую таблицу.


Для её создания использовался следующий скрипт.

1. USE ExampleDatabase;
2. GO
3.  
4. --Create table
5. CREATE TABLE Boundaries_Country(
6.  FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
7.  CountryName VARCHAR(100) NOT NULL UNIQUE,
8.  CountryBoundary GEOGRAPHY NOT NULL
9. )
10.  
11. CREATE SPATIAL INDEX SpatialIndex
12.  ON Boundaries_Country (CountryBoundary);
13. GO

* This source code was highlighted with Source Code Highlighter.

В строке 11 для поля CountryBoundary c типом данных geography, создаётся пространственный индекс с настройками по умолчанию.

Чтобы было с чем работать, я заполнил таблицу мультиполигонами стран мира, shape-файл для которых был найден на просторах интернет. Несколько стран не захотели конвертироваться - я не стал разбираться почему, было не важно, хотя за Россию, безусловно, обидно.

SQL Server имеет симпатичный встроенный просмотрщик (ну вот, теперь все видят, что я пишу с ошибками).

Начало работы с LINQ to SQL

Для работы с LINQ to SQL в проект нужно добавить ссылки на две сборки: System.Data.Linq и Microsoft.SqlServer.Types. Если с первой библиотекой проблем нет (её можно найти на вкладке ".NET" формы "Add Reference" - добавления ссылки на используемую в проекте библиотеку), то вторую нужно будет поискать в директории "C:Program FilesMicrosoft SQL Server100SDKAssemblies". Для того, чтобы последняя сборка впредь отображалась во вкладке ".NET" формы добавления сборок, нужно её зарегистрировать один раз с помощью утилиты gacutil.

Первый шаг в использовании LINQ to SQL - это создание классов-отображений для таблиц базы данных.

На одну таблицу - один класс.

1. using System;
2. using System.Data.Linq.Mapping;
3. using Microsoft.SqlServer.Types;
4. namespace MyNamespace
5. {
6.  [Table()]
7.  public sealed class Boundaries_Country
8.  {
9.     [Column(AutoSync = AutoSync.OnInsert, DbType = "uniqueidentifier", IsPrimaryKey = true, IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]
10.     public Guid FeatureID;
11.     [Column(DbType = "varchar(100)", CanBeNull = false)]
12.     public string CountryName;
13.     [Column(/*DbType = "geography", */CanBeNull = false)]
14.     public SqlGeography CountryBoundary;
15.  }
16. }
17.  

* This source code was highlighted with Source Code Highlighter.

Над объявлением класса и над полями расставлены атрибуты. Например, в строке 7, атрибут Table указывает, что этот класс ассоциирован с таблицей в базе данных. Если имя класса совпадает с именем таблицы, то атрибут можно записывать так, как у меня, а если нет, то нужно будет указать дополнительное свойство Name: [Table(Name = "Boundaries_Country")].

В строке 16, при описании атрибута для поля, содержащего пространственные данные, по идее, я должен указать тип данных geography, но поскольку поддержки этого типа данных ещё нет, то я его и не указываю.

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

1. using System.Data.Linq;
2.  
3. namespace MyNamespace
4. {
5.  public class ExampleDatabase: DataContext
6.  {
7.     public Table<Boundaries_Country> BoundariesCountry;
8.     public ExampleDatabase(string connectionString)
9.      : base(connectionString)
10.     {
11.  
12.     }
13.  }
14. }

* This source code was highlighted with Source Code Highlighter.

Пример, выгребем из базы данных всё, что есть, но так, чтобы название страны начиналось с буквы "С".

1. static void Main(string[] args)
2. {
3.   ExampleDatabase db = new ExampleDatabase(@"...");
4.  
5.   var q = from item in db.BoundariesCountry
6.           where item.CountryName.StartsWith("C")
7.           select item;
8.      
9.   foreach (var item in q)
10.      Console.WriteLine(item.CountryName);
11. }

* This source code was highlighted with Source Code Highlighter.

Получилось не так уж и много.

Один интересный момент. Если в режиме отладки остановить выполнение программы на строке 9 и просмотреть содержание переменной q, то мы увидим сформированный LINQ to SQL запрос.

LINQ to SQL: работа с пространственными данными

Рассмотрим запрос, выбирающий из базы данных страны, попавшие в заданный прямоугольник, и название которых начинается на букву "С".

Прямоугольник задан полигоном (WKT-представление): POLYGON ((40 -28, 40 30, 5 30, 5 -28, 40 -28)).

1. var q = from item in db.BoundariesCountry
2.         where item.CountryName.StartsWith("C") &&
3.                     item.CountryBoundary.STIntersects(sqlEnvelope).Value
4.         select item;
5.  
6. foreach (var item in q)
7.  Console.WriteLine(item.CountryName);

* This source code was highlighted with Source Code Highlighter.

Этот код скомпилируется, но работать не будет.

Во время выполнения, на строке 5, когда от LINQ to SQL потребуется отправить запрос на сервер, будет выброшено исключение: "Method 'System.Data.SqlTypes.SqlBoolean STIntersects(Microsoft.SqlServer.Types.SqlGeography)' has no supported translation to SQL.".

Чтобы решить эту проблему мы будем использовать хранимые процедуры и table-valued функции, а пересылать геометрические объекты на сервер будем в хорошо понятном SQL Server'у бинарном формате WKB.

Хранимые процедуры

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

1. CREATE PROCEDURE [dbo].[sp_bbx_Boundaries_Country]
2.  @boundingBox varbinary(max)
3. AS
4. BEGIN
5.  SET NOCOUNT ON;
6.  SELECT *
7.     FROM dbo.Boundaries_Country
8.     WHERE GEOGRAPHY::STGeomFromWKB(@boundingBox,
9.                                  4326).STIntersects(CountryBoundary) = 1;
10.  RETURN;
11. END

* This source code was highlighted with Source Code Highlighter.

Входной параметр - это прямоугольник (заданный полигоном) в WKB-формате. В строке 8 он преобразуется статическим методом STGeomFromWKB в объект типа данных geography и уже на нём вызывается функция STIntersects, осуществляющая проверку на попадание конкретной границы в прямоугольник.

В программе, в классе, реализующем DataContext (у нас этот класс называется ExampleDatabase), опишем обёртку для вызова этой процедуры.

1. [Function()]
2. public ISingleResult<Boundaries_Country> sp_bbx_Boundaries_Country(
3.                         [Parameter(DbType = "varbinary(max)")] byte[] boundingBox)
4. {
5.  IExecuteResult execResult = this.ExecuteMethodCall(this, ((MethodInfo)
6.                                   (MethodInfo.GetCurrentMethod())), boundingBox);
7.  ISingleResult<Boundaries_Country> result =
8.                      ((ISingleResult<Boundaries_Country>)execResult.ReturnValue);
9.  return result;
10. }

* This source code was highlighted with Source Code Highlighter.

Здесь, также как и для таблиц, описываются атрибуты для функции и параметров.

В строке 4 вызывается хранимая процедура и результат сохраняется в execResult, затем, в строке 5, он преобразуется к требуемому типу данных и возвращается в основную программу.

Пользуемся этой "радостью" следующим образом:

1. var q = from item in db.sp_bbx_Boundaries_Country(
2.                                           sqlEnvelope.STAsBinary().Buffer)
3.         where item.CountryName.StartsWith("C")
4.         select item;
5. foreach (var item in q)
6.  Console.WriteLine(item.CountryName);

* This source code was highlighted with Source Code Highlighter.

Результат на консоль.

Замечание, если у вас, как у меня в рабочем проекте, несколько таблиц с географическими данными, то имеются следующие варианты.

1. Для каждой таблицы создать свою хранимую процедуру, а в программе для каждой хранимой процедуры свою функцию-обёртку. Можно упростить жизнь пользователю api, если написать "центральный" generic-метод в котором, по актуальному типу, используемому при вызове generic-метода, будут вызываться приватные метод-обёртки хранимых процедур и выполняться необходимые приведения типов.
   
2. Написать одну хранимую процедуру с использованием динамического sql. В программе нужно будет сделать один generic-метод, из которого также, как и в предыдущем варианте будут вызывать специализированные (по типу данных) методы-обёртки вокруг одной и той же процедуры (с наскока уйти от этого не удалось, воевать не стал).

Хранимые процедуры - это хорошо, но при использовании в LINQ to SQL, в описанной манере, у них есть один существенный недостаток: хранимые процедуры исполняются сразу и с сервера на клиент пересылаются все, попавшие в заданный регион, страны и уже потом над этим массивом выполняется дополнительная фильтрация. Т.е. трансляции в SQL всего LINQ-выражения не происходит. Для ухода от этой проблемы мы можем использовать inline-функции SQL Server'а.

Table-valued функции

Table-valued функция, извлекающая из таблицы базы данных записи по критерию попадания в заданный регион, может быть создана следующим образом.

1. CREATE FUNCTION [dbo].[f_bbx_Boundaries_Country]
2. ( 
3.  @boundingBox varbinary(max)
4. )
5. RETURNS TABLE
6. AS
7. RETURN
8. (
9.  SELECT *
10.     FROM dbo.Boundaries_Country
11.     WHERE GEOGRAPHY::STGeomFromWKB(
12.                      @boundingBox, 4326).STIntersects(CountryBoundary) = 1
13. )

* This source code was highlighted with Source Code Highlighter.

Т.е. по содержанию полностью аналогично хранимой процедуре, описанной раньше.

Для функции также нужно будет создать свою обёртку.

1. [Function(IsComposable = true)]
2. public IQueryable<Boundaries_Country> f_bbx_Boundaries_Country(
3.                  [Parameter(DbType = "varbinary(max)")] byte[] boundingBox)
4. {
5.  return this.CreateMethodCallQuery<Boundaries_Country>(this,
6.                 ((MethodInfo)(MethodInfo.GetCurrentMethod())), boundingBox);
7. }

* This source code was highlighted with Source Code Highlighter.

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

Смотрим пример.

1. var q = from item in db.f_bbx_Boundaries_Country(
2.                                          sqlEnvelope.STAsBinary().Buffer)
3.         where item.CountryName.StartsWith("C")
4.         select item;
5. foreach (var item in q)
6.  Console.WriteLine(item.CountryName);
7.  

* This source code was highlighted with Source Code Highlighter.

Результат тот же, что и при использовании хранимой процедуры.

И в отладке видим прекрасную картину (всё linq-выражение было транслировано в sql-запрос):


Всё, мне больше нечего сказать народу.

• Каталог программ Microsoft: Серверные программы. SQL Server.
• Задать вопрос ONLine по покупке программ в интернет магазине ITShop
• Подписаться на рассылку "Вопросы и ответы по MS SQL Server"