Registrirani avtomobili v Sloveniji. Kako priti do podatkov?

V Sloveniji imamo spletno stran z naslovom OPSI. Gre za portal, kjer so objavljeni raznovrstni odprti podatki, med njimi tudi evidenca vseh registriranih vozil in opravljenih tehničnih pregledov. Ti podatki so v prvotni obliki precej katastrofalni. Vse je razmetano po nekih csv datotekah, veliko je tekstovnih napak, manjkajočih vrednosti, ni nobenih metapodatkov itd.

Pred nekaj tedni sem na Reddit objavil dokumentacijo za nek mini api, ki omogoča enostavnejši način pridobitve teh podatkov. Eden izmed komentarjev je bil, da je zadeva ok, če iščeš kaj specifičnega npr. iskanje po VIN številki, ni pa uporabno za izvajanje nekih statističnih analiz, kar je pravzaprav bil moj primarni cilj, da sem se sploh začel “zajebavati” s tem side projektom…

Ker ta cilj očitno ni bil ravno dosežen, sem se odločil, da se problema lotim malo drugače. Sprva sem razmišljal, da vse podatke vržem na Google BigQuery, ki bi služil kot podatkovno skladišče in bi uporabnikom omogočal poganjanje sql poizvedb. Vse lepo in fino dokler ne odpreš cenika. Imajo sicer free tier, ampak glede na količino podatkov, s katerimi razpolagam, ne bi bilo poceni, kaj šele zastonj. To rešitev sem zato hitro črtal iz seznama. Zaradi večje količine podatkov in s tem prevelikih stroškov gostovanja odpadejo praktično vsi oblačni ponudniki, ki ponujajo svoja podatkovna skladišča. Prav tako odpade tudi ideja o kakršnikoli self-hosting rešitvi, ker je zadnja stvar, ki si jo želim, vzdrževanje nekih lastnih ali zakupljenih strežnikov za ta hobi projektič (oz. za kateregakoli…). In kaj potem sploh še ostane?

Skupaj sem vrgel mini spletno aplikacijo, ki omogoča analiziranje in izvoz željenih podatkov. Zaradi vseh omejitev, ki sem jih zgoraj naštel, je potrebno ob prvem obisku vse podatke prenesti na računalnik. Nato lahko po njih poljubno brskamo in jih analiziramo z uporabo SQL-a. Pri tem se vse izvaja “client-side”, kar pomeni, da v ozadju ni nobenih strežnikov, torej tudi ni nobenih stroškov. Vsi podatki so shranjeni v eni parquet datoteki (~400MB). Povezavo do te datoteke, skupaj z vso kodo, lahko najdeš v tem repozitoriju. Notri je tudi skripta, ki je bila uporabljena za pripravo podatkov. Aplikacija pa je dostopna na tem naslovu: https://vozila-slo-app.netlify.app/

Screenshot

Še kratek vodič za uporabo spletne aplikacije

V primeru, da aplikacijo uporabljaš prvič, moraš najprej prenesti vse podatke. To narediš s klikom na gumb “Prenesi celotno bazo”. V mojem primeru prenos traja ~1min. Baza se shrani v brskalnik, tako, da jo ob naslednji uporabi ni potrebno ponovno prenašati. Vsi podatki so shranjeni v eni .parqeut datoteki, ki je shranjena na Dropbox-u. Dostopna je tukaj. Celotna skripta, ki je bila uporabljena za pripravo podatkov je dostopna v mapi data_prep_notebooks. Trenutno sta v bazi samo kategoriji M1 in M1G (to so osebni avtomobili) od leta 2016 do 2022. Podatki za celotno leto 2023 še niso objavljeni.

Primeri poizvedb:

Ime tabele, po kateri lahko delamo poizvedbe je data.parquet. Tabela vsebuje očiščene podatke o registriranih vozilih od leta 2016 dalje. Kot je že zgoraj napisano, je pomembno, da vemo, da je lahko vsak avto v tabeli zabeležen večkrat.

Poglejmo si kar na primeru. Če poženem poizvedbo;

SELECT * FROM 'data.parqeut'
WHERE vin='xxxxxxxxxxxxxxxxx'

dobim rezultat:

+-----------+-------+------------+
| vin       | ...   | leto_zapisa|
+-----------+-------+------------+
| xxxxxxx...| ...   | 2016       |
+-----------+-------+------------+
| xxxxxxx...| ...   | 2017       |
+-----------+-------+------------+
| xxxxxxx...| ...   | 2018       |
+-----------+-------+------------+
| xxxxxxx...| ...   | 2019       |
+-----------+-------+------------+

To pomeni, da je bil avto z vin xxxxxxxxxxxxxxxxx registriran v letih 2016, 2017, 2018 in 2019. To je pomembno zato, ker v primeru, da želim narediti določeno statistiko, npr. za leto 2019, moram k poizvedbi vedno dodati:

WHERE leto_zapisa = 2019 --oz. katero drugo leto, ki nas zanima

Recimo, da me zanima število električnih Volkswagnov v zadnjem letu (trenutno je to leto 2022, ker podatki za 2023 še niso na voljo).
Če poženem poizvedbo;

-- Ta poizvdeva NI OK
SELECT * FROM 'data.parquet'
WHERE gorivo = 'ni goriva' AND znamka = 'volkswagen';

ne bom dobil pravilnega odgovora, kajti v tej poizvedbi se lahko isti avto pojavi večkrat zaradi zgoraj omenjenega dejstva.

Spodaj pa je pravilna poizvedba, če želimo izpisati vse električne Volkswagne v letu 2022:

SELECT * FROM 'data.parquet'
WHERE leto_zapisa = 2022 AND gorivo = 'ni goriva' AND znamka = 'volkswagen';

Če pa bi želel enako statistiko za leto 2019:

SELECT * FROM 'data.parquet'
WHERE leto_zapisa = 2019 AND gorivo = 'ni goriva' AND znamka = 'volkswagen';

Ta poizvedba pa izpiše število e-golfov po posameznih letih:

SELECT leto_zapisa, COUNT(*) AS stevilo FROM 'data.parquet'
WHERE model_simple = 'e-golf'
GROUP BY leto_zapisa
ORDER BY leto_zapisa DESC;s

Kako filtrirati po datumu?:

-- filtriraj po letu
-- ta izpiše prvih 5 avtomobilov iz leta 2022, ki so imeli prvo registracijo leta 2010
SELECT * FROM 'data.parquet'
WHERE EXTRACT(YEAR FROM prva_registracija) = 2010 AND leto_zapisa = 2022
LIMIT 5;

-- filtriraj po mesecu
-- ta izpiše prvih 5 avtomobilov iz leta 2022, ki so imeli prvo registracijo meseca oktobra
SELECT * FROM 'data.parquet'
WHERE EXTRACT(MONTH FROM prva_registracija) = 10 AND leto_zapisa = 2022
LIMIT 5;

Kakršenkoli feedback je dobrodošel.

Lp.