LINQ to SQL and SQL Server Spatial Data

Starting from version 2008 (and so far ending with it) MS SQL Server has built-in support for spatial data. Perfectly!

At the moment, there are already several DBMSs offering indexed storage of spatial data. Probably the most popular ones are: MySql and PostGIS.

When programming in c #, naturally, in very many cases, you give preference to Microsoft products and solutions. The reasons are simple: better support of some technologies by others, good documentation, better implementation, for example, data providers, and much less buggy. I chose SQL Server. At the same time I wanted to learn LINQ in general and LINQ to SQL in. particular.
')
At first everything was fine. For me, a good start was made by the article “ LINQ to SQL: .NET Language-Integrated Query for Relational Data ” found on msdn.
But I was not very surprised when “all is well” ended.

Two additional types have been introduced to store geometric data in SQL Server: geometry and geography. The first is used to store geometric objects described in the Cartesian coordinate system, and the second - for geometric objects specified by geographic coordinates (latitude / longitude).
This separation, apparently, had to be done due to the fact that the spatial index is implemented in SQL Server based on B-trees . When using this index, the space is patterned by the grid several times and references to geometric objects are saved in the “cells” of this grid. And it turned out to be impossible to build a universal partition for both the rectangular coordinate system and the ellipsoidal one. In MySql, for example, a different indexing algorithm based on R-trees , operating on a completely different principle, is chosen, and one data type is used. Which way of indexing is better and which is worse is not obvious, so it is not yet clear who to swear at and whether it is worth it.

It turned out that LINQ to SQL does not understand these data types and refuses to work with them, as well as with the built-in geometric functions. Although it is probably more correct to say that the provider does not understand them. In any case, I am sure that these data will be supported, but now there is no such support.

I could not find a solution in the Internet that avoided this problem, so I had to invent it myself. There are no amazing moves here, but there are details that I think will be interesting. Also in this great note, for your interest, I will describe a little bit how to work with LINQ to SQL.

Database

For example, we will use the following table.


To create it, use the following script.

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 .

1. 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 .
2. 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 .
4. 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 .
5. 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 .
6. 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 .
7. 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 .
8. 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 .
9. 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. 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 .
12. 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 .
13. 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 .

In line 11, for the CountryBoundary field with the geography data type, a spatial index is created with default settings.

In order to have something to work with, I filled out the table with multipolygons of the countries of the world, the shape-file for which was found on the Internet. Several countries did not want to convert - I did not understand why it was not important, although for Russia, of course, insulting.

SQL Server has a nice built-in viewer (well, now everyone can see that I am writing with errors).

Getting Started with LINQ to SQL

To work with LINQ to SQL, you need to add references to two assemblies to your project: System.Data.Linq and Microsoft.SqlServer.Types. If there are no problems with the first library (it can be found on the “.NET” tab of the “Add Reference” form - adding links to the library used in the project), then the second one will need to be searched in the “C: \ Program Files \ Microsoft SQL Server \ 100 directory” \ SDK \ Assemblies \ ". In order for the last build to continue to be displayed in the “.NET” tab of the add assembly form, you need to register it once with the gacutil utility.

The first step in using LINQ to SQL is to create mapping classes for database tables.

One table - one class.

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

* This source code was highlighted with Source Code Highlighter .

Attributes are placed above the class declaration and above the fields. For example, in line 7, the Table attribute indicates that this class is associated with a table in the database. If the class name matches the table name, then the attribute can be written as I have, and if not, then the Name property should be specified: [Table (Name = "Boundaries_Country")].

In line 16, when describing an attribute for a field containing spatial data, in theory, I have to specify the geography data type, but since I don’t specify support for this data type yet, I don’t specify it.

Another class is needed to create a database context. We can use the already existing DataContext, but it is better to make our successor for strong typing.

1. using System.Data.Linq;
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. {
12. }
13. }
14. }

* This source code was highlighted with Source Code Highlighter .

An example, we will remove from the database all that is, but so that the name of the country begins with the letter "C".

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

* This source code was highlighted with Source Code Highlighter .

It turned out not so much.

One interesting point. If, in debug mode, stop the execution of the program on line 9 and view the contents of the variable q, then we will see a LINQ to SQL generated query.

LINQ to SQL: working with spatial data

Consider a query that selects countries from a database that fall into a given rectangle, and whose name begins with the letter “C”.

The rectangle is defined by a polygon (WKT-representation): 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;
6. foreach ( var item in q)
7. Console .WriteLine (item.CountryName);

* This source code was highlighted with Source Code Highlighter .

This code will compile, but will not work.

At runtime, on line 5, when LINQ to SQL needs to send a request to the server, an exception will be thrown: “Method 'System.Data.SqlTypes.SqlBoolean STIntersects (Microsoft.SqlServer.Types.SqlGeography) . "

To solve this problem, we will use stored procedures and table-valued functions, and we will send the geometric objects to the server in a well-understood SQL Server'u binary WKB format.

Stored procedures

To sample geometric shapes according to the criterion of hitting a given rectangle for one particular table, it is sufficient to create the following simple stored procedure.

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 .

The input parameter is a rectangle (specified by a polygon) in WKB format. In line 8, it is converted by the static method STGeomFromWKB into an object of the geography data type and already on it the STIntersects function is called, which checks for a specific boundary in a rectangle.

In the program, in the class that implements DataContext (we have this class called ExampleDatabase), we will describe the wrapper for calling this procedure.

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 .

Here, as well as for tables, attributes for functions and parameters are described.

In line 4, the stored procedure is called and the result is stored in execResult, then, in line 5, it is converted to the required data type and returned to the main program.

We use this "joy" as follows:

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 .

Result to console.

Note, if you, as in my working draft, have several tables with geographic data, then the following options are available.

1. For each table, create its own stored procedure, and in the program for each stored procedure its own wrapper function. You can simplify the life of the user api if you write a “central” generic method in which, according to the current type used when calling the generic method, the private method wrapper of stored procedures will be called and the necessary type conversions will be performed.
   
2. Write one stored procedure using dynamic sql. The program will need to make one generic method, from which also, as in the previous version, specialized (by data type) wrapper methods around the same procedure will be called (it was not possible to escape from the attack, did not fight).

Stored procedures are good, but when used in LINQ to SQL, in the described manner, they have one major drawback: the stored procedures are executed immediately and from the server to the client all that fall into a given region, countries, and only then over this array are executed additional filtering. Those. Translation in SQL of the entire LINQ expression does not occur. To avoid this problem, we can use SQL Server inline functions.

Table-valued functions

A table-valued function that retrieves records from a database table by the criterion of entering a specified region can be created as follows.

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 .

Those. the content is completely similar to the stored procedure described earlier.

For the function, you also need to create your own wrapper.

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 .

In the attribute of the method, we specify the IsComposable property, which says that now we will run the function on SQL Server, and not the stored procedure. To call a function, use the CreateMethodCallQuery method.

See an example.

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);

* This source code was highlighted with Source Code Highlighter .

The result is the same as when using a stored procedure.

And in debugging we see a beautiful picture (all linq-expression was translated into a sql query):


Everything, I have nothing more to say to the people.