[postgis-users] Array Size Error

Paul & Caroline Lewis paulcaz80 at hotmail.com
Tue Aug 23 10:31:04 PDT 2011





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_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) 
         )
         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_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) 
         )
         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_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) 
         )
         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   		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110823/f12d8ef2/attachment.html>


More information about the postgis-users mailing list