How to use LATERAL for multiple columns?-CodePudding

I am using to this query to convert raster values into vector data

SELECT dp.*
     , g.gid gid
     , g.geom ggeom
FROM public.t32ulc_entire_tile rast
     , LATERAL ST_PixelAsCentroids(rast.rast, 1) as dp
JOIN raster_grid g ON dp.geom && g.geom

;

this resulting on

b1    |geom                  |
------ ---------------------- 
5135.0|POINT (300005 5800015)|
4994.0|POINT (300015 5800015)|
4515.0|POINT (300025 5800015)|
3942.0|POINT (300035 5800015)|

As my raster data contains other bands and I want to retrieve the data from that bands too with ST_PixelAsCentroids(rast.rast, 2), ST_PixelAsCentroids(rast.rast, 3). How can I extract all necessary values within this query? or should I do it with as?

CodePudding user response：

Going out on a limb, and assuming at least Postgres 10, something like this might be what you are looking for:

SELECT (dp1).*, g1.gid AS gid1, g1.geom AS geom1
     , (dp2).*, g2.gid AS gid2, g2.geom AS geom2
     , (dp3).*, g3.gid AS gid3, g3.geom AS geom3
FROM  (
   SELECT ST_PixelAsCentroids(rast, 1) AS dp1
        , ST_PixelAsCentroids(rast, 2) AS dp2
        , ST_PixelAsCentroids(rast, 2) AS dp3
   FROM   public.t32ulc_entire_tile t
   ) rast
LEFT   JOIN raster_grid g1 ON (rast.dp1).geom && g1.geom  -- can only have 0 or 1 each!
LEFT   JOIN raster_grid g2 ON (rast.dp2).geom && g2.geom  -- can only have 0 or 1 each!
LEFT   JOIN raster_grid g3 ON (rast.dp3).geom && g3.geom  -- can only have 0 or 1 each!
;

The core feature is to put multiple set-returning functions in a single SELECT list to combine them as outlined here:

What is the expected behaviour for multiple set-returning functions in SELECT clause?

Second core feature is the syntax to access fields of a composite type.

CodePudding user response：

You can generate a series of integer to match the number of bands of your raster in another lateral join, then use it in ST_PixelAsCentroids

SELECT 
  i.band, 
  dp.*, 
  g.gid gid, 
  g.geom ggeom
FROM public.t32ulc_entire_tile rast
CROSS JOIN LATERAL generate_series(1,3) i(band)
CROSS JOIN LATERAL ST_PixelAsCentroids(rast.rast, i.band) AS dp
JOIN raster_grid g ON dp.geom && g.geom