I have 2 databases in ArcGIS Enterprise Geodatabase in SQL Server. One database (DB_A) contains a feature class with points and the other database (DB_B) contains a feature class with polygons. I'd like to perform a 'select by location' operation via query so I can determine which points intersect the polygons, and generate a table with the polygon name on the points. E.g., point 1 intersects polygon A, point 2 intersects polygon B. This is similar to the Spatial Join tool in ArcGIS.
I attempted with the following query:
select a.point_num ,b.poly_name from [GIS_Server_1].[DB_A].[Table_1] a join (select poly_name from [GIS_Server_2].[DB_B].[Table_2] where poly_name in ('A', 'B')) b on a.Shape.STIntersects(b.Shape) = 1
I've used this similar query before to successfully intersect polygons