Speeding up intersect query in PostGIS?

Question

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?

Answer 1

The way to improve it is to ensure you're using spatial indices.

Firstly, are your datasets in the same coordinate reference system? If not, reload the data after transforming or create a table and place the data in there in the same CRS.

Second, make sure you have a spatial index on the geometry columns you're using.

Next, remove the ST_Transforms from your query. If your results are required to be in a different CRS then do the transform on the results. But I think you're just counting, so don't worry about it!

If your data do have the same CRS, change the query to:

SELECT 
    gid,
    county_name, 
    (SELECT COUNT(*) FROM points WHERE
     ST_Intersects(points.location, counties.geom))
    ) AS count 
FROM counties;