2

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;
  • There is a mistake in my second script. Here the new one corrected : '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;' – sweber Nov 3 '16 at 13:42
  • 1
    You do know that your postgres version and postgis versions are roughly 7 years old? This may result in some of the suggestions here not working for your situation. The versions are not supported anymore and an upgrade would be highly recommended. – tilt Nov 3 '16 at 13:55
  • I test with a 2.1.8 postgis version. I reproduce the same problems with my second ST_Intersects spatial join. – sweber Nov 3 '16 at 14:53
0

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;
  • Thanks a lot it works. However, I have an error when postgis must perform too much spatial join (3 and more) : NOTICE: gserialized_gist_joinsel: jointype 1 not supported ! Is that related to a postgres memory issue ? – sweber Nov 3 '16 at 16:38
  • 2
    You can ignore that warning. It just means the planner is not smart enough to best optimize. It's been there since early days of PostGIS (0.whatever). I can't remember why we decided not to remove that message, cause it just confuses people. – LR1234567 Nov 3 '16 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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