1

Consider I have two POLYON geography object, @g1 and @g2. One from WKT and the other one converted from binary:

DECLARE @g1 geography = 'POLYGON ((-45.70072144031528 70.79588950876575,-45.70072144031528 -32.671894242015554,202.6781848096847 -32.671894242015554,202.6781848096847 70.79588950876575,-45.70072144031528 70.79588950876575))'
DECLARE @g2 geography = CONVERT(sys.geography,0xE610000001040500000038D88CDAEFB251404CE17A3DB1D946C0ECE969A1005640C04CE17A3DB1D946C0ECE969A1005640C0AC47A1B0B355694038D88CDAEFB25140AC47A1B0B355694038D88CDAEFB251404CE17A3DB1D946C001000000020000000001000000FFFFFFFF0000000003)

SQL Server reports they are equal if I execute the following query:

SELECT @g1.STEquals(@g2) G1EqualsG2
| G1EqualsG2 |
| ---------- |
| 1          |

Now consider I have the following LINESTRING object:

DECLARE @line geography
SET @line=CONVERT(geography,0xE610000001040700000022625E9DF79F3D40E89D984C229E5A402D625EED3DA03D403C9E98C81E9E5A400C625E2C86A03D40F39D08B8199E5A4091625E9CBBA03D4055A00808069E5A4033625EFCE2A03D400E9E0828F59D5A4083625E7C26A13D408C9F08F0F09D5A405A625EDC7AA13D409F9E0834E99D5A4001000000010000000001000000FFFFFFFF0000000002)

If I try to create intersections for @line with @g1 and @g2, I get two identical intersections:

SELECT @g1.STIntersection(@line) G1Intersection,
       @g2.STIntersection(@line) G2Intersection,
       @g2.STIntersection(@line).STEquals(@g1.STIntersection(@line)) IntersectionsEqual
| G1Intersection     | G2Intersection      | IntersectionsEqual |
| ------------------ | ------------------- | ------------------ |
| 0xE610000001040... | 0xE610000001040...  | 1                  |

But, if I check whether @line intersects with @g1 and @g2 using STIntersects, I get different result:

SELECT @line.STIntersects(@g1) IntersectsG1,
       @line.STIntersects(@g2) IntersectsG2
| InsersectsG1 | InsersectsG2 |
| ------------ | ------------ |
| 1            | 0            |

I'm totally confused here. Why does STIntersects produce different result for a LINESTRING with two identical POLYGONs, while they can produce identical intersections?

Can anyone shed some light on this?


Edit

You may have noticed that several longitude values in the code are greater that 180°. It's because I'm using EFCore+NTS to perform query from client, and NTS performs counter-clockwise check on POLYGONs before sending the query.

But it seems that NTS performs this check on a planar coordinate system. It only works when longitude value does not jump across 180° meridian.

In the above case, if I perform the query with orignal longitude value (-157.3218151903153°), NTS will throw an error complaining that my polygon is not counter-clockwise.

To work around this issue, I have to add 360° to longitude values on the right hand side.

This is supposed to work in SQL Server, and as you can see from the example above, it actually works if I create the polygon from WKT. So this comes back to my original question again, why does STIntersects produce different result for a LINESTRING with two identical POLYGONs?

2

It may be a wrong assumption that WKT and WKB are identical. Computing with floating point numbers is inaccurate.

Here is a test made with PostGIS

select ST_AsText(
ST_GeomFromText(
'POLYGON ((-45.70072144031528 70.79588950876575,-45.70072144031528 -32.671894242015554,202.6781848096847 -32.671894242015554,202.6781848096847 70.79588950876575,-45.70072144031528 70.79588950876575))'
)
);

Compare the source WKT and WKT that comes back from the WKB and see the difference in the last decimals.

'POLYGON ((-45.70072144031528 70.79588950876575,-45.70072144031528 -32.671894242015554,202.6781848096847 -32.671894242015554,202.6781848096847 70.79588950876575,-45.70072144031528 70.79588950876575))'

'POLYGON((-45.7007214403153 70.7958895087658,-45.7007214403153 -32.6718942420156,202.678184809685 -32.6718942420156,202.678184809685 70.7958895087658,-45.7007214403153 70.7958895087658))'

Perhaps SQL Server reports that geometries are identical because they are as identical as they can be, but some functions do not take into account the floating point inaccuracy.

2
  • I agree your point on floating point numbers. Parsing coordinates from WKT can introduce error. But the difference is small enough to be safely ignored in my situation (3.38706906236313E-08 meters in your example). Both polygons still intersect with the line string I defined in OP. I think the cause of the problem lies in STIntersects. – user849642 Jan 19 at 9:22
  • Right. It may not be easy to make a bug report docs.microsoft.com/en-us/sql/sql-server/… but try UserVoice. – user30184 Jan 19 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.