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

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

База данных

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.

Начало работы с 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.

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: работа с пространственными данными

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.

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

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.

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.

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-метод, из которого также, как и в предыдущем варианте будут вызывать специализированные (по типу данных) методы-обёртки вокруг одной и той же процедуры (с наскока уйти от этого не удалось, воевать не стал).

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.

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.