I'm trying to learn postgis sql by performing some familiar operations from ArcGIS. Here, I want to perform an area-weighted calculation of certain populations on the intersection of a set of parcels with census blocks.
I have parcels
parcels, parcel attributes
parcelinfo, census blocks
census, and census attributes
This calculation would entail
- Calculating the area of each census block
- Intersecting census blocks and parcels
- Calculating the area of each polygon in the intersection of census blocks and parcels
- Calculating the area weighted value of each attribute (attribute value * (area of polygon / area of census block)
In order to test ST_Intersection, I tried the following, but phppgadmin craps out (no error message, a proxy error):
SELECT ST_Intersection(p.the_geom, c.the_geom) as inter from parcels p, census c;
I could sum the attributes from each block that intersects with a parcel, but that is, obviously, a seriously flawed approach. E.g.,
SELECT sum(DISTINCT t.pop2000), sum(DISTINCT t.males) FROM parcels p, census c, tgr39035sf1blk t WHERE ST_Intersects(p.the_geom, c.the_geom) AND c.stfid = t.stfid;
I'm sorry if I'm missing any essential, additional information. I'm a novice at this.