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 tgr39035sf1blk
.
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.
Best Answer
It turns out that I needed to add a WHERE ST_Intersects to my ST_Intersection Query, as follows:
This may not matter for others who have more forgiving interfaces, but I was getting a POST / Proxy Error every time I tried st_intersection without testing if st_intersects, presumably because st_intersection needs to be constrained in order to function efficiently.