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

2 Answers 2


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.

    -- The child car count CTE
    ChildCareCount AS (
         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 (
         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
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;


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;
  • il check this out tomorrow at work, it does seem like it would be much tidier especially because I will be adding at least 6 more queries and joins
    – ziggy
    Sep 21, 2016 at 21:45
  • @ziggy Just thinking about this and I think you will need to have full outer joins on this query. I will check and edit when I get by a computer.
    – MickyT
    Sep 22, 2016 at 0:11
  • yes, exactly because I would still want a particular road type info even if it not hit by these 2 receptors.
    – ziggy
    Sep 23, 2016 at 15:46

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.