In my PostGIS / PostgreSQL DB I have a DEM cropped to the size of Germany. As a second (vector) layer I have 1300 catchment areas (<30km²) for which I want to calculate the slope. To reduce the calculation time and effort I decided to clip the DEM to the geometries of the catchment and calculate their slope values afterwards. The DEM has to be transformed to SRID 31467.

DROP TABLE IF EXISTS spatial_derived.ezg_slope;
CREATE TABLE spatial_derived.ezg_slope AS

SELECT gid rid, ST_Union(rast) rast
SELECT ezg.gid,
ST_slope(ST_Clip(ST_Transform(eu.rast, 31467), ezg.geom, -9999, true),1,'32BF','degree',1,false) rast
FROM spatial_derived.land_use_ezg30 ezg, spatial.eu_dem eu
WHERE ST_Intersects(eu.rast, ezg.geom) AND ST_IsValid(ezg.geom) )t1

This worked nicely for a LIMIT of 20 rows:

enter image description here

However when I try to apply the query on all the data I just get an area of the size of the bounding box with 0s, and the polygons are not presented anymore. Up to now I can't go into more detail what might have gone wrong. My question here is if the query contains any evident flaws?

  • Is your raster tiled? Why do you have to ST_Union() pieces together? – Pierre Racine Sep 6 '16 at 13:58
  • I changed my query. I'm not sure if it's tiles (since there are no real tiles in PostGIS?), but there are multiple rows. I want to union the rast rows which are overlaped by the same polygon. – andschar Sep 7 '16 at 19:12
  • What happens if you don't use the ST_Union? It might be that you have a very big polygon that kills of the rest of the output. In the ST_Union, the last raster defines the pixel value when there is overlap, so your first 20 results might be overridden by one last failing raster. – tilt Sep 23 '16 at 21:19
  • I'm pretty sure that I don't have a big polygon at the end (apart from PostGIS creating one when executing this code?) and I use ST_Union because otherwise I have some strange lines in the rasters. I can try it tomorrow. BTW: In the end I used GDAL to do this calculation, which doesn't mean I'm not interested in solving it with PostGIS anymore.. – andschar Sep 26 '16 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.