How to perform SQL query using .STContains in MS SQL Server

Question

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.

Answer 1

SELECT g.id , SUM(o.netdwellings) as sum from g , o WHERE g.geom.STIntersects(o.geom) = 1 group by g.id 

That would return sum of all o.geoms that intersect with g.geom. (did i understood question wrong ?)

• Ed Saunders: Thanks heaps, that's exactly what I need. You understood the question perfectly :) I can't see a tick mark next to your response - how do I mark it as answered? Thanks again for your quick response.
• PolyGeo: Hi @EdSaunders! Welcome to GIS SE! You'll need just a little more reputation before you'll see the green Accept button so for now just upvote useful answers. In the meantime I'll convert your "answer" here to a comment.