the austrian Rundfunk und Telekom Regulierungs-GmbH provides a lot of open data. nice!
but who wants to deal with json or csv? i wrote a little importer in ruby that takes json data as provided by the rtr and builds the corresponding sql statements to put this into a database. that way, we can run some GROUP BY queries and add up some numbers!
the thing that sparked my interest initially, which is also downloaded as demo in this repository, is the Förderung Digitale Transformation.
please wear a helmet, no SQL sanitization is applied, only run this against trusted data!
in general, this should work for any json returned by RTR, but it's more a proof of concept than anything else.
- clone the repository
- install the right ruby version. please look up how to do this yourself :)
bundle install- put your json into the
data/folder - adapt
main.rbto your new file and table name. - run
bundle exec rake run– this should now create two files in the out folder: one to create the table, and one to insert all the data. - if you have no entry in your json without
NULLvalues, add a dummy entry so the script can derive the correct data types. - please note, again that – no SQL sanitization is applied, only run this against trusted data!
this is all made for sqlite3. do the following
- clone the repository
- hopefully, your OS includes
sqlite3 - create a new database and pass in the create-table script:
sqlite3 test.db < 2023-12-29-create-table.sql - feed in the data:
sqlite3 test.db < 2023-12-29-insert-into.sql - open a shell in
sqlite3 test.db - run queries like:
SELECT foerderungsnehmer, sum(foerderbetrag) AS foerderung FROM rtr_data GROUP BY foerderungsnehmer HAVING foerderung > 1000000 ORDER BY foerderung DESC; - just have a look at the json or the create table to see what data is available
everything in here is BSD licensed. the actual data received from RTR is »Freie Werke gemäß § 7 Urheberrechtsgesetz«, according to the metadata at: Förderung Digitale Transformation.