I am intersecting a road buffer zone with schools and childcares and trying to get there count summed up by road type. I can accomplish what I want in a few steps
childcare count query:
select count(childcare.gid) as childcares,truckbuffer.road_type as road_type into ccount from truckbuffer,childcare
where st_intersects(truckbuffer.buffergeom,childcare.geom) group by road_type;
school count query:
select count(schools.gid) as schools,truckbuffer.road_type as road_type into scount from truckbuffer,schools
where st_intersects(truckbuffer.buffergeom,schools.geom) group by road_type
I am able to join them pretty simply like this
select * from ccount join scount on ccount.road_type = scount.road_type;
this process is fine and I get what i want accomplished but i will be doing more intersects on a few other layers and I want a way to write one large query that intersects, counts, and joins