# Calculating area of MultiPolygons within 250m buffer of points

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.

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

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

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

• Thanks so much Arthur. That's very close, but I really need the result to list all house_ids, and then summarise the area of scattered, the area of medium & and the area of dense forest within 250m of each house rather than giving me the sum of the total area of each category of tree density within 250m across all houses in the sample. Does that make sense? I'm down on myself that I can't work this out. I'll keep trying.
– Mike
Commented Aug 21, 2013 at 2:21
• Yes, that makes sense. I think you will need to join multiple subqueries together on the house_id field. Each subquery would calculate the total area in one of the tree density (tree_dens) categories for each house. I'll try this out later and get back to you. Commented Aug 21, 2013 at 12:23
• Edited my original answer. Commented Aug 21, 2013 at 17:29
• Arthur, thank you very much. For anyone else who reads this in the future, I can confirm that Arthur's syntax works beautifully.
– Mike
Commented Sep 14, 2013 at 10:26
• @Mike You should mark the answer as correct then. Commented Sep 27, 2013 at 12:07