Prostorové dotazy - druhá část

Otázky

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á?
(GPKG: 75s 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á?
(GPKG: 75s 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.0001) 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 lesy1 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%';

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í 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;

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)
);

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
AND    st_intersects(v.geom, st_buffer(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

CREATE TEMPORARY TABLE cr2 (
   id serial primary key,
   geom geometry(Linestring, 5514)
);

INSERT INTO cr2(geom)
WITH linie AS (
   SELECT st_boundary(geom) AS geom FROM ac.statpolygon
), a AS (
   SELECT generate_series(0,999,1) a
)
SELECT ST_LineMerge(ST_LineSubstring(geom, a * 0.001, (a + 1) * 0.001)) AS geom
FROM a, linie;

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

CREATE TEMPORARY TABLE kraje2 (
   id serial primary key,
   geom geometry(MultiLinestring, 5514)
);

INSERT INTO kraje2(geom)
WITH linie AS (
   SELECT st_boundary(geom) AS geom FROM ac.krajepolygony
), a AS (
   SELECT generate_series(0,999,1) a
)
SELECT st_multi(ST_LineMerge(ST_LineSubstring(geom, a * 0.001, (a + 1) * 0.001))) AS geom
FROM a, linie;

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*****');

Další možná řešení (žádné z nich se neschoduje s ArcGISem)

WITH kraje AS
(SELECT st_boundary(geom) AS geom FROM ac.krajepolygony)
SELECT   count(DISTINCT kod_obec)
FROM     ac.obcepolygony AS o
JOIN     kraje AS k
ON       st_intersects(o.geom, k.geom);

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