Geek Answers Handbook

Check if dbgeometry dbgeometry / dbgeography is within the polygon

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

I have a DbGeometry point (or DbGeography, I can use both) and I would like to check if this is in the DbGeometry polygon (alternatively 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 over the zones (EF object of my db) and check if this point is in this border.

The problem is that it does not return any result, but I know that this point is inside this border, because I manually created a border and a point inside this border.

Can someone tell me that what I'm doing is wrong if there is another way to do this or something else?

I really appreciate it.

Manuel

+4
source share
3 answers

It is pretty simple.

 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); }
+4
source

I would like to add a comment to Nick Strupat.

You must be careful with the orientation of the ring. SQL Server uses the left orientation, which means that if you go around the perimeter of the polygon, your left hand should be inside the polygon, and your right hand should be outside (counterclockwise or counterclockwise). I got a “ring orientation” error because I drew my polygon in the opposite direction (clockwise or right-handed), which meant that SQL Server was processing the entire surface of the earth. EXCEPTION FOR my polygon as a polygon area.

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

There is a decision to check whether your landfill is good or not by checking the size of the area. For more information, click on the link:

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

Here is my blog explanation based code:

  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); }
+4
source

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

The code polygon.Area.HasValue may cause the following error for some Multipolygons .

ArgumentException: 24144: This operation cannot be completed because the instance is invalid. Use MakeValid to convert an instance to a valid instance. Note that MakeValid can create points of geometry, the instance moves slightly.

However, this will not happen if we directly convert to SqlGeography .

 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 using Entity Framework 5 <:

I use this extension method to check all Polygon and Multipolygon before storing them in 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); }

Then I can use this method to test Intersects at the 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)

You can check 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 detailed information can be found here:

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

0
source

More articles:


All Articles