PostGIS join multiple queries in one step

Question

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

Answer 1

Another way would be to use a CTE (Common Table Expression) in your query. It doesn't change the way the query is done, but make's it look tidier in my opinion.

WITH
    -- The child car count CTE
    ChildCareCount AS (
    SELECT 
         count(childcare.gid) as childcares
        ,truckbuffer.road_type as road_type
    FROM truckbuffer, childcare
    WHERE st_intersects(truckbuffer.buffergeom,childcare.geom) 
    GROUP BY road_type
    ),
    -- The school count CTE
    SchoolCount AS (
    SELECT 
         count(schools.gid) as schools
        ,truckbuffer.road_type as road_type
    FROM truckbuffer,schools
    WHERE st_intersects(truckbuffer.buffergeom,schools.geom) 
    GROUP BY road_type
    )
-- The final query
SELECT *
FROM ChildCareCount cc
    --INNER JOIN SchoolCount sc ON cc.road_type = sc.road_type;
      FULL OUTER JOIN SchoolCount sc ON cc.road_type = sc.road_type;

edit

Having thought about it a bit, you should probably replace the join in the query above with a FULL OUTER JOIN. This means that if any of the queries don't hit a particular road_type they will still be reported. However a query similar to the following would probably work better in the long run

SELECT tb.road_type
    ,count(cc.geom) childcares -- you can replace geom with any NOT NULL column as count
    ,count(s.geom) schools     -- should not count nulls when a column is specified
FROM truckbuffer tb
    LEFT OUTER JOIN childcare cc   ON st_intersects(tb.buffergeom,cc.geom) 
    LEFT OUTER JOIN schools s      ON st_intersects(tb.buffergeom,s.geom)
GROUP BY tb.road_type;

Answer 2

figured it out. I just had to use aliases for the queries. this was more of a SQL question

 select * from(childcare count query) as q1 join (school count query) as q2 
    on q1.road_type = q2.road_type;