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]

STWithin won't return any results because you cannot have a polygon within a point.  Regarding STIntersects, is this versioned data you are working with?  If so, you should be querying the versioned views and not the base tables.

[JohnMay3]

I see the obvious error and rewrote the query as follows to make sure I look for the polygon that has the point within it:

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

Same result. As an aside I made a single point feature class to test and see if I got a result. At least this would let me know that the logic was evaluating correctly. Here is the query:

SELECT * FROM BASINS
JOIN POINTTEST as B
on B.Shape.STWithin(Basins.Shape) = 1;

This query does return the poly info. So it appears the Select statement is amiss, though it evaluates properly when run alone. The data is versioned however the versioning is set to write all edits to the Base so I don't think that is the issue, plus everything is reconciled and the delta tables are empty.

[JoshuaBixby]

If Increment is a text field, which its format makes me think it is, then you need to put the value in single quotes:  Increment = '02047'.

[JohnMay3]

With or without quotes same result. Funny thing is if I run the following it returns the shape:

Select Shape from D5 where Increment = '02047'

However when the select statement is used to make a recordset for the join it seems to return an empty set which of course means the join fails.

[JohnMay3]

Solution that works.

While I still cannot explain why the join operation is not working I found that the following solution would return the correct data.

DECLARE @point GEOMETRY = GEOMETRY::Point((select shape.STX as LONG from Pointcoverage where Increment = '02048'), (select shape.STY as LAT from Pointcoverage  where Increment = '02048'), 4326);
SELECT * from Polycoverage where @point.STWithin(Polycoverage .shape) = 1;

[JoshuaBixby]

Are the data layers in the same projection?  If not, from Spatial Reference Identifiers (SRIDs) | Microsoft Docs :

However, only spatial instances with the same SRID can be used when performing operations with SQL Server spatial data methods on your data.

and

If two spatial instances do not have the same SRID, the results from a geometry or geography Data Type method used on the instances will return NULL.

[JohnMay3]

Yes everything is WGS84. Here is some more info.

I created a feature class (POINTTEST) by selecting a single point from the original feature class (ALLPOINTS). When I use the feature class that has 1 point in the join it works, even when I use the select query as part of the join. In other words the following works when executed against a point feature class that has only 1 point:

SELECT * FROM BASINS
JOIN (SELECT * FROM POINTTEST WHERE INCREMENT = '02048')as B
on B.Shape.STWithin(Basins.Shape) = 1;

Now if I change the reference to the feature class that has a large collection of point, i.e. swapping out POINTTEST for ALLPOINTS it returns nothing.

SELECT * FROM BASINS 
JOIN (SELECT * FROM ALLPOINTS WHERE INCREMENT = '02048')as B
on B.Shape.STWithin(Basins.Shape) = 1;

[JoshuaBixby]

WGS84 is a datum, not a projection, it is possible to have datasets with the same datum but different projections.  What do the following return?

Select Shape.STSrid FROM POINTTEST WHERE INCREMENT = '02048'‍

and

Select Shape.STSrid FROM ALLPOINTS WHERE INCREMENT = '02048'‍

[JohnMay3]

It just gets more interesting.

Select Shape.STSrid FROM POINTTEST WHERE INCREMENT = '02048': - Result = 4326

Select Shape.STSrid FROM ALLPOINTS WHERE INCREMENT = '02048': - Result is NULL

And then here is what ArcGIS reports for each feature class.

ALLPOINT
Geographic Coordinate System: GCS_WGS_1984
Datum: D_WGS_1984
Prime Meridian: Greenwich
Angular Unit: Degree

POINTTEST
Geographic Coordinate System: GCS_WGS_1984
Datum: D_WGS_1984
Prime Meridian: Greenwich
Angular Unit: Degree

Furthermore the feature class named POINTTEST was created by selecting a single point from ALLPOINTS and exporting it to a new Feature Class which means inheritance should have applied.