SQL: Select points within any polygon with data from database directly

Question

A database contains the table Locations with the numeric fields Index, X, Y and the table Districts with the field borders of polygon geometry type. The polygon geometry type is verified by the test (Microsoft Learn - STGeometryType)

DECLARE @g geometry;  
SELECT @g = borders from Districts
SELECT @g.STGeometryType();

which returns Polygon.

Index of Locations should be selected if (X,Y) forms a point in any of the polygons of borders in Districts. My attempt follows the suggestions by Cadline Community and Microsoft Learn - STContains (it mainly differs from them as all data is from the database directly, i. e. there is no STGeomFromText-coding):

DECLARE @borderpolygons geometry
SELECT @borderpolygons = borders from Districts
SELECT Index FROM (SELECT * FROM Locations WHERE X IS NOT NULL AND Y IS NOT NULL) tab
WHERE @borderpolygons.STContains(geometry::Point(X,Y,2056)) > 0

No Index is selected by this approach although it is known that there are some (X,Y) which form points in at least one of the polygons of borders. I assume my problem is the understanding of the criteria > 0 at the end. I first tried with

WHERE @borderpolygons.STContains(geometry::Point(X,Y,2056))

as final line as all of borders should be considered (i. e. no restriction on @borderpolygons). This coding seems to be wrong as an expression of boolean type is expected. I then added > 0 to include all borders. However, this also does not seem to be the correct coding to include all polygons of borders. How should the coding be in order to select all Index for which (X,Y) form points within any polygons of borders?

Technical points:

• Product: Microsoft SQL Server Standard (64 bit)
• Operating System: Windows Server 2022 Standard (10.0)

Answer 1

SELECT @borderpolygons = borders from Districts will only get one row, and it's not necessarily the row you want

What you want it something like this (untested cause you didn't include any test data):

SELECT * FROM Districts 
cross join Locations 
where borders.STContains(case when x is null or y is null  
 then null else geometry::Point(X,Y,2056) end) = 1