[postgis-users] Array Size Error

Paragon Corporation lr at pcorp.us
Tue Aug 23 14:46:05 PDT 2011


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-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at 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
 
 


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110823/939c434e/attachment.html>


More information about the postgis-users mailing list