PostGIS Intersection multiple geometry

Question

I have a postgis polygon table (zipcode1) and two point table (pm253, blockpop). I would like to intersect the polygon table against the two point table and count how many point I have for each point table, sum up some other attributes to calculate a ratio. I am able to do it against a single table as you see here:

SELECT zipcode1.gid, count(*) as totpm, sum(pm) as totsum, sum(pm)/count(*) as res 
FROM pm253, zipcode1 
WHERE st_intersects(st_buffer(zipcode1.geom, 100),pm253.geom) 
GROUP BY zipcode1.gid 

Any suggestions? Thank you.

Answer 1

Try union;

SELECT 'zipcode1<->pm253' as tables, zipcode1.gid, count(*) as totpm, sum(pm) as totsum, sum(pm)/count(*) as res 
FROM pm253, zipcode1 
WHERE st_intersects(st_buffer(zipcode1.geom, 100),pm253.geom)  and zipcode1.geom&&pm253.geom -- to accelerate query
GROUP BY zipcode1.gid 

union

SELECT 'zipcode1<->blockpop' as tables, zipcode1.gid, count(*) as totpm, sum(pm) as totsum, sum(pm)/count(*) as res 
FROM blockpop, zipcode1 
WHERE st_intersects(st_buffer(zipcode1.geom, 100),blockpop.geom)  and zipcode1.geom && blockpop.geom -- to accelerate query
GROUP BY zipcode1.gid