sqlpostgresqlpostgislateral

Correct use of a LATERAL FROM


I am trying to use PostGIS to undertake a number of steps within an SQL statement to basically convert a raster to points, buffer, dissolve and then determine the overall boundary i.e. concave hull based on an attribute.

The following is where I am at but get an error suggesting the use of a LATERAL FROM but I do not know what I need to do.

Any assistance would be greatly appreciated.

[0A000] ERROR: aggregate function calls cannot contain set-returning function calls Hint: You might be able to move the set-returning function into a LATERAL FROM item. Position: 123

 SELECT name,
    st_union(
            st_buffer(
                    st_transform(
                            (ST_PixelAsCentroids(rast, 1)).geom
                        , 32756),
                    50, 2)
        )
 FROM rasters
 where id < 5

Solution

  • Just move the function ST_PixelAsCentroids() to a CROSS JOIN LATERAL and give it a label, e.g.

    SELECT name, ST_ASTEXT(
      ST_Union(
        ST_Buffer(
          ST_Transform((j).geom,32756),
          50, 2))
        )
    FROM rasters 
    CROSS JOIN LATERAL ST_PixelAsCentroids(rast, 1) j
    WHERE id < 5
    GROUP BY name;
    

    Although you can achieve the same results without the CROSS JOIN LATERAL

    SELECT name, ST_ASTEXT(
      ST_Union(
        ST_Buffer(
          ST_Transform((j).geom,32756),
          50, 2))
        )
    FROM rasters, ST_PixelAsCentroids(rast, 1) j
    WHERE id < 5
    GROUP BY name;