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

[Avernar]

I tried st_memunion first and aborted it after 8 hours.  Very little
disk activity during the process.  The data import including the other
constraints took 16-20 hours so that was way too long in comparison.

Then I tried your ST_Extent method.  Took 60 minutes total.  I ran the
two parts of _add_raster_constraint_extent manually to see how long
each took (and to verify the output of the first select).  The query

SELECT st_ashexewkb(ST_SetSRID(ST_Extent(ST_Envelope(rast)), (SELECT
ST_SRID(rast) FROM dem.srtm30 LIMIT 1))) FROM dem.srtm30;

took 30 minutes and 6 seconds to run.  The second part

SELECT _add_raster_constraint('enforce_max_extent_rast', 'ALTER TABLE
dem.srtm30 ADD CONSTRAINT enforce_max_extent_rast CHECK
(st_envelope(rast) @ ''010 ... 4CC0''::geometry)');

took 30 minutes and 3 seconds to run.  Much better.


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