SQL Server – Resolving SQL Syntax Issues with Geometry

geometrysql server

I must be missing something pretty basic, but i have 2 tables. Both are basic, just a primary key (as a uniqueidentifier) and a geometry column in each. I'm trying to find if a point in one of the table exists in the polygon in the other table.

So this works without any problems (returns a row from PolygonTable):

DECLARE @g geometry;  
SET @g = geometry::STGeomFromText('POINT (-117.6743333 33.8343333)', 0);  
select * from PolygonTable where Polygon.STIntersects(@g)= 1

and this works as well

declare @h geometry
select @h =  Point from PointTable where PointGUID=N'3B77C26E-ACAC-4328-ED94-08D5EDE20723'
SELECT @h.ToString();  // outputs POINT (-117.6743333 33.8343333) above

but for some reason, the following does NOT work

declare @h geometry
select @h =  Point from PointTable where PointGUID=N'3B77C26E-ACAC-4328-ED94-08D5EDE20723'
select * from PolygonTable where Polygon.STIntersects(@h)= 1

That query returns no rows at all. it's the same thing at the top query basically, what am i missing?

here is a gist of the basic table settings:
https://gist.github.com/vishnu4/9bd70658b49c2cfa00ca81ff1d25bc84
you can use the queries i have above to test my results.

Best Answer

The issue you are having is because your GEOMETRY points are not in the same SPACE REFERECEN ID. For the first case you are in SRID = 0, for the second case you are in SRID = 4326. This makes the Intersection returns an empty list.

Check this documentation:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stintersects-geometry-data-type?view=sql-server-2017

Related Question