Analiza podatkov o končnih lastnikih podjetij registriranih v Veliki Britaniji
Na spletni strani Companies House, gre za agencijo britanskega ministrstva za gospodarstvo, so objavljeni podatki o končnih lastnikih podjetij (t.i. ekonomskih upravičencev), ki so registrirana v Veliki Britaniji.
Pomembno je poudariti, da ta baza ne predstavlja britanskega poslovnega registra, pač pa je baza nastala zaradi prenosa EU direktive za preprečevanje pranja denarja in financiranja terorizma iz 2015. Velika Britanija je namreč prva država, ki je register popolnoma odprla javnosti (k temu so se zavezali že leta 2013).
Novinarska objava tim. Panama Papers pa je (posredno) spodbudila Evropsko komisijo, da je začela pripravljati še ostrejšo verzijo direktive. Lanska direktiva namreč v 14. členu zahteva, da mora biti register prosto dostopen državnim organom, ostalim pa če izrazijo “legitimen interes”, seveda pa se države lahko prostovoljno odločijo tudi za večjo transparentnost. Predlog, ki ga Evropska komisija pripravlja sedaj, pa bo zahteval, da so registri obvezno javni.
Podobna zakonodaja se pripravlja tudi v Sloveniji, v parlamentarni obravnavi bo predvidoma v jeseni, po predlogu vlade pa bo register javen.
Ker so objavljeni podatki v JSON formatu, je to odlična priložnost, da si v tokratnem prispevku ogledamo kako podatke prenesti v PostgreSQL bazo in v njej poiskati slovenske državljane.
Uvoz podatkov v bazo
JSON (JavaScript Object Notation) je preprost format za izmenjavo podatkov, a za razliko od podatkov v “klasičnih” podatkovnih bazah ni urejen v tabelarično obliko, pač pa je oblika lahko nekoliko bolj kompleksna. PostgreSQL je podporo za JSON dobil v različici 9.2, različica 9.4 je prinesla nov podatkovni tip JSONB, ukazi uporabljeni v nadaljevanju pa zahtevajo PostgreSQL različice 9.5.
Podatke iz spletne strani najprej prenesemo na računalnik. Baza s podatki se osvežuje vsakodnevno (pred 10h uro), v kompresirani obliki pa je velika slabih 70 Mb. Ime datoteke je sestavljeno v obliki “persons-with-significant-control-snapshot-”
wget http://download.companieshouse.gov.uk/persons-with-significant-control-snapshot-`date +"%Y-%m-%d"`.zip
Podatke sedaj uvozimo v bazo podatkov. Najprej ustvarimo bazo podatkov z npr. imenom companies:
sudo su su - postgres psql CREATE DATABASE companies OWNER matej;
Nato kot ciljni uporabnik (matej) vstopimo v bazo:
psql companies
Nato ustvarimo novo tabelo z imenom psc:
create table psc (line jsonb);
… in vanjo preberemo podatke, ki smo jih prej prenesli iz spleta:
\copy psc from program 'zcat persons-with-significant-control-snapshot-`date +"%Y-%m-%d"`.zip'
Sedaj si pogledamo prvo vrstico v tabeli psc, toliko, da vidimo strukturo:
select jsonb_pretty(line) from psc limit 1;
Dobimo približno takle izpis:
jsonb_pretty
-----------------------------------------------------------------------------------------------------------------
{
"data": {
"etag": "***",
"kind": "individual-person-with-significant-control",
"name": "***",
"links": {
"self": "/company/***"+
},
"address": {
"region": "***",
"country": "***",
"locality": "***",
"premises": "2",
"postal_code": "***",
"address_line_1": "***"
},
"nationality": "British",
"notified_on": "2016-04-06",
"date_of_birth": {
"year": 1976,
"month": 5
},
"name_elements": {
"surname": "***",
"forename": "***",
"middle_name": "***"
},
"natures_of_control": [
"ownership-of-shares-75-to-100-percent"
],
"country_of_residence": "United Kingdom"
},
"company_number": "***"
}
(1 row)
Sedaj podatke iz JSON oblike shranimo v običajno tabelo, ki jo bomo poimenovali psc_table:
select
line#>>'{data,etag}' as etag,
line#>>'{data,kind}' as kind,
line#>>'{data,name}' as name,
line#>>'{data,address,region}' as address_region,
line#>>'{data,address,country}' as address_country,
line#>>'{data,nationality}' as nationality,
line#>>'{data,notified_on}' as notified_on,
line#>>'{data,date_of_birth,year}' as birth_year,
line#>>'{data,date_of_birth,month}' as birth_month,
line#>>'{data,name_elements,surname}' as name_surname,
line#>>'{data,name_elements,forename}' as name_forename,
line#>>'{data,name_elements,middle_name}' as name_middle_name,
line#>>'{data,natures_of_control}' as natures_of_control,
line#>>'{data,country_of_residence}' as country_of_residence,
line#>>'{company_number}' as company_number
into psc_table
from psc;
Podatki so s tem uvoženi v tabelo psc_table. V primeru, da določen “stolpec” vsebuje več podatkov, so le-ti ločeni z vejico. Primer:
["ownership-of-shares-25-to-50-percent", "voting-rights-25-to-50-percent", "significant-influence-or-control"]
Izbor Slovencev iz baze
V naslednjem koraku iz tabele izberemo vse zapise, kjer je narodnost osebe slovenska, država bivanja Slovenija ali se naslov osebe nahaja v Sloveniji:
select * from psc_table where (nationality ~* 'sloven') or (country_of_residence ~* 'sloven') or (address_country ~* 'sloven');
Baza nam vrne 85 zapisov, ki jih sedaj lahko izvozimo v datoteko:
\COPY (select * from psc_table where (nationality ~* 'sloven') or (country_of_residence ~* 'sloven') or (address_country ~* 'sloven')) to 'slovenistan.csv' with csv header delimiter E'\t';
Podatki so s tem izvoženi v datoteko slovenistan.csv, ki je tab-delimited CSV datoteka. Odpremo jo lahko v LibreOffice Calc ali v Excelu.
* * *
Mimogrede – obdelava podatkov (uvoz in izbor) v PostgreSQL traja nekaj minut. Enaka obdelava podatkov z orodjem Microsoft Excel je trajala več kot en dan…
Zahvala Gašperju za pomoč in TI Slovenija za informacijo o bazi.
Ključne besede: baze podatkov, PostgreSQL