Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a polygon layer of territory and point layer with values from measurements. I must to add sum values from points layer to polygon layer . How to do this in PostGIS?

Supplemented:

Point layer is:

id|name   |value
1 |first  |547051
2 |second |539949
3 |third  |539864
4 |fourth |539724
5 |fifth  |500208

!pattern layers1

and result must be

id|name|sum
55|I5  |1619037
61|H7  |1047259
share|improve this question
1  
so you have something like point_id|location|value 1|<A>|42 2|<A>|23 3|<B>|99? If location <A> lies inside the polygon <P1> you want a sum field of polygon <P1> to read 65 (42+23)? –  til_b Apr 2 '13 at 10:48
add comment

1 Answer 1

Edit: field names corresponding to the questions field name

SELECT polygons.id, polygons.name, coalesce(sum(points.value),0) AS sum
FROM polygons  
LEFT JOIN points 
ON ST_Intersects(polygons.geom, points.geom) 
GROUP BY polygons.id;

Then you will get also the polygons without points represented with 0 as sum_value

HTH

Nicklas

share|improve this answer
    
+1 for the coalesce function. –  nickves Apr 2 '13 at 14:54
    
A cast will be needed since coalesce outputs text I realize. A link to the postgresql coalesce function .postgresql.org/docs/9.2/static/functions-conditional.html –  Nicklas Avén Apr 2 '13 at 16:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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