'Re: [postgis-users] Array Size Error'

[prev in list] [next in list] [prev in thread] [next in thread] 

List: postgis-users
Subject: Re: [postgis-users] Array Size Error
From: "Paragon Corporation" <lr () pcorp ! us>
Date: 2011-08-23 21:46:05
Message-ID: 224D096BB8B945959221115441EAF5F5 () D
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

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

[Attachment #5 (text/html)]

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<STYLE>.hmmessage P {
	PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; \
PADDING-TOP: 0px }
BODY.hmmessage {
	FONT-FAMILY: Tahoma; FONT-SIZE: 10pt
}
</STYLE>

<META name=GENERATOR content="MSHTML 9.00.8112.16434"></HEAD>
<BODY class=hmmessage>
<DIV dir=ltr align=left>yah that error is just because the postgis optimizer 
isn't&nbsp;optimized for left joins.&nbsp; You can ignore.</DIV>
<DIV dir=ltr align=left>&nbsp;</DIV>
<DIV dir=ltr align=left>Hmm never tried that function with that many points.&nbsp; Will be 
interesting to see what happens.&nbsp; I know there are some topological 
exceptions that arise which we've tried to work around.&nbsp; I blame GEOS for 
that.&nbsp; 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.&nbsp;:)</DIV>
<DIV dir=ltr align=left>&nbsp;</DIV>
<DIV dir=ltr align=left>Right now the concave hull function is implemented in plpgsql, but I 
think the new version of GEOS has some delaunay triangulation&nbsp;functions 
which will make this process hopefully easier and a pure C 
solution.</DIV>
<DIV dir=ltr align=left>&nbsp;</DIV>
<DIV dir=ltr align=left>strk can comment on that.</DIV>
<DIV dir=ltr align=left>&nbsp;</DIV>
<DIV dir=ltr align=left>Thanks,</DIV>
<DIV dir=ltr align=left>Regina</DIV>
<DIV dir=ltr align=left><A 
href="http://www.postgis.us">http://www.postgis.us</A></DIV>
<DIV dir=ltr align=left>&nbsp;</DIV>
<DIV dir=ltr align=left>&nbsp;</DIV>
<DIV dir=ltr align=left>&nbsp;</DIV> 
<BLOCKQUOTE 
style="BORDER-LEFT: #0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; \
MARGIN-RIGHT: 0px" dir=ltr>
 <DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
 <HR tabIndex=-1>
 From: postgis-users-bounces@postgis.refractions.net 
 [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of 
 Paul &amp; Caroline Lewis Sent: Tuesday, August 23, 2011 1:31 
 PM To: PostGIS Users Subject: [postgis-users] Array Size 
 Error </DIV>
 <DIV></DIV>
 <DIV dir=ltr>Thanks for you previous help and quick responses. I'm only 
 getting to look at your suggestions now, however I noticed in&nbsp;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&nbsp;both 
 these&nbsp;functions. &nbsp; Anyway I started my first test 
 implementation from your suggestions in the following form: &nbsp; WITH 
 ctsubgroup AS &nbsp;&nbsp;&nbsp; ( SELECT 
 ST_ConcaveHull(ST_Force_2D(ST_Union(local_geom)), 0.9) As 
 geom &nbsp;&nbsp;&nbsp; FROM lidar_099 &nbsp;&nbsp;&nbsp; GROUP BY 
 ST_SnapToGrid(local_geom, 10,10) ) SELECT 
 ST_AsEWKT(ST_ConcaveHull(ST_Union(geom), 0.9)) FROM 
 ctsubgroup; &nbsp; 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: &nbsp; **************PGADMIN MESSAGE - 
 START************** NOTICE:&nbsp; geometry_gist_joinsel called with 
 incorrect join type CONTEXT:&nbsp; SQL statement "SELECT 
 ST_Union(ARRAY(SELECT geom &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-- fuse near neighbors 
 together &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT DISTINCT ON (i) 
 i,&nbsp; 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 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 generate_series(1,array_upper(var_atempgeoms, 1)) As 
 i &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INNER JOIN 
 generate_series(1,array_upper(var_atempgeoms, 1)) As j 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT 
 ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND \
 ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], 
 var_buf*10) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION \
 ALL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-- catch the ones with no near 
 neighbors &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT i, 0, 
 ST_Buffer(var_atempgeoms[i] , var_buf*10, 'quad_segs=3') As 
 geom &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 generate_series(1,array_upper(var_atempgeoms, 1)) As 
 i &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LEFT JOIN 
 generate_series(ceiling(array_upper(var_atempgeoms,1)/2)::integer,array_upper(var_atempgeoms, \
 1)) As j &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT 
 ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND \
 ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE \
 j IS NULL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY 1, 
 2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) As foo&nbsp;) )" PL/pgSQL 
 function "st_concavehull" line 39 at assignment NOTICE:&nbsp; 
 geometry_gist_joinsel called with incorrect join type CONTEXT:&nbsp; SQL 
 statement "SELECT ST_Union(ARRAY(SELECT 
 geom &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-- fuse near neighbors 
 together &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT DISTINCT ON (i) 
 i,&nbsp; 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 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 generate_series(1,array_upper(var_atempgeoms, 1)) As 
 i &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INNER JOIN 
 generate_series(1,array_upper(var_atempgeoms, 1)) As j 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT 
 ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND \
 ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], 
 var_buf*10) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION \
 ALL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-- catch the ones with no near 
 neighbors &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT i, 0, 
 ST_Buffer(var_atempgeoms[i] , var_buf*10, 'quad_segs=3') As 
 geom &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 generate_series(1,array_upper(var_atempgeoms, 1)) As 
 i &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LEFT JOIN 
 generate_series(ceiling(array_upper(var_atempgeoms,1)/2)::integer,array_upper(var_atempgeoms, \
 1)) As j &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT 
 ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND \
 ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE \
 j IS NULL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY 1, 
 2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) As foo&nbsp;) )" PL/pgSQL 
 function "st_concavehull" line 39 at assignment NOTICE:&nbsp; 
 geometry_gist_joinsel called with incorrect join type CONTEXT:&nbsp; SQL 
 statement "SELECT ST_Union(ARRAY(SELECT 
 geom &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-- fuse near neighbors 
 together &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT DISTINCT ON (i) 
 i,&nbsp; 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 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 generate_series(1,array_upper(var_atempgeoms, 1)) As 
 i &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INNER JOIN 
 generate_series(1,array_upper(var_atempgeoms, 1)) As j 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT 
 ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND \
 ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], 
 var_buf*10) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION \
 ALL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-- catch the ones with no near 
 neighbors &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT i, 0, 
 ST_Buffer(var_atempgeoms[i] , var_buf*10, 'quad_segs=3') As 
 geom &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM 
 generate_series(1,array_upper(var_atempgeoms, 1)) As 
 i &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LEFT JOIN 
 generate_series(ceiling(array_upper(var_atempgeoms,1)/2)::integer,array_upper(var_atempgeoms, \
 1)) As j &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON 
 ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT 
 ST_Intersects(var_atempgeoms[i],var_atempgeoms[j]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND \
 ST_DWithin(var_atempgeoms[i],var_atempgeoms[j], var_buf*10) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE \
 j IS NULL &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY 1, 
 2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) As foo&nbsp;) )" PL/pgSQL 
 function "st_concavehull" line 39 at assignment **************PGADMIN 
 MESSAGE - END************** &nbsp; Is this a significant issue as the 
 Query still&nbsp;seems to be 
 running. &nbsp; Cheers, &nbsp; Paul &nbsp; &nbsp; </DIV></BLOCKQUOTE></BODY></HTML>

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 