Closed cmiles closed 3 years ago
@cmiles Out of curiosity, what are you using Z for?
@ajcvickers I am using Z for Elevation in a personal project where filtering the dataset of points by elevation is an important feature - currently the elevation is sometimes imported from a GPX file and sometimes from the Google Elevation API. I am relatively new to working with Spatial data - certainly let me know if this usage is somehow not typical and there is a better pattern!
@cmiles That's cool, and sounds totally reasonable. It's interesting to hear how people are using spatial types as we prioritize work and plot future direction. From what we have seen, it seems using Z is not super common, but it's totally valid to do and we should make it work correctly.
@ajcvickers Interesting to know Z is not super common, I am glad for the support and thanks for getting this labeled and into the 3 milestone! Fwiw I am very excited about the SpatiaLite support - for me the idea of having a spatial db with full EF Core support that can be deployed to Windows/Android/iOS, opened in QGIS in a couple of clicks and used offline is pretty inspiring.
I'm facing the same issue. I need to store Points with the XYZ dimension and receive the error: Microsoft.Data.Sqlite.SqliteException: SQLite Error 19: '... violates Geometry constraint [geom-type or SRID not allowed]'.
I also reproduced it in a sample project: https://github.com/faryu/EntityFrameworkCore-Demo
EF Core version: 2.2.1 Database Provider: Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite 2.2.1 Operating system: Windows 10 1809 IDE: (e.g. Visual Studio 2017 15.9.6)
@ajcvickers, are there any plans to add Property to RelationalTypeMappingInfo? I need to use a provider-specific facet (dimension) in the type mapping to fix this.
WIP branch bricelam:zim
I could also try overriding FindMapping(IProperty)
, but I'd be copying A LOT of code from Relational.
@bricelam Exposing IProperty is dangerous because the "shadow state" property in the snapshot has different information than the real IProperty in the model. This can lead to different type mappings being used in the real model and the snapshot.
Making dimension part of the type name simplified things and enabled a fix.
- x.Property(e => e.Geometry).HasGeometricDimension(Ordinates.XYZ)
+ x.Property(e => e.Geometry).HasColumnType("GEOMETRYZ")
We need to discuss how much we can break HasGeometricDimension. (The PR currently removes it entirely.) In 3.x, it was unnecessary for query, broken for SaveChanges, but worked in Migrations.
Discussed with the team and we decided to remove HasGeometricDimension in 5.0 given that it never worked end to end.
I've been testing the EFCore/NTS/SpatiaLite stack in .NET 5. The Z-coordinates is stored in the database when I use .HasColumnType("GEOMETRYZ")
. However the resulting tables in the database are not recognized as a SpatiaLite geometry types. This is illustrated by the following console app:
using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;
using NetTopologySuite.Utilities;
using System;
using System.IO;
using System.Linq;
namespace EFSpatialTest
{
public class TypeWithPointZ
{
public Point Geometry { get; set; }
}
public class TypeWithLinestring
{
public LineString Geometry { get; set; }
}
public class TypeWithLinestringZ
{
public LineString Geometry { get; set; }
}
public class TestDbContext : DbContext
{
public DbSet<TypeWithPointZ> PointZs { get; set; }
public DbSet<TypeWithLinestring> Linestrings { get; set; }
public DbSet<TypeWithLinestringZ> LinestringZs { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<TypeWithPointZ>(tpz =>
{
tpz.Property(hl => hl.Geometry).HasSrid(28992).HasColumnType("GEOMETRYZ");
tpz.Property<int>("Id");
tpz.HasKey("Id");
});
modelBuilder.Entity<TypeWithLinestring>(tls =>
{
tls.Property(hl => hl.Geometry).HasSrid(28992);
tls.Property<int>("Id");
tls.HasKey("Id");
});
modelBuilder.Entity<TypeWithLinestringZ>(tlsz =>
{
tlsz.Property(hl => hl.Geometry).HasSrid(28992).HasColumnType("GEOMETRYZ");
tlsz.Property<int>("Id");
tlsz.HasKey("Id");
});
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
options.UseSqlite("Data Source=test.db", x =>x.UseNetTopologySuite());
}
}
class Program
{
static void Main(string[] _)
{
var c1 = new CoordinateZ(155_000, 463_000, 20);
var c2 = new CoordinateZ(155_100, 463_100, 21);
var pt1 = new Point(c1) { SRID = 28992 };
var ls = new LineString(new[] { c1, c2 }) { SRID = 28992 };
if (File.Exists("test.db"))
File.Delete("test.db");
using (var db = new TestDbContext())
{
Console.WriteLine("Writing to test database");
db.Database.EnsureCreated();
db.PointZs.Add(new TypeWithPointZ { Geometry = pt1 });
db.Linestrings.Add(new TypeWithLinestring { Geometry = ls });
db.LinestringZs.Add(new TypeWithLinestringZ { Geometry = ls });
db.SaveChanges();
Console.WriteLine("Writing to test database finished");
}
using (var db2 = new TestDbContext())
{
Console.WriteLine("Reading from database");
var pointZ = db2.PointZs.First();
Assert.IsTrue(double.IsFinite(pointZ.Geometry.Z));
Console.WriteLine($"PointZ = ({pointZ.Geometry.X},{pointZ.Geometry.Y},{pointZ.Geometry.Z})");
var linestring = db2.Linestrings.First();
var lsc1 = linestring.Geometry.Coordinates[0];
var lsc2 = linestring.Geometry.Coordinates[1];
Console.WriteLine($"LinestringZ = [({lsc1.X},{lsc1.Y},{lsc1.Z}) - ({lsc2.X},{lsc2.Y},{lsc2.Z})]");
var linestringZ = db2.LinestringZs.First();
var lszc1 = linestringZ.Geometry.Coordinates[0];
var lszc2 = linestringZ.Geometry.Coordinates[1];
Console.WriteLine($"LinestringZ = [({lszc1.X},{lszc1.Y},{lszc1.Z}) - ({lszc2.X},{lszc2.Y},{lszc2.Z})]");
}
}
}
}
The app demonstrates that a roundtrip to and from the database preserves the Z-coordinate. However, when I open the database in a GIS program (e.g. QGIS, the types with Z-coordinate (PointZs
and LinestringZs
) are not recognized as geometry types:
The 2D type Linestrings
however is correctly recognized:
Under the hood, only the linestrings table has an entry in the geometry_columns:
@bricelam Not convinced there is anything to do here, but re-opening to discuss in triage.
This is a bug. The columns aren't being created correctly:
SELECT InitSpatialMetaData();
CREATE TABLE "Linestrings" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Linestrings" PRIMARY KEY AUTOINCREMENT
);
SELECT AddGeometryColumn('Linestrings', 'Geometry', 28992, 'LINESTRING', -1, 0);
CREATE TABLE "LinestringZs" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_LinestringZs" PRIMARY KEY AUTOINCREMENT,
"Geometry" GEOMETRYZ NULL -- BUG: Should use AddGeometryColumn
);
CREATE TABLE "PointZs" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_PointZs" PRIMARY KEY AUTOINCREMENT,
"Geometry" GEOMETRYZ NULL -- BUG: Should use AddGeometryColumn
Out for review in PR #23298--forgot to update one of the lists of spatial types
@cmiles
I'm trying to get your sample project to run, but I'm getting an exception right out of the gate:
System.InvalidOperationException: Sequence contains more than one matching element
The full stack trace is below.
Basically I'm just trying to get SQLite working with a lat/lon column under EF Core 7 (I don't need the Z coordinate). I'm afraid I'm floundering a bit, as the official documentation is a bit sparse. I've installed the packages you mention here—the latest versions—but I'm still getting an error when I try to add the first migration:
The property 'ZipCode.Location' is of an interface type ('IPoint')
I'd hoped to get a handle on understanding this setup by running your demo project, but I ran into a wall at Square1.
Here's my entity:
Public Class ZipCode
Inherits Entity
Public Property Location As IPoint
Public Property StateId As Integer
Public Property CityId As Integer
Public Property Code As String
Public Overridable Property State As State
Public Overridable Property City As City
End Class
I'm not sure what I need to do next.
Can you assist?
The type initializer for 'Microsoft.EntityFrameworkCore.Query.ResultOperators.Internal.TrackingExpressionNode' threw an exception.
---> System.TypeInitializationException: The type initializer for 'Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions' threw an exception.
---> System.InvalidOperationException: Sequence contains more than one matching element
at System.Linq.ThrowHelper.ThrowMoreThanOneMatchException()
at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Func`2 predicate, Boolean& found)
at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source, Func`2 predicate)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.GetMethod(String name, Int32 parameterCount, Func`2 predicate)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions..cctor()
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Query.ResultOperators.Internal.TrackingExpressionNode..cctor()
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Query.Internal.MethodInfoBasedNodeTypeRegistryFactory..ctor(MethodInfoBasedNodeTypeRegistry methodInfoBasedNodeTypeRegistry)
at Microsoft.EntityFrameworkCore.Query.Internal.DefaultMethodInfoBasedNodeTypeRegistryFactory..ctor()
at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean wrapExceptions)
--- End of stack trace from previous location ---
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCreateInstance(CreateInstanceCallSite createInstanceCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitSingleton(SingletonCallSite singletonCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(IServiceCallSite callSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
at Microsoft.EntityFrameworkCore.Internal.InternalAccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.get_DatabaseCreator()
at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureDeleted()
at ConsoleTest.Program.Main(String[] args) in D:\Dev\Projects\SpatialiteXyzPointTestEfCore\ConsoleTest\Program.cs:line 56
@cmiles
OK, got it. I was failing to call x.UseNetTopologySuite()
in my Context
configuration.
It's working now. Thanks. Your demo project helped a lot.
Using EFCore/NTS/SpatiaLite creating a table from an entity containing an IPoint property built with .ForSqliteHasDimension(Ordinates.XYZ) works as expected but trying to save an entity to the table fails with '[point] violates Geometry constraint [geom-type or SRID not allowed]'. This failure occurs with both Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite 2.2 or 3.0.0-preview.18572.1 .
Steps to reproduce
Create a new netcoreapp3.0 console project, add Microsoft.EntityFrameworkCore.Sqlite, Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite (either 2.2 or 3 preview) and mod_spatialite.
Create an entity with an IPoint property with .ForSqliteHasSrid(4326) and .ForSqliteHasDimension(Ordinates.XYZ).
Create the db, set the IPoint property and save - an error is thrown.
'Show Spatial Metadata' in the spatialite_gui on the generated column reports: f_geometry_column xyzpoint geometry_type 1001 coord_dimension 3 srid 4326
There are quite a few details here that I don't have much experience with but to try to eliminate general database/code errors and srid as issues the code sample also builds an XY point - which saves without error. I did wonder about the geometry_type of 1001 vs 1 for an xypoint is expected by EF...
The zipped solution and project included uses the code below to show the issue.
SpatialiteXyzPointTestEfCore.zip
Further technical details
EF Core version: netcoreapp3.0 Database Provider: Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite Operating system: Win10 1809 IDE: VS 2019 Version 16.0.0 Preview 1.1