🍲 👍🏾 🕯️ LINQ to SQLおよびSQL Server空間データ 🤳🏿 🏇 🕕

バージョン2008以降（これまでで終了）、MS SQL Serverには空間データのサポートが組み込まれています。いいね！

現時点では、空間データのインデックスストレージを提供するDBMSが既にいくつかあります。おそらく最も人気があるのは、「フォーク」MySqlとPostGISです。

C＃でプログラミングする場合、当然多くの場合、Microsoft製品とソリューションを優先します。理由は簡単です。他のテクノロジーによる一部のテクノロジーの完全なサポート、優れたドキュメント、データプロバイダーなどのより完全な実装、およびグリッチの大幅な削減です。 SQL Serverを選択しました。同時に、LINQ全般とLINQ to SQLを学びたいと思いました。特に。

最初はすべて良かった。 msdnで作成された記事「 LINQ to SQL：リレーショナルデータの.NET 言語統合クエリ」は、私にとって良い出発点となりました。

しかし、「すべてが順調」に終わったとき、私はあまり驚かなかった。

ジオメトリックデータを格納するために、ジオメトリとジオグラフィという2つの追加タイプがSQL Serverに導入されました。 1つ目はデカルト座標系で記述された幾何学オブジェクトを保存するために使用され、2つ目は地理座標（緯度/経度）で指定された幾何学オブジェクトのために使用されます。

明らかに、空間インデックスはBツリーに基づいてSQL Serverに実装されているため、このような分離を行う必要がありました。 このインデックスを使用すると、スペースは定期的にグリッドで数回分割され、幾何学的オブジェクトへの参照はこのグリッドの「セル」に保存されます。 また、直交座標系と楕円座標系の両方にユニバーサルパーティションを構築することは不可能であることが判明しました。 たとえば、MySqlでは、別のRツリーベースのインデックスアルゴリズムが選択され、まったく異なる原則に基づいて動作し、1つのデータ型が使用されます。 どのインデックス作成方法が優れているか、どのインデックス作成方法が悪いかは 明らかで は ないため、誰がそれを誓うのか、それが価値があるのかはまだ明確ではありません。

LINQ to SQLはこれらのデータ型を理解せず、組み込みの幾何学的関数と同様にそれらを操作することを拒否することが判明しました。ただし、プロバイダーがそれらを理解していないと言う方がおそらく正しいでしょう。いずれにせよ、このデータはサポートされると確信していますが、現在はそのようなサポートはありません。

この問題を回避する解決策をインターネット上で見つけることができなかったため、自分で解決する必要がありました。ここには驚くような動きはありませんが、興味深いと思う詳細があります。また、この大きなメモでは、興味のある方のために、LINQ to SQLの動作について少し説明します。

データベース

例として、次の表を使用します。

次のスクリプトを使用して作成しました。

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

USE ExampleDatabase; GO -- Create table CREATE TABLE Boundaries_Country( FeatureID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY , CountryName VARCHAR (100) NOT NULL UNIQUE , CountryBoundary GEOGRAPHY NOT NULL ) CREATE SPATIAL INDEX SpatialIndex ON Boundaries_Country (CountryBoundary); GO * This source code was highlighted with Source Code Highlighter .

11行目では、geographyデータタイプのCountryBoundaryフィールドに対して、空間インデックスがデフォルト設定で作成されます。

何か作業をするために、私はテーブルを世界の国々のマルチポリゴンで満たしました。シェイプファイルはインターネット上で見つかりました。いくつかの国は改宗を望んでいませんでした-ロシアにとっては確かに恥ずべきことでしたが、なぜ重要ではなかったのか理解できませんでした。

SQL Serverには優れたビルトインビューアーがあります（まあ、今では誰もが私が書いているものをエラーで表示します）。

LINQ to SQLの使用を開始する

LINQ to SQLを使用するには、プロジェクト内の2つのアセンブリSystem.Data.LinqおよびMicrosoft.SqlServer.Typesへの参照を追加する必要があります。最初のライブラリに問題がない場合（「参照の追加」フォームの「.NET」タブで見つけることができます-プロジェクトで使用されるライブラリへのリンクを追加します）、2番目は「C：\ Program Files \ Microsoft SQL Server \ 100」ディレクトリで検索する必要があります\ SDK \アセンブリ\ "。アセンブリの追加フォームの[.NET]タブに最新のアセンブリが引き続き表示されるようにするには、gacutilユーティリティを使用して一度登録する必要があります。

LINQ to SQLを使用する最初の手順は、データベーステーブルのマッピングクラスを作成することです。

1つのテーブル-1つのクラス。

システムを使用して ;
using System.Data.Linq.Mapping;
using Microsoft.SqlServer.Types;
名前空間 MyNamespace
{
[表（）]
パブリックシールクラス Boundaries_Country
{
[列（AutoSync = AutoSync.OnInsert、DbType = "uniqueidentifier" 、IsPrimaryKey = true 、IsDbGenerated = true 、UpdateCheck = UpdateCheck.Never）]
public Guid FeatureID;
[列（DbType = "varchar（100）" 、CanBeNull = false ）]
パブリック文字列 CountryName;
[列（ / * DbType = "geography"、* / CanBeNull = false ）]
public SqlGeography CountryBoundary;
}
}
*このソースコードは、ソースコードハイライターで強調表示されました。

属性はクラス宣言とフィールドの上に配置されます。たとえば、7行目のTable属性は、このクラスがデータベース内のテーブルに関連付けられていることを示しています。クラス名がテーブルの名前と一致する場合、属性は現在のように記述できますが、そうでない場合は、追加のプロパティName：[Table（Name = "Boundaries_Country"）]を指定する必要があります。

16行目では、空間データを含むフィールドの属性を説明するときに、理論上、地理データタイプを指定する必要がありますが、このデータタイプはまだサポートされていないため、指定しません。

データベースコンテキストを作成するには、別のクラスが必要です。既存のDataContextを使用できますが、厳密な型指定のために独自の子孫を作成することをお勧めします。

using System.Data.Linq;
名前空間 MyNamespace
{
パブリッククラス ExampleDatabase：DataContext
{
パブリックテーブル<Boundaries_Country> BoundariesCountry;
public ExampleDatabase（ string connectionString）
：ベース（connectionString）
{
}
}
}
*このソースコードは、ソースコードハイライターで強調表示されました。

たとえば、データベースからすべてのものを抽出しますが、国の名前は文字「C」で始まります。

static void Main（ string [] args）
{
ExampleDatabase db = new ExampleDatabase（ @ "..." ）;
var q = db.BoundariesCountryのアイテムから
where item.CountryName.StartsWith（ "C" ）
アイテムを選択します。
foreach （qのvarアイテム）
Console .WriteLine（item.CountryName）;
}
*このソースコードは、ソースコードハイライターで強調表示されました。

それほど多くはありませんでした。

興味深い点が1つあります。デバッグモードで9行目でプログラムの実行を停止し、q変数の内容を表示すると、生成されたLINQ to SQLクエリが表示されます。

LINQ to SQL：空間データの操作

データベース「国」から、指定された長方形に分類され、名前が文字「C」で始まる国を選択するクエリを検討します。

長方形は、ポリゴン（WKTビュー）によって定義されます：POLYGON（（40 -28、40 30、5 30、5 -28、40 -28））。

var q = db.BoundariesCountryのアイテムから
ここで item.CountryName.StartsWith（ "C" ）&&
item.CountryBoundary.STIntersects（sqlEnvelope）.Value
アイテムを選択します。
foreach （qのvarアイテム）
Console .WriteLine（item.CountryName）;
*このソースコードは、ソースコードハイライターで強調表示されました。

このコードはコンパイルされますが、機能しません。

実行時、5行目で、LINQ to SQLがサーバーに要求を送信するように要求されると、例外がスローされます。「Method 'System.Data.SqlTypes.SqlBoolean STIntersects（Microsoft.SqlServer.Types.SqlGeography）'にはSQLへのサポートされた変換がありません」。

この問題を解決するために、ストアドプロシージャとテーブル値関数を使用し、SQL Serverが十分に理解できるバイナリ形式のWKBでジオメトリオブジェクトをサーバーに送信します。

ストアドプロシージャ

特定のテーブルの特定の長方形に収まるという基準で幾何学的形状を選択するには、次の単純なストアドプロシージャを作成するだけで十分です。

CREATE PROCEDURE [dbo] [Sp_bbx_Boundaries_Country]
@boundingBox varbinary（ max ）
として
開始
NOCOUNT ONを設定します。
選択 *
FROM dbo.Boundaries_Country
WHERE GEOGRAPHY :: STGeomFromWKB（@boundingBox、
4326）.STIntersects（CountryBoundary）= 1;
戻る
終了
*このソースコードは、ソースコードハイライターで強調表示されました。

入力パラメーターは、WKB形式の長方形（ポリゴンで指定）です。 8行目では、静的メソッドSTGeomFromWKBによってgeographyデータ型のオブジェクトに変換され、STIntersects関数が呼び出されて、四角形の特定の境界を確認します。

プログラムでは、DataContextを実装するクラス（このクラスはExampleDatabaseと呼ばれます）で、このプロシージャを呼び出すためのラッパーを記述します。

[機能（）]
public ISingleResult <境界国> sp_bbx_Boundaries_Country（
[パラメーター（DbType = "varbinary（max）" ）] バイト [] boundingBox）
{
IExecuteResult execResult = this .ExecuteMethodCall（ this 、（（MethodInfo）
（MethodInfo.GetCurrentMethod（）））、boundingBox）;
ISingleResult <Boundaries_Country> result =
（（ISingleResult <Boundaries_Country>）execResult.ReturnValue）;
結果を返す ;
}
*このソースコードは、ソースコードハイライターで強調表示されました。

ここでは、テーブルと同様に、関数とパラメーターの属性について説明します。

4行目で、ストアドプロシージャが呼び出され、結果がexecResultに格納されます。次に、5行目で必要なデータ型に変換され、メインプログラムに返されます。

この「喜び」を次のように使用します。

var q = db.sp_bbx_Boundaries_Countryのアイテムから（
sqlEnvelope.STAsBinary（）。バッファー）
where item.CountryName.StartsWith（ "C" ）
アイテムを選択します。
foreach （qのvarアイテム）
Console .WriteLine（item.CountryName）;
*このソースコードは、ソースコードハイライターで強調表示されました。

コンソールの結果。

私の作業ドラフトのように、地理データを含む複数のテーブルがある場合、次のオプションが利用可能であることに注意してください。

テーブルごとに独自のストアドプロシージャを作成します。各ストアドプロシージャのプログラムには独自のラッパー関数があります。ジェネリックメソッドを呼び出すときに使用される実際の型に応じて、ストアドプロシージャのプライベートラッパーメソッドが呼び出され、必要な型変換が実行される「中央」ジェネリックメソッドを記述することにより、APIユーザーの生活を簡素化できます。
動的SQLを使用して単一のストアドプロシージャを記述します。プログラムは、前のバージョンのように、同じプロシージャの特殊な（データ型による）ラッパーメソッドを呼び出す汎用メソッドを1つ作成する必要があります（それから逃れることはできず、戦うことはできませんでした）。

ストアドプロシージャは優れていますが、LINQ to SQLで説明した方法で使用すると、1つの重大な欠点があります。ストアドプロシージャはすぐに実行され、指定された地域にあるすべての国、サーバーからクライアントに国が送信され、この配列のみが実行されます追加のフィルタリング。つまり LINQ式全体のSQLでの変換は行われません。この問題を回避するために、SQL Serverインライン関数を使用できます。

テーブル値関数

特定の領域に入るための基準によってデータベーステーブルからレコードを取得するテーブル値関数は、次のように作成できます。

CREATE FUNCTION [dbo]。[F_bbx_Boundaries_Country]
（
@boundingBox varbinary（ max ）
）
返品表
として
戻る
（
選択 *
FROM dbo.Boundaries_Country
WHERE GEOGRAPHY :: STGeomFromWKB（
@ boundingBox、4326）.STIntersects（CountryBoundary）= 1
）
*このソースコードは、ソースコードハイライターで強調表示されました。

つまり内容は、前述のストアドプロシージャに完全に類似しています。

また、関数の独自のラッパーを作成する必要があります。

[関数（IsComposable = true ）]
public IQueryable <Boundaries_Country> f_bbx_Boundaries_Country（
[パラメーター（DbType = "varbinary（max）" ）] バイト [] boundingBox）
{
この .CreateMethodCallQuery <Boundaries_Country>（ this 、
（（MethodInfo）（MethodInfo.GetCurrentMethod（）））、boundingBox）;
}
*このソースコードは、ソースコードハイライターで強調表示されました。

メソッド属性で、IsComposableプロパティを指定します。これは、ストアドプロシージャではなく、SQL Serverで関数を実行することを示しています。関数を呼び出すには、CreateMethodCallQueryメソッドを使用します。

例を見てみましょう。

var q = db.f_bbx_Boundaries_Countryのアイテムから（
sqlEnvelope.STAsBinary（）。バッファー）
where item.CountryName.StartsWith（ "C" ）
アイテムを選択します。
foreach （qのvarアイテム）
Console .WriteLine（item.CountryName）;
*このソースコードは、ソースコードハイライターで強調表示されました。

結果は、ストアドプロシージャを使用した場合と同じです。

デバッグでは、美しい画像が表示されます（linq式全体がsqlクエリに変換されました）。

それだけです、私は人々にこれ以上言うことはありません。

LINQ to SQLおよびSQL Server空間データ

データベース

LINQ to SQLの使用を開始する

LINQ to SQL：空間データの操作

ストアドプロシージャ

テーブル値関数

More articles: