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.

6
  • How do you define max for an RGB image? Max of each band?
    – user30184
    Dec 3, 2017 at 15:59
  • @user30184 Yes, by each band. Dec 3, 2017 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], .... Dec 3, 2017 at 20:02
  • @JohnPowellakaBarça rasterTable1 is a table. Dec 3, 2017 at 20:19
  • Well that is your problem. Union takes the raster field not the table name Dec 3, 2017 at 20:22

2 Answers 2

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
7
  • 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. Dec 4, 2017 at 21:30
  • @Pierre, really, that isn't very clear from the docs which manifestly show an example taking a setof rasters. Dec 5, 2017 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? Dec 6, 2017 at 2:38
  • @JohnPowellakaBarça a setof raster is not the same as an array of raster... Dec 6, 2017 at 2:40
  • 1
    So what if you unset the nodata value like this: ST_SetBandNodataValue(rast, NULL)? You can reset it afterward. Dec 8, 2017 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 and acknowledge you have read our privacy policy.

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