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:[email protected]] On Behalf Of Avernar Sent: Sunday, November 20, 2016 2:36 PM To: PostGIS Users Discussion <[email protected]> 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 <[email protected]> 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 > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users
