10.1 Prostorové dotazy - druhá část

Úlohy

12. Vyberte katastrální území, ve kterých leží alespoň částečně jedna vodní plocha, seskupte tyto území podle kódu NUTS LAU1. Uveďte jaký kód NUTS má největší výměru a z kolika katastrálních území se skládá?
(SpatiaLite: 9s vs PostGIS: 0.3s) CZ0313, 78

7. Kolik železničních stanic leží v lese a zároveň jejich název nezačíná na písmeno 'L'?
100

2. Kolika obcemi v ČR neprochází žádná silnice?
241

4. Vyberte silnice, které kříží vodní toky. Jaké procento z těchto silnic tvoří silnice první třídy?
14,5%

5. Kolik procent rybníků z celkového počtu leží celou svojí plochou na území Jihočeského kraje?
41,85%

10. Kolik vodních ploch leží alespoň částí své plochy ve vzdálenosti do 10 km od poledníku se zeměpisnou délkou 15°?
45

3. Kolik obcí leží na hranici ČR?
285

11. Kolik obcí se dotýká alespoň jedním liniovým segmentem hranice kraje?
1248

Řešení

12. Vyberte katastrální území, ve kterých leží alespoň částečně jedna vodní plocha, seskupte tyto území podle kódu NUTS LAU1. Uveďte jaký kód NUTS má největší výměru a z kolika katastrálních území se skládá?
(SpatiaLite: 9s vs PostGIS: 0.3s) CZ0313, 78

SELECT   kod_lau1, count(*) AS pocet
FROM     ac.katastralniuzemipolygony AS k
WHERE    objectid IN
(
 SELECT  DISTINCT k.objectid
 FROM    ac.katastralniuzemipolygony AS k
 JOIN    ar.vodniplochy AS v
 ON      st_intersects(k.geom, v.geom)
)
GROUP BY kod_lau1
ORDER BY pocet DESC
LIMIT    1;

7. Kolik železničních stanic leží v lese a zároveň jejich název nezačíná na písmeno 'L'?
100

SELECT   count(*)
FROM     ar.zeleznicnistanice as z
join     ar.lesy as l
ON       st_intersects(z.geom, l.geom)
where    nazev not like 'L%';

výsledek vychází jinak v porovnání s Esri ArcGIS, mnoho stanic leží na hranici lesa

WITH lesy1 AS
(
 SELECT st_buffer(geom, 0.001) AS geom
 FROM ar.lesy
)
SELECT   count(*)
FROM     ar.zeleznicnistanice as z
JOIN     lesy1 as l
ON       st_intersects(z.geom, l.geom)
WHERE    nazev not like 'L%';

vs

CREATE TEMPORARY TABLE lesy2 AS
 SELECT st_buffer(geom, 0.001) AS geom
 FROM ar.lesy;
CREATE INDEX on lesy2 USING gist (geom);

SELECT   count(*)
FROM     ar.zeleznicnistanice as z
JOIN     lesy2 as l
ON       st_intersects(z.geom, l.geom)
WHERE    nazev not like 'L%';

vs

SELECT   count(*)
FROM     ar.zeleznicnistanice as z
JOIN     ar.lesy as l
ON       st_dwithin(z.geom, l.geom, 0.001)
WHERE    nazev not like 'L%';

2. Kolika obcemi v ČR neprochází žádná silnice?
241

SELECT  count(*)
FROM    ac.obcepolygony AS obce
WHERE   objectid NOT IN (
 SELECT DISTINCT obce.objectid
 FROM   ac.obcepolygony AS obce
 JOIN   ar.silnice_2015 AS silnice
 ON     st_intersects(obce.geom, silnice.geom)
);

Alternativní řešení (optimalizované)

SELECT  count(*)
FROM    ac.obcepolygony AS obce
WHERE NOT EXISTS
(
 SELECT *
 FROM   ar.silnice_2015 AS silnice
 WHERE  st_intersects(obce.geom, silnice.geom)
);

Alternativní řešení https://www.w3schools.com/sql/sql_join_left.asp

SELECT    count(*)
FROM      ac.obcepolygony AS obce
LEFT JOIN ar.silnice_2015 AS sil
ON        st_intersects(obce.geom, sil.geom)
WHERE     sil.objectid IS NULL;

4. Vyberte silnice, které kříží vodní toky. Jaké procento z těchto silnic tvoří silnice první třídy?
14,5%

CREATE TEMPORARY TABLE silnice1 AS
 SELECT    silnice.objectid, trida
 FROM      ar.silnice_2015 AS silnice
 JOIN      ar.vodnitoky
 ON        st_intersects(silnice.geom, vodnitoky.geom);

SELECT round((
 SELECT count(*)
 FROM silnice1
 WHERE trida = 3
)::numeric / (
 SELECT count(*)
 FROM silnice1
)::numeric * 100, 1);

nebo (CTE) https://www.root.cz/clanky/cte-a-analyticke-funkce-v-postgresql/

WITH silnice2 AS
 (SELECT   silnice.objectid, trida
 FROM      ar.silnice_2015 AS silnice
 JOIN      ar.vodnitoky
 ON        st_intersects(silnice.geom, vodnitoky.geom))
SELECT round((
 SELECT count(*)
 FROM silnice2
 WHERE trida = 3
)::numeric / (
 SELECT count(*)
 FROM silnice2
)::numeric * 100, 1);

5. Kolik procent rybníků z celkového počtu leží celou svojí plochou na území Jihočeského kraje?
41,85%

SELECT round((
 SELECT count(*)
 FROM   ar.vodniplochy AS v
 JOIN   ac.krajepolygony AS k
 ON     v.typ = 2
 AND    k.naz_cznuts3 = 'Jihočeský kraj'
 AND    st_within(v.geom, k.geom)
)::numeric / (
 SELECT count(*)
 FROM   ar.vodniplochy AS v
 WHERE v.typ = 2)::numeric * 100, 2);

10. Kolik vodních ploch leží alespoň částí své plochy ve vzdálenosti do 10 km od poledníku se zeměpisnou délkou 15°?
45

SELECT count(*)
FROM   ar.vodniplochy as v
JOIN   ar.zemepisnasitwgs84 as s
ON     s.delka_dd = 15
WHERE  st_dwithin(v.geom, st_transform(s.geom, 5514), 10e3);

3. Kolik obcí leží na hranici ČR?
285

WITH cr AS
(SELECT st_boundary(geom) AS geom FROM ac.statpolygon)
SELECT   count(DISTINCT kod_obec)
FROM     ac.obcepolygony AS o
JOIN     cr as c
ON       st_intersects(o.geom, c.geom); -- st_touches()

Optimalizace (rozdel_pocet)

CREATE TEMPORARY TABLE cr2 (
   id serial primary key,
   geom geometry(Linestring, 5514)
);
INSERT INTO cr2(geom)
SELECT rozdel_pocet(st_boundary(geom), 1000)
FROM ac.statpolygon;
CREATE INDEX ON cr2 USING gist(geom);

SELECT   count(DISTINCT kod_obec)
FROM     ac.obcepolygony AS o
JOIN     cr2 as c
ON       st_intersects(o.geom, c.geom);

11. Kolik obcí se dotýká alespoň jedním liniovým segmentem hranice kraje?
1248

SELECT   count(DISTINCT kod_obec)
FROM     ac.obcepolygony AS o
JOIN     ac.krajepolygony AS k
ON       o.geom && k.geom
AND      st_relate(o.geom, k.geom, '****1****');

Optimalizace (rozdel_pocet)

CREATE TEMPORARY TABLE kraje2 (
   id serial primary key,
   geom geometry(MultiLinestring, 5514)
);
INSERT INTO kraje2(geom)
SELECT rozdel_pocet(st_boundary(geom), 1000)
FROM ac.krajepolygony;
CREATE INDEX ON kraje2 USING gist(geom);

SELECT   count(DISTINCT kod_obec)
FROM     ac.obcepolygony AS o
JOIN     kraje2 AS k
ON       o.geom && k.geom
AND      st_relate(o.geom, k.geom, '***1*****');