Running loop in query in PostGIS

Question

I am new to PostGIS.

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.

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

I receive the following error:

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

Answer 1

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.

• Catlover: 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: 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: 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: ERROR: transform: couldn't project point (1.11978e+006 7.08654e+006 0): latitude or longitude exceeded limits (-14)
• simpleuser001: 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)