I have a table that stores polygons and a table that stores points with a value field. Both sets of data are stored using the Geometry type in MS SQL Server.
I have a simple requirement - I need to retrieve the records of points that are contained within the polygons and then sum the result based on a value field in the points table. So, the end result of the query is two columns: - polygon names - sum of the value field in the point table
DECLARE @area GEOMETRY = (SELECT Shape FROM GREENFIELDAREAS WHERE AREANAME = 'Aerodrome')
SELECT NETDWELLINGS, Shape FROM ORA_NETDWELLINGCOMPLETIONS WHERE @area.STIntersects(Shape) = 1
The above query returns point geometries but only for 1 polygon (Aerodrome). What I need is to sum NETDWELLINGS and return that value for all the polygons.