Multiple spatial joins in PostGIS

Question

My version : PostgreSQL 8.4.1/PostGIS 1.4.0

I want to perform multiple spatial joins between a layer of polygons (geo_act.coop_world_country - 250 entities) and two point layers to count the numbers of points in each polygon.

The first join (ST_Intersects function) works with the following code:

SELECT t1.cntry_name
    ,COUNT(t2.shape) as "PoleCompet"
FROM geo_act.coop_world_country t1
LEFT JOIN geo_act.coop_partenariat_union_polecompet t2
    ON ST_Intersects(t1.shape, t2.shape)
GROUP BY t1.cntry_name
ORDER BY t1.cntry_name;

How do I add in my request a second ST_Intersects spatial join? This code does not work, the results are cumulative :

SELECT t1.cntry_name
    ,COUNT(t2.shape) as "PoleCompet"
    ,COUNT(t3.shape) as "Entreprise"
FROM geo_act.coop_world_country t1 
LEFT JOIN geo_act.coop_partenariat_union_polecompet t2 
    ON ST_Intersects(t1.shape, t2.shape) 
LEFT JOIN geo_act.coop_partenariat_union_entreprises t3 
    ON ST_Intersects(t1.shape, t3.shape) 
GROUP BY t1.cntry_name 
ORDER BY t1.cntry_name;

Answer 1

Try using COUNT(DISTINCT the_key) instead. I'm going to assume primary key of your tables is gid. You could use shape, but that is not always guaranteed to work since DISTINCT shape would give the bounding box which could be duplicated even for non-distinct geometries.

So

SELECT t1.cntry_name, COUNT(DISTINCT t2.gid) as "PoleCompet", 
     COUNT(DISTINCT t3.gid) as "Entreprise" 
FROM geo_act.coop_world_country t1 LEFT JOIN     
   geo_act.coop_partenariat_union_polecompet t2 ON 
       ST_Intersects(t1.shape, t2.shape) LEFT JOIN       
        geo_act.coop_partenariat_union_entreprises t3 
ON ST_Intersects(t1.shape, t3.shape) 
 GROUP BY t1.cntry_name ORDER BY t1.cntry_name;