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;
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