1

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?

1
  • Somehow I was looking at the 2.2 documentation and completely missed st_interpolateraster because its not available until 3.2. It seems like exactly what I need but I am struggling to get it to create an output that makes sense. postgis.net/docs/manual-dev/RT_ST_InterpolateRaster.html
    – TurboGus
    Dec 21, 2022 at 16:23

2 Answers 2

0

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

2
  • I need the raster cell to be calculated based on the centroids location on the tin. If I dump the tin to points I would have to do the elevation interpolation myself. I'll experiment with the idea.
    – TurboGus
    Dec 16, 2022 at 21:01
  • I also, need to perform the raster creation within PostGIS if at all possible. This is is going in a cloud application and I want to avoid moving the datasets back and forth for processing if possible. I tried QGIS's tin Interpolation and it does create what I need.
    – TurboGus
    Dec 16, 2022 at 21:03
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.