How to only sum the bits of buildings that are actually in the given zone?

Question

I have this query:

select sum(way_area) from planet_osm_polygon 
      where building<>'no' and building<>'' 
      and way && st_transform(
           st_setsrid(st_makebox2d(
                st_point(2.2852565067385426, 50.990981849954764),
                st_point(2.2861549793351283, 50.990083377358175))
           ,4326)
      ,3857);

I would like to change it so that it only considers the area of the building that is in the zone, instead of the whole building. I suppose I should use st_intersects but how do I use it with my box to only have the little bits counted?

I tried this query but it gives me the same result as the first one (which is not what I need):

select sum(way_area) from planet_osm_polygon where building<>'no' and building<>'' and 
       st_intersects(
             st_transform(st_setsrid(st_makebox2d(st_point(2.2852565067385426, 50.990981849954764),  st_point(2.2861549793351283, 50.990083377358175)),4326),3857)
            ,way);

Additional info that may be useful: the way_area column contains the total surface on ground of a polygon, and the way column contains the geometry.

Answer 1

Since the && or st_interescts are located in the where clause, they will be used decide if a row from the table should or not be returned.

Using the returned rows, both queries instructs to compute the sum using the entire geometry of each row.

Instead, you will want to compute the area of the instersection between returned rows and the BBox and then to sum it.

Let's also move the BBox code to a CTE, so we have to write it once only:

WITH bbox(geom) as (
  SELECT st_transform(
           st_setsrid(st_makebox2d(
                st_point(2.2852565067385426, 50.990981849954764),
                st_point(2.2861549793351283, 50.990083377358175))
           ,4326)
      ,3857)
)
SELECT sum(st_area(st_intersection(way,bbox.geom))) 
FROM planet_osm_polygon
  JOIN bbox 
   ON ST_Intersects(way, bbox.geom)
WHERE building<>'no' and building<>'';