yah that error is just because the postgis optimizer isn't optimized for left joins. You can ignore. Hmm never tried that function with that many points. Will be interesting to see what happens. I know there are some topological exceptions that arise which we've tried to work around. I blame GEOS for that. Will be nice to get some funding for GEOS to solve some of those cases since those are a big pain for this and anything involving Union. :) Right now the concave hull function is implemented in plpgsql, but I think the new version of GEOS has some delaunay triangulation functions which will make this process hopefully easier and a pure C solution. strk can comment on that. Thanks, Regina http://www.postgis.us
_____ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paul & Caroline Lewis Sent: Tuesday, August 23, 2011 1:31 PM To: PostGIS Users Subject: [postgis-users] Array Size Error Thanks for you previous help and quick responses. I'm only getting to look at your suggestions now, however I noticed in PostGIS 2.0 the ST_ConcaveHull() which is closer to our functionality needs so have started to try and work with this instead of the convexhull option. Maybe another to-do re aggregate operations of both these functions. Anyway I started my first test implementation from your suggestions in the following form: WITH ctsubgroup AS ( SELECT ST_ConcaveHull(ST_Force_2D(ST_Union(local_geom)), 0.9) As geom FROM lidar_099 GROUP BY ST_SnapToGrid(local_geom, 10,10) ) SELECT ST_AsEWKT(ST_ConcaveHull(ST_Union(geom), 0.9)) FROM ctsubgroup; The table in question has 62 million popints and is a small one. The Query is still running after 6 hours but quite quickly it reported the following: **************PGADMIN MESSAGE - START************** NOTICE: geometry_gist_joinsel called with incorrect join type CONTEXT: SQL statement "SELECT ST_Union(ARRAY(SELECT geom FROM ( -- fuse near neighbors together SELECT DISTINCT ON (i) i, ST_Distance(var_atempgeoms[i],var_atempgeoms[j]), ST_Buffer(ST_MakeLine(var_atempgeoms[i], var_atempgeoms[j]) , var_buf*5, 'quad_segs=3') As geom FROM generate_series(1,array_upper(var_atempgeoms, 1)) As i INNER JOIN generate_series(1,array_upper(var_atempgeoms, 1)) As j ON ( NOT ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) AND ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) ) UNION ALL -- catch the ones with no near neighbors SELECT i, 0, ST_Buffer(var_atempgeoms[i] , var_buf*10, 'quad_segs=3') As geom FROM generate_series(1,array_upper(var_atempgeoms, 1)) As i LEFT JOIN generate_series(ceiling(array_upper(var_atempgeoms,1)/2)::integer,array_uppe r(var_atempgeoms, 1)) As j ON ( NOT ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) AND ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) ) WHERE j IS NULL ORDER BY 1, 2 ) As foo ) )" PL/pgSQL function "st_concavehull" line 39 at assignment NOTICE: geometry_gist_joinsel called with incorrect join type CONTEXT: SQL statement "SELECT ST_Union(ARRAY(SELECT geom FROM ( -- fuse near neighbors together SELECT DISTINCT ON (i) i, ST_Distance(var_atempgeoms[i],var_atempgeoms[j]), ST_Buffer(ST_MakeLine(var_atempgeoms[i], var_atempgeoms[j]) , var_buf*5, 'quad_segs=3') As geom FROM generate_series(1,array_upper(var_atempgeoms, 1)) As i INNER JOIN generate_series(1,array_upper(var_atempgeoms, 1)) As j ON ( NOT ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) AND ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) ) UNION ALL -- catch the ones with no near neighbors SELECT i, 0, ST_Buffer(var_atempgeoms[i] , var_buf*10, 'quad_segs=3') As geom FROM generate_series(1,array_upper(var_atempgeoms, 1)) As i LEFT JOIN generate_series(ceiling(array_upper(var_atempgeoms,1)/2)::integer,array_uppe r(var_atempgeoms, 1)) As j ON ( NOT ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) AND ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) ) WHERE j IS NULL ORDER BY 1, 2 ) As foo ) )" PL/pgSQL function "st_concavehull" line 39 at assignment NOTICE: geometry_gist_joinsel called with incorrect join type CONTEXT: SQL statement "SELECT ST_Union(ARRAY(SELECT geom FROM ( -- fuse near neighbors together SELECT DISTINCT ON (i) i, ST_Distance(var_atempgeoms[i],var_atempgeoms[j]), ST_Buffer(ST_MakeLine(var_atempgeoms[i], var_atempgeoms[j]) , var_buf*5, 'quad_segs=3') As geom FROM generate_series(1,array_upper(var_atempgeoms, 1)) As i INNER JOIN generate_series(1,array_upper(var_atempgeoms, 1)) As j ON ( NOT ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) AND ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) ) UNION ALL -- catch the ones with no near neighbors SELECT i, 0, ST_Buffer(var_atempgeoms[i] , var_buf*10, 'quad_segs=3') As geom FROM generate_series(1,array_upper(var_atempgeoms, 1)) As i LEFT JOIN generate_series(ceiling(array_upper(var_atempgeoms,1)/2)::integer,array_uppe r(var_atempgeoms, 1)) As j ON ( NOT ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) AND ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) ) WHERE j IS NULL ORDER BY 1, 2 ) As foo ) )" PL/pgSQL function "st_concavehull" line 39 at assignment **************PGADMIN MESSAGE - END************** Is this a significant issue as the Query still seems to be running. Cheers, Paul
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
