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.