[postgis-users] Optimise this query, point in polygon on quite a large scale

[Mark Cave-Ayland]

On 26/06/11 22:02, Louis Holford wrote:

> So...I have a large list of points (~30,000). I need to count the number
> of points that overlap a polygon (or multiple polyongs e.g. 100's). Each
> polygon represents part of a grid that overlaps the envelope of the list
> of points, so I need to know the count of points per grid cell.
>
> Currently I can generate each grid cell "in memory" and feed them to a
> query using ST_GeomFromText. Now, I know this takes a long time to
> complete and is not optimal.
>
> I would like any opinions as to what the best solution to implementing
> the query would be. I am thinking along the lines of generating the full
> grid as a table with either each cell stored separately or the whole
> grid as a single multipolygon and then performing the ST_Intersects part
> of the query.
>
> And also I know I should generate an index to help the query
> planning/search.

Hi Louis,

Yes - this is a good starting point. In-memory grids cannot use the 
index and so you'll need to materialise the resulting grid somehow 
whether that's just in a temporary table or somewhere more permanent, 
and then build an index on it in order to get worthwhile performance.

It's likely you'll be looking at a combination of a cross join based 
upon the && operator, ST_Intersects() and a GROUP BY over a SUM() 
aggregate in order to generate the totals for each grid square.


HTH,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs