Calculating area of MultiPolygons within 250m buffer of points

Question

I am using using PostgreSQL 9.2 and PostGIS 2.0. I have two tables: 1) a geocoded table of home addresses (address_list) and 2) a table containing data on tree density in the same geographical area. I have included examples of the relevant columns in each table below.

address_list:

house_id (serial) |  geom(Point, 4283)
------------------+-----------------------
        1         | 
        2         |

tree_density:

  gid (serial)  |  tree_dens |  geom(MultiPolygon, 4283)
    ------------+------------+----------------------------
          1     |  scattered |
          2     |  medium    |
          3     |  dense     |

What I would like to do is compute the area of forest (m2) by tree_density categories within 250m of each home address for my whole address table (approx 6,000 entries). (E.g for house_id = 1: area_scattered_within250m = 50m2, area_med_within250m = 0m2, area_dense_within250m = 20m2)

Can this be done easily? Even if in 3 or more seperate queries? Does anyone have a moment to assist me with the syntax? I guess I need to combine ST_Area, ST_Buffer and perhaps ST_Contains or St_Within, but I'm new to PostGIS and although I've been ok with other (simpler) spatial joins, I'm lost here and haven't found any similar examples. For the area of my study I transform to SRID 3112 (meters) for measurements.

Many thanks Mike

Answer 1

EDIT: This gives you bulk area for each of the tree density categories:

SELECT SUM(ST_Area(ST_Intersection(sq.house_aoi, tree_density.geom))), tree_dens
  FROM tree_density,

-- Assuming the linear unit in your projection is meters
(SELECT ST_Buffer(address_list.geom, 250) AS house_aoi
  FROM address_list) sq

 GROUP BY tree_density.tree_dens;

This, instead, should give you the area of each unique house-tree density pair after a spatial intersection:

SELECT aoi.house_id, t.tree_dens, SUM(ST_Area(ST_Intersection(aoi.house_aoi, t.geom))) AS area
  FROM
    (SELECT a.house_id, ST_Buffer(a.geom, 250) AS house_aoi
       FROM address_list a) aoi,
    tree_density t
 WHERE ST_Intersects(house_aoi, t.geom)
 GROUP BY aoi.house_id, t.tree_dens

If I'm right, it wasn't as complicated as I initially thought. My test set of geometries is totally contrived, so this may not make sense for your application.