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?

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?

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?

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?