current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 1 down vote favorite

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

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

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
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 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 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 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 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 at 10:07
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.