logo标签列表

Entity Framework Core 3.1与NetTopologySuite.Geometries.Point：SqlException：提供的值不是地理数据类型的有效实例

c#entity-framework-coregeospatialef-core-3.1nettopologysuite
14

我有一个模型，长这样：

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

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

添加一个点的测试代码：

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();

我正在使用以下NuGet包，版本为3.1.0

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

我在保存时遇到的异常如下：

SqlException：传入的表格数据流（TDS）远程过程调用（RPC）协议流不正确。参数7（“@p6”）：提供的值不是地理数据类型的有效实例。检查源数据是否存在无效值。无效值的示例是具有比精度更大的比例的数字类型数据。

根据所有文档，经度应该是X，纬度应该是Y，因此我认为这不是问题。我尝试反转坐标以防万一，但是正如您可以在我尝试的示例中看到的那样，我收到了相同的错误。

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

纬度 = Y 经度 = X

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

我找不到任何明显的错误。Optionsbuilder已设置，表格创建时使用数据类型geography，这对于Entity Framework 6中的DbGeography非常有效。

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);

根据SQL服务器文档，单个Point没有特定的处理方式。

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

我保存的坐标来自谷歌地图，因此使用 EPSG 3857。

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

我错过了什么？

我的直觉告诉我，NET Topology Suite将其“Point”类映射到MSSQL的“geometry”类型，而不是“geography”类型。因此，在尝试持久化记录时，NTS序列化了一个“geometry”，而MSSQL期望一个“geography”，结果一切都爆炸了。 - Ian Kemp
1
@IanKemp 我认为它们可能需要进行调整。;) 这是 SQL Server sys.spatial_reference_systems 中未出现的 SRID。 - Ogglas
2个回答
21

简短概述

在SQL Server的sys.spatial_reference_systems中不存在SRID。

enter image description here

把它改成已存在的一个，例如4326，它就会起作用：

enter image description here

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

长答案:

谷歌地图API使用 EPSG 3857，但谷歌地图Web应用程序使用 EPSG 4326

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

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

enter image description here

因此，应该像这样创建并保存来自Google Maps Web应用程序的一个点：
var testFacility = new Facility();
testFacility.Location = new NetTopologySuite.Geometries.Point(13.003725d, 55.604870d) { SRID = 4326 };
db.Facilities.Add(testFacility);
db.SaveChanges();

然而，将EPSG 4326坐标投影到EPSG 3857坐标系有点棘手。微软推荐使用ProjNet4GeoAPI，所以我决定使用它。

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

我已经验证它在这里可行：

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

示例转换：

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);

完整的示例程序:

运行该程序需要：

  • 安装NuGets
    • 以下NuGets版本为3.1：
      • Microsoft.EntityFrameworkCore
      • Microsoft.EntityFrameworkCore.SqlServer
      • Microsoft.EntityFrameworkCore.Tools
      • Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
    • ProjNET4GeoAPI
  • Add-Migration InitialCreate
  • Update-Database

代码:

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);

        }
    }
}

更多信息请见：

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

2
为什么不能像这样修复：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)？ - ono2012
0

它可能已经是4326了, 快乐的日子，易于存储，SQL应该让您存储此信息（一个API可能使用3857，但提供以度为单位而不是米为单位的位置的纬度/经度值，实际上您已经获得了4326的纬度/经度值）

假设您的经纬度采用 SRID=3857 格式，并希望以此方式存储：
请确保您的数据库中有适用于 3857 的 SRID 版本。
SELECT * FROM sys.spatial_reference_systems 
WHERE authorized_spatial_reference_id 
IN('3857', '900913', '3587', '54004', '41001', '102113', '102100', '3785')

例如，如果你恰好有900913，在插入经纬度时可以尝试使用它而无需进行转换。我基于比较超链接“替代代码”的属性与EPSG:3857得出这个假设。

我不知道它是否有效，因为这完全不是我的专业领域。

假设你没有收到SQL行返回，则必须将3857转换为4326以存储在你的数据库中...

如何将3857转换为4326以便存储：

通过NuGet安装ProjNet4GeoAPI并使用以下代码：

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)

现在将其保存到您的数据库中

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

要从存储的4326值中转换为3857并使用它，很容易弄清楚或查看Ogglas的答案

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接