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. Nov 3, 2020 at 17:01
  • The rasters were tiled for import (500x500).
    – Pemburu
    Nov 4, 2020 at 6:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.