# Problem with runing a loop in a query in PostGIS

I am new to PostGIS and I have a question which can't solve. I have a table of buildings each having a value for number of people living in it. I would like to calculate the total value (sum of number of people) in all buildings which fall into a defined circular buffer.

I have written this code for it which uses a loop, however it gives an error which is followed. It would be highly appreciated if you can help me regarding this problem. I run the following query in PgAdminIII.

query:

``````BEGIN
FOR building_pop IN SELECT total_pop FROM Buildings WHERE ST_DWithin(ST_Transform(geom, 26918), (SELECT ST_Transform(ST_GeomFromText('POINT(1116858.6062789 7086290.6680572)', 900913), 26918)), 1000)
LOOP
totalpop = totalpop+building_pop;
NEXT building_pop;
END LOOP;
UPDATE public.h_grid200m SET grid_pop = totalpop WHERE gid =1
END
``````

ERROR: syntax error at or near "FOR" LINE 2: FOR building_pop IN SELECT total_pop FROM h_buildings WHERE ...

-

I think you're making it too complicated. Try this instead:

``````SELECT sum(building_pop) FROM Buildings WHERE ST_Distance(ST_SetSRID( ST_MakePoint(1116858.6062789 7086290.6680572), 900913), geom) < 10000
``````

That would give you the sum of building_pop column of all the buildings that are less than 10,000 units away from the defined Point.

-
Thank you. It works but when I put it in the UPDATE command it doesn't work. Can you please show me what could be the problem with my update command? Attached below: – Catlover Mar 2 '14 at 20:51
UPDATE public.grid200m SET total_pop = (SELECT sum(total_pop) FROM Buildings WHERE ST_Distance(ST_Transform(center_geom,900913), ST_Transform(geom, 900913)) < 1000); – Catlover Mar 2 '14 at 20:51
in order to clarify, I have another table called grid200, which each row in it has a point (center_geom) and a column named total_pop which I want to update the final result in it. All tables are in SRID:4326 , but I need to transform them both to 900913 in order to do the St_Distance in meters. No the error which the above-mentioned query returns is below: – Catlover Mar 2 '14 at 20:54
ERROR: transform: couldn't project point (1.11978e+006 7.08654e+006 0): latitude or longitude exceeded limits (-14) – Catlover Mar 2 '14 at 20:54
Error seems to be that your data is not in 4326. Point (1119780 7086540) is in 900913 it would be faster use WHERE ST_DWithin(..) than ST_Distance(...) . Another hint is that if you are planning to use your PostGIS to as long term database , it would be better to put all data to different schema from public (It makes easier to updating PostGIS) – simplexio Mar 3 '14 at 10:07