3

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?

  • 1
    The solution from this answer here might help you with your question as well. gis.stackexchange.com/questions/31310/… – Matte Apr 22 '17 at 9:46
  • do you have to transform both datasets? could you not transform the points into the polygons' projection? – Ian Turton Apr 22 '17 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 Apr 22 '17 at 15:45
  • then clip the points to the bounds of the polygons – Ian Turton Apr 22 '17 at 15:49
4

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;
  • What a difference. The whole query in 9359ms! Thanks so much! – Bill Apr 22 '17 at 16:04
  • That's what you get when you use an index!! No worries, @Bill. – Alex Leith Apr 23 '17 at 3:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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