CODEWITHSUNDEEP
c#.netsql-serverentity-frameworkgeospatial
Görkem Öğüt
Görkem Öğüt

Reputation: 1791

Intersection of MultiPolygon and Polygon by using DbGeography

I am trying to get entities from the intersection DbGeography column which has MultiPolygons inside and a Polygon which is the boundary box

var sqlPoly = SqlGeography.Parse(bbox); //bbox is in WKT format

if (sqlPoly.EnvelopeAngle() >= 90)
    sqlPoly = sqlPoly.ReorientObject();

var box = DbGeography.FromBinary(sqlPoly.STAsBinary().Value); //resulting bbox is OK and tested by parsing WKT

var query = from ls in _mdb.Listings where box.Intersects(ls.Geo) select ls;

So the result is highly weird. I am selecting a bbox which is thousand miles away from the db result set but this query still returns results.

I believe it is an issue about MultiPolygons.

EDIT:

I tried to eliminate Entity Framework and used Raw SQL but result is same:

var q = "DECLARE @g geography; SET @g = geography::Parse('" + box.AsText() + "'); SELECT * FROM Listings WHERE @g.STIntersects(Geo) = 1;";

When I change STIntersects with STContains or STWithin the resulting sets get filtered but this time; it is too much filtered too many missing geographies appearing.

Upvotes: 2

Views: 1002

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

Using the sample data that you gave me, many of the objects have what is called a ring orientation problem. Simply stated, the order in which you specify the corners of a geography polygon matter. That is to say ABCDA <> ADCBA. Luckily the fix is simple. I ran the following update on the sample table:

update l
set Geo = Geo.ReorientObject()
from Listele as l
where Geo.EnvelopeAngle() = 180

And now when I run a select on the data, it appears to represent a city with some rivers (or perhaps streets) flowing through it. Which I'm guessing is much more what you're looking for.

Upvotes: 1

Related Questions