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