Correct use of EFCore Spatial Types (NetTopologySuite)

[Danielku15]

In Entity Framework Core 2.2 support for spatial data types was introduced. For this the types of the NetTopologySuite are used. If you use those types (e.g NetTopologySuite.Geometries.Point) the property is not mapped to a EdmPrimitiveTypeKind..GeographyPoint).

It is unclear how those new types can be used properly together with OData especially to have it working with all functions.

Assemblies affected

Microsoft.AspNetCore.OData 7.1.0

Reproduce steps

1. Create a data model with Spatial Types used, like NetTopologySuite.Geometries.Point (https://docs.microsoft.com/en-us/ef/core/modeling/spatial)
2. Expose the data model via OData
3. Query metadata or object data

Expected result

• Correct spatial types are advertised in the EDM
• Loading and storing of data via API works.
• There is a clear way to control whether geographic or geometric types should be used.

Actual result

• EDM highlights the model type as NetTopologySuite.Geometries.Point
• Querying data does not work.

Additional detail

I found this rather old article on how to bring spatial types to OData using custom expression visitors but I guess this is not needed nowadays.

[madansr7]

@Danielku15 , Thanks for letting us know about the issue. We are going to investigate it.

[Avyakta]

I followed that article but am getting the error: “LINQ to Entities does not recognize the method ‘System.Nullable`1[System.Double] Distance(Microsoft.Spatial.Geography, Microsoft.Spatial.Geography)’ method, and this method cannot be translated into a store expression.”

[zp978]

I am using Database first approach, so have created Geography Type Columns in my Database for Storing a Polygon as well as Point and then run the Scafold command now my models have

    public IGeometry CityBoundary { get; set; }
    public IGeometry Location{ get; set; }

As you see its IGeometry for both Polygon and Location Point in the properties and DB has column type Geography.

Is my Approach correct?

I am not able to also find any examples of saving Polygons, Location Points using NetTopologySuite and Entity Core. Any Examples would be great.

[Danielku15]

Here an update on what I found out on my own so far: The OData library builds on Microsoft.Spatial types. The serialization/deserialization will work based on these classes when you manually register a spatial type to your EDM. If your property uses the NetTopology Suite GeoAPI types, this will result in a conversion error as Microsoft.AspNet.OData.Formatter.EdmPrimitiveHelpers.ConvertPrimitiveValue will do a simple cast via Convert.ChangeType.

https://github.com/OData/WebApi/blob/12f41d7d97156a34e9456a9c9d5cb238b2777027/src/Microsoft.AspNet.OData.Shared/Formatter/EdmPrimitiveHelpers.cs#L137

Also when it then comes to functions, it seems that the query parser will translate the spatial functions to methods on the Microsoft.Spatial types.

https://github.com/OData/odata.net/blob/962aa97d616b34243bbddc9c7bf6cead4a8fe275/test/FunctionalTests/Service/Microsoft/OData/Service/Parsing/FunctionDescription.cs#L355

I fear the whole assumption of the AspNet Core version of OData in this regards is wrong. It uses the Microsoft.Spatial meant for the normal Entity Framework, not the NTS GeoAPI types.

To get this running manually, the whole ODataResourceDeserializer.ApplyStructuralProperty needs to be re-implemented for converting Microsoft.Spatial types to NTS types for property assignment. I also don't think that you can extend the custom functions in the OData core library. the related FunctionDescription and NodeToExpressionTranslator doing it are internal. This basically means the whole parsed expression trees must be traversed and rewritten for NTS spatial functions.

This just shows me: This feature was never actually tested and used against the ASP.net Core version expecting EFCore and NTS underneath.

Long story short: This feature is missing and needs implementation in the OData/odata.net lib + WebApi core lib.

[brinehart]

Is there any plan to fix this? This is causing me to have to have a separate controller which I have manually had to do the spatial queries. It's definitely not ideal as I am unable to use spatial queries in my .Net Core 2.2 app and have access to the powerful OData querying tools.

I am using NPGSQL and Postgres. A Microsoft SQL license cost isn't an option for me.

How can we contribute to this issue to help resolve it?

[brinehart]

I last commented on December 6. Is there anything we can do to help with this? What is the plan to fix this? This has become urgent now as 3.1.1 is in production and 2.2 has moved out of support.

[brinehart]

Is there an update on this problem? Will a fix be included in the upcoming .Net 5 aligned release?

[xuzhg]

@brinehart did you get a change to try the workaround in https://devblogs.microsoft.com/odata/how-to-consume-sql-spatial-data-with-web-api-v2-2-for-odata-v4/? I'd like involve @mikepizzo here for priority.

[joaopgrassi]

@xuzhg the work around above is for WebApi 2 but what about ASP.NET Core with EF Core + NTS?

[brinehart]

@madansr7 This workaround only works with .Net 2.2 which is out of long term support. Is this being worked on at all?

[brinehart]

@hassanhabib Does Spatial querying currently work in .Net Core 3.1? If so, how do we use it? I did try using the geo.distance call per these docs but seem to get the same amount of items back every time indicating to me that maybe it's not working?

https://www.odata.org/blog/geospatial-properties/

For context, I am currently using Npgsql.EntityFrameworkCore.PostgreSQL and per the .Net Core 2.2 docs, Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite to add geo data to my models.

Right now I have a separate REST API endpoint for all models that require geography, using the NetTopologySuite Point. It would be great if I could remove this need and have odata endpoing that can query distance.

[davidyee]

@xuzhg Any updates on using NetTopologySuite with OData?

[Kasper888]

Any updates? @hassanhabib @xuzhg #2039

[xuzhg]

@Kasper888 @ufonator

You can build the model by including the required properties.

        public static IEdmModel GetEdmModel()
        {
            var builder = new ODataModelBuilder();

            builder.EntitySet<Customer>("Customers");
            var customer = builder.EntityType<Customer>();
            customer.Property(c => c.Id);
            customer.Property(c => c.Name);
            customer.ComplexProperty(c => c.Location);
            customer.HasKey(c => c.Id);

            var point = builder.ComplexType<Point>();
            point.Property(p => p.X);
            point.Property(p => p.Y);
            point.Property(p => p.Z);
            point.Property(p => p.M);
            point.ComplexProperty(p => p.CoordinateSequence);
            point.Property(p => p.NumPoints);

            var coordinateSequence = builder.ComplexType<CoordinateSequence>();
            coordinateSequence.Property(c => c.Dimension);
            coordinateSequence.Property(c => c.Measures);
            coordinateSequence.Property(c => c.Spatial);
          //  coordinateSequence.Property(c => c.Ordinates);
            coordinateSequence.Property(c => c.HasZ);
            coordinateSequence.Property(c => c.HasM);
            coordinateSequence.Property(c => c.ZOrdinateIndex);
            coordinateSequence.Property(c => c.MOrdinateIndex);
            coordinateSequence.Property(c => c.Count);

            return builder.GetEdmModel();
        }

Then, I can have the following output:

[borbelyzs]

Any tips about how should i use the "geo.distance" function with this model? It still results in "No function signature for the function with name 'geo.distance' matches the specified arguments. The function signatures considered are: geo.distance(Edm.GeographyPoint Nullable=true, Edm.GeographyPoint Nullable=true); geo.distance(Edm.GeometryPoint Nullable=true, Edm.GeometryPoint Nullable=true)."

[brinehart]

@borbelyzs From what I am reading in this issue (which has been open since Jul 26, 2016) they still do not have the geo.distance spec implemented in WebAPI.

@xuzhg Can you please confirm that the geo.distance and geo.intersect are not implemented yet?

Sadly, It appears that we will need to continue to have a separate API controller to handle any geospatial querying for the time being until Microsoft can put in this feature.

[xuzhg]

@brinehart yes, geo.distance is not supported yet in Web API.

[faceoffers28]

@brinehart Did you ever get Spatial queries to work correctly? I've got an Asp.net Core 3.1 MVC Web App with Azure SQL. It doesn't matter what radius I use to query the database. I get back the same information if I'm trying to get data that is either 20,000 feet or 5,000 feet from me. I'm converting feet to meters but, the result is the same. It's simply not working. It seems to work for this guy, though. https://gavilan.blog/2020/01/07/entity-framework-core-3-1-spatial-queries-nearby-places/

Doesn't matter if I structure like this.

if (location.GeoLocation.Distance(crime.GeoLocation) <= radiusParam)
            {
                return true;
            }
            else return false;

THIS

var galleria = crimeListGeo.Where(c => c.GeoLocation.Distance(gall.GeoLocation) <= gall.Proximity.Value * 0.304800610);

OR THIS

var galleria2 = crimeListGeo
                .OrderBy(x => x.GeoLocation.Distance(gall.GeoLocation))
                .Where(x => x.GeoLocation.IsWithinDistance(gall.GeoLocation, gall.Proximity.Value * 0.304800610))
                //.Select(x => new { x., x.City, Distance = x.Location.Distance(myLocation) })
                .ToList();

This is what GeoLocation looks like.

//[Column(TypeName = "geometry")]
        public Point GeoLocation { get; set; }

[faceoffers28]

var distanceBetween = Convert.ToDouble(location.GeoLocation.Distance(crime.GeoLocation).ToString());

Every distanceBetween is a number like this 0.15574635408139578, which is always less than 5,000 feet converted to meters (1524).

What am I doing wrong or why is this is not working as expected?

[faceoffers28]

According to this post, the distanceBetween mentioned above would be calculating degrees, which is not useful.

https://stackoverflow.com/questions/58363982/nettopologysuite-distance-is-returning-odd-results-in-net-core-3