Topografická analýza povrchu, reklasifikace rastrových dat

CREATE TABLE dmt_uk_700 AS
SELECT st_reclass(rast, '0-700:0, 700-2000:1', '1BB') AS rast
FROM   ar.dmt_uk;

SELECT (st_valuecount(rast)).* from dmt_uk_700;

SELECT round(sum((vc).count)) / 4 AS area_ha
FROM
(
SELECT st_valuecount(rast) as vc from dmt_uk_700
) AS foo
WHERE  (vc).value = 1;

WITH slope_uk AS
(
 SELECT st_slope(rast) AS rast
 FROM   ar.dmt_uk
)
SELECT sum((vc).count) / 4 as area_ha
FROM
 (
  SELECT st_valuecount(st_reclass(rast, '0-15:0, 15-90:1', '1BB')) AS vc
   FROM   slope_uk
 ) AS foo
where (vc).value = 1;

CREATE TABLE aspect_uk AS
SELECT st_aspect(rast) AS rast
FROM   ar.dmt_uk;

SELECT (st_summarystats(rast)).* AS stats
FROM   aspect_uk;

CREATE TABLE slope_uk AS
SELECT st_slope(rast) AS rast
FROM   ar.dmt_uk;

SELECT (st_summarystats(rast)).* AS stats
FROM   slope_uk;

SELECT (st_valuecount(rast)).* from slope_uk;

SELECT sum((vc).count) / 4 as area_ha
FROM
(
SELECT st_valuecount(st_mapalgebra(a.rast, s.rast,
       'CASE WHEN (([rast1] BETWEEN 0 AND 22.5) OR ([rast1] BETWEEN 337.5 AND 360)) AND ([rast2] > 15) THEN 1 ELSE 0 END')) AS vc
FROM   aspect_uk AS a, slope_uk AS s
) AS foo
where (vc).value = 1;

SELECT sum((vc).count) / 4 as area_ha
FROM
(
SELECT st_valuecount(st_mapalgebra(a.rast, s.rast,
       'CASE WHEN (([rast1] BETWEEN 0 AND 22.5) OR ([rast1] BETWEEN 337.5 AND 360)) OR ([rast2] > 15) THEN 1 ELSE 0 END')) AS vc
FROM   aspect_uk AS a, slope_uk AS s
) AS foo
where (vc).value = 1;

WITH
slope_uk_15 AS (
SELECT st_mapalgebra(rast,'4BUI', 'CASE WHEN [rast] > 15 THEN 1 ELSE 0 END') AS rast
FROM   slope_uk),
aspect_uk_sever AS (
SELECT ST_MapAlgebra(rast, '4BUI',
                     'CASE WHEN ([rast] BETWEEN 0 AND 22.5) OR ([rast] BETWEEN 337.5 AND 360) THEN 1 ELSE 0 END') AS rast
FROM   aspect_uk),
aspect_slope_uk AS (
SELECT ST_MapAlgebra(a.rast, s.rast,
                     '[rast1] + [rast2]') AS rast
FROM   aspect_uk_sever AS a, slope_uk_15 AS s)
SELECT (st_valuecount(rast)).count / 4 as area_ha
FROM
(
SELECT st_mapalgebra(d.rast, sa.rast, 'CASE WHEN [rast1] + [rast2] >= 2 THEN 1 ELSE 0 END') AS rast
FROM   dmt_uk_700 AS d, aspect_slope_uk as sa
) AS foo;

CREATE TABLE silnice_uk_500 AS
WITH silnice_uk_buf AS (
 SELECT st_union(st_buffer(s.geom, 500)) AS geom FROM ar.silnice_2015 AS s
 JOIN ac.krajepolygony as k on s.geom && k.geom where k.naz_cznuts3 = 'Ústecký kraj'
)
SELECT st_intersection(s.geom, k.geom) AS geom
FROM silnice_uk_buf AS s, ac.krajepolygony as k
WHERE k.naz_cznuts3 = 'Ústecký kraj';

CREATE TABLE slope_uk_15 AS
SELECT st_mapalgebra(rast, NULL, 'CASE WHEN [rast] > 15 THEN 1 ELSE 0 END') AS rast
FROM
(
 SELECT st_slope(rast) AS rast
 FROM ar.dmt_uk
) as foo;

EXPLAIN ANALYZE 
SELECT st_intersection(rast, geom) as geomval
FROM   slope_uk_15
CROSS JOIN silnice_uk_500;

CREATE TABLE slope_uk_15_tiles AS
SELECT st_mapalgebra(rast, NULL, 'CASE WHEN [rast] > 15 THEN 1 ELSE 0 END') AS rast
FROM
(
 SELECT st_slope(rast) AS rast
 FROM ar.dmt_uk_tiles
) as foo;

CREATE TABLE slope_silnice_geom AS
WITH slope_silnice AS
(
 SELECT st_intersection(rast, geom) as geomval
 FROM   slope_uk_15_tiles
 CROSS JOIN silnice_uk_500
)
SELECT (gv).geom AS geom, (gv).val AS val FROM
(
 SELECT geomval AS gv
 FROM slope_silnice
) AS s;

SELECT round(SUM(st_area(geom))/1e4)
FROM slope_silnice_geom
WHERE val = 1;

WITH ds AS
(
 SELECT ST_MapAlgebra(d.rast, s.rast,
        'CASE WHEN [rast] < 10 * [rast2] THEN 1 ELSE NULL END') AS rast
 FROM ar.dmt_uk AS d, slope_uk AS s
)
SELECT round((st_valuecount(rast)).count)
FROM ds;

SELECT ROUND(ST_Value(rast, 1, ST_SetSRID(ST_GeomFromText('POINT(-770118 -986668)'), 5514)))
FROM   ar.dmt_uk;