Chỉnh sửa

Chia sẻ qua

Spatial Data in the SQLite EF Core Provider

This page includes additional information about using spatial data with the SQLite database provider. For general information about using spatial data in EF Core, see the main Spatial Data documentation.

Installing SpatiaLite

On Windows, the native mod_spatialite library is distributed as a NuGet package dependency. Other platforms need to install it separately. This is typically done using a software package manager. For example, you can use APT on Debian and Ubuntu; and Homebrew on MacOS.

# Debian/Ubuntu
apt-get install libsqlite3-mod-spatialite

# macOS
brew install libspatialite

Unfortunately, newer versions of PROJ (a dependency of SpatiaLite) are incompatible with EF's default SQLitePCLRaw bundle. You can work around this by using the system SQLite library instead.

Important

Don't use Microsoft.EntityFrameworkCore.Sqlite or Microsoft.Data.Sqlite with SpatiaLite on macOS and Linux. Both packages pull in SQLitePCLRaw.bundle_e_sqlite3 by default—a bundled version of SQLite that is incompatible with system-installed Sqlite. Using it may result in a silent crash at run time. Use Microsoft.EntityFrameworkCore.Sqlite.Core or Microsoft.Data.Sqlite.Core instead, together with the system SQLite provider as shown below.

Replace Microsoft.EntityFrameworkCore.Sqlite with Microsoft.EntityFrameworkCore.Sqlite.Core and reference the SQLitePCLRaw.provider.sqlite3 package to use the system SQLite library:

<ItemGroup>
  <!-- Use Sqlite.Core with the system SQLite provider instead of Microsoft.EntityFrameworkCore.Sqlite -->
  <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="10.0.0" />
  <PackageReference Include="SQLitePCLRaw.provider.sqlite3" Version="2.1.10" />

  <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="10.0.0" />
</ItemGroup>

Note

Starting with EF Core 11 (SQLitePCLRaw 3.0), replace the SQLitePCLRaw.provider.sqlite3 version with 3.x.x. See the breaking changes for details.

Then add explicit initialization before using SQLite:

SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());

On macOS, you'll also need to set an environment variable before running your app so it uses Homebrew's version of SQLite:

DYLD_LIBRARY_PATH="$(brew --prefix sqlite)/lib"

Configuring SRID

In SpatiaLite, columns need to specify an SRID per column. The default SRID is 0. Specify a different SRID using the HasSrid method.

modelBuilder.Entity<City>().Property(c => c.Location)
    .HasSrid(4326);

Note

4326 refers to WGS 84, a standard used in GPS and other geographic systems.

Dimension

The default dimension (or ordinates) of a column is X and Y. To enable additional ordinates like Z or M, configure the column type.

modelBuilder.Entity<City>().Property(c => c.Location)
    .HasColumnType("POINTZ");

Spatial function mappings

This table shows which NetTopologySuite (NTS) members are translated into which SQL functions.

.NET SQL
geometry.Area Area(@geometry)
geometry.AsBinary() AsBinary(@geometry)
geometry.AsText() AsText(@geometry)
geometry.Boundary Boundary(@geometry)
geometry.Buffer(distance) Buffer(@geometry, @distance)
geometry.Buffer(distance, quadrantSegments) Buffer(@geometry, @distance, @quadrantSegments)
geometry.Centroid Centroid(@geometry)
geometry.Contains(g) Contains(@geometry, @g)
geometry.ConvexHull() ConvexHull(@geometry)
geometry.CoveredBy(g) CoveredBy(@geometry, @g)
geometry.Covers(g) Covers(@geometry, @g)
geometry.Crosses(g) Crosses(@geometry, @g)
geometry.Difference(other) Difference(@geometry, @other)
geometry.Dimension Dimension(@geometry)
geometry.Disjoint(g) Disjoint(@geometry, @g)
geometry.Distance(g) Distance(@geometry, @g)
geometry.Envelope Envelope(@geometry)
geometry.EqualsTopologically(g) Equals(@geometry, @g)
geometry.GeometryType GeometryType(@geometry)
geometry.GetGeometryN(n) GeometryN(@geometry, @n + 1)
geometry.InteriorPoint PointOnSurface(@geometry)
geometry.Intersection(other) Intersection(@geometry, @other)
geometry.Intersects(g) Intersects(@geometry, @g)
geometry.IsEmpty IsEmpty(@geometry)
geometry.IsSimple IsSimple(@geometry)
geometry.IsValid IsValid(@geometry)
geometry.IsWithinDistance(geom, distance) Distance(@geometry, @geom) <= @distance
geometry.Length GLength(@geometry)
geometry.NumGeometries NumGeometries(@geometry)
geometry.NumPoints NumPoints(@geometry)
geometry.OgcGeometryType CASE GeometryType(@geometry) WHEN 'POINT' THEN 1 ... END
geometry.Overlaps(g) Overlaps(@geometry, @g)
geometry.PointOnSurface PointOnSurface(@geometry)
geometry.Relate(g, intersectionPattern) Relate(@geometry, @g, @intersectionPattern)
geometry.Reverse() ST_Reverse(@geometry)
geometry.SRID SRID(@geometry)
geometry.SymmetricDifference(other) SymDifference(@geometry, @other)
geometry.ToBinary() AsBinary(@geometry)
geometry.ToText() AsText(@geometry)
geometry.Touches(g) Touches(@geometry, @g)
geometry.Union() UnaryUnion(@geometry)
geometry.Union(other) GUnion(@geometry, @other)
geometry.Within(g) Within(@geometry, @g)
geometryCollection[i] GeometryN(@geometryCollection, @i + 1)
geometryCollection.Count NumGeometries(@geometryCollection)
lineString.Count NumPoints(@lineString)
lineString.EndPoint EndPoint(@lineString)
lineString.GetPointN(n) PointN(@lineString, @n + 1)
lineString.IsClosed IsClosed(@lineString)
lineString.IsRing IsRing(@lineString)
lineString.StartPoint StartPoint(@lineString)
multiLineString.IsClosed IsClosed(@multiLineString)
point.M M(@point)
point.X X(@point)
point.Y Y(@point)
point.Z Z(@point)
polygon.ExteriorRing ExteriorRing(@polygon)
polygon.GetInteriorRingN(n) InteriorRingN(@polygon, @n + 1)
polygon.NumInteriorRings NumInteriorRing(@polygon)

Aggregate functions

.NET SQL Added in
GeometryCombiner.Combine(group.Select(x => x.Property)) Collect(Property) EF Core 7.0
ConvexHull.Create(group.Select(x => x.Property)) ConvexHull(Collect(Property)) EF Core 7.0
UnaryUnionOp.Union(group.Select(x => x.Property)) GUnion(Property) EF Core 7.0
EnvelopeCombiner.CombineAsGeometry(group.Select(x => x.Property)) Extent(Property) EF Core 7.0

Additional resources

  • Last updated on