LINQ to SQL и пространственные данные SQL Server

Original english version
Эта же заметка в google docs

Начиная с версии 2008 (и пока что заканчивая ей) MS SQL Server имеет встроенную поддержку пространственных данных. Прекрасно!

На данный момент времени уже существует несколько СУБД, предлагающих индексированное хранение пространственных данных. Наверное, самые популярные из них, это: «народная» MySql и PostGIS.

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

Поначалу всё было хорошо. Для меня хороший старт сделала, обнаруженная на msdn, статья «LINQ to SQL: .NET Language-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
    
14. 
    

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

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

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

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

Для работы с LINQ to SQL в проект нужно добавить ссылки на две сборки: System.Data.Linq и Microsoft.SqlServer.Types. Если с первой библиотекой проблем нет (её можно найти на вкладке «.NET» формы «Add Reference» — добавления ссылки на используемую в проекте библиотеку), то вторую нужно будет поискать в директории «C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\». Для того, чтобы последняя сборка впредь отображалась во вкладке «.NET» формы добавления сборок, нужно её зарегистрировать один раз с помощью утилиты gacutil.

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

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

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

Над объявлением класса и над полями расставлены атрибуты. Например, в строке 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. 
   
9. public ExampleDatabase(string connectionString)
   
10. : base(connectionString)
    
11. {
    
12. 
    
13. }
    
14. }
    
15. }
    

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

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. }
    

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

Один интересный момент. Если в режиме отладки остановить выполнение программы на строке 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") &&
   item.CountryBoundary.STIntersects(sqlEnvelope).Value
   
3. select item;
   
4. 
   
5. foreach (var item in q)
   
6. Console.WriteLine(item.CountryName);
   

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

Во время выполнения, на строке 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,
   4326).STIntersects(CountryBoundary) = 1;
   
9. RETURN;
   
10. END

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

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

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

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

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

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

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

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

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

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(
    @boundingBox, 4326).STIntersects(CountryBoundary) = 1
12. )

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

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

1. [Function(IsComposable = true)]
2. public IQueryable<Boundaries_Country> f_bbx_Boundaries_Country(
   [Parameter(DbType = "varbinary(max)")] byte[] boundingBox)


3. {
4. return this.CreateMethodCallQuery<Boundaries_Country>(this,
   ((MethodInfo)(MethodInfo.GetCurrentMethod())), boundingBox);
5. }

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

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

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

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

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


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

The realization of the generic names

GenericName is a sequence of identifiers rooted within the context of a namespace, for geoTools project was derived in restricted form from the ISO 19103. Restrictions were made for the reason of useless some parts of the standard for tasks of the developed library, and also becouse of some elements of classes in the standard are not specificated and have no clear meaning.

I want to offer a little bit more flexible classes for Generic name than I found in geoTools realization. Also, for my realization i've used another program language: c# instead of java.

GenericName is the base class for two descendants: ScopedName and LocalName.

Figure 1. GenericName uml class diagram

The main question is, what all these methods do?

GenericName.ParsedNames — returns a list of LocalNames which constitute this name.
For the LocalName the list contain just one LocalName, concretely, this name.

//LocalName:
public override List<LocalName> ParsedNames
{
  get
  {
    if (parsedNames == null)
      parsedNames = new List<LocalName>(new LocalName[1] { this });
    return parsedNames;
  }
}

//ScopedName:
public override List<LocalName> ParsedNames
{
  get
  {
    if (parsedNames == null)
    {
      parsedNames = new List<LocalName>();
      parsedNames.Add(head);
      parsedNames.AddRange(tail.ParsedNames);
    }
    return parsedNames;
  }
}


GenericName.FullyQualifiedName — returns a view of this name as a fully-qualified name.
In the common this method returns a ScopedName which head lays in the global namespace, for my realization it means, that the namespace property has value null. This method may return a LocalName instance in the case if we call it for LocalName instance with a null namespace.

And the realization for LocalName, and for ScopedName.

//LocalName:
public override GenericName FullyQualifiedName
{
  get
  {
    if (nameSpace != null)
    {
      ScopedName fqn = new ScopedName(this.Head.NameSpace.Tip, this);
      while (fqn.Head.NameSpace != null)
        fqn = new ScopedName(fqn.Head.NameSpace.Tip, fqn);
      return fqn;
    }
    else
      return new LocalName(null, name);
  }
}

//ScopedName:
public override GenericName FullyQualifiedName
{
  get
  {
    ScopedName fqn = this;
    if (this.Head.NameSpace != null)
    {
      fqn = new ScopedName(this.Head.NameSpace.Tip, this);
      while (fqn.Head.NameSpace != null)
        fqn = new ScopedName(fqn.Head.NameSpace.Tip, fqn);
    }
    return fqn;
  }
}


Generic.ToString() — returns a textual representation of this name. The code for LocalName and ScopedName is presented below.

//LocalName:
public override string ToString()
{
  return name;
}

//ScopedName:
public override string ToString()
{
  StringBuilder builder = new StringBuilder();
  for (int i = 0; i < ParsedNames.Count - 1; i++)
  {
    builder.Append(parsedNames[i].ToString());
    builder.Append(GenericName.DELIMETER);
  }
  builder.Append(parsedNames[parsedNames.Count - 1].ToString());
  return builder.ToString();
}


Last the properties and methods are obvious.

Conversions between WKB, WKT, GML and geography/geometry types in the SQL Server

If we want to make a proper conversion from WKB/WKT or GML formats to geography/geometry SQL Server data types, we have to follow next rules about coordinates order.

From / To	geography	geometry
WKB/WKT	long-lat	x-y
GML	lat-long	x-y

For example, making conversion from WKB/WKT to geography data type, we must write coordinates to WKB in long-lat order.

According to OGC geometries specification coordinates are stored in the program class DirectPosition. This class speaks nothing about latitude or longitude it is just a sequence of numbers.

DirectPosition::coordinate : Sequence<Number>

But this class has a reference to Coordinate Reference System (CRS) in which the DirectPosition coordinates are given, and through the CRS it has a reference to Coordinate System (CS) and its axes with the fixed in the EPSG database standard order.

As it was showed in the topic "About polygon orientations in EPSG/OGC standards" there is no problem with geographic CRSs becouse all, not deprecated, CRSs are based on just three CSs with the axes labeled with same abbreviations and enumerated in the same order.

So, as a practical result, for geographic CRSs, during serialization in to WKB/WKT format, we must write a second coordinate as first and first as second. And what is all, and what is relatively for always.

In the case of projects CRSs (and compound CRSs), it seems, we meet a problem, because of coordinate axes labels are not always "x" and "y" and not always "x" preсeds "y". But, if we bethink, what for SQL Server, the ring orientation does not matter in any Cartesian CS then we will store coordinates with simple algorithm: 1) if CS axes labeled with "x" and "y" — we calculate right order (most of common cases); 2) if axes labeled in another way — we store them as they come from the DirectPosition array; 3) also we can add a predefined cases to the algorithm of WKB/WKT creation.

To my mind, it is not too good, to extend an algorithm of the WKT/WKT serialization with cases, dependent on used CS.

Polygons in SQL Server

The orientation of the polygon rings in SQL Server does matter if we are using geographical data type. For outer rings we have to use counter-clockwise direction and the opposite direction for inner rings, which usually define holes.

Here is the test.

A polygon with a hole (note, coordinates are in lat-lon order) in GML format.

1. <Polygon xmlns="http://www.opengis.net/gml">
2. <exterior>
3. <LinearRing>
4. <posList>15.0 30.0 15.0 20.0 5.0 25.0 15.0 30.0</posList>
5. </LinearRing>
6. </exterior>
7. <interior>
8. <LinearRing>
9. <posList>12.0 27.0 8.0 25.0 12.0 23.0 12.0 27.0</posList>
10. </LinearRing>
11. </interior>
12. </Polygon>
    

We have two rings, the first is outer (counter-clockwise oriented), and the second is inner (clockwise oriented).

The next C# code works fine.

1. XmlReader xmlReader = XmlReader.Create(new FileStream(@"k:\GIS\poly.xml", FileMode.Open));
2. SqlXml sqlXml = new SqlXml(xmlReader);
3. SqlGeography sqlGeography = SqlGeography.GeomFromGml(sqlXml, 4326);
   

If test conversion with the contrary directions (i just swap two middle points for each polygon ring), we'll get the next exception message.

GLArgumentException

"24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation."

For geometry SQL Server data type the polygon orientation does not matter. We can try both cases with the next program code.

1. XmlReader xmlReader = XmlReader.Create(new FileStream(@"k:\GIS\poly.xml", FileMode.Open));
2. SqlXml sqlXml = new SqlXml(xmlReader);
3. SqlGeometry sqlGeometry = SqlGeometry.GeomFromGml(sqlXml, 0);
   

Of course, it is not too good, to have different rules for the same tasks, but, taking in account previous topic "About polygon orientations in EPSG/OGC standards" it looks not too bad. We can't control the projected coordinate systems orientation, and as result, we can't control the orientation of the polygon rings. And it is just a lucky case, what we can control it for geographic coordinate systems.

About polygon orientation in EPSG/OGC standards

If we look at the EPSG dataset, we can find a lot of Coordinate Reference Systems (CRS) with Coordinate Systems (CS) associated to them. Each CS has an ordered set of axes (which will later be repeated in coordinates order of the geometry objects, uses this CS).

The labeling of axes directions is not standardized and some times labels looks very arbitrary (some examples are presented in the Table 1). Also database haven't any information about coordinate system orientation.

	COORD_AXIS_ORIENTATION	COORD_AXIS_ABBREVIATION
1	east	Long
2	down	D
3	east	E
4	EAST	u
5	east south east	I
6	east-south-east	I
7	north	Lat
8	Geocentre > equator/0°E	X
9	Geocentre > equator/90°E	Y

Table 1. Some examples of the axes direction labeling in EPSG dataset

Information about ring orienation is used in some optimized geometry algorithms, and allows to determine if we have deal with outer ring, or the hole of the polygon.To compute the polygon ring orientation we must know the orientation of the CS in which this ring is set, but by the mentioned reasons we can't do what automatically. Also the orientation is a very helpfull in work on ellipsoidal (bounded) CSs, there we cann't use a ray-crossing algorithm to determine, for example, if equator ring describes south or north hemisfere — in ellipsoidal CSs we can do it correctly just using information about ring orientation.

As result, we can't develop universal programs, that can work with different CRSs. Our programs can work just with predefined CRSs with a manually setted information about their orientation.

One lucky case with geographic CRSs. All geographic, not deprecated, CRSs in EPSG database (version 6.18) are based just on three CSs. Next SQL statement shows it.

1. SELECT ca.[COORD_SYS_CODE], [COORD_REF_SYS_KIND], [COORD_AXIS_ORIENTATION], [COORD_AXIS_ABBREVIATION], [ORDER]
   
2. FROM [EPSG_v6_18].[dbo].[Coordinate Axis] AS ca
3. INNER JOIN (SELECT DISTINCT [COORD_REF_SYS_KIND], [COORD_SYS_CODE]
4. FROM [EPSG_v6_18].[dbo].[Coordinate Reference System]
5. WHERE [COORD_REF_SYS_KIND] LIKE 'geographic%' AND [DEPRECATED] <> 1) AS cs
6. ON (ca.COORD_SYS_CODE = cs.COORD_SYS_CODE)

The result set...

	COORD_SYS_CODE	COORD_REF_SYS_KIND	COORD_AXIS_ORIENTATION	COORD_AXIS_ABBREVIATION	ORDER
1	6403	geographic 2D	north	Lat	1
2	6403	geographic 2D	east	Long	2
3	6422	geographic 2D	north	Lat	1
4	6422	geographic 2D	east	Long	2
5	6423	geographic 3D	north	Lat	1
6	6423	geographic 3D	east	Long	2
7	6423	geographic 3D	up	h	3

Table 2. The CSs used by geographic CRSs and their axes, fetche from EPSG-daset

The axes of the each CS have the same labeling, same orientation and order. Each CS is right-oriented. The just difference between 2D CSs is in used unit of measure for axis.

о google maps и booble drugs

По ссылке рассказ о том, как устроен google maps. С картинками, иллюстрациями: всё как положено.
Завершается рассказ демонстрацией результата переосмысления google maps в сторону booble drugs. Сохраняем карты с google maps!
Не понятно? Читаем! Скачиваем.

UPD.
Google постоянно изменяет версии тэйлов и префексы доменов с которых скачиваются изображения. Предлагаемая программа не умеет (не научили её) автоматически распознавать эти префиксы и коды версий (хотя это возможно), поэтому актуализировать программу нужно вручную, редактированием файла Settings.cs... ну и с последующей перекомпиляцией. )