4

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.

7
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 ?)

  • 1
    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. – Ed Saunders Sep 9 '14 at 8:32
  • 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. – PolyGeo Sep 9 '14 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.