Intersect points with polygons using SQL Server query from ArcGIS Enterprise Geodatabase

Question

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.

Point_num	Poly_name
Point 1	A
Point 2	B

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

Answer 1

The below should work on SQL Server:

SELECT  point_table.NAME, polygon_table.NAME
FROM [POLYGON TABLE NAME] polygon_table
INNER JOIN [POINT TABLE NAME] point_table
ON
polygon_table.Shape.STIntersects(point_table.Shape) = 1

or with the use of the spatial index for larger datasets:

SELECT  point_table.NAME, polygon_table.NAME
FROM [POLYGON TABLE NAME] polygon_table WITH (INDEX([SPATIAL INDEX NAME]))
INNER JOIN [POINT TABLE NAME] point_table
ON
polygon_table.Shape.STIntersects(point_table.Shape) = 1

Please note: the Shape is the name of the geometry field within the tables.