SQL a indexové hodnocení geo- jevů

Doc. Dr. Vladimír Homola, Ph.D.

Abstrakt

V počítačovém prostředí je uloženo značné množství dat, převážně spravovaných relačními databázovými systémy. Pro některé způsoby jejich zpracování není zapotřebí používat nákladný specializovaný software. Dobře poslouží opomíjený, přesto velmi silný prostředek pro formulování dotazů do databází, Structured Query Language (SQL). Jeho hlavní součást, příkaz SELECT, je vhodným nástrojem jednak pro dotazy se seskupováním, jednak s kombinováním údajů v tabulkách. Příkladem prvního typu úlohy je hodnocení geo- jevů souhrnným indexem získaným z vážených ocenění jednotlivých faktorů spolupůsobících na jev. Příkladem druhého typu úlohy je tzv. “gridování”, tj. odhad hodnot v uzlech pravidelné sítě na základě hodnot v několika lokalitách terénu. V postupném použití dotazu nejprve jednoho, pak druhého typu spočívá např. hodnocení zranitelnosti akviferu povrchovým znečištěním. Pouhými dvěma dotazy je možno nejen provést vlastní hodnocení, ale i připravit data pro plošné nebo prostorové zobrazení standardním software. Na počítačích třídy PC je prakticky použitelný už i jen samotný MS Office: relační databázový systém Access a tabulkový procesor Excel.

Abstract

In computer environment, there is a great amount of geo- data stored mainly in relational database tables format. Some particular methods of that data processing need not powerful, expensive specialized software systems. Structured Query Language (SQL), strong but unfamiliar data processing tool is sufficient for either grouping queries or combining queries. Index phenomenon evaluation based on weighted factor rates is the example of first type of such query. The second type represents gridding, i.e. estimation of net node values depending on known spatial data values. The only one SELECT query followed by the second one is sufficient for not only the proper enumeration, but data outputting in standard software packages format, too. In PC environment, Microsoft Office is usable for both data processing (Access) and graphic presentation (Excel).

Úvod

Mezi zdroji geo- dat (uloženými v počítačovém prostředí databázových systémů) a koncovými informacemi (požadovaných odbornou veřejností) stojí programové vybavení. Jeho pořízení - zvláště software kategorie GIS - dnes představuje často násobky ceny samotných počítačů a ne vždy umožňuje analyzovat data právě tím způsobem, které požaduje uživatel.

Na druhé straně existují obecné nástroje pro formulování dotazů do relačních databází (které jsou nejčastější formou uspořádání obecných dat) dodávaných jako součást některého programového systému. Příkladem může být Structured Query Language (zkratka SQL) implementovaný jak v řadě operačních systémů přímo (Unix), tak formou různých programových mezivrstev (SQL server fy. Microsoft), ale i přímo ve většině RDBS (Oracle, SyBase, Informix, Access, Fox aj.).

Geo- data jsou charakterizována značným množstvím typů. Po konverzi reálných typů na typy dat databázových systémů se sice počet typů jako takových zmenší, ale hodnoty jednotlivých typů (byť stejných) zůstávají neporovnatelné.

Pro úlohy, které hodnotí úroveň nějakého geo- jevu (=“závisle proměnná”) na základě několika jiných, navzájem neporovnatelných geo- atributů (=“nezávisle proměnných”, faktorů), je možno použít metody výpočtu indexu jevu; jeho hodnota se pak stává hodnotícím kriteriem (závislého) jevu v relaci na (nezávislé) faktory.

Tato metoda je právě jednou z metod, která není přímo řešena ve specializovaných softwarových systémech. Přesto její použití pro hodnocení geo- jevů je rychle a jednoduše možné pomocí SQL tak, jak je to popsáno dále.

Podstata metody

Matematická podstata metody je následující (viz také Homola, 1998):

Mějme n faktorů F₁ až F_n. Každému faktoru F_i je přiřazena váha v_i, což je obecně reálné číslo. Každý faktor F_i nabývá jedné z konečného počtu hodnot, které určují rozklad množiny všech hodnot faktoru na třídy. Faktor F_i tedy nabývá jedné z hodnot {T_i,1, T_i,2, … ,T_i,ki}. Každé třídě T_i,j každého faktoru F_i je přiřazeno ocenění w_i,j, což je obecně reálné číslo.

Hodnotící index I je pak dán jako součet vážených ocenění.

(1)

kde se sčítá přes všechny faktory.

Praktické hodnocení

V praxi nebývá faktorů mnoho (do deseti) a samotný výpočet jednoho indexu podle (1) tedy není problém.

Dva problémy se však vyskytují jinde. Především se v praxi - zvláště v geo- vědách zkoumajících rozsáhlé plochy a geologická tělesa - nepožaduje výpočet jednoho, ale spíše statisíců indexů. To je problém praktický.

Teoretickým problémem je stanovení jednak samotných vah jednotlivých faktorů, jednak ocenění jednotlivých tříd tak, aby výsledný index odpovídal realitě v místech, kde je možno ocenění provést i přímým způsobem. Pro ověřování a “dolaďování” číselných hodnot vah a ocenění je opět zapotřebí provádět stovky a tisíce opakovaných výpočtů podle (1).

Z hlediska uvedených dvou problémů se výpočet podle (1) stává kritickým místem jak teoretické přípravy, tak praktického použití. Následující kapitoly ukazují využití SQL pro řešení tohoto problému mimo drahé softwarové produkty, které navíc jednoduché přímé řešení této úlohy neobsahují (pokud vůbec nějaké obsahují).

Modelový příklad

Poměrně široce se popisovaná metoda. uplatnila např. při hodnocení zranitelnosti akviferu (Aller, 1987); v US EPA je známa jako DRASTIC, její modifikace jako SINTACS. V obou aplikacích je použito sedmi faktorů.

Jako modelový příklad bude použito právě hodnocení zranitelnosti akviferu, ovšem v podobě redukované jak vzhledem k počtu faktorů, tak vzhledem k počtu tříd.

Nechť jsou tedy dány tři faktory:

• typ půdy,
• sklon terénu,
• množství vsaku ze srážek.

Jest ohodnotit riziko, s jakým bude případné lokální povrchové znečištění infiltrovat směrem k podzemním vodám.

Je logické, že

• lépe bude prostup probíhat např. ve štěrcích než v jílech,
• plochý terén vodu spíše pohltí, ze svažitého terénu voda spíše odteče,
• v oblastech s velkými srážkami je riziko větší než v suchých oblastech.

Poznámka: Zájemci o právě tento problém najdou jeho úplnou aplikaci pro oblast Ostravy a Karviné v (Paganelli, 1996), obecnější pak v (Fabbri, 1995)..

Datové prostředí

Obr. 1: Příklad datových vazeb

Protože dotaz SELECT umožňuje vytvořit relační datový model (dále jen tabulka) v podstatě libovolným výběrem z libovolných jiných tabulek, budou dotazy zde uváděné pracovat s tabulkami dat redukované struktury. Schéma datových vazeb uvádí předchozí obrázek (DATA - tabulka dat, TABVAH - tabulka vah, TABOCEN - tabulka ocenění):

Řídící datová tabulka přitom může mít např. následující strukturu (LOKALITA - kód lokality, KÓDF - kód faktoru zjištěného na dané lokalitě, KÓDT - kód třídy daného faktoru zjištěného na dané lokalitě):

Tab. 1: Příklad dat

Váhy jednotlivých faktorů mohou být uvedeny v následující tabulce (KÓDF - kód faktoru, FAKTOR - popis faktoru, VÁHA - váha daného faktoru):

Tab. 2: Příklad vah faktorů

Obdobně lze ocenění jednotlivých tříd jednotlivých faktorů umístit např. do tabulky (KÓDF - kód faktoru, KÓDT - kód třídy, TŘÍDA - popis třídy, OCENĚNÍ - hodnota ocenění dané třídy daného faktoru):

Tab. 3: Příklad ocenění

Vazby ze souboru dat do “číselníků” vah a ocenění (viz obr. 1) jsou tedy zprostředkovány údaji ve sloupci kódf (do tabulky vah faktorů) a údaji ve sloupcích kódf a kódt (do tabulky ohodnocení tříd).

Dotaz na indexy

Vytvoření tabulky s indexy pro jednotlivé lokality provede např. dotaz

SELECT
lokalita, SUM (váha*ocenění) AS index
FROM data a, tabvah b, tabocen c
INTO TABLE indexy
WHERE a.kódf=b.kódf AND a.kódf=c.kódf AND a.kódt=c.kódt
GROUP BY lokalita
ORDER BY index

Výsledkem dotazu provedeného pro uvedená data je následující tabulka INDEXY:

Tab. 4: Výsledek dotazu

Největší index zranitelnosti akviferu (50) má podle toho lokalita A05, tj. lokalita tvořená štěrky, s minimálním sklonem a většími srážkami, zatímco nejmenší index zranitelnosti akviferu má lokalita A33 tvořená hlínami na svažitějším terénu a malými srážkami. Tato zjištění neodporují skutečnosti. Účelem článku však není diskutovat konkrétní použití, ale obecný mechanismus.

Poznámka: Výpočet indexu pro jedinou lokalitu (např. A37) provede stejný dotaz jako shora doplněný o podmínku

where
lokalita = ”A37” and ... ;

Indexy na ploše

Předchozí odstavec ukázal výpočet indexů v konkrétních místech (bodech plochy). Zvláště v geo- vědách je často požadováno provést na základě hodnot nějaké veličiny v několika izolovaných bodech jisté oblasti odhad chování této veličiny v celé oblasti. Nechť jsou pro shora uvedené lokality známy také polohy v terénu dané souřadnicemi nějaké soustavy souřadné:

Tab. 5: Polohy lokalit

Lokality jsou tedy umístěny v obdélníkové oblasti

[4260;5480] x [4320;5520]

Princip odhadů na ploše spočívá v pokrytí plochy obdélníkovou příp. čtvercovou sítí (o potřebné hustotě) a zjištění bodových odhadů ve všech uzlech sítě. Hodnoty v uzlech pak s přesností odpovídající hustotě sítě aproximují hodnoty plochy vyjadřující chování sledované veličiny na ploše.

Zjednodušeně pak zmíněný princip ukazuje následující obrázek:

Obr. 2: Výpočet pro síť na ploše

Postup naznačený na předchozím obrázku je vhodný pro ty situace, kdy nejsou pro všechny lokality známy hodnoty všech faktorů. Pokud známy jsou, je výhodnější opačný postup: nejprve spočíst všechny indexy a teprve poté (jednou) provést odhady pro síť.

Dotaz na plošný odhad

Síť pokrývající obdélníkovou plochu je dána množinou x-ových a množinou y-ových souřadnic svých uzlů. Mějme tyto hodnoty ve dvou tabulkách:

Tab. 6: Určení sítě

Všechny uzly sítě jsou pak dány kombinacemi "každý z X s každým z Y".

Pro výpočet odhadů v uzlech lze použít např. metodu inverzní vzdálenosti, při níž je odhadovaná hodnota v uzlu P dána vztahem

(2)

kde i probíhá všechny známé body s hodnotami M_i a r_i je vzdálenost i-tého známého bodu od uzlu P.

Vytvoření tabulky se všemi uzly sítě a odhady v nich provede dotaz

SELECT x, y,
SUM (index/((x-x0)^2+(y-y0)^2)) / SUM (1/((x-x0)^2+(y-y0)^2)) AS odhad
FROM xsit, ysit, indexy c, poloha d
INTO TABLE odhady
WHERE c.lokalita = d.lokalita
GROUP BY x, y
ORDER BY x, y

Výsledná tabulka má tvar

Tab. 7: Odhady v uzlech sítě

a ve skutečnosti obsahuje všech 13 x 9 = 117 uzlových hodnot. Tuto tabulku lze pak dále zpracovat ať přímo relačními databázovými systémy (Oracle, Access, Fox) a tabulkovými procesory (Lotus, Excel), tak otevřením nebo importem do specializovaných systémů (GIS-y, Surfer, mapové systémy apod).

Diskuse, závěr

Článek ukázal na opomíjený, přitom levný a rychlý způsob zpracování a předzpracování dat bez nutnosti zakoupení a instalace drahého specializovaného software. Výhodou je standardizovaná údržba dat v běžně používaných tabulkách relačních databázových systémů (v systému DOS počítačů třídy PC soubory s příponou .DBF). Ty jsou bezproblémově přímo použitelné (nebo alespoň bezproblémově přenositelné) do všech komerčně úspěšných specializovaných systémů.

To dokumentuje následující obrázek. Tabulka ODHADY byla otevřena v Excelu a vykreslena jako prostorový graf (obr. 3).

Obr. 3: Odhady hodnot na ploše

Stejně tak tabulka INDEXY může být zpracována dále numericky i graficky např. systémem Surfer, jak to dokumentuje obrázek 4.

Obr. 4: Příklad zpracování výsledku dotazu jako topografická mapa

Historické ohlédnutí

Na závěr je vhodné se zmínit o rychlosti zpracování. První dotaz SELECT zde uvedený (vytvoření tabulky INDEXY) je vcelku bezproblémový, probíhá sekvenčně a i tisíce dat nejsou z časového hlediska kritické. Na druhé straně dotaz SELECT pro zjištění údajů na ploše už principielně je kvadraticky závislý na počtu dělení os.

Následující obrázek ukazuje závislost času výpočtu jednak na počtu dat (=zde lokalit), jednak na hustotě čtvercové sítě. Výpočet byl proveden na počítači třídy PC s procesorem Pentium 150MHz, 64MB vnitřní paměti - tedy na podzim’99 zcela průměrný počítač. Jako RDBS sloužil obyčejný FoxPro 2.0 spouštěný v prostředí Windows 95.

Obr. 4: Časy výpočtu

Pro zajímavost je dále uvedena tabulka časů výpočtu na tomtéž počítači pro síť 70 x 70 a 10 lokalit různými verzemi RDBS v různých prostředích. Časová závislost pro různý počet dat a různou hustotu sítě je proporcionální obrázku 4.

Tab. 8: Časy výpočtu v různých systémech

Porovnání rychlosti provedení stejného dotazu SELECT jako shora v systémech Microsoft Access a Microsoft Fox 5.0 ukazuje následující tabulka:

Tab. 9: Porovnání Access a Fox

Oba systémy byly spuštěny v prostředí Windows 95.

Literatura

Aller L., Bennet T., Leher J. H., Petty R. J., Hackett G.: DRASTIC: A standardized system for evaluating groundwater pollution potential using hydrogeological settings. U. S. EPA 600 / 2-87-035. EPA 1987.

Fabbri, A. G.: The use of environmental indicators in the geosciences. ITC, Enschede. 1995.

Homola, V.: Geostatistika a teorie datových struktur. Disertační práce doktorandského studia. Ostrava, 1998.

van den Meer, F. D.: Introduction to Geostatistics. Enschede, ITC 1992.

Paganelli F.: Monitoring Spectral Characteristics of Vegetation in Karviná and Ostrava District. ITC Enschede, 1996.

Rev. 6 / 2016