No spatial data support in OData

[Kasper888]

No support for the Spatial data in asp.net core OData, we cannot send, receive, or do spatial queries.

Here are some issues related to this: https://stackoverflow.com/questions/65798043/spatial-data-with-odata https://github.com/OData/WebApi/issues/2271 https://github.com/OData/AspNetCoreOData/issues/68 https://github.com/OData/WebApi/issues/2039 https://github.com/OData/WebApi/issues/1792 @odero @odero @marabooy @xuzhg @shim @weitzhandler @mikepizzo

Please replay! what is the plan?

[xuzhg]

@Kasper888 does the method in this blog work for you? https://devblogs.microsoft.com/odata/how-to-consume-sql-spatial-data-with-web-api-v2-2-for-odata-v4/

[Kasper888]

@xuzhg Not working as the EDM generation failed, this 6 years old workaround is not valid now for asp.net core, EF core & NTS.

[PaulTodd]

Is there any update on how to correctly consume spatial data in Odata v4 with EF Core?

[jmjcoelho]

I'm also desperate for a solution for this.

[PaulTodd]

I ended up making a property that returns geo json in string format. Then in js code, I did JSON.parse(result[x].geo_json)). I haven't tested the set yet, but you should be able to do this to update the geom field. And you need to either have a field in your database for the geo_jso or you can ignore it in the model:

protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity().Ignore(e => e.geo_json); }

using NetTopologySuite.IO; using NetTopologySuite.Geometries; using Newtonsoft.Json;

[IgnoreDataMember]
public Geometry geom { get; set; }
public string geo_json
{
  get
  {
    JsonSerializer serializer = GeoJsonSerializer.Create();
    using (StringWriter stringWriter = new StringWriter())
    using (JsonTextWriter jsonWriter = new JsonTextWriter(stringWriter))
    {
      serializer.Serialize(jsonWriter, geom);
      return stringWriter.ToString();
    };
  }
  set {
    JsonSerializer serializer = GeoJsonSerializer.Create();
    using (StringReader stringReader = new StringReader(value))
    using (JsonTextReader jsonReader = new JsonTextReader(stringReader))
    {
      geom = serializer.Deserialize<Geometry>(jsonReader);
    }
  }
}

[jmjcoelho]

Hi Paul,

Thanks for sharing your solution, unfortunately using this technique we can´t query the geography data directly using OData.

I was hoping this was something that would work out of the box and have Microsoft or and the OData Team support, but it does not seem to be the case.

Many thanks anyway JC

[PaulTodd]

Have you tried a custom odata function that you pass parameters to for your filtering and return a result? I know you would have to predefine how you want your users to search but it could offer a solution.

[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:

[weitzhandler]

Thank you @xuxhg for the effort!

[PaulTodd]

I was able to simplify my code by using NetTopologySuite.IO.GeoJSON4STJ if anyone finds this and is looking for geojson and also wants to remove all Newtonsoft references. I've tested and this does get geojson string from my sql server and also will save a geojson string back to my sql geography column.

public string geo_json
{
  get
  {
    if (geom == null) return null;
    JsonSerializerOptions options = new JsonSerializerOptions();
    options.Converters.Add(new NetTopologySuite.IO.Converters.GeoJsonConverterFactory());
    return JsonSerializer.Serialize(geom, options).ToString();
  }
  set
  {
    if (value.IsNullOrEmpty()) geom = null;
    else
    {
      JsonSerializerOptions options = new JsonSerializerOptions();
      options.Converters.Add(new NetTopologySuite.IO.Converters.GeoJsonConverterFactory());
      geom = JsonSerializer.Deserialize<Geometry>(value, options);
    }
  }
}