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 WHERE 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
LEFT JOIN but failed.