Counting geometries which intersect another geometry using PostGIS

Question

I'm trying to calculate the total area for each layer I have within a specific area and count the geometries which intersects with that specific area.

I can calculate the area and the results seem plausible, but how do I count the objects?

SELECT st_area(ST_Transform(ST_Setsrid((st_intersection(
        st_union(st_makevalid(object.postgis_area_geometry)),
        specific_area_geometry
    )), 3857), 4326)::geography),
       layer.name

FROM    object
        LEFT JOIN layer ON layer.id = object.layer_id
WHERE
        ST_ISVALID(object.postgis_area_geometry)
GROUP BY 
        layer.id;

for instance:

Here I have 4 Layers. Each layer has just one Geometry, just to keep it simple.

Object 1: Black 100 m2 = Layer 1
Object 2: Blue 70 m2 =   Layer 2
Object 3: Green 120 m2 = Layer 3
Object 4: Purple 70 m2 = Layer 4

The red square is the specific area within I want to know the total area of each layer.

So I want the following results:

Layer 1 = 35 m2, count = 1
Layer 2 = 10 m2, count = 1
Layer 3 = 35 m2, count = 1
Layer 4 = 0  m2, count = 0

Answer 1

What you want is a combination of ST_Intersects and ST_Intersection. The count you're doing doesn't make a lot of sense to me, since you'd only ever get either 1 or 0. For that reason, you could just use ST_Intersects(a.geom,b.geom) for a boolean return of either True or False. And as per geozelot's comment, you can cast bool to int to get the output as you specify. I've also included how you'd get the full count of intersecting features.

I've created two layers; gisse (5 pink features) and gisse2 (1 blue feature), both using SRID: 4326:

select id,st_area(st_transform(geom,3857)) from gisse order by id

Gives:

id	st_area
1	571990204493.9556
2	782180369787.4814
3	717504875654.8381
4	645453473213.9517
5	683918275314.2

To generate polygons showing the parts of table 1 that intersect with table 2:

create view theintersects as 
select a.id,
st_area(st_transform(st_intersection(a.geom,b.geom),3857)),
st_intersection(a.geom,b.geom) from gisse a 
inner join gisse2 b on st_intersects(a.geom,b.geom)
order by a.id

Computed features in yellow:

If I instead want to get the statistics without the geometry field, including non-intersecting rows:

select a.id,
st_area(st_transform(st_intersection(a.geom,b.geom),3857)),
st_intersects(a.geom,b.geom)::int as intersect_count
from gisse a left join gisse2 b on st_intersects(a.geom,b.geom)
order by a.id

id	st_area	intersect_count
1	0	0
2	56153016337.158646	1
3	261289632649.45654	1
4	49506223558.761505	1
5	197914797339.4397	1

If you just want the count of features from gisse that intersect with gisse2, execute:

select count(a.id) as intersecting_features
from gisse a inner join gisse2 b on st_intersects(a.geom,b.geom)

Result:

intersecting_features
4