0

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.

1

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

  • Ok, sure. this explains WHY it doesn't work, but i don't really know the best path forward from here to get my SQL statement #3 to work. is there a way to update the data in the polygon table to have the same SRID value? do i have to scrap that data entirely and build it in another fashion? – Phil Jul 20 '18 at 18:03
  • 1
    You could try to update the SRID in both tables to a unique one. But I don't know what the impact of this update will be on other tables or procedures you may have... Just to be sure, create 2 new tables from selecting both old tables and then update the new ones, for example 4326 for both and see what happens after that: to update the SRID try this query..... update TABLE set COLUMN.STSrid=4326; – AMG Jul 20 '18 at 18:33

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.