[postgis-users] Memory problem with ST_Within

[Birgit Laggner]

Hallo,

I have 2 large datasets (the first one consists of polygons and has 
15099748 rows and the second is a point geometry with 76421902 rows) and 
would like to sort out which points are located within which polygons. 
The aim is to calculate some statistics (count, min, max, avg) for the 
points in every polygon. When I tested the query with a small subset, 
everything worked fine and the result looked the way I expected. But 
applied on the whole datasets, the query runs for approximately 10 mins 
and then stops because the server is out of memory.

Some days earlier, I already tried to run the query and had not yet 
created gists for both datasets. At this occasion, the query ran for 
more then 4 days without any result until I stopped the query manually.

Here is my query:

SELECT
polygon_id,
count(hoehe) as hoehe_count,
min(hoehe) as hoehe_min,
max(hoehe) as hoehe_max,
avg(hoehe) as hoehe_avg,
count(neigung) as neig_count,
min(neigung) as neig_min,
max(neigung) as neig_max,
avg(neigung) as neig_avg,
count(exposition) as expos_count,
min(exposition) as expos_min,
max(exposition) as expos_max,
avg(exposition) as expos_avg,
p.the_geom
FROM bfn.ni_hoehendaten h, bfn.ni_polygone2 p
WHERE ST_Within(h.the_geom, p.the_geom)
GROUP BY p.polygon_id, p.the_geom
ORDER BY p.polygon_id ASC;

Does anybody know a method to reduce the memory usage? Or are there 
other suggestions how this problem could be solved?

Thanks a lot,

Birgit.