简短概述
在SQL Server的sys.spatial_reference_systems
中不存在SRID。
![enter image description here](https://istack.dev59.com/wXp4m.webp)
把它改成已存在的一个,例如4326
,它就会起作用:
![enter image description here](https://istack.dev59.com/HIOZs.webp)
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](https://istack.dev59.com/StqGr.webp)
因此,应该像这样创建并保存来自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);
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");
}
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
sys.spatial_reference_systems
中未出现的 SRID。 - Ogglas