空间数据Spatial Data

备注

此功能是在 EF Core 2.2 中添加的。This feature was added in EF Core 2.2.

空间数据表示对象的物理位置和形状。Spatial data represents the physical location and the shape of objects. 许多数据库提供对此类数据的支持,以便能够与其他数据一起进行索引和查询。Many databases provide support for this type of data so it can be indexed and queried alongside other data. 常见方案包括从位置在给定距离内查询对象,或选择其边框包含给定位置的对象。Common scenarios include querying for objects within a given distance from a location, or selecting the object whose border contains a given location. EF Core 支持使用 NetTopologySuite 空间库映射到空间数据类型。EF Core supports mapping to spatial data types using the NetTopologySuite spatial library.

安装Installing

若要在 EF Core 中使用空间数据,需要安装相应的支持 NuGet 包。In order to use spatial data with EF Core, you need to install the appropriate supporting NuGet package. 需要安装哪个包取决于所使用的提供程序。Which package you need to install depends on the provider you're using.

EF Core 提供程序EF Core Provider 空间 NuGet 包Spatial NuGet Package
Microsoft.EntityFrameworkCore.SqlServerMicrosoft.EntityFrameworkCore.SqlServer Microsoft.entityframeworkcore. NetTopologySuiteMicrosoft.EntityFrameworkCore.SqlServer.NetTopologySuite
Microsoft.EntityFrameworkCore.SqliteMicrosoft.EntityFrameworkCore.Sqlite Microsoft.entityframeworkcore. NetTopologySuiteMicrosoft.EntityFrameworkCore.Sqlite.NetTopologySuite
Microsoft.EntityFrameworkCore.InMemoryMicrosoft.EntityFrameworkCore.InMemory NetTopologySuiteNetTopologySuite
Npgsql.EntityFrameworkCore.PostgreSQLNpgsql.EntityFrameworkCore.PostgreSQL Npgsql. Microsoft.entityframeworkcore. PostgreSQL. NetTopologySuiteNpgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite

反向工程Reverse engineering

空间 NuGet 包还启用具有空间属性的 反向工程 模型,但需要在运行或 之前 安装包 Scaffold-DbContext dotnet ef dbcontext scaffoldThe spatial NuGet packages also enable reverse engineering models with spatial properties, but you need to install the package before running Scaffold-DbContext or dotnet ef dbcontext scaffold. 否则,你将收到有关找不到列的类型映射的警告,将跳过这些列。If you don't, you'll receive warnings about not finding type mappings for the columns and the columns will be skipped.

NetTopologySuite (NTS) NetTopologySuite (NTS)

NetTopologySuite 是用于 .NET 的空间库。NetTopologySuite is a spatial library for .NET. EF Core 使用模型中的 NTS 类型启用映射到数据库中的空间数据类型。EF Core enables mapping to spatial data types in the database by using NTS types in your model.

若要通过 NTS 启用到空间类型的映射,请在提供程序的 DbContext 选项生成器上调用 UseNetTopologySuite 方法。To enable mapping to spatial types via NTS, call the UseNetTopologySuite method on the provider's DbContext options builder. 例如,对于 SQL Server,你应将其称为。For example, with SQL Server you'd call it like this.

optionsBuilder.UseSqlServer(
    @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=WideWorldImporters",
    x => x.UseNetTopologySuite());

有几种空间数据类型。There are several spatial data types. 使用哪种类型取决于您想要允许的形状的类型。Which type you use depends on the types of shapes you want to allow. 下面是可用于模型中的属性的 NTS 类型的层次结构。Here is the hierarchy of NTS types that you can use for properties in your model. 它们位于 NetTopologySuite.Geometries 命名空间内。They're located within the NetTopologySuite.Geometries namespace.

  • 几何结构Geometry
    • Point
    • LineStringLineString
    • PolygonPolygon
    • GeometryCollectionGeometryCollection
      • MultiPointMultiPoint
      • MultiLineStringMultiLineString
      • MultiPolygonMultiPolygon

警告

NTS 不支持 CircularString、CompoundCurve 和 CurePolygon。CircularString, CompoundCurve, and CurePolygon aren't supported by NTS.

使用基本几何图形类型允许属性指定任意类型的形状。Using the base Geometry type allows any type of shape to be specified by the property.

以下实体类可用于映射到 广角导入示例数据库中的表。The following entity classes could be used to map to tables in the Wide World Importers sample database.

[Table("Cities", Schema = "Application"))]
class City
{
    public int CityID { get; set; }

    public string CityName { get; set; }

    public Point Location { get; set; }
}

[Table("Countries", Schema = "Application"))]
class Country
{
    public int CountryID { get; set; }

    public string CountryName { get; set; }

    // Database includes both Polygon and MultiPolygon values
    public Geometry Border { get; set; }
}

创建值Creating values

您可以使用构造函数来创建 geometry 对象;但是,NTS 建议改为使用几何工厂。You can use constructors to create geometry objects; however, NTS recommends using a geometry factory instead. 这使你可以指定默认的 SRID (坐标使用的空间引用系统) 并使你能够控制更高级的功能,如) 计算期间使用的精度模型 (,并且坐标序列 (确定哪些坐标维度和度量值可用。This lets you specify a default SRID (the spatial reference system used by the coordinates) and gives you control over more advanced things like the precision model (used during calculations) and the coordinate sequence (determines which ordinates--dimensions and measures--are available).

var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
var currentLocation = geometryFactory.CreatePoint(new Coordinate(-122.121512, 47.6739882));

备注

4326指的是 WGS 84,是 GPS 和其他地理系统中使用的标准。4326 refers to WGS 84, a standard used in GPS and other geographic systems.

经度和纬度Longitude and Latitude

NTS 中的坐标采用 X 和 Y 值。Coordinates in NTS are in terms of X and Y values. 若要表示经度和纬度,请将 X 用于经度,将 Y 用于纬度。To represent longitude and latitude, use X for longitude and Y for latitude. 请注意,这backwards是从 latitude, longitude 通常会看到这些值的格式反向进行的。Note that this is backwards from the latitude, longitude format in which you typically see these values.

在客户端操作过程中忽略 SRIDSRID Ignored during client operations

NTS 在操作过程中忽略 SRID 值。NTS ignores SRID values during operations. 它假定为平面坐标系统。It assumes a planar coordinate system. 这意味着,如果在经度和纬度方面指定了坐标,则某些客户端计算的值(例如,距离、长度和区域)将为度数,而不是计量。This means that if you specify coordinates in terms of longitude and latitude, some client-evaluated values like distance, length, and area will be in degrees, not meters. 若要获得更有意义的值,首先需要使用库(如 ProjNet4GeoAPI )在计算这些值之前投影到另一个坐标系统的坐标。For more meaningful values, you first need to project the coordinates to another coordinate system using a library like ProjNet4GeoAPI before calculating these values.

如果通过 EF Core 通过 SQL 对操作进行服务器计算,则该结果的单元将由数据库确定。If an operation is server-evaluated by EF Core via SQL, the result's unit will be determined by the database.

下面是一个示例,说明如何使用 ProjNet4GeoAPI 来计算两个城市之间的距离。Here is an example of using ProjNet4GeoAPI to calculate the distance between two cities.

static class GeometryExtensions
{
    static readonly CoordinateSystemServices _coordinateSystemServices
        = new CoordinateSystemServices(
            new CoordinateSystemFactory(),
            new CoordinateTransformationFactory(),
            new Dictionary<int, string>
            {
                // Coordinate systems:

                [4326] = GeographicCoordinateSystem.WGS84.WKT,

                // This coordinate system covers the area of our data.
                // Different data requires a different coordinate system.
                [2855] =
                @"
                    PROJCS[""NAD83(HARN) / Washington North"",
                        GEOGCS[""NAD83(HARN)"",
                            DATUM[""NAD83_High_Accuracy_Regional_Network"",
                                SPHEROID[""GRS 1980"",6378137,298.257222101,
                                    AUTHORITY[""EPSG"",""7019""]],
                                AUTHORITY[""EPSG"",""6152""]],
                            PRIMEM[""Greenwich"",0,
                                AUTHORITY[""EPSG"",""8901""]],
                            UNIT[""degree"",0.01745329251994328,
                                AUTHORITY[""EPSG"",""9122""]],
                            AUTHORITY[""EPSG"",""4152""]],
                        PROJECTION[""Lambert_Conformal_Conic_2SP""],
                        PARAMETER[""standard_parallel_1"",48.73333333333333],
                        PARAMETER[""standard_parallel_2"",47.5],
                        PARAMETER[""latitude_of_origin"",47],
                        PARAMETER[""central_meridian"",-120.8333333333333],
                        PARAMETER[""false_easting"",500000],
                        PARAMETER[""false_northing"",0],
                        UNIT[""metre"",1,
                            AUTHORITY[""EPSG"",""9001""]],
                        AUTHORITY[""EPSG"",""2855""]]
                "
            });

    public static Geometry ProjectTo(this Geometry geometry, int srid)
    {
        var transformation = _coordinateSystemServices.CreateTransformation(geometry.SRID, srid);

        var result = geometry.Copy();
        result.Apply(new MathTransformFilter(transformation.MathTransform));

        return result;
    }

    class MathTransformFilter : ICoordinateSequenceFilter
    {
        readonly MathTransform _transform;

        public MathTransformFilter(MathTransform transform)
            => _transform = transform;

        public bool Done => false;
        public bool GeometryChanged => true;

        public void Filter(CoordinateSequence seq, int i)
        {
            var result = _transform.Transform(
                new[]
                {
                    seq.GetOrdinate(i, Ordinate.X),
                    seq.GetOrdinate(i, Ordinate.Y)
                });
            seq.SetOrdinate(i, Ordinate.X, result[0]);
            seq.SetOrdinate(i, Ordinate.Y, result[1]);
        }
    }
}
var seattle = new Point(-122.333056, 47.609722) { SRID = 4326 };
var redmond = new Point(-122.123889, 47.669444) { SRID = 4326 };

var distance = seattle.ProjectTo(2855).Distance(redmond.ProjectTo(2855));

查询数据Querying Data

在 LINQ 中,可用作数据库函数的 NTS 方法和属性将转换为 SQL。In LINQ, the NTS methods and properties available as database functions will be translated to SQL. 例如,在以下查询中转换距离和包含方法。For example, the Distance and Contains methods are translated in the following queries. 本文末尾的表格显示了不同 EF Core 提供商支持哪些成员。The table at the end of this article shows which members are supported by various EF Core providers.

var nearestCity = db.Cities
    .OrderBy(c => c.Location.Distance(currentLocation))
    .FirstOrDefault();

var currentCountry = db.Countries
    .FirstOrDefault(c => c.Border.Contains(currentLocation));

SQL ServerSQL Server

如果你正在使用 SQL Server,你还应该注意一些其他问题。If you're using SQL Server, there are some additional things you should be aware of.

地理或几何图形Geography or geometry

默认情况下,空间属性映射到 geography SQL Server 中的列。By default, spatial properties are mapped to geography columns in SQL Server. 若要使用 geometry ,请在模型中 配置列类型To use geometry, configure the column type in your model.

地理多边形环Geography polygon rings

当使用 geography 列类型时,SQL Server 对外环 (或 shell) 和内部环 (或孔) 施加附加要求。When using the geography column type, SQL Server imposes additional requirements on the exterior ring (or shell) and interior rings (or holes). 外部环必须逆时针旋转,并顺时针旋转内部环。The exterior ring must be oriented counterclockwise and the interior rings clockwise. NTS 在将值发送到数据库之前对其进行验证。NTS validates this before sending values to the database.

FullGlobeFullGlobe

在使用列类型时,SQL Server 具有非标准几何类型来表示全地球 geographySQL Server has a non-standard geometry type to represent the full globe when using the geography column type. 它还可以基于全地球 (来表示多边形,而无需外环) 。It also has a way to represent polygons based on the full globe (without an exterior ring). NTS 不支持这两种方法。Neither of these are supported by NTS.

警告

NTS 不支持基于 FullGlobe 和多边形。FullGlobe and polygons based on it aren't supported by NTS.

SQLiteSQLite

下面是使用 SQLite 的一些其他信息。Here is some additional information for those using SQLite.

安装 SpatiaLiteInstalling SpatiaLite

在 Windows 上,本机 mod_spatialite 库以 NuGet 包的依赖项的形式分发。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. 例如,可以在 Ubuntu 上使用 APT 和 MacOS 上的 Homebrew。For example, you can use APT on Ubuntu and Homebrew on MacOS.

# Ubuntu
apt-get install libsqlite3-mod-spatialite

# macOS
brew install libspatialite

遗憾的是,较新版本的 PROJ (SpatiaLite) 的依赖项与 EF 的默认 SQLitePCLRaw 绑定不兼容。Unfortunately, newer versions of PROJ (a dependency of SpatiaLite) are incompatible with EF's default SQLitePCLRaw bundle. 若要解决此情况,可以创建使用系统 SQLite 库的自定义 SQLitePCLRaw 提供程序 ,也可以安装 SPATIALITE 禁用 PROJ 支持的自定义生成。You can work around this by either creating a custom SQLitePCLRaw provider that uses the system SQLite library, or you can install a custom build of SpatiaLite disabling PROJ support.

curl https://www.gaia-gis.it/gaia-sins/libspatialite-4.3.0a.tar.gz | tar -xz
cd libspatialite-4.3.0a

if [[ `uname -s` == Darwin* ]]; then
    # Mac OS requires some minor patching
    sed -i "" "s/shrext_cmds='\`test \\.\$module = .yes && echo .so \\|\\| echo \\.dylib\`'/shrext_cmds='.dylib'/g" configure
fi

./configure --disable-proj
make
make install

配置 SRIDConfiguring SRID

在 SpatiaLite 中,列需要为每个列指定一个 SRID。In SpatiaLite, columns need to specify an SRID per column. 默认的 SRID 为 0The default SRID is 0. 使用 ForSqliteHasSrid 方法指定其他 SRID。Specify a different SRID using the ForSqliteHasSrid method.

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

维度Dimension

类似于 SRID,列的维度 (或坐标) 也被指定为列的一部分。Similar to SRID, a column's dimension (or ordinates) is also specified as part of the column. 默认坐标为 X 和 Y。使用 ForSqliteHasDimension 方法启用其他坐标 (Z 和 M) 。The default ordinates are X and Y. Enable additional ordinates (Z and M) using the ForSqliteHasDimension method.

modelBuilder.Entity<City>().Property(c => c.Location)
    .ForSqliteHasDimension(Ordinates.XYZ);

转换的操作Translated Operations

此表显示每个 EF Core 提供程序将哪些 NTS 成员转换为 SQL。This table shows which NTS members are translated into SQL by each EF Core provider.

NetTopologySuiteNetTopologySuite SQL Server (几何) SQL Server (geometry) SQL Server (地域) SQL Server (geography) SQLiteSQLite NpgsqlNpgsql
GeometryGeometry.Area
AsBinary ( # A1Geometry.AsBinary()
AsText ( # A1Geometry.AsText()
GeometryGeometry.Boundary
Geometry (双) Geometry.Buffer(double)
(double、int) 的 GeometryGeometry.Buffer(double, int)
质心Geometry.Centroid
Geometry:包含 (几何) Geometry.Contains(Geometry)
ConvexHull ( # A1Geometry.ConvexHull()
CoveredBy (几何) Geometry.CoveredBy(Geometry)
几何。覆盖 (几何) Geometry.Covers(Geometry)
Geometry (几何交叉) Geometry.Crosses(Geometry)
Geometry (几何) 的差异Geometry.Difference(Geometry)
Geometry。维度Geometry.Dimension
不连续的 (几何) Geometry.Disjoint(Geometry)
Geometry (几何) Geometry.Distance(Geometry)
Geometry 信封Geometry.Envelope
EqualsExact (几何) Geometry.EqualsExact(Geometry)
EqualsTopologically (几何) Geometry.EqualsTopologically(Geometry)
GeometryTypeGeometry.GeometryType
GetGeometryN (int) Geometry.GetGeometryN(int)
InteriorPointGeometry.InteriorPoint
几何图形 (几何) Geometry.Intersection(Geometry)
Geometry (几何的交集) Geometry.Intersects(Geometry)
IsEmptyGeometry.IsEmpty
IsSimpleGeometry.IsSimple
GeometryGeometry.IsValid
IsWithinDistance (Geometry、double) Geometry.IsWithinDistance(Geometry, double)
Geometry。长度Geometry.Length
NumGeometriesGeometry.NumGeometries
X.numpointsGeometry.NumPoints
OgcGeometryTypeGeometry.OgcGeometryType
Geometry (几何重叠) Geometry.Overlaps(Geometry)
PointOnSurfaceGeometry.PointOnSurface
Geometry (Geometry、string) 相关Geometry.Relate(Geometry, string)
Geometry ( # A1Geometry.Reverse()
SRIDGeometry.SRID
SymmetricDifference (几何) Geometry.SymmetricDifference(Geometry)
ToBinary ( # A1Geometry.ToBinary()
ToText ( # A1Geometry.ToText()
几何图形 (几何) Geometry.Touches(Geometry)
Geometry ( # A1Geometry.Union()
Geometry (几何) Geometry.Union(Geometry)
Geometry (几何) Geometry.Within(Geometry)
GeometryCollectionGeometryCollection.Count
GeometryCollection [int]GeometryCollection[int]
LineStringLineString.Count
LineString 终结点LineString.EndPoint
LineString. GetPointN (int) LineString.GetPointN(int)
LineString. IsClosedLineString.IsClosed
LineString. IsRingLineString.IsRing
LineString. StartPointLineString.StartPoint
MultiLineString. IsClosedMultiLineString.IsClosed
点 MPoint.M
点 XPoint.X
Point。 YPoint.Y
点 ZPoint.Z
多边形。 ExteriorRingPolygon.ExteriorRing
GetInteriorRingN (int) Polygon.GetInteriorRingN(int)
多边形。 NumInteriorRingsPolygon.NumInteriorRings

其他资源Additional resources