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.

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

  1. Calculating the area of each census block
  2. Intersecting census blocks and parcels
  3. Calculating the area of each polygon in the intersection of census blocks and parcels
  4. 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.

Thanks in advance for any help you can provide.

share|improve this question

2 Answers 2

Are your parcels and census blocks in the same projection? If not you'll need to reproject one of them, or use ST_Transform to reproject one on the fly.

For the second part, especially if you have to reproject, I'd use a subquery. Join your blocks to the attribute data then use that dataset in the main intersection query.

If you want to display this data you can use QGIS and the DB Manager plugin to run the query and display the results.

share|improve this answer
    
Thanks for the response, heyoverthere. I've already projected them. Is st intersection the right way to go about what you're suggesting? –  dubhousing Nov 10 '12 at 20:31
    
Yeah, ST_Intersect will get you what you want. –  HeyOverThere Nov 10 '12 at 21:48
    
Sorry -- I think I'm confused about one piece of this -- is it ST_Intersects (boolean) or ST_Intersection (geometry)? Or does that depend on the implementation I'm using? –  dubhousing Nov 11 '12 at 5:43
    
ST_Intersects, sorry about the confusion –  HeyOverThere Nov 11 '12 at 18:11
up vote 3 down vote accepted

It turns out that I needed to add a WHERE ST_Intersects to my ST_Intersection Query, as follows:

SELECT sum(((st_area (st_intersection (p.the_geom,c.the_geom))/st_area(c.the_geom))*ci.pop2000)) AS Parcels_pop
FROM parcel_proj p, census_proj c, tgr39035sf1blk ci
WHERE ST_Intersects(p.the_geom,c.the_geom) and ci.stfid=c.stfid;

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.

share|improve this answer

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.