PostGIS TIN to Raster

Question

I am trying to convert a TIN to raster, in PostGIS, with a 1m cell size but sampling the tin for elevations is very slow. My test data is about 2km x 2km and relatively flat, think farm field. Below is the sql I am using to sample the tin for elevations.

with grid as
    (select st_centroid(grid) as pixelCentroid from
        (select st_transform((ST_SquareGrid(1,
        st_transform(ST_SetSRID(geometry, 4326), 3857))).geom, 4326) as grid
     from boundary WHERE "Id" = 2) as grid
     inner join boundary on st_intersects(grid, geometry))

select st_intersection(pixelCentroid, tinGeom)
    from
(select 2 as id,  (st_dump(ST_DelaunayTriangles(ST_collect(geometry_4326),0, 0))).geom as tinGeom
FROM survey_points 
WHERE layer_id = 2 AND type IN (0,3)) as tin
inner join grid
on st_intersects(pixelCentroid, tinGeom)

I have experimented with storing the tin and indexing it but it didnt help.

Is there a way, within PostGIS, to speed up the intersection or calculate a raster cell directly from the TIN?

Answer 1

You can consider using ST_DumpPoints function instead of ST_Dump to sample the elevations from the TIN as it makes the process faster by reducing the number of triangle that need to be processed: https://postgis.net/docs/ST_DumpPoints.html

Alternatively, calculating a raster cell directly from the TIN is achievable using TIN based interpolation. Refer: Using TIN Interpolation algorithm in PyQGIS

Answer 2

The solution to my problem was to avoid having to convert the tin to raster by using a raster interpolation to create the raster directly using Delaunay triangulation. I was able to use ST_InterpolateRaster with the option 'linear:radius=-1:nodata=0' and it creates the raster in 20 seconds which I can live with.