Prostorové funkce - první část

Otázky

odkaz

1. Jaká je výměra (v ha) bažin a rašelinišť ležících v lese. Kolik to je procent z celkové výměry bažin a rašelinišť?
5743; 64%
3. Kolik obcí v ČR leží celou svojí plochou do vzdálenosti 10 km od řeky Labe. Jaký je celkový počet obyvatel těchto obcí?
520; 868 320
4. Na kolika místech kříží dálnice, rychlostní silnice či silnice 1.třídy s železnicí. Kolik z těchto křížení leží do vzdálenosti 1km od nejbližší železniční stanice?
1165; 442 (výsledek se neshoduje s Esri ArcGIS)
5. Jaká je výměra území (v ha), na kterých leží les či vodní plocha. Existuje území, které by odpovídalo současně oběma podmínkám?
2 832 583; ne
8. Jaká je výměra uzemí ČR (v km2), která leží dále než 5 km od nejbližší silnice a zároveň dále než 10 km od nejbližší železniční stanice? Na území kterých obcí leží největší z hledaných lokalit?
18,6 km2; Modrava (TODO)

Řešení

CREATE SCHEMA myschema;

SET search_path TO myschema, ar, ac, public;
1. Jaká je výměra (v ha) bažin a rašelinišť ležících v lese. Kolik to je procent z celkové výměry bažin a rašelinišť?
5743; 64% 
CREATE TEMPORARY TABLE baziny_lesy AS
SELECT st_intersection(b.geom, st_makevalid(l.geom)) AS geom
FROM ar.bazinyaraseliniste AS b
JOIN ar.lesy AS l
ON st_intersects(b.geom, l.geom);

SELECT round(sum(st_area(b.geom))/1e4)
FROM baziny_lesy AS b;

SELECT round(
 sum(st_area(b.geom)) /
 sum(st_area(bc.geom)) * 100
 )
FROM baziny_lesy AS b, ar.bazinyaraseliniste AS bc;
3. Kolik obcí v ČR leží celou svojí plochou do vzdálenosti 10 km od řeky Labe. Jaký je celkový počet obyvatel těchto obcí?
520; 868 320 
WITH labe AS
(
SELECT st_buffer(geom, 10e3, 25) AS geom FROM ar.vodnitoky WHERE nazev = 'Labe'
)
SELECT count(*), sum(pocet_obyv) FROM ac.obcepolygony
WHERE objectid IN
(
 SELECT distinct o.objectid FROM ac.obcepolygony AS o
 JOIN labe
 ON st_within(o.geom, labe.geom)
);
4. Na kolika místech kříží dálnice, rychlostní silnice či silnice 1.třídy s železnicí. Kolik z těchto křížení leží do vzdálenosti 1km od nejbližší železniční stanice?
1165; 442 (výsledek se neshoduje s Esri ArcGIS) 
WITH silnice1 AS
(
SELECT objectid, geom FROM ar.silnice_2015 WHERE trida < 4
)
SELECT count(*) FROM
(
 SELECT st_intersection(s.geom, z.geom) as geom
 FROM silnice1 AS s
 JOIN ar.zeleznice AS z
 ON st_intersects(s.geom, z.geom)
) AS a;

vs 

WITH silnice1 AS
(
SELECT objectid, geom FROM ar.silnice_2015 WHERE trida < 4
)
SELECT count(*) FROM
(
 SELECT st_intersection(s.geom, z.geom) as geom
 FROM silnice1 AS s
 JOIN ar.zeleznice AS z
 ON s.geom && z.geom
) AS a
WHERE NOT st_isempty(geom);

možné řešení 

CREATE TEMPORARY TABLE s_z AS 
WITH silnice1 AS
(
 SELECT objectid, geom FROM ar.silnice_2015 WHERE trida < 4
)
SELECT geom FROM
(
 SELECT st_intersection(s.geom, z.geom) AS geom
 FROM silnice1 AS s
 JOIN ar.zeleznice AS z
 ON s.geom && z.geom
) AS foo
WHERE NOT st_isempty(geom);

SELECT count(*) FROM s_z;

WITH z AS
(
SELECT st_buffer(geom, 1e3) AS geom
FROM ar.zeleznicnistanice
)
SELECT count(*) FROM s_z
JOIN z
ON st_within(s_z.geom, z.geom);

vs 

SELECT count(*) FROM s_z
JOIN ar.zeleznicnistanice as z
ON st_dwithin(s_z.geom, z.geom, 1e3);

vs 

WITH z AS
(
SELECT st_buffer(geom, 1e3) AS geom
FROM ar.zeleznicnistanice
)
SELECT count(*) FROM (select (st_dump(geom)).geom from s_z) as s_z1
JOIN z
ON st_within(s_z1.geom, z.geom);
5. Jaká je výměra území (v ha), na kterých leží les či vodní plocha. Existuje území, které by odpovídalo současně oběma podmínkám?
2 832 583; ne 
WITH lesy_vplochy AS
(
 SELECT l.objectid, l.geom FROM ar.lesy AS l
 UNION
 SELECT v.objectid, v.geom FROM ar.vodniplochy AS v
)
SELECT round(sum(st_area(st_union(t.geom, t.geom)))/10e3)
FROM lesy_vplochy AS t;

výsledek se neshoduje s Esri ArcGIS 

SELECT count(st_intersection(st_makevalid(l.geom), v.geom)) AS typ
FROM ar.lesy AS l
JOIN ar.vodniplochy AS v
ON st_intersects(l.geom, v.geom);

odpověď 

SELECT gtyp,count(*) FROM
(
 SELECT st_geometrytype(st_intersection(st_makevalid(l.geom), v.geom)) AS gtyp
 FROM ar.lesy AS l
 JOIN ar.vodniplochy AS v
 ON st_intersects(l.geom, v.geom)
) AS foo
GROUP BY gtyp;

možné řešení 

SELECT count(*) FROM
(
 SELECT st_intersection(st_makevalid(l.geom), v.geom) as geom
 FROM ar.lesy AS l
 JOIN ar.vodniplochy AS v
 ON st_intersects(l.geom, v.geom)
) AS foo
WHERE st_dimension(geom) > 1;

anebo 

SELECT sum(st_area(st_intersection(st_makevalid(l.geom), v.geom))) AS typ
FROM ar.lesy AS l
JOIN ar.vodniplochy AS v
ON st_intersects(l.geom, v.geom);
8. Jaká je výměra uzemí ČR (v km2), která leží dále než 5 km od nejbližší silnice a zároveň dále než 10 km od nejbližší železniční stanice? Na území kterých obcí leží největší z hledaných lokalit?
18,6 km2; Modrava (TODO) 
CREATE TEMPORARY TABLE s5 AS
SELECT st_union(st_buffer(geom, 5e3)) AS geom
FROM ar.silnice_2015;

CREATE TEMPORARY TABLE z10 AS
SELECT st_union(st_buffer(geom, 10e3)) AS geom
FROM ar.zeleznicnistanice;

CREATE TEMPORARY TABLE s5z10_cr AS
WITH s5z10 AS
(
 SELECT st_union(s5.geom, z10.geom) AS geom
 FROM s5
 JOIN z10
 ON s5.geom && z10.geom
)
SELECT st_difference(s.geom, s5z10.geom) as geom
FROM s5z10
JOIN ac.statpolygon AS s
ON s5z10.geom && s5z10.geom;

SELECT round(st_area(geom)::numeric/1e6, 1) FROM s5z10_cr;

WITH s5z10_max AS
(
 SELECT geom FROM
 (
  SELECT (st_dump(geom)).geom AS geom
  FROM s5z10_cr
  ORDER BY st_area(geom)
 ) AS foo
 ORDER by st_area(geom) DESC
 LIMIT 1
)
SELECT naz_obec
FROM ac.obcepolygony AS o
JOIN s5z10_max AS s
ON st_overlaps(o.geom, s.geom);