1

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

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.

| improve this answer | |
  • 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 Aug 21 '13 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. – Arthur Aug 21 '13 at 12:23
  • Edited my original answer. – Arthur Aug 21 '13 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 Sep 14 '13 at 10:26
  • @Mike You should mark the answer as correct then. – Jakub Kania Sep 27 '13 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.