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, 
     st_intersects(ST_Transform(points.location::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?

  • 1
    The solution from this answer here might help you with your question as well. gis.stackexchange.com/questions/31310/…
    – Matte
    Commented Apr 22, 2017 at 9:46
  • do you have to transform both datasets? could you not transform the points into the polygons' projection?
    – Ian Turton
    Commented Apr 22, 2017 at 10:57
  • @iant the polygons dataset is in 27700 but the points go beyond the bounds of the 27700. Will investigate the other answer too.
    – Bill
    Commented Apr 22, 2017 at 15:45
  • then clip the points to the bounds of the polygons
    – Ian Turton
    Commented Apr 22, 2017 at 15:49

1 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:

     ST_Intersects(points.location, counties.geom))
    ) AS count 
FROM counties;
  • What a difference. The whole query in 9359ms! Thanks so much!
    – Bill
    Commented Apr 22, 2017 at 16:04
  • That's what you get when you use an index!! No worries, @Bill.
    – Alex Leith
    Commented Apr 23, 2017 at 3:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.