1

I want to combine two rasters (GeoTIFF or PostGIS raster) so that the result is a table showing each combination of values and their count.

Example:

raster 1 raster 2

1 2 3 40 40 10

2 3 3 10 20 20

2 3 1 10 10 20

The resulting Table would look like this

raster1|raster2| cnt

1| 40| 1

2| 40| 1

2| 10| 2

3| 10| 1

2| 10| 2

3| 10| 2

3| 20| 2

2| 10| 2

Essentially this is what ESRI's Spatial Analyst Tool 'combine' does (really fast), by creating a raster and an associated value attribute table.

I was able to do this in PostGIS using the following code

with hw as (
SELECT val::integer as hw,geom as geom
    FROM 
    (
      SELECT dp.* FROM raster1 hw
         , LATERAL ST_PixelAsCentroids(hw.rast, 1) AS dp 
     ) foo1
)         
, beam as 
(

SELECT val::integer as beam, geom as geom
    FROM (SELECT dp.* FROM raster2 beam
         , LATERAL ST_PixelAsCentroids(beam.rast, 1) AS dp 
  ) foo2
)
, 
stats as
(

  select (split_part(combi,'_',1))::integer as hw
      ,(split_part(combi,'_',2))::integer as beam
      , n 
  from 
  (
    select  combi, count(*) as n from
    (  
        select  hw::TEXT || '_' || beam::TEXT as combi
         from hw, beam 
         where hw.geom=beam.geom 
    ) c
    group by combi
  ) g
)

select stats.esri_id, stats.hw, 100, 'Echaz', stats.n, 5 

However, it works only for rasters which are not too large. I have huge rasters 50000x50000) , with those I run into memory problems quickly.

2
  • Did you try tiling your raster when loading it in PostGIS? The query becomes more complex but that should solve the memory problem. – Pierre Racine Nov 3 '20 at 17:01
  • The rasters were tiled for import (500x500). – Pemburu Nov 4 '20 at 6:20

Your Answer

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

Browse other questions tagged or ask your own question.