Loading radio.garden into SQLite using jq#

http://radio.garden/ is an amazing website which displays a 3D globe covered in radio stations and lets you listen to any of them.

I wanted to have a play around with their data. I fired up the Firefox Developer Tools, switched to the Network tab and sorted by largest first, which revealed this URL to a 1.4MB JSON file:

1
http://radio.garden/api/ara/content/places

I ran that through jq to pretty print it and see what it looked like:

1
% cat places | jq | head -n 20
2
{
3
  "apiVersion": 1,
4
  "version": "e56d3ed3",
5
  "data": {
6
    "list": [
7
      {
8
        "id": "GBy0N9TE",
9
        "title": "Sukhumi",
10
        "country": "Abkhazia, Georgia",
11
        "url": "/visit/sukhumi/GBy0N9TE",
12
        "size": 1,
13
        "boost": true,
14
        "geo": [
15
          41.023,
16
          43.001
17
        ]
18
      },
19
      {
20
        "id": "PbqG2Mmi",
21
        "title": "Ghazni",

I need a flat JSON list to load this into sqlite-utils. I used jq to reshape the data like so:

1
% cat places | jq '[.data.list[] | {
2
    id: .id,
3
    title: .title,
4
    country: .country,
5
    url: .url,
6
    size: .size,
7
    latitude: .geo[1],
8
    longitude: .geo[0]
9
}]' | head -n 20
10
[
11
  {
12
    "id": "GBy0N9TE",
13
    "title": "Sukhumi",
14
    "country": "Abkhazia, Georgia",
15
    "url": "/visit/sukhumi/GBy0N9TE",
16
    "size": 1,
17
    "latitude": 43.001,
18
    "longitude": 41.023
19
  },
20
  {
21
    "id": "PbqG2Mmi",
22
    "title": "Ghazni",
23
    "country": "Afghanistan",
24
    "url": "/visit/ghazni/PbqG2Mmi",
25
    "size": 1,
26
    "latitude": 33.545,
27
    "longitude": 68.417
28
  },
29
  {

Then I piped that result to sqlite-utils:

1
% cat places | jq '[.data.list[] | {
2
    id: .id,
3
    title: .title,
4
    country: .country,
5
    url: .url,
6
    size: .size,
7
    latitude: .geo[1],
8
    longitude: .geo[0]
9
}]' | sqlite-utils insert radio.db stations - --pk=id

Here’s the whole process as a one-liner:

1
curl http://radio.garden/api/ara/content/places | jq '[.data.list[] | {
2
    id: .id,
3
    title: .title,
4
    country: .country,
5
    url: .url,
6
    size: .size,
7
    latitude: .geo[1],
8
    longitude: .geo[0]
9
}]' | sqlite-utils insert radio.db stations - --pk=id

I then opened it in Datasette so I could do things like see it on a map and facet by country:

1
datasette install datasette-cluster-map
2
datasette radio.db -o