Calculating the area of polygons within bigger polygons

Question

I work with PostGreSQL and have 2 tables, both in EPSG 2950:

Table 1 has a couple of big polygons and table 2 has smaller polygons that INTERSECTS with the polygons of table 1.

I am looking for a function to calculate the area of the portions of the polygons of table 2 that are WITHIN the polygons of table 1.

Having no idea how such a formula would look, i tried the following:

ALTER TABLE table_1 ADD COLUMN area NUMERIC (10,2);

UPDATE table_1 SET area = ST_Area((ST_Within(table_2.geom, table_1.geom)));

The resulting error was that there was a missing entry for the FROM clause for table_2.

I tried adding the FROM before and than after my SET constraint but both turned out with errors.

Any idea about what would be missing in my function?

Answer 1

You have several issues with your query.

First: ST_Within only returns true or false, no geometry. ST_Intersection will give you the intersecting geometries(the portions you want to have). Also the syntax of SET is wrong (SET is also no constraint, try to read some database basics, and also in the documentaion are several examples).

Second: You are trying to alter the geometry of your table 1 with new geometries as calculating the area this way will not give you the area of the overlapping portions but only the area of the polygons that do overlap. Beside that is not working the way you tried it is not a good idea. Also you can´t fill a geometry into a numeric field. Geometry is no single value but a lot of tables with coordinates.

A better idea is to create a new table, as you want to have entirely new geometries (if you don´t want to have it as a new permanent table you could also create a view). The spatial function for your questions should be an intersection if i read it correct. For these new table you calculate your area afterwards.

CREATE TABLE table_new AS
  SELECT ST_Intersection(table1.geom, table2.geom) as geom;

Answer 2

If I understand your question correctly, this UPDATE statement will do what you want:

UPDATE table_1                       -- For each row in table_1...
SET area = (                         -- set its area...
  SELECT SUM(ST_Area(table_2.geom))  -- to the sum of the areas of the geometries...
  FROM table_2                       -- of table 2...
  WHERE ST_Within(table_2.geom, table_1.geom)  -- that are within the table_1 geom.
)

Note the subquery (delimeted by the parentheses) that finds the Table 2 rows within each Table 1 polygon.

Answer 3

Try out:

UPDATE table_1 SET area = XXX
FROM
(SELECT ST_Area((ST_Within(table_2.geom, table_1.geom))) AS XXX from table_2 or more where conditions) mysubquery;

I had same problem, but I solved it in this way.