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

[Avernar]

SELECT PostGIS_full_version();

"POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel.
4.9.2, 08 September 2015" GDAL="GDAL 2.1.0, released 2016/04/25"
LIBXML="2.9.4" LIBJSON="0.12" RASTER"

As I said, SFCGAL was not compiled by default.  The ST_MemUnion
function is listed as being a 3D function and from what I can figure
out that requires SFCGAL.  I'm building PostGIS 2.3.0 as it was added
a few days ago to the FreeBSD ports collection.  I've added the SFCGAL
compile option so I'll see if that gives me ST_MemUnion.

I do use QGIS but it's currently not showing me any raster tables so
I'll have to see what's up with that.  Maybe because I didn't make any
overview.  I use QGIS for editing the shape files I imported into
PostGIS.  The dem data is for looking up the elevation of various
lat/lon coordinates.

I'll look into the ST_Extent way of doing it.  I'm not worried about
adding more data as I've added the entire SRTM 1 arc-second data into
the table.  The row estimate for that table is 19,575,700.  Used the
usual 100x100 tile size.

John


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