Check if dbgeometry dbgeometry/dbgeography point i

问题:

I have a problem I hope you guys can help me solve.

I have got a DbGeometry point (or DbGeography, I can use both) and I would like to check if this is within a DbGeometry Polygon (alternatively a DbGeography).

I am doing this at the moment:

var dbZones = new List<WasteManager.Database.Zone>();
foreach(var zone in zones)
        {
            var res = from z in DatabaseContext.Zones
                   let boundary =
                       !z.BoundaryGeometry.IsValid
                           ? SqlSpatialFunctions.MakeValid(z.BoundaryGeometry)
                           : z.BoundaryGeometry
                      where z.ID == zone.ID && point.Within(boundary)
                      select z;

            if(res.FirstOrDefault() != null) dbZones.Add(res.FirstOrDefault());

        }

So I iterate through zones (EF entity of my db) and check if this point I have is within this boundary.

Problem is that it does not return any result, but I know that that point is within that boundary because I created manually the boundary and the point to be inside that boundary.

Can anyone tell me if what I am doing is wrong, if there is another way to do this or whatever else?

Much appreciate.

Manuel

回答1:

I would like to add a comment to Nick Strupat.

You should be carefull with ring orientation. SQL Server uses left-handed orientation, which means that if you are walking along the perimeter of a polygon, your left hand should be on the inside of the polygon and your right hand on the outside (counter-clockwise or anti-clockwise). I got the “ring orientation” error because I drew my polygon in the opposite direction (clockwise, or right-handed) which meant that SQL Server was treating the whole surface of the earth EXCEPT FOR my polygon as the area of the polygon.

To check if a point is in the polygon you should always use point.Intersects(polygon) and not !point.Intersects(polygon).

There is a solution to check if your polygon is ok or not by checking the size of the Area, For more information go to :

https://blog.falafel.com/ring-orientation-sql-spatial/

Here is my code based on the blog explanation :

    private 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);

        // If the polygon area is larger than an earth hemisphere (510 Trillion m2 / 2), we know it needs to be fixed
        if (polygon.Area.HasValue && polygon.Area.Value > 255000000000000L)
        {
            // Convert our DbGeography polygon into a SqlGeography object for the ReorientObject() call
            SqlGeography sqlPolygon = SqlGeography.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes(polygon.AsBinary()), DbGeography.DefaultCoordinateSystemId);

            // ReorientObject will flip the polygon so the outside becomes the inside
            sqlPolygon = sqlPolygon.ReorientObject();

            // Convert the SqlGeography object back into DbGeography
            polygon = DbGeography.FromBinary(sqlPolygon.STAsBinary().Value);

        }
        return point.Intersects(polygon);
    }

回答2:

It's quite easy actually.

bool isInside(DbGeometry polygon, double longitude, double latitude) //or DbGeography in your case
{
    DbGeometry point = DbGeometry.FromText(string.Format("POINT({0} {1})",longitude, latitude), 4326);
    return polygon.Contains(point);    
}

回答3:

I would also like to add a comment but to @BenoitGlaizette.

The code polygon.Area.HasValue can throw the following error for some Multipolygons.

ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.

However this won't occur if we convert to SqlGeography directly.

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);
}

For those that use Entity Framework 5<:

I use this extension method to check every Polygon and Multipolygon before saving them to the database.

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);
}

I can then use a method like this to check Intersects at database level.

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 generated by Entity Framework:

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)

Can be tested like this manually:

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

More information can be found here:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stintersects-geometry-data-type