STIntersection gives wrong result

Question

Hello,

Using following example as described in documentation (https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/stintersects-geometry-data-type?view=sql-server-ver15#examples) one would expect that the result would be 1... instead of 0 as I getwhen I run it on my Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) installation.

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STIntersects(@h); --returns 0!

I however get correct answer '1' if I use older version (Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64).

Could you explain what could be the issue?

Sincerely,
Tryggvi Már

Answer 1

I find that all version from SQL 2012 and on returns 0, no matter the compatibility level. It kind of smells as "by design". After all, the intersection of a line and a point sounds a little funny to me.

I note that this very example appear in the BOL Topic for STIntersects. I also note that the text is very careful not to spell out the correct result.

Answer 2

Good day,

In physics and mathematics, the dimension of an object is defined as the minimum number of coordinates needed to specify any point within it. Thus a POINT has no dimension (or it has a zero dimension), a line has a dimension of one, and a POLYGON has a dimension of two for exxample.

Since a POINT has no dimension it cannot intersect another POINT or a line.

Lines are said to intersect each other if they cut each other at a POINT . Same works with POLYGON.

Straight parallel lines can be concedered as intersect at any point if they are in the same position (like a POINT and a POINT) or as no intersect each other same as a POINT do not intersect a POINT or a LINE.

A POINT do not have a size or direction which mean a point cannot Intersects another POINT or a line.

A POINT can Intersects POLYGON if it is insied the POLYGON

Geospatial data types were added in SQL Server 2008 and this was NOT well implementyed first. If you will test your query on SQL Server 2008, then it will return 1 which is a mistake according to the defanition of Intersect In physics and mathematics. This issue was fixed in SQL Server 2012 and good that it was fixed!

By the way, this is a bit like the mistakes of comparing NULL to NULL. Since NULL is not a value, NULL is not necessarily the same as another NULL. Same as a POINT do not intersect a LINE or another POINT.

More information you can get from the following demo (read the comments)

DECLARE @LINE GEOMETRY;
DECLARE @POINT GEOMETRY;
DECLARE @polygon GEOMETRY;
SET @LINE       = GEOMETRY::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 4326);
SET @POINT       = GEOMETRY::STGeomFromText('POINT(1 1)', 4326);
SET @polygon = GEOMETRY::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326)

-- Use SSMS and view the geometric objext in the graph. 
-- You can notice that the point is at the same position as the line
select @LINE as a, @POINT as h, @polygon as P
-- but the POINT is not intersecting the line

-- Next we can determine if two geometry instances intersect each other.
SELECT [Intersect line and point] = @LINE.STIntersects(@POINT); --returns 0!
-- Lines are said to intersect each other if they cut each other at a point.
-- But a point do not have a size or direction which mean a point cannot Intersects another point or a line. 
-- A point can Intersects POLYGON if it is insied the 
SELECT @polygon.STIntersects(@POINT)
GO

Answer 3

Hi @Tryggvi Már Ingvarsson ,

Welcome to the microsoft TSQL Q&A forum!

DECLARE @g geometry;  
DECLARE @h geometry;  
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);  
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);  
SELECT @g.STIntersects(@h);  

Why do you think the correct result of the above code is 1?

In mathematics, a line and a line or a line and a surface can intersect, but there is no saying that a point and a line intersect. So actually returning 0 is the correct result.

Each new version of SQL Server will make improvements and upgrades on the basis of the old version, and even fix old bugs. So the different results you get on the earlier version and the new version are only the result of improved accuracy or bug fixes.

If you really need this code to return a result of 1, please refer to:

    DECLARE @g geometry;    
    DECLARE @h geometry;    
    SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);    
    SET @h = geometry::STGeomFromText('POINT(1 1)', 0);    
    SELECT IIF(@g.STIntersects(@h)=1,@g.STIntersects(@h),1);   

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Viorel @Tryggvi Már Ingvarsson ，

This message is posted to show that I am studying this issue. According to the current test, most of the test results on straight lines and points on straight lines are 1, but the test results on points on polylines and polylines are 0. If there is any progress in the follow-up, I will reply under this post.

Regards
Echo