Entity Framework Core 3.1 with NetTopologySuite.Geometries.Point: SqlException: The supplied value is not a valid instance of data type geography

Question

I have a model that looks like this:

public class Facility
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

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

Test code for adding a Point:

var testFacility = new Facility();
testFacility.Location = new NetTopologySuite.Geometries.Point(13.003725d, 55.604870d) { SRID = 3857 };

//Other values tested with the same error error

//testFacility.Location = new NetTopologySuite.Geometries.Point(13.003725d, 55.604870d);

//testFacility.Location = new NetTopologySuite.Geometries.Point(55.604870d, 13.003725d);

//var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 3857);
//var currentLocation = geometryFactory.CreatePoint(new Coordinate(13.003725d, 55.604870d));
//testFacility.Location = currentLocation;

db.Facilities.Add(testFacility);
//Exception on Save
db.SaveChanges();

I'm using the following NuGets, version 3.1.0

Microsoft.AspNetCore.Identity.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite

The exception I get on save is the following:

SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 7 ("@p6"): The supplied value is not a valid instance of data type geography. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.

According to all documentation it should be X for longitude and Y for latitude so I don't think that is a problem. I tried to reverse the coordinates just in case but I got the same error as you can see in the examples I have tried.

https://learn.microsoft.com/en-us/ef/core/modeling/spatial

Lat = Y Long = X

https://gis.stackexchange.com/a/68856/71364

I can't find out anything obvious that seems wrong. Optionsbuilder is set up, the table is created with Data Type geography that worked really well with DbGeography for Entity Framework 6.

var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true",
    x => x.UseNetTopologySuite());

var db = new ApplicationDbContext(optionsBuilder.Options);

There is no specific cases to handle for a single Point either what I can see in documentation for SQL server.

https://learn.microsoft.com/en-us/ef/core/modeling/spatial#sql-server

The coordinates I'm saving is from Google Maps and therefore EPSG 3857 is used.

https://gis.stackexchange.com/questions/48949/epsg-3857-or-4326-for-googlemaps-openstreetmap-and-leaflet

What am I missing?

Fanya

My psychic powers tell me that NET Topology Suite maps its Point class to the MSSQL geometry type, not geography. Hence when attempting to persist the record, NTS serializes a geometry while MSSQL expects a geography and everything explodes. – Margertmargery

@IanKemp I think they might need a tuning then. ;) It was the SRID not present in SQL Server sys.spatial_reference_systems – Fanya

Answer 1

TLDR

The SRID is not present in SQL Server sys.spatial_reference_systems

Change to one that exist like 4326 and it will work:

select *
from sys.spatial_reference_systems
where spatial_reference_id = '4326'

Long answer:

Google Maps API uses EPSG 3857 but Google Maps Web application uses EPSG 4326

https://developers.google.com/maps/documentation/javascript/markers

https://www.google.com/maps/@55.604933,13.003662,14z

Therefore a point from Google Maps Web Application should be created and saved like this:

var testFacility = new Facility();
testFacility.Location = new NetTopologySuite.Geometries.Point(13.003725d, 55.604870d) { SRID = 4326 };
db.Facilities.Add(testFacility);
db.SaveChanges();

It was however a bit tricky to project EPSG 4326 coordinates to EPSG 3857 coordinate system. Microsoft recommends using ProjNet4GeoAPI so I decided to use that.

https://learn.microsoft.com/en-us/ef/core/modeling/spatial#srid-ignored-during-client-operations

I have verified that it works here:

http://epsg.io/transform#s_srs=4326&t_srs=3857&x=13.003725&y=55.604870

Example conversion:

var x = 13.003725d;
var y = 55.604870d;

var epsg3857ProjectedCoordinateSystem = ProjNet.CoordinateSystems.ProjectedCoordinateSystem.WebMercator;
var epsg4326GeographicCoordinateSystem = ProjNet.CoordinateSystems.GeographicCoordinateSystem.WGS84;

var coordinateTransformationFactory = new ProjNet.CoordinateSystems.Transformations.CoordinateTransformationFactory();
var coordinateTransformation = coordinateTransformationFactory.CreateFromCoordinateSystems(epsg4326GeographicCoordinateSystem, epsg3857ProjectedCoordinateSystem);

var epsg4326Coordinate = new GeoAPI.Geometries.Coordinate(x, y);

var epsg3857Coordinate = coordinateTransformation.MathTransform.Transform(epsg4326Coordinate);

Complete example program:

To get it running:

• Install NuGets
  • Following NuGets are at version 3.1:
    • Microsoft.EntityFrameworkCore
    • Microsoft.EntityFrameworkCore.SqlServer
    • Microsoft.EntityFrameworkCore.Tools
    • Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
  • ProjNET4GeoAPI
• Add-Migration InitialCreate
• Update-Database

Code:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using NetTopologySuite;
using NetTopologySuite.Geometries;
using ProjNet.CoordinateSystems;
using ProjNet.CoordinateSystems.Transformations;
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace TestConsoleAppEFGeo
{
    public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
    {
        public ApplicationDbContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
            optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=TestApp;Trusted_Connection=True;MultipleActiveResultSets=true",
                x => x.UseNetTopologySuite());

            return new ApplicationDbContext(optionsBuilder.Options);
        }
    }

    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Facility> Facilities { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }

    public class Facility
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Id { get; set; }

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

    class Program
    {
        static void Main(string[] args)
        {
            var applicationDbContextFactory = new ApplicationDbContextFactory();
            var db = applicationDbContextFactory.CreateDbContext(null);

            var x = 13.003725d;
            var y = 55.604870d;
            var srid = 4326;

            if (!db.Facilities.AnyAsync(x => x.Id == 1).Result)
            {
                var testFacility = new Facility();
                var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid);
                var currentLocation = geometryFactory.CreatePoint(new NetTopologySuite.Geometries.Coordinate(x, y));
                testFacility.Id = 1;
                testFacility.Location = currentLocation;

                var testFacility2 = new Facility();
                testFacility2.Id = 2;
                testFacility2.Location = new Point(x, y) { SRID = srid };
                db.Facilities.Add(testFacility);
                db.Facilities.Add(testFacility2);

                //Will throw an exception
                //var testFacility3 = new Facility();
                //testFacility3.Id = 3;
                //testFacility3.Location = new Point(1447568.0454157612d, 7480155.2276327936d) { SRID = 3857 };
                //db.Facilities.Add(testFacility3);

                db.SaveChanges();
            }

            var facility1 = db.Facilities.FirstAsync(x => x.Id == 1).Result;
            var facility2 = db.Facilities.FirstAsync(x => x.Id == 2).Result;

            if(facility1.Location == facility2.Location)
            {
                Console.WriteLine("facility1.Location is equal to facility2.Location");
            }
            else
            {
                Console.WriteLine("facility1.Location is NOT equal to facility2.Location");
            }

            //Test conversion
            //Show coordinate: http://epsg.io/map#srs=4326&x=13.003725&y=55.604870&z=14&layer=streets
            //Conversion: http://epsg.io/transform#s_srs=4326&t_srs=3857&x=13.0037250&y=55.6048700
            //Google Maps - https://www.google.se/maps shows EPSG:4326 when viewing a location
            //https://epsg.io/3857 - Google Maps API is EPSG:3857 however
            //Example: https://developers.google.com/maps/documentation/javascript/markers

            var epsg3857ProjectedCoordinateSystem = ProjectedCoordinateSystem.WebMercator;
            var epsg4326GeographicCoordinateSystem = GeographicCoordinateSystem.WGS84;

            var coordinateTransformationFactory = new CoordinateTransformationFactory();
            var coordinateTransformation = coordinateTransformationFactory.CreateFromCoordinateSystems(epsg4326GeographicCoordinateSystem, epsg3857ProjectedCoordinateSystem);

            var epsg4326Coordinate = new GeoAPI.Geometries.Coordinate(facility1.Location.Coordinate.X, facility1.Location.Coordinate.Y);

            var epsg3857Coordinate = coordinateTransformation.MathTransform.Transform(epsg4326Coordinate);

        }
    }
}

More info here:

https://github.com/dotnet/efcore/issues/19416

Fanya

Why can't the fix be something like: INSERT INTO sys.spatial_reference_systems (spatial_reference_id, authority_name, authorized_spatial_reference_id, well_known_text, unit_of_measure, unit_conversion_factor) VALUES (3857, 'EPSG', 3857, 'GEOGCS["idk"]', 'radian', 1)? – Rummer

Answer 2

It's probably already in 4326, happy days, easy to store, sql should let you store this (an API might use 3857 but supply a location's lat/lon in degrees not metres and actually you've already been given the lat/lon in 4326)

---

Assuming you are getting lat/lon's in SRID=3857 and want to try to store it that way:

Check you have a version of an SRID equivalent to 3857 that'll work in your DB

SELECT * FROM sys.spatial_reference_systems 
WHERE authorized_spatial_reference_id 
IN('3857', '900913', '3587', '54004', '41001', '102113', '102100', '3785')

For example if you happen to have 900913 try using that on a lat/lon insert with no conversion if you have it, I'm basing this assumption on comparing the properties of the hyperlinked "alternatives codes" to EPSG:3857

I have no idea if it'll work, and this is totally not my field of knowledge.

Assuming you get no SQL rows back then you'll have to convert 3857 to 4326 to store in your DB...

---

How to convert 3857 to 4326 so you can store it:

Install ProjNet4GeoAPI via NuGet and use the following code:

using GeoAPI.Geometries;
using ProjNet.CoordinateSystems;
using ProjNet.CoordinateSystems.Transformations;

...

// setup
var epsg3857 = ProjectedCoordinateSystem.WebMercator;
var epsg4326 = GeographicCoordinateSystem.WGS84;
var convertTo4326 = new CoordinateTransformationFactory()
                        .CreateFromCoordinateSystems(epsg3857, epsg4326);

// input 6415816.17/171048.38 (Brussels lat/lon in meters SRID 3857)
//       N.B. method called needs the values as lon/lat (x/y), not lat/lon 
var coordIn3857 = new GeoAPI.Geometries.Coordinate(171048.38, 6415816.17);

var coordIn4326 = convertTo4326.MathTransform.Transform(coordIn3857);
// output 49.82379612579344/1.5365537407788388 (Brussels lat/lon in degrees SRID 4326)

now go save that into your DB

testFacility.Location = new NetTopologySuite.Geometries.Point(1.536553, 49.823796) 
                             { SRID = 4326 };

To convert in the other direction and use 3857 from stored 4326 values, it's pretty easy to figure out or see Ogglas's answer

Rummer