[postgis-users] add raster constraint exceeds array size limit

[Caitríona Smith]

Hi All

I’ve a number of largish rasters that I’m wanting to tile to relatively small tiles, but I’m hitting the array limit on the add maximum extent constraint. 

If the tile_size is 32x32 or 50x50 I get the error, but a tile size of 64x64 completes. 

At 50x50 there are 13M rows and at 64x64 there are 8M

I’m wanting to use fairly small tiles as I am intending to version control the data at the tile level, and would prefer to keep the area that doesn’t change under control to a minimum.

I’m thinking that I might be able to get away with a parent child relationship, and that I may have to do that anyway, for performance, but I’m not sure what the effect of that will be on the extent constraint. 

Is there something else that I am doing wrong, or is there another way around this (I’ve not really looked at the component functions to see what they do)



I’m running postgis 2.2.1 on postgres 9.5.1 on SL7.2 for my sins




select version();
                                                 version                        
                         
--------------------------------------------------------------------------------
-------------------------
 PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (
Red Hat 4.8.5-4), 64-bit
(1 row)




select postgis_full_version();
                                                                            post
gis_full_version                                                                
            
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------
 POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 
2012" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" TOPO
LOGY RASTER
(1 row)



hmdb=> select count(*) from dtm2015_50x50;
  count   
----------
 13360464
(1 row)

hmdb=> select count(*) from dtm2015;
  count  
---------
 8152320
(1 row)



hmdb=> select addrasterconstraints('dtm2015_50x50'::name, 'rast'::name); 
NOTICE:  Adding SRID constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding scale-X constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding scale-Y constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding blocksize-X constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding blocksize-Y constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding alignment constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding number of bands constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding pixel type constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding nodata value constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding out-of-database constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding maximum extent constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
ERROR:  array size exceeds the maximum allowed (1073741823)
CONTEXT:  SQL statement "SELECT st_ashexewkb(st_envelope(st_collect(st_envelope(rast)))) FROM cjsmith.dtm2015_50x50"
PL/pgSQL function _add_raster_constraint_extent(name,name,name) line 20 at EXECUTE
PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 104 at assignment
PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160311/58feb563/attachment.html>