1

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?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Manap Shymyr
  • 149
  • 7
  • You have 3 columns in value expressions but 2 in result. – Sahap Asci Aug 02 '22 at 23:09
  • Please present a consistent question. The result does not match the query. Start by disclosing Postgres and PostGIS version. And show the desired result - there are many forms to output the mentioned result ... – Erwin Brandstetter Aug 02 '22 at 23:34

2 Answers2

0

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:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

You can generate a series of integers that match the number of bands of your raster in separated lateral join, then use it in ST_PixelAsCentroids, e.g.

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
Jim Jones
  • 18,404
  • 3
  • 35
  • 44