V januári 2016 začalo MV SR v spolupráci s NASES zverejňovať údaje z Registra adries (RA) v podobe otvorených údajov prostredníctvom portálu data.gov.sk.

Keďže RA obsahuje o čosi viac než len zoznam adresných bodov, je na data.gov.sk zverejnený ako sada viacerých datasetov, viď https://data.gov.sk/dataset?tags=register+adries .

Nuž a je tu napr. otázka "takže aký je postup pre niekoho kto chce zistiť všetky adresy v meste X?" (viď Platforma Slovensko.Digital).

Úvod

Dátový model RA obsahuje zoznamy viacerých typov objektov:

• adresné body
• vchody
• ulice
• obce
• ...

Zjednodušene povedané, jeden typ objektov = jeden zoznam = jedna DB tabuľka.

Mapovanie na datasety je teda realizované systémom jedna DB tabuľka = jeden dataset. V datasetoch sú potom údaje dostupne pod tzv. resources, konkrétne vždy dvojmo:

1. konsolidované dáta: všetky časové verzie všetkých objektov - aktuálne aj historické
2. zmenové dáta: jeden riadok v tabuľke predstavuje jednu zmenu a dokumentuje históriu zmien

A API je implementované pomocou CKAN DataStore, takže budeme postupovať podľa SQL API na baze CKAN DataStore: JOIN.

Krok 1

Chceme teda vyhľadávať cez názov mesta, takže nás najprv zaujíma dataset "Register Adries - Register obcí" (dokumentácia).

Údaje budeme brať z resource "Obce - konsolidované dáta" ku ktorému je podstatnou informáciou jeho ID "15262453-4a0f-4cce-a9e4-7709e135e4b8" (viď už spomenutý článok o SQL API na báze CKAN DataStore).

Zaujímajú nás pre jednoduchosť tieto položky:

• municipalityName (názov obce): podla neho budeme vyhľadávať
• objectId (identifikátor objektu): s ohľadom na dátovú schému, pomocou tohto identifikátora budeme filtrovať ulice patriace k mestu s týmto ID

Takže povedzme, že ideme hľadať adresy pre mesto Poprad. Prvý dopyt teda bude vyzerať takto:

SELECT "objectId", "municipalityName"
FROM "15262453-4a0f-4cce-a9e4-7709e135e4b8"
WHERE "municipalityName" = 'Poprad'

URL: https://data.gov.sk/api/action/datastore_search_sql?sql=SELECT%20%22objectId%22,%20%22municipalityName%22%20from%20%2215262453-4a0f-4cce-a9e4-7709e135e4b8%22%20WHERE%20%22municipalityName%22%20=%20%27Poprad%27

Výsledok (krátený):

{"help": "...", "success": true, "result": {
"records":[{"municipalityName": "Poprad", "objectId": "2087"}],
"fields": [{"type": "int8", "id": "objectId"}, {"type": "text", "id": "municipalityName"}],
"sql": "SELECT \"objectId\", \"municipalityName\" from \"15262453-4a0f-4cce-a9e4-7709e135e4b8\" WHERE \"municipalityName\" = 'Poprad'"}}

Krok 2

Poďme sa teda pozrieť na ulice v danom meste. Zaujíma nás dataset "Register Adries - Register ulíc" (dokumentácia).

Údaje vezmeme z resource "Ulice - konsolidované dáta" ktorého ID je "47f0e853-3a67-487e-b45f-3f5d099105cf".

Zaujímajú nás tieto položky:

• streetName (názov)

• municipalityIdentifiers (buď identifikátor obce alebo identifikátory mestských častí, cez ktoré ulica prechádza): tot by malo byt ID obce ktora nas zaujima
• objectId (identifikátor objektu): s ohľadom na dátovú schému, pomocou tohto identifikátora budeme filtrovať vchody patriace k ulici s týmto ID

Takže ďalší jednoduchý dopyt:

SELECT "objectId", "municipalityIdentifiers"
FROM "47f0e853-3a67-487e-b45f-3f5d099105cf"
WHERE "municipalityIdentifiers" = '2087'

URL: https://data.gov.sk/api/action/datastore_search_sql?sql=SELECT%20%22objectId%22,%20%22municipalityIdentifiers%22%20from%20%2247f0e853-3a67-487e-b45f-3f5d099105cf%22%20WHERE%20%22municipalityIdentifiers%22%20=%20%272087%27

Výsledok (krátený):

{"help": "...", "success": true, "result": {
"records": [{"objectId": "39436", "municipalityIdentifiers": "2087"}, {"objectId": "40013", "municipalityIdentifiers": "2087"}, ...],
"fields": [{"type": "int8", "id": "objectId"}, {"type": "text", "id": "municipalityIdentifiers"}],
"sql": "..."}}

Takže poďme začať robiť JOIN:

SELECT "municipalityName", "streetName"
FROM "15262453-4a0f-4cce-a9e4-7709e135e4b8", "47f0e853-3a67-487e-b45f-3f5d099105cf"
WHERE "municipalityName" = 'Poprad' AND "15262453-4a0f-4cce-a9e4-7709e135e4b8"."objectId"::text = "47f0e853-3a67-487e-b45f-3f5d099105cf"."municipalityIdentifiers"

URL: https://data.gov.sk/api/action/datastore_search_sql?sql=SELECT%20%22municipalityName%22,%20%22streetName%22%20from%20%2215262453-4a0f-4cce-a9e4-7709e135e4b8%22,%20%2247f0e853-3a67-487e-b45f-3f5d099105cf%22%20WHERE%20%22municipalityName%22%20=%20%27Poprad%27%20AND%20%2215262453-4a0f-4cce-a9e4-7709e135e4b8%22.%22objectId%22::text%20=%20%2247f0e853-3a67-487e-b45f-3f5d099105cf%22.%22municipalityIdentifiers%22

Výsledok (krátený):

{"help": "...", "success": true, "result": {
"records": [{"municipalityName": "Poprad", "streetName": "Feldzamova"}, {"municipalityName": "Poprad", "streetName": "Marxova"}, ...],
"fields": [{"type": "text", "id": "municipalityName"}, {"type": "text", "id": "streetName"}],
"sql": "..."}}

Krok 3

A teraz poďme na vchody:

• dataset "Register Adries - Register vchodov (orientačných čísiel)"
• dokumentácia: https://data.gov.sk/dataset/register-adries-register-vchodov/resource/849d6364-10cf-40dd-a354-aba17ae06c52
• resource: "Vchody (orientačné čísla) - konsolidované dáta"
• resource ID: 011f4ec3-7a73-4dff-a63e-81b64cc52947

kde nás zaujímajú tieto položky:

• buildingNumber (orientačné číslo vchodu)
• streetNameIdentifier (identifikátor (StreetName/objectId) ulice)

Poďme rovno na JOIN (a pridajme aj LIMIT, nech zbytočne nepreťažujeme)::

SELECT "municipalityName", "streetName", "buildingNumber"
FROM "15262453-4a0f-4cce-a9e4-7709e135e4b8", "47f0e853-3a67-487e-b45f-3f5d099105cf", "011f4ec3-7a73-4dff-a63e-81b64cc52947"
WHERE "municipalityName" = 'Poprad' AND "15262453-4a0f-4cce-a9e4-7709e135e4b8"."objectId"::text = "47f0e853-3a67-487e-b45f-3f5d099105cf"."municipalityIdentifiers" AND "47f0e853-3a67-487e-b45f-3f5d099105cf"."objectId" = "011f4ec3-7a73-4dff-a63e-81b64cc52947"."streetNameIdentifier"
LIMIT 10

URL: https://data.gov.sk/api/action/datastore_search_sql?sql=SELECT%20%22municipalityName%22,%20%22streetName%22,%20%22buildingNumber%22%20from%20%2215262453-4a0f-4cce-a9e4-7709e135e4b8%22,%20%2247f0e853-3a67-487e-b45f-3f5d099105cf%22,%20%22011f4ec3-7a73-4dff-a63e-81b64cc52947%22%20WHERE%20%22municipalityName%22%20=%20%27Poprad%27%20AND%20%2215262453-4a0f-4cce-a9e4-7709e135e4b8%22.%22objectId%22::text%20=%20%2247f0e853-3a67-487e-b45f-3f5d099105cf%22.%22municipalityIdentifiers%22%20AND%20%2247f0e853-3a67-487e-b45f-3f5d099105cf%22.%22objectId%22%20=%20%22011f4ec3-7a73-4dff-a63e-81b64cc52947%22.%22streetNameIdentifier%22%20LIMIT%2010

Výsledok (krátený):

{"help": "...", "success": true, "result": {
"records": [{"municipalityName": "Poprad", "buildingNumber": "8", "streetName": "1.m\u00e1ja"}, {"municipalityName": "Poprad", "buildingNumber": "27", "streetName": "1.m\u00e1ja"}, ...],
"fields": [{"type": "text", "id": "municipalityName"}, {"type": "text", "id": "streetName"}, {"type": "text", "id": "buildingNumber"}],
"sql": "..."}}

Výsledok je vlastne tabuľka:

Záver

SQL dopyt z kroku 3 by nám teda mal dať časť odpovede na zadanie, zbytok ponechávam na čitateľa. Pre praktické využitie sa ešte treba viacej venovať zoznamu stľpcov ktoré si vypýtame, zoradeniu a tomu, či kompletný výsledok stiahneme na jeden šup alebo použijeme stránkovanie na báze OFFSET + LIMIT či ORDER BY + WHERE.

Príklady sú naschvál zjednodušené, aby sa to čo najľahšie čítalo. Asi najväčším opomenutím je to, že v príkladoch neberiem do úvahy platnosť údajov (t.j. aktuálny dátum a položky validFrom a validTo). Možno niekedy nabudúce.

Doplnky

Indexy

Doplnok k 2.3.3016: V OpenData.sk skupine na Facebooku bol dopyt ohľadom toho, na ktorých stĺpcoch sú nastavené indexy. Keďže cez CKAN DataStore API zatiaľ nefunguje "explain", tak vedomosť o indexoch môže napomôcť pri vylaďovaní výkonu dopytov.

Open Data API napr. na data.gov.sk alebo na odn.opendata.sk je implementované na báze CKAN DataStore a poskytuje pre bežný používateľov dva základné spôsoby na prístup k údajov a vyhľadávanie v nich (viď http://docs.ckan.org/en/latest/maintaining/datastore.html#the-datastore-api):

1. full-text vyhľadávanie (viď datastore_search)
2. vyhľadávanie pomocou SQL (viď datastore_search_sql)

Zameriam sa najmä na ten druhý, t.j. dopytovanie pomocou SQL.

Z pohľadu ľudí, ktorí poznajú SQL má toto API jednu nepeknú vlastnosť: namiesto "pekných názvov" sú ako mená tabuliek použité "škaredé hash-e" (tzv. resource ID). Je to tak preto, lebo CKAN DataStore ukladá všetky datasety do jednej databáze systémom "jeden dátový zdroj = jedna tabuľka". A keďže si datasety a zdroje môže nazývať kto chce ako chce, je unikátnosť názvov tabuliek riešená tými škaredými hash-mi.

Tento "kozmetický nedostatok" nám však v prípade SQL dopytovania otvára jednu významnú možnosť: v SQL dopytoch môžeme robiť JOIN! Takže v prípadoch CKAN katalógov, ktoré využívajú DataStore (napr. už spomenutý data.gov.sk alebo ODN), môžeme robiť zložitejšie dopytovanie cez viaceré datasety naraz. Stačí si len všimnúť, aký "resource ID" ten ktorý dátový zdroj (resource) má, viď napr. datasetu Sponzori politických strán (odkopírovaný z Datanest-u) - vidno ho v URL alebo stačí kliknúť na "Data API" a posunúť sa trochu nižšie:

Takže poďme rovno na veľmi "primitívny investigatívny SQL dopyt" v ktorom okrem už spomenutého datasetu sponzorov politických strán použijeme aj dataset Vestník verejného obstrávania (taktiež z Datanest-u):

SELECT "SUPPLIER_COMPANY_NAME", "SUPPLIER_ICO", "ICO_DARCU", "FIRMA_DARCU"
FROM "ea9b28eb-da22-4594-9fb7-4609e8507982", "c6639c7e-92ad-4b31-8f74-e46eff96825a"
WHERE "SUPPLIER_ICO" = "ICO_DARCU" AND "ICO_DARCU" != ''
LIMIT 2

Stĺpce SUPPLIER_COMPANY_NAME a SUPPLIER_ICO sú z datasetu o obstarávaní a stĺpce ICO_DARCU a FIRMA_DARCU z datasetu o obdarúvaní. Hľadáme teda také IČO, ktoré dodávajú aj obdarúvajú (a keďže dopyt je primitívny, je to len jednoduchý prienik a závery treba robiť až po dôkladnej investigatíve). Tie úvodzovky to tam tiež trochu špatia, ale tu vstupuje do hry PostgreSQL, ktorý názvy stĺpcov (ak nie sú v úvodzovkách) najprv prevádza na malé písmená. A ak teda máme v zdroji názvy stĺpcov s použitím aj veľkých písmen, SQL dopyt nefunguje správne dokým názvy stĺpcov nedáme do úvodzoviek.

Dopyt vieme "zavolať" či už napr. cez curl alebo priamo kliknutím na linku:

http://odn.opendata.sk/api/action/datastore_search_sql?sql=SELECT%20%22SUPPLIER_COMPANY_NAME%22,%20%22SUPPLIER_ICO%22,%20%22ICO_DARCU%22,%20%22FIRMA_DARCU%22%20from%20%22ea9b28eb-da22-4594-9fb7-4609e8507982%22,%20%22c6639c7e-92ad-4b31-8f74-e46eff96825a%22%20WHERE%20%22SUPPLIER_ICO%22%20=%20%22ICO_DARCU%22%20AND%20%22ICO_DARCU%22%20!=%20%27%27%20LIMIT%202

(náš dopyt sme len vložili za '...?sql=')

Takže, hor sa na dopytovanie otvorených údajov.