Komplexní úlohy zpracování dat katastru nemovitostí - první část

Otázky

1. Kolik je v daném katastrálním území parcel? Jaké procento tvoří parcely zjednodušené evidence?
1517 parcel, z toho se žádné parcely ZE se v k.ú. 602515 nevyskytují

2. Na kolika parcelách stojí budovy? Kolik procent těchto budov je "součástí pozemku" a které nikoli?
168 parcel. Budovy, které na těchto parcelách stojí jsou z 89 % vedeny, že jsou "součástí pozemku".

3. Spočti nový atribut ANALYZA_VYMERY, který při absolutním rozdílu mezi výměrou SPI a SGI menším než 2 m2 bude mít textovou hodnotu 'Ok'. V opačném případě bude mít textovou hodnotu 'Rozdíl větší než 2 m2'. Kolik parcel s odchylkou větší než 2 m2 se v katastrálním území vyskytuje?
1145

4. Sestav dotaz, ve kterém budou parcely s odchylkou ve výměře větší než 2 m2 zgrupovány podle druhu pozemku. Co se stane, když vezmeme v potaz kód způsobu určení výměry?
DRUH POZEMKU: POCET PARCEL, ostatní plocha = 360, trvalý travní porost = 305, orná půda = 216, lesní pozemek = 96, zastavěná plocha a nádvoří = 80, zahrada = 59, vodní plocha = 29. Žádná z parcel s výměrou určenou ze souřadnic v S-JSTK ale mezní odchylku 2 m2 nepřekračuje (WHERE "ZPURVY_KOD" = 2).

5. Vypiš statistiku počtu parcel podle všech existujících druhů pozemku. Tuto statistiku ulož do pohledu.
DRUH_POZEMKU: POCET_PARCEL ostatní plocha = 511, trvalý travní porost = 355, orná půda = 251, zastavěná plocha a nádvoří = 172, lesní pozemek = 107, zahrada = 76, vodní plocha = 45, vinice = 0, chmelnice = 0, ovocný sad = 0

6. Vypiš statistiku počtu parcel podle všech druhů pozemku a způsobu využití parcely. Tuto statistiku ulož do pohledu.

7. Napiš dotaz na sestavení parcelního čísla. Skladá se z kmenového čísla a poddělení. Poddělení může být i NULL, pak je uvedeno pouze kmenové číslo.

Řešení

1. Kolik je v daném katastrálním území parcel? Jaké procento tvoří parcely zjednodušené evidence?
1517 parcel, z toho se žádné parcely ZE se v k.ú. 602515 nevyskytují

select
(select count(*) from "PAR" where "PAR_TYPE" = 'PZE')
/
(select count(*) from "PAR")::numeric;

2. Na kolika parcelách stojí budovy? Kolik procent těchto budov je "součástí pozemku" a které nikoli?
168 parcel. Budovy, které na těchto parcelách stojí jsou z 89 % vedeny, že jsou "součástí pozemku".

SELECT
(select count(*) from "PAR" WHERE "SOUCASTI" = 'n' AND "BUD_ID" IS NOT NULL)
/
(select count(*)
FROM "PAR"
WHERE "BUD_ID" IS NOT NULL)
::numeric;

3. Spočti nový atribut ANALYZA_VYMERY, který při absolutním rozdílu mezi výměrou SPI a SGI menším než 2 m2 bude mít textovou hodnotu 'Ok'. V opačném případě bude mít textovou hodnotu 'Rozdíl větší než 2 m2'. Kolik parcel s odchylkou větší než 2 m2 se v katastrálním území vyskytuje?
1145

SELECT COUNT(*) 
FROM (
SELECT "ID", "VYMERA_PARCELY" AS "VYMERA_SPI", ST_Area("geom") AS "VYMERA_SGI",
CASE WHEN ABS("VYMERA_PARCELY" - ST_Area("geom")) <= 2
THEN 'Ok' ELSE 'Rozdil vetsi nez 2 m2'
END AS "ANALYZA_VYMERY"
FROM "PAR"
) AS subquery
WHERE "ANALYZA_VYMERY" = 'Rozdil vetsi nez 2 m2';

4. Sestav dotaz, ve kterém budou parcely s odchylkou ve výměře větší než 2 m2 zgrupovány podle druhu pozemku. Co se stane, když vezmeme v potaz kód způsobu určení výměry?
DRUH POZEMKU: POCET PARCEL, ostatní plocha = 360, trvalý travní porost = 305, orná půda = 216, lesní pozemek = 96, zastavěná plocha a nádvoří = 80, zahrada = 59, vodní plocha = 29. Žádná z parcel s výměrou určenou ze souřadnic v S-JSTK ale mezní odchylku 2 m2 nepřekračuje (WHERE "ZPURVY_KOD" = 2).

SELECT "NAZEV" AS "DRUH_POZEMKU", COUNT("ID") AS "POCET_PARCEL"
FROM (
SELECT "ID", "VYMERA_PARCELY" AS "VYMERA_SPI", ST_Area("geom") AS "VYMERA_SGI", "DRUPOZ"."NAZEV",
CASE WHEN ABS("VYMERA_PARCELY" - ST_Area("geom")) <= 2 THEN 'Ok'
ELSE 'Rozdil vetsi nez 2 m2'
END AS "ANALYZA_VYMERY"
FROM "PAR"
JOIN "DRUPOZ" ON ("PAR"."DRUPOZ_KOD" = "DRUPOZ"."KOD")
) AS subquery
WHERE "ANALYZA_VYMERY" = 'Rozdil vetsi nez 2 m2'
GROUP BY "NAZEV"
ORDER BY "POCET_PARCEL" DESC;

5. Vypiš statistiku počtu parcel podle všech existujících druhů pozemku. Tuto statistiku ulož do pohledu.
DRUH_POZEMKU: POCET_PARCEL ostatní plocha = 511, trvalý travní porost = 355, orná půda = 251, zastavěná plocha a nádvoří = 172, lesní pozemek = 107, zahrada = 76, vodní plocha = 45, vinice = 0, chmelnice = 0, ovocný sad = 0

SELECT "NAZEV" AS "DRUH_POZEMKU", COUNT("ID") AS "POCET_PARCEL"
FROM "PAR"
RIGHT JOIN "DRUPOZ" ON ("PAR"."DRUPOZ_KOD" = "DRUPOZ"."KOD")
GROUP BY "NAZEV"
ORDER BY "POCET_PARCEL" DESC;

stejné řešení pomocí LEFT JOINu

SELECT "NAZEV" AS "DRUH_POZEMKU", COUNT("ID") AS "POCET_PARCEL"
FROM "DRUPOZ"
LEFT JOIN "PAR" ON ("PAR"."DRUPOZ_KOD" = "DRUPOZ"."KOD")
GROUP BY "NAZEV"
ORDER BY "POCET_PARCEL" DESC;

vytvoření pohledu

CREATE OR REPLACE VIEW "DRUPOZ_STATS_VIEW" AS
SELECT "NAZEV" AS "DRUH_POZEMKU", COUNT("ID") AS "POCET_PARCEL"
FROM "DRUPOZ"
LEFT JOIN "PAR" ON ("PAR"."DRUPOZ_KOD" = "DRUPOZ"."KOD")
GROUP BY "NAZEV";

select * from DRUPOZ_STATS_VIEW;

6. Vypiš statistiku počtu parcel podle všech druhů pozemku a způsobu využití parcely. Tuto statistiku ulož do pohledu.

CREATE OR REPLACE VIEW "DRUPOZ_ZPVYPA_STATS_VIEW" AS
SELECT "DRUPOZ"."NAZEV" AS "DRUH_POZEMKU",
        "ZPVYPO"."NAZEV" AS "ZPUSOB_VYUZITI",
        COUNT("ID") AS "POCET_PARCEL"
FROM "DRUPOZ"
JOIN "PAR" ON ("PAR"."DRUPOZ_KOD" = "DRUPOZ"."KOD")
LEFT JOIN "ZPVYPO" ON ("PAR"."ZPVYPA_KOD" = "ZPVYPO"."KOD")
GROUP BY "DRUPOZ"."NAZEV", "ZPVYPO"."NAZEV";

7. Napiš dotaz na sestavení parcelního čísla. Skladá se z kmenového čísla a poddělení. Poddělení může být i NULL, pak je uvedeno pouze kmenové číslo.

SELECT
  "ID", "KMENOVE_CISLO_PAR" ||
  CASE WHEN "PODDELENI_CISLA_PAR" IS NOT NULL
  THEN '/' || "PODDELENI_CISLA_PAR"
  ELSE ''
  END AS "Parcelni_cislo"
FROM
  "PAR";

Další možné řešení

SELECT
  "ID", "KMENOVE_CISLO_PAR" || COALESCE('/' || "PODDELENI_CISLA_PAR", '') AS "PARCELNI_CISLO"
FROM
  "PAR";

Řešení pomocí obecné funkce a jejího zavolání v rámci SELECTu

CREATE OR REPLACE FUNCTION sestav_parcelni_cislo(kmenove_cislo INTEGER, poddeleni INTEGER)
RETURNS text AS $$
BEGIN
  RETURN kmenove_cislo || COALESCE('/' || poddeleni, '');
END;
$$ LANGUAGE plpgsql;

SELECT
  "ID", sestav_parcelni_cislo("KMENOVE_CISLO_PAR", "PODDELENI_CISLA_PAR") AS "PARCELNI_CISLO"
FROM
  "PAR";