1

I need to merge RGB raster tables from Sentinel-2 data into a mosaic of same date using ST_Union(), but following the example of Postgis manual for all bands and all input rasters like this one I got errors.

Ps.: All rasters were processed to ressample and reproject to same resolution and projection before to upload at postgresql server.

--My code

select ST_Union(ARRAY[rast1, rast2], 'MAX') from 
    sentinel.rasterTable1 as rast1,
          sentinel.rasterTable2 as rast2; 

-- the error
ERROR:  function st_union(sentinel.rasterTable1, sentinel.rasterTable2) does not exist

How can I fix this to get the max by each band?

  #################################################################

First appoach by @Pierre using this code:

 create table mergedData as SELECT ST_Union(rast, 'MAX') rast FROM 
 (SELECT rast FROM rasterTable1  
  UNION ALL
  SELECT rast FROM rasterTable2) foo

Example of code application

Aparently, only copy the first image listted in the code above.

  • How do you define max for an RGB image? Max of each band? – user30184 Dec 3 '17 at 15:59
  • @user30184 Yes, by each band. – Isaque Daniel Dec 3 '17 at 16:11
  • Is rasterTable1 a table or a field. If the former, you want, to specify the raster in ARRAY, eg, ARRAY[table1.rast, table2.rast], .... – John Powell Dec 3 '17 at 20:02
  • @JohnPowellakaBarça rasterTable1 is a table. – Isaque Daniel Dec 3 '17 at 20:19
  • Well that is your problem. Union takes the raster field not the table name – John Powell Dec 3 '17 at 20:22
3

There is no raster function taking an array of rasters yet. You need to union the two tables before:

SELECT ST_Union(rast, 'MAX') rast
FROM (SELECT rast FROM rasterTable1 
      UNION ALL
      SELECT rast FROM rasterTable2) foo
  • aparently the code don't merge the tables, only copy the first table listed in the code. look at the figure at the question which I got from your suggestion. – Isaque Daniel Dec 4 '17 at 21:30
  • @Pierre, really, that isn't very clear from the docs which manifestly show an example taking a setof rasters. – John Powell Dec 5 '17 at 10:09
  • @Isaque It's probably because the black areas are nodata values. The result of the MAX operation becomes nodata. What are the nodata values? – Pierre Racine Dec 6 '17 at 2:38
  • @JohnPowellakaBarça a setof raster is not the same as an array of raster... – Pierre Racine Dec 6 '17 at 2:40
  • 1
    So what if you unset the nodata value like this: ST_SetBandNodataValue(rast, NULL)? You can reset it afterward. – Pierre Racine Dec 8 '17 at 14:14
0

Following the solution proposed by @Pierre, it merge the raster tables in a perfect way.

  create table testmerge as SELECT ST_Union(ST_SetBandNodataValue(rast, NULL), 'MAX') rast
                      FROM (SELECT rast FROM rasterTable1  
                        UNION ALL
                         SELECT rast FROM rasterTable2) foo;

################################## The result

Merged image result

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.