Using PostGIS how do I find which county in California has the most settlements in the forest? I have point settlements settlements_CA, polygon forests forests_CA and polygon counties counties_CA in PostGIS Database. I tried this:

SELECT COUNT(st.*) as count, ct.name
FROM settlements_CA AS st, forests_CA as fo, counties_CA AS ct
ST_Contains(fo.geom, st.the_geom)
GROUP BY ct.name
ORDER BY ct.name

this query was very slow and the result is wrong because it reports that all counties have the same numbers of settlements.

I also tried JOIN and LEFT JOIN but failed.

  • I would try to do 2 iterations 1) Select locations (points) that fall into the forests; 2) From the result of the first iteration, select localities that fall into the counties and count their maximum number in each county – Cyril Mikhalchenko Jan 11 '19 at 7:36
  • Do you have a lookup between counties and settlements? If they are in separate tables, this is why you are getting the wrong answer, as your spatial join in on forests and settlements, and there is nothing to link counties to settlements. So, you either need a 2nd spatial query as in @RoVo's answer, or you need to do a join by attribute, eg, settlement to counties. Also, as regards this being slow, make sure you have a spatial index on the columns involved in the contains. – John Powell Jan 11 '19 at 9:20
  • Also, it is often helpful to show an EXPLAIN statement, as concepts like big and slow are somewhat meaningless out of context. I have queries that run from anything from 1ms to 2 days -- it just depends what they are doing. – John Powell Jan 11 '19 at 9:23
  • @Cyril The idea of ​​your decomposition task is very inspiring to me. – dorbodwolf Jan 13 '19 at 3:40
  • @JohnPowell You have indicated in principle where the mistake is, I am very benefiting. The answer of @RoVo's 2nd join seems to be correct, but what you mean of join by attribute, eg, settlement to counties? – dorbodwolf Jan 13 '19 at 3:45

You're missing to join the result from ST_Contains(fo.geom, st.the_geom) with the counties. I'd do a subselect as it's better to read.

Something like this should do it:

SELECT COUNT(ct.name) as count, ct.name
FROM counties_CA AS ct

  SELECT st.the_geom
  FROM settlements_CA AS st
  JOIN forests_CA as fo
  ON ST_Contains(fo.geom, st.the_geom)
) as st_in_fo
ON st_intersects(ct.the_geom, st_in_fo.the_geom)

GROUP BY ct.name

As for the performance, you will need to have indexes on all the_geom columns.

|improve this answer|||||
  • What RoVo says, unless you have a lookup between county and settlement, in which case, you can do the 2nd join on that. – John Powell Jan 11 '19 at 9:24

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.