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