Ajoneuvodataa ja sql:ää

Toinen aineisto, open-data, jota esittelin laivaseminaarissa oli Trafin avoin data
ajoneuvorekisteristä.

Se löytyy:
https://www.trafi.fi/tietopalvelut/avoin_data

ja on ladattavissa zippinä.

- Aineistossa on rivejä 5 013 460 kpl
- Aineiston päivämäärä 31.3.2018
- Julkaisuajankohta 25.4.2018
- Tiedostomuoto: ZIP-pakattu CSV
- Tiedoston koko: 829 MT (pakattuna 235 MT)
- Aineisto on lisensoitu Creative Commons Nimeä 4.0 Kansainvälinen -lisenssillä

Testailin sitä lähinnä siinä mielessä, että missä menee Survon kapasiteetin raja -
ja miten Survo-R:n sqlite-pakettia voi hyödyntää apuna.

Zippi pitää sisällään csv-tiedoston, joka on ihan siistin ja survo-kelpoisen näköinen semicolon-eroteltu
tekstitiedosto.

SHOWlla katsoessa kannattaa huomioida pari asiaa - toinen on se, että useimmilla varmaa
rajoituksena on näyttää 2 M riviä - rajan saa muutettua system-komennolla

SYSTEM max_show_lines=5000000

toinen huomioitava asia on se, että SHOWlla kestää jonnin aikaa, jos esimerkiksi
haluaa siirtyä tiedoston loppuun.

File save vie tekstitiedoston muutamassa minuutissa svo-tiedostoksi,

*VARLEN=200
*TIME COUNT START
*FILE SAVE AT52.csv TO NEW KOE  /  DELIMITER=;
*TIME COUNT END   204.335
#
*204(s:hms)=00:03:24

Sitten kokeilin tuplata tiedoston - ja tuplauksen jälkeen svon raja tuli vastaan -
näkyi siten, että
FILE SHOW -kaatoi Survon.

Jostain Reijon vanhasta viestistä kopioin, että ainakin seuraavat paketit
tarvitaan:

install.packages("sqldf")
install.packages("RSQLite")

Erilaisten hapuilujen jälkeen sain luotua tietokannan, taulun ja
vietyä dataa sinne:

*
R library(sqldf)
*
*
Rdb <- dbConnect(SQLite(), dbname="Testi.db")
*
*
*
*show k.csv
* ensin pienempi testiaineisto:
Rread.csv2.sql("k.csv", sql = "create table at1   as select * from file",  dbname = "Testi.db")
*
RdbListTables(db)
* sitten lisää...
Rread.csv2.sql("k2.csv", sql = "insert into at1   select * from file",  dbname = "Testi.db", append=TRUE)
*
*
Se, mikä minua erityisesti viehättää tässä Survon SQL.ssä, että kyseskriptejä voi kirjoittaa
survolla, ja tuottaa otuputin suoraan survotiedostoksi, eli esim

*FILE SQL KYSELY1 TO NEW KYS1   /   DATABASE=Testi.db
*
*QUERY KYSELY1
* SELECT COUNT(*) AS LKM FROM at1;
*END
*
*FILE SHOW KYS1
*

Seuraavaksi kokeilin, miten onnistuu kysely, kun ko. Trafi-aineisto on viety tuplana tietokantaa


*/activate a
aTIME COUNT START
aFILE DEL T10
aFILE SQL TAJO  TO NEW T10  / DATABASE=Testi.db
aTIME COUNT END   853.763
#
*
*853(s:hms)=00:14:13   (ei mennyt näin kauaa, kävin juomassa kupin kahvia välillä!)
*
*FILE SHOW T10
*
*
*QUERY TAJO
*SELECT ajoneuvoluokka,ajoneuvoryhma,SUBSTR(ensirekisterointipvm,1,4),
*merkkiSelvakielinen,mallimerkinta,count(mallimerkinta)
*FROM TEST
* GROUP BY ajoneuvoluokka,ajoneuvoryhma,SUBSTR(ensirekisterointipvm,1,4),
*merkkiSelvakielinen,mallimerkinta
* ORDER BY ajoneuvoluokka,ajoneuvoryhma,merkkiSelvakielinen;
*END
*
*
Pitkät muuttujanimet aiheuttivat pienen herjan survo-tiedoston syntyessä
kyselyn tuloksena, minkä vuoksi seuraava update on tarpeen.


*FILE update T10
* Copied from R data frame resu
*FIELDS: (active)
*   1 SA_   3 aluokka
*   2 NA_   2 ajoneuvoryhma
*   3 SA_   4 VV
*   4 SA_  43 merkkiSelvakielinen
*   5 SA_ 105 mallimerkinta
*   6 NA_   2 count
*END


*
*MTAB T10 CUR+2  /  VARIABLES=count  OPTIONS=SUMS
*
*Sums
*
*         count
*
* S    10026920
*
*
*
Tässähän nyt ei vielä ole hyödynnetty mitään esimerkiksi SQL:n relaatiotaulujen käyttöä,
vaan oli lähinnä koe / opettelu, miten Rsqliten ja Survon yhteispeli sujuu.

Tästähän on alunperin Reijo viestinyt
Feb 12, 2013; 11:21am Musteen uusi FILE SQL

ja jonkinlaista keskustelua siinä ketjussa käyty (Reijo ja Petri) vuosina 2014 ja 2015.

Olisi varmaan hyvä, jos toimivia esimerkkejä saataisiin malliksi palstalle lisääkin...

**