DbGeography交集方法不起作用
本文关键字:不起作用 方法 DbGeography | 更新日期: 2023-09-27 18:15:37
System.Data.Spatial.DbGeography.Intersects方法似乎总是返回true。我不知道为什么会这样。我在下面创建了一个简单的命令行代码片段,产生如下控制台输出
Intersects
Intersects
该点显然不在边界附近,因此不应相交。
DbGeography bounds = DbGeography.PolygonFromText("POLYGON ((146 -20,148 -20,148 -22,146 -22,146 -20))", 4326);
DbGeography point = DbGeography.PointFromText("POINT (0 0)", 4326);
if (point.Intersects(bounds) == true)
Console.WriteLine("Intersects");
else
Console.WriteLine("Does NOT intersect");
if (bounds.Intersects(point) == true)
Console.WriteLine("Intersects");
else
Console.WriteLine("Does NOT intersect");
该点显然不在边界附近,因此不应相交。
有一条规则:一旦你说"清楚",就要准备好犯错。:)
不开玩笑,你有一个环的方向问题。也就是说,指定点的顺序很重要。当你指定了角的时候,你就定义了一个区域,这个区域就是整个地球,里面有一个非常小的洞。试着用这个代替:
POLYGON ((146 -20,146 -22,148 -22,148 -20,146 -20))
那么,你如何从本质上知道你有取向问题呢?我喜欢使用的一个启发是,如果对象的包络角很大(90度=一个半球),那么您指定的顺序不正确。在DB引擎的Geography数据类型上有一个EnvelopeAngle
方法来确定这一点(但看起来不在c#的DbGeography类中)。还有一种方便的方法(当然在DB中,但在c#中不是)用于重定向环调用,不足为奇的是ReorientObject
。
如上所述,这是一个环方向错误。对于那些使用SQL Server
-它使用左手方向,这意味着如果你沿着多边形的周长行走,你的左手应该在多边形的内部,你的右手在外部(逆时针或逆时针)。我甚至从政府数据中得到了"环方向"错误,因为多边形是在相反的方向上绘制的(顺时针,或右手),这意味着SQL Server将整个地球表面处理为多边形的面积,除了我的多边形。
这是一个自动修复此问题的方法:
public bool IsInside(DbGeography polygon, double longitude, double latitude)
{
DbGeography point = DbGeography.FromText(string.Format("POINT({1} {0})", latitude.ToString().Replace(',', '.'), longitude.ToString().Replace(',', '.')), DbGeography.DefaultCoordinateSystemId);
var wellKnownText = polygon.AsText();
var sqlGeography =
SqlGeography.STGeomFromText(new SqlChars(wellKnownText), DbGeography.DefaultCoordinateSystemId)
.MakeValid();
//Now get the inversion of the above area
var invertedSqlGeography = sqlGeography.ReorientObject();
//Whichever of these is smaller is the enclosed polygon, so we use that one.
if (sqlGeography.STArea() > invertedSqlGeography.STArea())
{
sqlGeography = invertedSqlGeography;
}
polygon = DbSpatialServices.Default.GeographyFromProviderValue(sqlGeography);
return point.Intersects(polygon);
}
对于使用实体框架5<:
我使用这个扩展方法来检查每个Polygon
和Multipolygon
保存到数据库之前。
public static DbGeography MakePolygonValid(this DbGeography geom)
{
var wellKnownText = geom.AsText();
//First, get the area defined by the well-known text using left-hand rule
var sqlGeography =
SqlGeography.STGeomFromText(new SqlChars(wellKnownText), DbGeography.DefaultCoordinateSystemId)
.MakeValid();
//Now get the inversion of the above area
var invertedSqlGeography = sqlGeography.ReorientObject();
//Whichever of these is smaller is the enclosed polygon, so we use that one.
if (sqlGeography.STArea() > invertedSqlGeography.STArea())
{
sqlGeography = invertedSqlGeography;
}
return DbSpatialServices.Default.GeographyFromProviderValue(sqlGeography);
}
我可以使用这样的方法在数据库级别检查Intersects
。
public static class GeoHelper
{
public const int SridGoogleMaps = 4326;
public const int SridCustomMap = 3857;
public static DbGeography PointFromLatLng(double lat, double lng)
{
return DbGeography.PointFromText(
"POINT("
+ lng.ToString(CultureInfo.InvariantCulture) + " "
+ lat.ToString(CultureInfo.InvariantCulture) + ")",
SridGoogleMaps);
}
}
public County GetCurrentCounty(double latitude, double longitude)
{
var point = GeoHelper.PointFromLatLng(latitude, longitude);
var county = db.Counties.FirstOrDefault(x =>
x.Area.Intersects(point));
return county;
}
实体框架生成的T-SQL:
SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Code] AS [Code],
[Extent1].[Area] AS [Area]
FROM [Election].[County] AS [Extent1]
WHERE ([Extent1].[Area].STIntersects(@p__linq__0)) = 1
-- p__linq__0: 'POINT (10.0000000 32.0000000)' (Type = Object)
可以这样手工测试:
declare @p__linq__0 varchar(max)
set @p__linq__0 = 'POINT (10.0000000 32.0000000)'
SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Code] AS [Code],
[Extent1].[Area] AS [Area]
FROM [Election].[County] AS [Extent1]
WHERE ([Extent1].[Area].STIntersects(@p__linq__0)) = 1
更多信息可以在这里找到:
https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/stintersects-geometry-data-type这个线程的来源:
检查dbgeometry/dbgeometry点是否在一个多边形内