I would like to count the number of points that intersect with a list of polygons.
The polygons are very complex (county/state boundaries).
I'm using this query:
SELECT gid, county_name, (SELECT COUNT(*) FROM points WHERE st_intersects(ST_Transform(points.location::geometry, 4326), ST_Transform(counties.geom::geometry,4326)) ) AS c FROM counties;
In a query where there are 630 counties and 100 points, the query takes 290,000ms.
The difficulty is that I have 100,000 points and this query will then take 4 days.
Needless to say, this isn't viable.
The outcome would be:
County | count ------------------ Wiltshire | 356
Do you have any advice on how to dramatically improve the efficiency of this query?