[postgis-users] raster2pgsql, _add_raster_constraint_extent fails with "array size exceeds the maximum allowed"

[Regina Obe]

I've reopened the ticket you noticed and will use ST_Extent for the fix.

I think we should avoid using transform though.

I've put my notes on ticket.

https://trac.osgeo.org/postgis/ticket/3501

Thanks for the benchmarks,
Regina

 

-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Avernar
Sent: Sunday, November 20, 2016 2:36 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] raster2pgsql, _add_raster_constraint_extent fails with "array size exceeds the maximum allowed"

I made one last change.  I added ST_Transform in there just for the case when the SRID constraint is not set and there are more than one SRID in the raster column.  Only adds 9 seconds to the run time.
Here's what I have now:

CREATE OR REPLACE FUNCTION _add_raster_constraint_extent(
    rastschema name,
    rasttable name,
    rastcolumn name)
  RETURNS boolean AS
$BODY$
    DECLARE
        fqtn text;
        cn name;
        sql text;
        attr text;
        srid integer;
    BEGIN
        fqtn := '';
        IF length($1) > 0 THEN
            fqtn := quote_ident($1) || '.';
        END IF;
        fqtn := fqtn || quote_ident($2);

        cn := 'enforce_max_extent_' || $3;

    sql := 'SELECT ST_SRID('
            || quote_ident($3)
      || ') FROM '
            || fqtn
            || ' LIMIT 1;';
    EXECUTE sql INTO srid;

        sql := 'SELECT
st_ashexewkb(st_setsrid(st_extent(st_transform(st_envelope('
            || quote_ident($3)
            || '), '
            || srid
            || ')), '
            || srid
            || ')) FROM '
            || fqtn;
        EXECUTE sql INTO attr;

        sql := 'ALTER TABLE ' || fqtn
            || ' ADD CONSTRAINT ' || quote_ident(cn)
            || ' CHECK (st_envelope('
            || quote_ident($3)
            || ') @ ''' || attr || '''::geometry)';
        RETURN _add_raster_constraint(cn, sql);
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;

On Sat, Nov 19, 2016 at 1:53 PM, Regina Obe <lr at pcorp.us> wrote:
> I'm a little suspicious you don't have ST_MemUnion.  That's been 
> around for a while (since 1.* days) and hasn't been deprecated.  Which 
> version of PostGIS are you using?  And did you upgrade from earlier versions?
>
>
>
> Changing the logic to
>
>
>
> SELECT st_ashexewkb(st_envelope(st_memunion(st_envelope( 
>
>
>
> Should just work.  In thinking about this, what I really would have 
> liked to do is just use ST_Extent which takes advantage that it's just 
> collapsing boxes and needs to return a box
>
> And does do it one at a time similar to ST_MemUnion, but it has a 
> downside that it returns a box and not a geometry with SRID so we'd 
> have to throw a ST_SetSRID in there to convert the box to a geometry 
> with srid, but then we can dispense with the last ST_Envelope.
>
>
>
> As far as whether you need an extent constraint, there is no harm in 
> leaving it out in most cases.  Main reason it exists is for tools like 
> QGIS that interrogate the
>
>
>
> raster_columns table to be able to return a quick extent.  I think for 
> geometry they use ST_EstimateExtent.
>
> Even then with those tools, I think no biggie if you have another 
> layer limiting the map extent.  If you have a table that big, using 
> raster overviews is more important.
>
>
>
> One annoying thing about having an extent constraint is you can't add 
> more records without removing the constraint and recomputing.  So if 
> you plan to add more records,
>
> I would suggest A) dispense with the constraint  or B) Hand-code the 
> extent constraint to cover the full area you expect your dataset to 
> cover in future.
>
>
>
> Question:  How many rows do you have and what is the pixel width height?
>
>
>
> Hope that helps,
>
> Regina
>
> http://www.postgis.us
>
> http://postgis.net
>
>
>
>
>
>
>
>
> --- ORIGINAL MESSAGE --
>
> I was importing a very large raster set and after a day it failed with 
> array size exceeds the maximum allowed when setting the extent 
> constraint.
>
> I redid the import again by just doing the table create and then 
> adding the data and then doing the final bunch of sql statements 
> manually to avoid having the whole thing rollback again.
>
> I first tried the fix discussed here
> https://trac.osgeo.org/postgis/ticket/3501 and implemented here
> https://trac.osgeo.org/postgis/changeset/15115 but that failed with 
> the array size error as well.  So I redid the constraints without the 
> extent one.
>
> I would try ST_MemUnion but unfortunately that one is missing in my 
> installation.  I guess it's because it also handles 3D and the 3D 
> option is not compiled in by default on my platform.
>
> So, what would a pl/pgsql script to do what MemUnion does, ie 
> accumulate one at a time, look like?
>
> Also, what issues would not having an extent constrain have?
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users