How to consume SQL Spatial Data with Web API V2.2 for OData V4

Sam Xu

Introduction

Today, along with the increasing demands of Location-Based Services (LBS), it becomes more and more important to provide functionalities on SQL Spatial Data through a unique, robust and scalable service based on a standard protocol.

This post is intended to give a tutorial about how to consume the SQL Spatial Data through EF & Web API V2.2 for OData V4. The method in this blog is similar to the method in How to use SQL Spatial Data with WCF ODATA Spatial, but the latter is based on WCF Data service. However, for more information about how to create a simple OData V4 service based on Web API, please refer to Create an OData v4 Endpoint Using ASP.NET Web API 2.2.

Ok, let’s get started.

Overview of Spatial Data types

First of all, let’s quickly review the Spatial Data types both in OData and SQL. OData defines eight Spatial Data types for geography & geometry respectively. Both of them are implemented in the Microsoft.Spatial name-space of the corresponding OData library. However, the CLR classes for SQL Spatial Data types are defined in the System.Data.Spatial namespace, in which class DbGeography is for geography Spatial Data type and class DbGeometry is for geometry Spatial Data type.

In order to make the SQL Spatial Data types working on Web API, we should make a mapping between them. Below are the Spatial Data types in OData and the mapping between SQL Spatial Data types.

Create database with Spatial Data types

Code First

Let’s define a simple CLR class and use Code First to perform the database access.

Where, Location describes a point, Line describes a LineString.

Database context

Based on the above class, we can define a class derived from DbContext to represent a connection with the Database, by which we can create, query, update and delete the data.

For simplicity, we create the following sample “CustomerGeoContext” table for test.

Apply the OData Spatial Data types

Wrapping the Spatial Data types

We use the explicit and implicit operator to define wrappers by which SQL Spatial Data types can convert to and from ODL Spatial Data types. Here, we provide the wrapper for point and LineString, users can easily add more into this wrapper to convert other Spatial Data types.

//GeographyWrapper

//GeographConvert

Change the Model

Based on the wrapper, we can change the class to apply the OData Spatial Data types:

Where, EdmLocation and EdmLine are new added properties and marked them by NotMappedAttribute to exclude them from database mapping.

Build Edm Mode

Now, we can use the model builder to build the Edm model.

Where, two Ignore() calls are necessary to exclude the DbGeography type from final Edm model.

Expose Metadata document

Once the Edm model is built, we can query the metadata document as:

From the metadata document, we can find that:

1. Customer is an entity type with four properties.
2. Locationand Line are OData Spatial Data types.

Consume SQL Spatial Data

It’s time to query the SQL Spatial Data through Web API OData service.

Build OData Controller

Let’s build a Web API convention controller in which we provide the basic query functionalities.

Expose single entity

Let’s have an example to query single entity with spatial data.

Request: GET ~/Customers(2)

Here’s the response: Thanks.

Sam Xu Senior Software Engineer, OData, Microsoft Graph, Open API, Swagger

Follow