convert flight json data to csv for sqlite import

[rochaporto]

Sample json entry, as stored after netcoupe collection:

[
  {
    "URL": "http://archive2016.netcoupe.net/Results/FlightDetail.aspx?FlightID=11399",
    "ID": "11399",
    "Pilot": "Peter HABERKERN",
    "Club": "Aéroclub International Sisteron",
    "Date": "2016-07-07T00:00:00Z",
    "Takeoff": "Baumerlenbach",
    "Region": "",
    "Country": "Allemagne",
    "Distance": 57.599998474121094,
    "Points": 58.18000030517578,
    "Glider": "Taurus",
    "Type": "Libre",
    "TrackURL": "http://archive2016.netcoupe.net/Download/DownloadIGC.aspx?FileID=14657",
    "TrackID": "14657",
    "CompetitionID": "",
    "CompetitionURL": "",
    "Speed": 54.34000015258789,
    "Comments": ""
  },
...
]

[rochaporto]

-r option covers for quoting the comments field - due to newlines.

cat 2016.json | jq -r '.[] | [ 2016, .ID, .Pilot, .Crew, .Club, .Date, .Takeoff, .Region, .Country, .Distance, .Points, .Glider, .Type, .TrackURL, .TrackID, .CompetitionID, .CompetitionURL, .Speed, .URL, .Comments] | @csv'  > ~/netcoupe-2016.csv

[rochaporto]

Assuming the following sqlite schema:

CREATE TABLE flight (
    year               INTEGER,
    id                 INTEGER, 
    pilot              TEXT,
    crew               TEXT,
    club               TEXT,
    date               TEXT,
    takeoff            TEXT,
    region             TEXT,
    country            TEXT,
    distance           REAL,
    points             REAL,
    glider             TEXT,
    type               TEXT,
    track_url          TEXT UNIQUE,
    track_id           INTEGER UNIQUE,
    competition_id     INTEGER,
    competition_url    TEXT,
    speed              REAL,
    url                TEXT UNIQUE,
    comments           TEXT,
    PRIMARY KEY (id, year)
)

[rochaporto]

sqlite3 ~/ezgliding.db < sqlite-schema.sql
sqlite> .mode csv
sqlite> .import 2016.csv flight
sqlite> select count(*) from flight;
25119

[rochaporto]

Client parsing implemented in 9a0e09d2b9a90ed0be6723d1e4da2a22362f4993

[rochaporto]

An alternative with a simple python script: https://gist.github.com/rochaporto/721a9bd48ee245d923e20c8ca6bb3013

A Go version would be much more efficient, just leaving this here as an example.