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
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;