Spatial Data and NetTopologySuite

The Oracle database can store spatial data that represents physical locations and object shapes. ODP.NET can operate on Oracle spatial data natively.

EF Core maps to spatial data types with the NetTopologySuite (NTS) spatial library. Starting with Oracle EF Core 10, Oracle database SDO_GEOMETRY spatial data types can be accessed and mapped using the Oracle EF Core NTS library.

This library enables Oracle spatial columns to be mapped to .NET geometry types, such as Point and Polygon. LINQ statements for the Oracle database can include spatial logic, such as distance and intersection. EF Core apps can now use a standard way to query, insert, update, and delete Oracle database spatial data.

See Also:

Setup

This Oracle spatial library is available in the Oracle.EntityFrameworkCore.NetTopologySuite package on NuGet Gallery. The NuGet package manager automatically retrieves other NTS packages needed to support Oracle EF Core spatial types.

To setup an app that uses Oracle spatial data in EF Core, follow these steps:

Add the Oracle.EntityFrameworkCore.NetTopologySuite package to your .NET project.
Use DbContext to enable the library. The extension method, UseNetTopologySuite(), configures the spatial provider settings, including the spatial tolerance level which controls the allowable geometric calculation error margin. For example:

services.AddDbContext<MyContext>(options =>
    options.UseOracle("<CONNECTION STRING>",
        o => o.UseNetTopologySuite(tolerance: 0.01)));

In the entity model, define the spatial class. For example:

public class MySpatialEntity
{
    public int Id { get; set; }

   // NetTopologySuite.Geometries.Point
    public Point Location { get; set; }
}

Write a LINQ for the spatial data, such as

Point myPoint = new Point(10, 20);
var nearby = context.MySpatialEntities
    .Where(e => e.Location.IsWithinDistance(myPoint, 1000))
    .ToList();

Features

Oracle EF Core supports the following features:

Automatically map Oracle SDO_GEOMETRY column data to NTS .NET data types, including
- Point
- LineString
- Polygon
- MultiPoint
- MultiLineString
- MultiPolygon
- GeometryCollection
Execute DML operations on NTS .NET types.
Translate LINQ spatial operations to Oracle SQL spatial functions.
EF Core migrations and database scaffolding with Oracle spatial columns
Tolerance configuration in the UseNetTopologySuite() extension method.
- If tolerance is not specified, then 0.005 is set as the default value.

Note: Oracle Spatial supports a superset of features not available or with limited features in NTS, such as curved geometry. EF Core spatial support is limited to NTS capabilities.

Oracle NetTopologySuite Members

Table 6 NetTopologySuite Methods and Oracle Spatial Functions Mapping

NetTopologySuite Methods	Oracle Spatial Functions
`EnvelopeCombiner.CombineAsGeometry(geom[])`	`SDO_AGGR_MBR`
`Geometry.AsBinary()`	`SDO_UTIL.TO_WKBGEOMETRY`
`Geometry.AsText()`	`SDO_UTIL.TO_WKTGEOMETRY`
`Geometry.Buffer(distance)`	`SDO_GEOM.SDO_BUFFER`
`Geometry.Contains(geom)`	`SDO_CONTAINS`
`Geometry.ConvexHull()`	`SDO_GEOM.SDO_CONVEXHULL`
`Geometry.CoveredBy(geom)`	`SDO_RELATE`
`Geometry.Covers(geom)`	`SDO_RELATE`
`Geometry.Crosses(geom)`	`SDO_RELATE`
`Geometry.Disjoint(geom)`	`SDO_RELATE`
`Geometry.Difference(geom)`	`SDO_GEOM.DIFFERENCE`
`Geometry.Distance(geom)`	`SDO_GEOM.SDO_DISTANCE`
`Geometry.EqualsExact(geom)`	`SDO_RELATE`
`Geometry.EqualsTopologically(geom)`	`SDO_RELATE`
`Geometry.GetGeometryN(n)`	`SDO_UTIL.EXTRACT`
`Geometry.Intersection(geom)`	`SDO_GEOM.SDO_INTERSECTION`
`Geometry.Intersects(geom)`	`SDO_RELATE`
`Geometry.IsWithinDistance(geom, dist)`	`SDO_WITHIN_DISTANCE`
`Geometry.Overlaps(geom)`	`SDO_RELATE`
`Geometry.Relate(geom, pattern). Instead of the DE-9IM pattern, Oracle uses relevant mask words`	`SDO_RELATE`
`Geometry.Reverse() for LineString only and requires Oracle Database 23ai or higher.`	`SDO_UTIL.REVERSE_LINESTRING`
`Geometry.SymmetricDifference(geom)`	`SDO_GEOM.SDO_XOR`
`Geometry.ToBinary()`	`SDO_UTIL.TO_WKBGEOMETRY`
`Geometry.ToText()`	`SDO_UTIL.TO_WKTGEOMETRY`
`Geometry.Touches(geom)`	`SDO_RELATE`
`Geometry.Within(geom)`	`SDO_CONTAINS`
`Geometry.Union()`	`SDO_GEOM.SDO_SELF_UNION`
`Geometry.Union(geom)`	`SDO_GEOM.SDO_UNION`
`GeometryCombiner.Combine(geom[])`	`SDO_AGGR_UNION`
`ConvexHull.Create(geom[])`	`SDO_AGGR_CONVEXHULL`
`UnaryUnionOp.Union(geom[])`	`SDO_AGGR_UNION`

Table 7 NetTopologySuite Properties and Oracle Spatial Functions Mapping

NetTopologySuite Properties	Oracle Spatial Functions
`Geometry.Area`	`SDO_GEOM.SDO_AREA`
`Geometry.Centroid`	`SDO_GEOM.SDO_CENTROID`
`Geometry.Envelope`	`SDO_GEOM.SDO_MBR`
`Geometry.GeometryType`	`CASE on SDO_GTYPE`
`Geometry.InteriorPoint`	`SDO_GEOM.SDO_POINTONSURFACE`
`Geometry.IsEmpty`	`Composite null checks on SDO_GEOMETRY fields`
`Geometry.IsSimple`	`SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT`
`Geometry.IsValid`	`SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT`
`Geometry.Length`	`SDO_GEOM.SDO_LENGTH`
`Geometry.NumGeometries`	`SDO_UTIL.GETNUMELEM`
`Geometry.NumPoints`	`SDO_UTIL.GETNUMVERTICES`
`Geometry.OgcGeometryType`	`CASE on SDO_GTYPE`
`Geometry.PointOnSurface`	`SDO_GEOM.SDO_POINTONSURFACE`
`Geometry.SRID`	`SDO_SRID(field)`
`GeometryCollection.Count`	`SDO_UTIL.GETNUMELEM`
`LineString.Count`	`SDO_UTIL.GETNUMVERTICES`
`LineString.EndPoint`	`SDO_LRS.GEOM_SEGMENT_END_PT`
`LineString.IsClosed`	`SDO_EQUAL(SDO_LRS.GEOM_SEGMENT_START_PT and SDO_LRS.GEOM_SEGMENT_END_PT)`
`LineString.IsRing`	`SDO_EQUAL(SDO_LRS.GEOM_SEGMENT_START_PT, SDO_LRS.GEOM_SEGMENT_END_PT), and SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT`
`LineString.StartPoint`	`SDO_LRS.GEOM_SEGMENT_START_PT`
`Point.X`	`SDO_POINT.X`
`Point.Y`	`SDO_POINT.Y`
`Point.Z`	`SDO_POINT.Z`
`Polygon.ExteriorRing`	`SDO_UTIL.POLYGONTOLINE and SDO_UTIL.EXTRACT(geom, 1, 0)`
`Polygon.NumInteriorRings`	`SDO_UTIL.GETNUMRINGS - 1`

If a NTS member requires a specific minimum Oracle database version, such as Geometry.Reverse(), then set the [UseOracleSQLCompatibility(enum version)](EFCoreAPI.html#GUID-41786CF0-11E3-4AD2-8ED1-3D31D5FE2082) extension method enum value to a supported database version.

Limitations and Workarounds

M-values (Measure Coordinates): Oracle EF Core with NTS does not support M-value (measure) coordinates. Geometries with M or ZM ordinates have the M-value dropped during serialization. It is not included in the generated SDO_GEOMETRY. When reading geometries from Oracle Spatial, M values are not materialized into the corresponding NTS geometry objects.

Workaround:

For M-aware or LRS (Linear Referencing System) operations, use direct Oracle SQL or stored procedures:

Example 1: Reading M-value geometries

SDO_GEOMETRY object in the Oracle Database containing geometries with the following M values:
```
SDO_GEOMETRY(
    3303,        -- LineString with Z and M
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(10,20,5,100, 30,40,10,200)
)
```
Oracle EF Core code to read the SDO_GEOMETRY object in the Oracle Database and its output:
```
// C#
var segment = context.RoadSegments.First();
var coords = segment.Geometry.Coordinates;

// output:
Coordinate[0] = (10, 20, 5)
Coordinate[1] = (30, 40, 10)
```
The M-values (100, 200) are dropped. They do not appear in the resulting objects.

Example 2: Writing M-value geometries

Oracle EF Core code to update geometries with M values:
```
// C#
// EF Model
public class RoadSegment
{
    public int Id { get; set; }
    public LineString Geometry { get; set; }
}

// SaveChanges Code:
var segment = new RoadSegment
{
    Geometry = new LineString(new[]
    {
        new CoordinateZM(10, 20, 5, 100),
        new CoordinateZM(30, 40, 10, 200)
    })
};

context.RoadSegments.Add(segment);
context.SaveChanges();
```
Executing Oracle EF Core code to update geometries with M values results in the following SDO_GEOMETRY object in the Oracle Database:
```
SDO_GEOMETRY(
    2003,        -- 2D LineString type (M is ignored)
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(10,20,5, 30,40,10)
)
```
Relate() method: The Relate() method in NTS checks spatial relationships between geometries using the Dimensionally Extended Nine-Intersection Model (DE-9IM) matrix. Oracle Spatial uses a different approach known as topological relationship masks, rather than the DE-9IM patterns used by other spatial libraries. When you use the Relate() method in Entity Framework Core LINQ queries with a DE-9IM pattern (the standard NTS usage), these patterns will not translate correctly to Oracle SQL. Queries using the DE-9IM pattern may fail or return incorrect results.

Workaround

Instead of the DE-9IM matrix, provide an Oracle Spatial relationship mask string as the second parameter to the Relate() method. The Oracle EF Core NTS library inserts this string as the spatial relationship mask in the SDO_RELATE mask parameter during SQL translation.

Example

LINQ query with .Relate()and Oracle mask.

The following query uses the Relate() method to check the spatial relationship between each row’s geometry and the target geometry, specifying the Oracle spatial relationship mask TOUCH instead of a DE-9IM pattern.
```
// C#
var targetGeometry = factory.CreatePolygon(new[]
{
    new Coordinate(0, 0),
    new Coordinate(10, 0),
    new Coordinate(10, 10),
    new Coordinate(0, 10),
    new Coordinate(0, 0)
});

var result = context.GeometryEntities
    .Where(e => e.Geometry.Relate(targetGeometry, "TOUCH"))
    .Select(e => new { e.Id, e.Geometry })
    .ToList();
```
Oracle EF Core translates this LINQ expression (LINQ query with .Relate()and Oracle mask ) into the following Oracle SQL:
```
SELECT e."Id", e."Geometry"
FROM "GeometryEntities" e
WHERE **SDO_RELATE**(e."Geometry", :targetGeometry, **'mask=TOUCH'**) = 'TRUE'
```
Oracle supports combining multiple relationship masks using the logical OR operator (+).