SQL find a polygon that a point falls within

[JohnMay3]

I have a feature class of points and want to retrieve information about the polygon that a particular point falls within using SQL. The following query works however it does not return any results:

select * from Basins
join (Select Shape from Pointlayer where Increment = 02047) as Point
on Basins.Shape.STWithin(Point.Shape) = 1;

I have also tried STIntersects with the same result.

Next I created a feature class that contained a single point and the query did return the correct result. It seems to be failing at evaluation the Select statement for the join. When run by itself the select statement does return the Shape field however when embedded in the join query it fails. 

Any help would be appreciated.

[JoshuaBixby · Accepted solution]

I am guessing ALLPOINTS was loaded into SQL Server not using Esri tools and then later registered with the enterprise geodatabase.  The ALLPOINTS layer was created from points, but whoever created those points did not associate an SRID with the points, hence why it is NULL.  When the table was registered with the EGDB, a spatial reference was assigned, but assigning the spatial reference updates metadata, it doesn't go back and add it to every record in the table.  When you selected the point and exported to POINTTEST, ArcGIS took the assigned spatial reference and properly assigned it to the point when it created it in the new feature class.

If ALLPOINTS is registered with the EGDB, I believe you will not be allowed to update the SRID outside of ArcGIS.  The easiest way to get the correct SRID assigned to each of the geometries in ALLPOINTS is to export the entire feature class back into the EGDB, delete the old one, and then you can rename the new one back to ALLPOINTS.

[JohnMay3]

Yep. I finally figured that out around 6:00 this morning. Thanks for the help.