[postgis-users] ST_Intersects(geom, geom) Memory issue

[Andreas Forø Tollefsen]

Hi Sandro,

Rewrote the script to:
INSERT INTO geoepr_cell SELECT a.gid, a.gridyear,  g.cowgroup, g.type,
SUM(ST_Area(ST_Intersection(p.cell, g.the_geom)))
FROM priogridall a, priogrid p, geoepreth2valid g
WHERE a.gid = p.gid AND a.gridyear = 1946 AND a.gwcode = g.cowcode AND
ST_Intersects(p.cell, g.the_geom) AND g.startyear <= 1946 AND
g.endyear >= 1946
AND ST_Dimension(p.cell) != 0 AND ST_Dimension(g.the_geom) != 0
GROUP BY a.gid, a.gridyear, g.cowgroup, g.type ORDER BY gid, gridyear ;

Same problem.

Andreas

2012/2/9 Andreas Forø Tollefsen <andreasft at gmail.com>:
> Yes,
> I will test this as soon as I am done running a couple of my queries.
>
> Andreas
>
> 2012/2/9 Sandro Santilli <strk at keybit.net>:
>> Thanks Andreas.
>> Could I ask you to also try further filtering your inputs so to avoid
>> point geometries as a whole ? It'd help further debugging the issue.
>> It would take adding something like this:
>>
>>  AND ST_Dimension(p.cell) != 0 AND ST_Dimension(p.geom) != 0
>>
>> --strk;
>>
>>
>> On Wed, Feb 08, 2012 at 04:55:08PM +0100, Andreas Forř Tollefsen wrote:
>>> I tested the query again and identified which commit of revisions
>>> introduced the problem.
>>> Until revision 8876 the query works fine.
>>> After revision 8877 the query leaks memory and eats up all of the
>>> servers memory in a couple of minutes.
>>>
>>> Tested:
>>> 8096 OK
>>> 8796 OK
>>> 8866 OK
>>> 8876 OK
>>> 8877 NOT OK
>>> 8886 NOT OK
>>> 8896 NOT OK
>>> 8996 NOT OK
>>> 9096 NOT OK
>>>
>>> Again the query was simply:
>>>
>>> SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO geoepr_cell
>>> FROM priogridall a, priogrid p, geoepreth2 g WHERE a.gid = p.gid AND
>>> a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell,
>>> g.geom)
>>> AND g.startyear <= 1946 AND g.endyear >= 1946 GROUP BY a.gid,
>>> g.startyear, g.endyear, g.cowgroup, a.gridyear ORDER BY gid ;
>>>
>>> Would be great if you could look into this.
>>>
>>> Best,
>>>
>>> Andreas
>>>
>>>
>>> 2012/2/4 Andreas Forř Tollefsen <andreasft at gmail.com>:
>>> > I created a new clean db and installed latest rev. Then loaded my tables and
>>> > ran the query with the same error and postgres server crash.
>>> > Any suggestions on how i can solve this? As for now i am using rev 8292 and
>>> > then the query and my raster summaries work.
>>
>> --
>>
>>  ,------o-.
>>  |   __/  |    Thank you for PostGIS-2.0 Topology !
>>  |  / 2.0 |    http://www.pledgebank.com/postgistopology
>>  `-o------'
>>