Using Entity Framework Spatials with Oracle Spatial and SharpMap

Note

The information in this article is partially outdated. For the newer information see the Enhanced Entity Framework Spatials support for Oracle, MySQL and PostgreSQL article.

Spatials Overview

Entity Framework v5 introduces spatial data types support. They are represented as two new DbGeometry and DbGeography data types from System.Data.Entity.dll in .NET Framework 4.5.

Many our Oracle users use Oracle Spatial functionality in their projects. Before Entity Framework v5 there was no easy way to use Oracle Spatial with Entity Framework, though users of PostgreSQL, PostGIS, and SharpMap could use this cumbersome and limited workaround. This workaround was even harder to use with Oracle Spatial and was almost not used because of numerous limitations.

Now Entity Framework v5 changes the situation, and Devart dotConnect for Oracle provides the best possible support for Entity Framework Spatials and Oracle Spatial. It includes new versions of Devart.Data.Oracle.Entity.dll and Devart.Data.Oracle.Entity.Migrations.dll assemblies built for .NET Framework 4.5 (Entity Framework v5) and the new unsigned Devart.Data.Oracle.Entity.SharpMap.dll assembly for SharpMap library support.

A set of spatial-specific configuration settings was added. You can read about them in the Spatial Options section of dotConnect for Oracle documentation. All these settings are optional except for the spatial service that will be used for reading spatial objects from the database, saving them to the database, and run-time support for the functionality of DbGeometry/DbGeography classes.

The following spatial services were implemented:

• SharpMap spatial service
• Well-Known Text (WKT) spatial service
• Extended Well-Known Text (EWKT) spatial service
• Well-Known Binary (WKB) spatial service
• OracleObject spatial service

SharpMap spatial service provides the richest functionality out-of-the-box. Other spatial services can be used together with user/3rd-party GIS libraries if you implement the interaction between them. You can read more in the Choosing Spatial Service article.

SharpMap + Oracle Spatial

In order to use SharpMap spatial service, you need to add references to Devart.Data.Oracle.Entity.dll (EF v5) and Devart.Data.Oracle.Entity.SharpMap.dll to your project. Additionally you need to add references to the SharpMap assemblies SharpMap.dll and ProjNet.dll. Then set our SpatialServiceType provider configuration option in code:

  var config = OracleEntityProviderConfig.Instance;
  config.SpatialOptions.SpatialServiceType = SpatialServiceType.SharpMap;

Alternatively you can set this option in the config file of the applications.

SharpMap is an easy-to-use geospatial mapping library for use in web and desktop applications. It provides access to many types of GIS data and renders maps. Though it has a rich set of features, they are still limited in comparison with Oracle Spatial server-side functionality and with the claimed functionality of the DbGeometry/DbGeography spatial classes.

As we use Well-Known Text (WKT) spatial type representation for the interaction between Oracle and SharpMap, there are the following limitations:

• Supported for Oracle 10g Release 2 and higher
• Oracle Well-Known Text (WKT) parser has some limitations and cannot work with 3D coordinates (POINT (30 10 20)) or with the scientific E notation coordinates (2.0475839E23)
• Only IN parameters are supported

Note that it is recommended to use Oracle Spatial, not Oracle Locator for the wider support of LINQ to Entities queries because Oracle Locator functionality is rather limited.

Demo

We have created a small simple demo, showing some features of the following technologies:

• Oracle Spatial
• The SharpMap library:
  • Geometry objects (Point, LineString, Polygon, etc)
  • Visualization of geometry objects
• Entity Framework:
  • LINQ to Entities
  • Code-First
  • Entity Framework Spatials
• Devart dotConnect for Oracle ADO.NET Entity Framework provider
• Monitoring of SQL queries with Devart dbMonitor

This application demonstrates two kinds of actions: finding distances between cities and finding the area of regions. The corresponding cities and regions are displayed on the map. Here we won’t discuss the internal details of the demo implementation, you can view its source code to study the details.

This demos needs two tables in the Oracle database: REGIONS and CITIES. They store the borders of France departments and the locations of some French cities respectively.

We use a simple Entity Framework model consisting of DbContext and two classes with the attribute mapping for accessing the database.

As we need the results in kilometers and square kilometers, we need to set these units for spatial function execution in Oracle:

 var config = OracleEntityProviderConfig.Instance;
 config.SpatialOptions.GeographyDistanceUnit = DistanceMeasurementUnit.Kilometer;
 config.SpatialOptions.GeographyAreaUnit = AreaMeasurementUnit.SquareKilometer;

This is not mandatory, if you omit it, meters and square meters will be used. To read more about setting measurement units, see the Area and Distance Units topic.

Calculating Distances

The distance between two cities is calculated using a LINQ to Entities query using Oracle Spatial. The query also returns the coordinates of the both cities.

 var query = from city in ctx.Cities
             where city.Name == firstCityName
             let secondCity = ctx.Cities
               .Where(secondCity => secondCity.Name == secondCityName)
               .Select(secondCity => secondCity.Geometry)
               .FirstOrDefault()
             select new
             {
               FirstCity = city.Geometry,
               SecondCity = secondCity,
               Distance = city.Geometry.Distance(secondCity)
             };

You can see the SQL generated for this LINQ to Entities query using dbMonitor.

SELECT 
1 AS C1,
(CASE WHEN Extent1.Geometry IS NULL THEN NULL ELSE 'SRID=' || NVL(Extent1.Geometry.SDO_SRID, '0') || ';' || SDO_UTIL.TO_WKTGEOMETRY(Extent1.Geometry) END) AS Geometry,
(CASE WHEN Project1.Geometry IS NULL THEN NULL ELSE 'SRID=' || NVL(Project1.Geometry.SDO_SRID, '0') || ';' || SDO_UTIL.TO_WKTGEOMETRY(Project1.Geometry) END) AS Geometry1,
SDO_GEOM.SDO_DISTANCE(Extent1.Geometry, Project1.Geometry, 0.005, 'unit=KM') AS C2
FROM  Cities Extent1
LEFT OUTER JOIN  (SELECT 
        Extent2.Geometry
        FROM Cities Extent2
        WHERE (Extent2.Name = :p__linq__1) AND ROWNUM <= 1 ) Project1 ON 1 = 1
WHERE Extent1.Name = :p__linq__0

The result read is a Double distance value and two DbGeography objects, each containing a SharpMap object of the SharpMap.Geometries.Point type. When visualizing it we create a SharpMap.Geometries.LineString object using these two points and label the distance:

Calculating Areas

The second part of the demo application calculates the areas of regions that are polygons and multipoligons.

The area of the N biggest departments is performed with a LINQ to Entities query using Oracle Spatial. The region itself is also returned by this query.

 var query = ctx.Regions
   .Select(region => new
   {
     Geometry = region.Geometry,
     Area = region.Geometry.Area
   })
   .OrderByDescending(row => row.Area)
   .Take(N);

You can see the SQL generated for this LINQ to Entities query for a case of 10 regions using dbMonitor:

SELECT 
top.C1,
(CASE WHEN top.Geometry IS NULL THEN NULL ELSE 'SRID=' || NVL(top.Geometry.SDO_SRID, '0') || ';' || SDO_UTIL.TO_WKTGEOMETRY(top.Geometry) END) AS Geometry,
top.C2
FROM ( SELECT 
        Project1.Geometry,
        Project1.C1,
        Project1.C2
        FROM ( SELECT 
                Extent1.Geometry,
                1 AS C1,
                SDO_GEOM.SDO_AREA(Extent1.Geometry, 0.005, 'unit=SQ_KM') AS C2
                FROM Regions Extent1
        )  Project1
        ORDER BY Project1.C2 DESC
)  top
WHERE  ROWNUM <= 10

This screenshot demonstrates an interesting thing: there is a region with an area equal to 0 in the north eastern part of France. Oracle Spatial could not calculate the area for this region, which is a multipoligon having more than 3000 points on our test server and returned a NULL value. Oracle could not calculate the area of this region when using the WGS84 coordinate system. If the area of the same polygon is calculated with no coordinate system specified (on the Cartesian plane), Oracle Spatial calculates its are successfully. We could modify this region into the one that can be processed successfully by Oracle Spatial, however we decided that it would be better to demonstrate a case showing that you cannot blindly trust Oracle Spatial in all cases.

Areas are calculated for all French departments, they are colored differently depending on their area (darker regions are larger than lighter ones):

You can download the sources of this demo. It is written in С# in Visual Studio 2012. The Devart dbMonitor free tool is used for monitoring of SQL sent by the Entity Framework provider.

Conclusion

We are glad to provide the new Entity Framework provider functionality – support for Oracle Spatial, Entity Framework Spatials, and SharpMap library – to our users. Our roadmap of extending and improving spatial functionality support will be based on the feedback from our users via product feedback pages, forum and UserVoice. Probably we will implement spatial support for MySQL, PostgreSQL, and SQLite in the corresponding Entity Framework providers dotConnect for MySQL, dotConnect for PostgreSQL and dotConnect for SQLite. As for Oracle, we probably support new spatial services for popular GIS libraries and improve existing ones, improve SQL generation and provide support for additional Oracle Spatial functionality.

Download DevartSharpMapDemo sources