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 Jan 11 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 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 at 9:23
  • @Cyril The idea of ​​your decomposition task is very inspiring to me. – dorbodwolf Jan 13 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 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.

  • 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 at 9:24

So, after a short talk, Winnetou said the following:

1) Run a script that will create for us polygons of interest between the counties and the forests that fall into them, for example:

create table interests_CA as
SELECT a.id,a.name,b.name,
st_intersection(a.geom, b.geom) as geom from 
counties_CA a, forests_CA b
WHERE ST_Intersects (a.geom, b.geom)

The result is a table with forest data that is included in the districts of the state of California.

2) Run the second script and determine the number of city which fall into the areas we are interested in:

create table settlements_CA_cnt as
SELECT st.id, st.geom, c.name, count (c.name) as cnt 
FROM settlements_CA AS st, interests_CA as c 
WHERE st_intersects (st.geom, c.geom) 
GROUP BY c.name, st.id, st.geom 
ORDER BY c.name, st.id;

Result number city that fall into the area of interest to us.

3) Run the script:

SELECT st.name, count(*) as city_count FROM "settlements_CA_cnt" AS st GROUP BY st.name ORDER BY city_count desc;

with respect, :-) ...

  • Is this an answer or a new question? – BERA Jan 14 at 11:41
  • A creative and humourous answer. By the way, you may add <code> block in step 3). – dorbodwolf Jan 15 at 0:56
  • Good luck, ululululuuuu ... – Cyril Jan 16 at 7:05

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.