SQL find a polygon that a point falls within

7105
11
Jump to solution
10-23-2018 10:30 AM
JohnMay3
New Contributor III

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.

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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.

View solution in original post

11 Replies
JoshuaBixby
MVP Esteemed Contributor

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
New Contributor III

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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

0 Kudos
JohnMay3
New Contributor III

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.

0 Kudos
JohnMay3
New Contributor III

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
MVP Esteemed Contributor

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.
0 Kudos
JohnMay3
New Contributor III

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;

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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'
0 Kudos
JohnMay3
New Contributor III

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.

0 Kudos