1

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 ...

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
5
  • 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, 2014 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, 2014 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, 2014 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, 2014 at 20:54
  • 1
    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)
    – simpleuser001
    Mar 3, 2014 at 10:07

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.