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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
