topic Re: SQL find a polygon that a point falls within in Geodatabase Questions

SQL find a polygon that a point falls within

JohnMay3 — Tue, 23 Oct 2018 17:30:39 GMT

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.

Re: SQL find a polygon that a point falls within

JoshuaBixby — Tue, 23 Oct 2018 20:35:21 GMT

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.

Re: SQL find a polygon that a point falls within

JohnMay3 — Tue, 23 Oct 2018 20:57:57 GMT

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.

Re: SQL find a polygon that a point falls within

JoshuaBixby — Tue, 23 Oct 2018 22:49:52 GMT

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'.

Re: SQL find a polygon that a point falls within

JohnMay3 — Wed, 24 Oct 2018 09:01:47 GMT

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.

Re: SQL find a polygon that a point falls within

JohnMay3 — Wed, 24 Oct 2018 10:10:25 GMT

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;

Re: SQL find a polygon that a point falls within

JoshuaBixby — Wed, 24 Oct 2018 12:56:54 GMT

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.

Re: SQL find a polygon that a point falls within

JohnMay3 — Wed, 24 Oct 2018 13:13:51 GMT

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;

Re: SQL find a polygon that a point falls within

JoshuaBixby — Wed, 24 Oct 2018 16:17:45 GMT

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'‍

Re: SQL find a polygon that a point falls within

JohnMay3 — Wed, 24 Oct 2018 18:57:56 GMT

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.

Re: SQL find a polygon that a point falls within

JoshuaBixby — Thu, 25 Oct 2018 13:50:06 GMT

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.

Re: SQL find a polygon that a point falls within

JohnMay3 — Thu, 25 Oct 2018 13:54:45 GMT

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