PostGIS Raster compute min, max altitude value and slope from a DEM

478 Views Asked by At

I have a DEM dataset and some polygons that represents parcels. For each parcels I would like to compute the maximum/minimum altitude and the average slope. Based on the PostGIS documentation and several example on Internet, two functions could be used to compute this data. The first one is ST_SummaryStatsAgg and the other is ST_DumpAsPolygons.

So, I've created a trigger that computes, before a new parcel is inserted, some statistics, but I am confused about the results. Here is my code:

--First cut the raster based on the parcel shape
SELECT INTO __rasterClip 
        ST_Union(ST_Clip(foncier.reunion_mnt.rast, NEW.geom, -9999, TRUE))
        FROM foncier.reunion_mnt 
        WHERE NEW.geom && foncier.reunion_mnt.rast;

--Compute slope with ST_DumpAsPolygons OR ST_SummaryStatsAgg
SELECT INTO __slope1 (ST_DumpAsPolygons(ST_Slope(__rasterClip, 1, '32BF', 'DEGREES', 1.0))).val;
SELECT INTO __slope2 (ST_SummaryStatsAgg(ST_Slope(__rasterClip, 1, '32BF', 'DEGREES', 1.0), 1, TRUE, 1)).max;
RAISE NOTICE 'Slope1 %', MAX(__slope1 );
RAISE NOTICE 'Slope2 %', __slope2;

--Compute min/max altitude
SELECT INTO __rasterStats (ST_SummaryStatsAgg(__rasterClip, 1, TRUE, 1)).*;
SELECT INTO __polyDump (ST_DumpAsPolygons(__rasterClip, 1, TRUE)).*;
RAISE NOTICE 'Stat % - %', __rasterStats.min,  __rasterStats.max;
RAISE NOTICE 'Poly % - %', Min( __polyDump.val ), Max( __polyDump.val );

The results of the RAISE NOTICE:

NOTICE:  Slope1 5.14276456832886
NOTICE:  Slope2 51.9147148132324
NOTICE:  Stat 222.76 - 251.22
NOTICE:  Poly 225.929992675781 - 225.929992675781

There is clearly something wrong. The slope between the two functions is not the same and the min and max altitude for the ST_DumpAsPolygons is the same.

So could you please help me and tell me:

  1. What is the most effective way to compute the min/max altitude and the average slope for a parcel based on a raster DEM?
  2. For my general knowledge is it best to use ST_SummaryStatsAgg or ST_DumpAsPolygons. In which case is it best to use on or the other?
  3. In a trigger how to declare the variable type of these two functions (ST_SummaryStatsAgg, ST_DumpAsPolygons). My first attempt was to declare them using their return type (summarystats and geomval). But I was getting errors so I switch to Record. Is it correct?

Thanks for your help!

0

There are 0 best solutions below