1

Still a little new to Postgis. I'm trying to calculate the percentage of a wetland layer that intersects with a parcel layer. The desire is to eventually add "percent_wetland" to the parcel layer, but I'm having trouble working out the basic query. Both layers are polygons and have the same EPSG. My query below returns a value of ~300 whereas the correct value should be about 35(%).

SELECT (sum(st_area(st_intersection(a.geom,b.geom))/st_area(b.geom)))*100 percent_area, b.id

from hydrology.wetland a, boundaries.parcels as b

WHERE b.id=9801

GROUP BY b.id;

How does one calculate the percentage of area that intersects between two polygon layers?

1 Answer 1

2

The only tricky bits are keeping in mind that:

  • It's possible a single parcel could intersect more than one wetland polygon. To deal with this, we sum up the areas of all intersections per parcel.
  • It's possible a parcel could intersect no wetland polygons. To deal with this, we left join the original table to the result of the intersection, so we still have one record per parcel.

So the SQL:

CREATE TABLE parcels_with_wl_area AS
WITH parcel_wetland_area AS (
  SELECT p.id, Sum(ST_Area(ST_Intersection(wl.geom, p.geom))) AS wetland_area
  FROM wetlands wl
  JOIN parcels p
  ON ST_Intersects(wl.geom, p.geom)
  GROUP BY p.id
)
SELECT p.*, 
  CASE 
    WHEN pwa.wetland_area IS NULL 
    THEN 0.0 
    ELSE pwa.wetland_area 
  END AS wetland_area,
  CASE 
    WHEN pwa.wetland_area IS NULL 
    THEN 0
    ELSE round(100 * pwa.wetland_area / ST_Area(p.geom))
  END AS wetland_pct
FROM parcels p
LEFT JOIN parcel_wetland_area pwa
ON p.id = pwa.id

The case statement is needed to convert the left join nulls into zeroes. Now you have wetland areas, and you can get a percentage just by dividing by the ST_Area() of the parcel geometry.

4
  • Works perfectly! Brilliant! Thanks @Paul for your help. I was very impressed that it executed in only 1.1 sec. I was sorta, kinda on the right track... :) One followup question: this query produces the total area (in meters), but what if I want the percentage of the total parcel area covered with wetland? The parcel area is currently in a field called "area_sqm". Can I compute this in the main SELECT statement (e.g. SELECT p.*, CASE..., wetland_area/parcels.area_sqm*100)?
    – Cliff
    Aug 3, 2016 at 13:06
  • I answered my own followup question... adding wetland_area/p.area_sqm*100 AS perc_wetland works. Thanks again, @Paul.
    – Cliff
    Aug 3, 2016 at 13:09
  • You can add % inline, as I've edited the example, or just calculate it in subsequent queries when you read the new table. Aug 3, 2016 at 13:11
  • This works perfectly. Thanks again! I didn't realize you added both pct and area in your updated query. All is good.
    – Cliff
    Aug 3, 2016 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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