{"id":2737,"date":"2015-01-11T17:05:00","date_gmt":"2015-01-11T17:05:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/odatateam\/2015\/01\/11\/how-to-consume-sql-spatial-data-with-web-api-v2-2-for-odata-v4\/"},"modified":"2024-02-16T15:04:31","modified_gmt":"2024-02-16T22:04:31","slug":"how-to-consume-sql-spatial-data-with-web-api-v2-2-for-odata-v4","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/odata\/how-to-consume-sql-spatial-data-with-web-api-v2-2-for-odata-v4\/","title":{"rendered":"How to consume SQL Spatial Data with Web API V2.2 for OData V4"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p>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.<\/p>\n<p>This post is intended to give a\u00a0tutorial about how to consume the SQL Spatial Data through EF &amp; <a href=\"https:\/\/www.nuget.org\/packages\/Microsoft.AspNet.OData\/\"><span style=\"text-decoration: underline;\">Web API V2.2 for OData V4<\/span><\/a>. The method in this blog is similar to the method in <a href=\"https:\/\/www.odata.org\/blog\/how-to-use-sql-spatial-data-with-wcf-odata-spatial\/\"><span style=\"text-decoration: underline;\">How to use SQL Spatial Data with WCF ODATA Spatial<\/span><\/a><span style=\"text-decoration: underline;\">, <\/span>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 <a href=\"https:\/\/www.asp.net\/web-api\/overview\/odata-support-in-aspnet-web-api\/odata-v4\/create-an-odata-v4-endpoint\">Create an OData v4 Endpoint Using ASP.NET Web API 2.2<\/a>.<\/p>\n<p>Ok, let\u2019s get started.<\/p>\n<h2>Overview of Spatial Data types<\/h2>\n<p>First of all, let\u2019s quickly review the Spatial Data types both in OData and SQL. OData defines eight Spatial Data types for geography &amp; geometry respectively. Both of them are implemented in the <strong><em>Microsoft.Spatial<\/em><\/strong> name-space of the corresponding OData library. However, the CLR classes for SQL Spatial Data types are defined in the <strong><em>System.Data.Spatial<\/em><\/strong> namespace, in which class <strong>DbGeography<\/strong> is for geography Spatial Data type and class <strong>DbGeometry <\/strong>is for geometry Spatial Data type.<\/p>\n<p>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.<\/p>\n<table style=\"width: 550px;\" border=\"1\" cellspacing=\"0\" cellpadding=\"2\">\n<caption><span style=\"color: #870098;\"><strong>OData &amp; SQL Spatial Data Types and the Mapping<\/strong><\/span><\/caption>\n<tbody>\n<tr align=\"CENTER\" bgcolor=\"#989797\">\n<th><strong>Geography*<\/strong><\/th>\n<th><strong>Geometry*<\/strong><\/th>\n<th><strong>SQL Spatial Data Type<\/strong><\/th>\n<\/tr>\n<tr align=\"LEFT\">\n<td bgcolor=\"#add3ad\">Edm.Geography<\/td>\n<td bgcolor=\"#f6d96d\">Edm.Geometry<\/td>\n<td bgcolor=\"#d8c0b1\">~<\/td>\n<\/tr>\n<tr align=\"LEFT\">\n<td bgcolor=\"#add3ad\">Edm.GeographyPoint<\/td>\n<td bgcolor=\"#f6d96d\">Edm.GeometryPoint<\/td>\n<td bgcolor=\"#d8c0b1\">Point<\/td>\n<\/tr>\n<tr align=\"LEFT\">\n<td bgcolor=\"#add3ad\">Edm.GeographyLineString<\/td>\n<td bgcolor=\"#f6d96d\">Edm.GeometryLineString<\/td>\n<td bgcolor=\"#d8c0b1\">LineString<\/td>\n<\/tr>\n<tr align=\"LEFT\">\n<td bgcolor=\"#add3ad\">Edm.GeographyPolygon<\/td>\n<td bgcolor=\"#f6d96d\">Edm.GeometryPolygon<\/td>\n<td bgcolor=\"#d8c0b1\">Polygon<\/td>\n<\/tr>\n<tr align=\"LEFT\">\n<td bgcolor=\"#add3ad\">Edm.GeographyMultiPoint<\/td>\n<td bgcolor=\"#f6d96d\">Edm.GeometryMultiPoint<\/td>\n<td bgcolor=\"#d8c0b1\">MultiPoint<\/td>\n<\/tr>\n<tr align=\"LEFT\">\n<td bgcolor=\"#add3ad\">Edm.GeographyMultiLineString<\/td>\n<td bgcolor=\"#f6d96d\">Edm.GeometryMultiLineString<\/td>\n<td bgcolor=\"#d8c0b1\">MultiLineString<\/td>\n<\/tr>\n<tr align=\"LEFT\">\n<td bgcolor=\"#add3ad\">Edm.GeographyMultiPolygon<\/td>\n<td bgcolor=\"#f6d96d\">Edm.GeometryMultiPolygon<\/td>\n<td bgcolor=\"#d8c0b1\">MultiPolygon<\/td>\n<\/tr>\n<tr align=\"LEFT\">\n<td bgcolor=\"#add3ad\">Edm.GeographyCollection<\/td>\n<td bgcolor=\"#f6d96d\">Edm.GeometryCollection<\/td>\n<td bgcolor=\"#d8c0b1\">GeometryCollection<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<h2>Create database with Spatial Data types<\/h2>\n<h2>Code First<\/h2>\n<p>Let\u2019s define a simple CLR class and use Code First to perform the database access.<\/p>\n<p><script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/0193791886ff228d8a35.js\"><\/script><\/p>\n<p>Where, <strong>Location<\/strong> describes a point, <strong>Line<\/strong> describes a LineString.<\/p>\n<h2>Database context<\/h2>\n<p>Based on the above class, we can define a class derived from <strong>DbContext<\/strong> to represent a connection with the Database, by which we can create, query, update and delete the data.<\/p>\n<p><script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/bbf64cf8396f390c2e37.js\"><\/script><\/p>\n<p>For simplicity, we create the following sample \u201c<strong>CustomerGeoContext<\/strong>\u201d table for test.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/odatateam\/wp-content\/uploads\/sites\/23\/2015\/01\/0741.sd_1.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/odatateam\/wp-content\/uploads\/sites\/23\/2015\/01\/0741.sd_1.png\" alt=\"\" border=\"0\" \/><\/a><\/p>\n<h2>Apply<strong> the <\/strong>OData Spatial Data types<\/h2>\n<h2><strong>Wrapping the Spatial Data types<\/strong><\/h2>\n<p>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\u00a0add more into this wrapper to convert other Spatial Data types.<\/p>\n<p><span style=\"color: #339966;\">\/\/GeographyWrapper<\/span><\/p>\n<p><script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/27934dbf20e0da243f48.js\"><\/script><\/p>\n<p><span style=\"color: #339966;\">\/\/GeographConvert<\/span><\/p>\n<p><script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/532d46ab352485a9ca18.js\"><\/script><\/p>\n<h2>Change the Model<\/h2>\n<p>Based on the wrapper, we can change the class to apply the OData Spatial Data types:<\/p>\n<p><script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/9d7128fe6edd83b9de1a.js\"><\/script><\/p>\n<p>Where, <strong><em>EdmLocation <\/em><\/strong>and <strong><em>EdmLine <\/em><\/strong>are new added properties and marked them by <strong>NotMappedAttribute<\/strong> to exclude them from database mapping.<\/p>\n<h2><strong>Build Edm Mode<\/strong><\/h2>\n<p>Now, we can use the model builder to build the Edm model.<\/p>\n<p><script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/5010ec34599570be2608.js\"><\/script><\/p>\n<p>Where, two <strong><em>Ignore<\/em><\/strong>() calls are necessary to exclude the <strong>DbGeography<\/strong> type from final Edm model.<\/p>\n<h2><strong>Expose Metadata document<\/strong><\/h2>\n<p>Once the Edm model is built, we can query the metadata document as:<\/p>\n<p><script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/1e113128787dcfcce08c.js\"><\/script><\/p>\n<p>From the metadata document, we can find that:<\/p>\n<ol>\n<li><strong>Customer<\/strong> is an entity type with four properties.<\/li>\n<li><strong>Location<\/strong>and <strong>Line<\/strong> are OData Spatial Data types.<\/li>\n<\/ol>\n<h2><strong>Consume SQL Spatial Data<\/strong><\/h2>\n<p>It\u2019s time to query the SQL Spatial Data through Web API OData service.<\/p>\n<h2><strong>Build OData Controller<\/strong><\/h2>\n<p>Let\u2019s build a Web API convention controller in which we provide the basic query functionalities.\n<script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/15e57dd1d365ffc90bc5.js\"><\/script><\/p>\n<h2><strong>Expose single entity<\/strong><\/h2>\n<p>Let\u2019s have an example to query single entity with spatial data.<\/p>\n<p>Request:\n<strong>GET<\/strong> ~\/Customers(2)<\/p>\n<p>Here&#8217;s the response:\n<script type=\"text\/javascript\" src=\"https:\/\/gist.github.com\/xuzhg\/0a4069cfe419896c540a.js\"><\/script>\nThanks.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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\u00a0tutorial about how to consume the SQL Spatial Data through EF &amp; Web [&hellip;]<\/p>\n","protected":false},"author":514,"featured_media":3253,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2737","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-odata"],"acf":[],"blog_post_summary":"<p>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\u00a0tutorial about how to consume the SQL Spatial Data through EF &amp; Web [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts\/2737","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/users\/514"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/comments?post=2737"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts\/2737\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/media\/3253"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/media?parent=2737"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/categories?post=2737"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/tags?post=2737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}