• datasette
  • Database Setup
    • Use snake_case for all file names
    • Earth
      • Cumulative CO2 emissions
      • Population, Historic and Projected
    • Stroll
      • Fetching *.gpx files
      • Importing *.gpx files
      • Selecting some GPX tracks with metadata on individual points
  • Pitalls
    • My queries are slow, what do I do?

datasette

This repository contians everything you need to deploy SQLite databases to dokku via https://datasette.io/.

• All files matching `db/*.db` will get exposed in the interface. Simply add your new database if all you want to do is make it accessible through the web.
• You can edit `metadata.yml` to add descriptions, link to sources and add predefined queries.
• The `requirements.txt` contains additional packages such as plugins. Please add them manually and without a version string because python package management is kind of messy.
• The `CHECKS` file contains a URL and an expected string that is returned in this URLs HTTP response. This is added as a post-deployment sanity check and you shouldn't need to change it.
• Optional: If anything should happen after deployment, edit the `bin/post_compile` script. You can use this to fetch data from other sources for example.

The databases are assumed to be immutable or read-only. This allows us to use efficient caching by configuring nginx as a caching reverse proxy and serving content a static cache. This effectively that queries often only have to be run the first time after the database has changed and are afterwards served from a file-system cache.

Database Setup

This section aims to contain all information needed to convert data from their respective source files to SQLite databases. This should make the updating process easier when the sources change

Use snake_case for all file names

cd sources
for file in $(fd --type f); do
    mv $file $(basename $file | sed 's/-/_/g')
done

Earth

Cumulative CO2 emissions

Source: https://ourworldindata.org/grapher/cumulative-co-emissions

csvs-to-sqlite \
    --shape 'Entity:entity(text),Code:code(text),Year:year(integer),Cumulative CO2 emissions:cumulative_co2_emissions(real)' \
    --index entity \
    --index code \
    --index year \
    --replace-tables \
    sources/cumulative_co2_emissions.csv  dbs/earth.db

Loaded 1 dataframes
Created dbs/earth.db from 1 CSV file

Population, Historic and Projected

sed -i 's/"Population by country and region, historic and projections (Gapminder, HYDE & UN)"/Population/' sources/population.csv
csvs-to-sqlite \
    --shape 'Entity:entity(text),Code:code(text),Year:year,Population:population(integer)' \
    --index entity \
    --index code \
    --index year \
    --replace-tables \
    sources/population.csv  dbs/earth.db

Loaded 1 dataframes
Added 1 CSV file to dbs/earth.db

Stroll

This database is created from a series of GPX file which I track on my phone using Osmand. They are in turn synced to my Odroid HC2 to which I have access from wherever.

strolling.db

Fetching *.gpx files

mkdir -p sources/strolling
rsync -srv arne@timbre.arnes.space:osmand-tracks/rec/ sources/strolling/

receiving incremental file list
2019-06/2019-06-09_11-54_Sun.gpx
2019-06/2019-06-09_19-22_Sun.gpx
2019-06/2019-06-10_11-57_Mon.gpx
2019-06/2019-06-17_19-56_Mon.gpx
2019-06/2019-06-26_07-54_Wed.gpx
2019-06/2019-06-27_12-26_Thu.gpx
2019-06/Kanufahrt auf der Lahn.gpx
2019-08/2019-08-16_08-15_Fri.gpx
2019-08/2019-08-20_08-42_Tue.gpx
2019-08/2019-08-22_08-21_Thu.gpx
2019-08/2019-08-22_14-15_Thu.gpx
2019-08/2019-08-23_09-00_Fri.gpx
2019-08/2019-08-24_08-42_Sat.gpx
2019-08/2019-08-24_18-07_Sat.gpx
2019-08/2019-08-25_10-39_Sun.gpx
2019-08/2019-08-25_23-37_Sun.gpx
2019-08/2019-08-26_12-25_Mon.gpx
2019-08/2019-08-27_06-28_Tue.gpx
2019-08/2019-08-27_09-07_Tue.gpx
2019-08/2019-08-27_19-53_Tue.gpx
2019-08/2019-08-28_08-26_Wed.gpx
2019-08/2019-08-28_11-33_Wed.gpx
2019-09/2019-09-02_18-19_Mon.gpx
2019-09/2019-09-04_07-36_Wed.gpx
2019-09/2019-09-19_08-31_Thu.gpx
2019-09/2019-09-21_11-30_Sat.gpx
2019-09/2019-09-29_16-52_Sun.gpx
2019-09/2019-09-29_18-54_Sun.gpx
2019-10/2019-10-02_08-42_Wed.gpx
2019-10/2019-10-02_08-57_Wed.gpx
2019-10/2019-10-03_14-04_Thu.gpx
2019-10/2019-10-09_09-05_Wed.gpx
2019-10/2019-10-12_09-57_Sat.gpx
2019-10/2019-10-14_19-46_Mon.gpx
2019-10/2019-10-14_19-58_Mon.gpx
2019-10/2019-10-18_08-49_Fri.gpx
2019-10/2019-10-29_09-56_Tue.gpx
2019-10/2019-10-31_07-45_Thu.gpx
2019-11/2019-11-01_19-19_Fri.gpx
2019-11/2019-11-08_09-57_Fri.gpx
2019-11/2019-11-09_10-21_Sat.gpx
2019-11/2019-11-13_09-17_Wed.gpx
2019-12/2019-12-04_18-49_Wed.gpx
2019-12/2019-12-07_09-25_Sat.gpx
2019-12/2019-12-12_07-50_Thu.gpx
2019-12/2019-12-18_07-59_Wed.gpx
2019-12/2019-12-20_08-47_Fri.gpx
2020-01/2020-01-02_20-31_Thu.gpx
2020-01/2020-01-06_13-48_Mon.gpx
2020-01/2020-01-09_20-10_Thu.gpx
2020-01/2020-01-12_09-48_Sun.gpx
2020-01/2020-01-15_07-45_Wed.gpx
2020-01/2020-01-17_08-28_Fri.gpx
2020-01/2020-01-20_09-43_Mon.gpx
2020-01/2020-01-22_07-38_Wed.gpx
2020-01/2020-01-27_09-35_Mon.gpx
2020-01/2020-01-31_07-21_Fri.gpx
2020-02/2020-02-01_10-54_Sat.gpx
2020-02/2020-02-04_08-01_Tue.gpx
2020-02/2020-02-23_11-52_Sun.gpx
2020-02/2020-02-24_09-17_Mon.gpx
2020-02/2020-02-26_09-03_Wed.gpx
2020-03/2020-03-02_10-00_Mon.gpx
2020-03/2020-03-04_08-43_Wed.gpx
2020-03/2020-03-06_08-59_Fri.gpx
2020-03/2020-03-09_08-46_Mon.gpx
2020-03/2020-03-11_08-07_Wed.gpx
2020-03/2020-03-14_11-41_Sat.gpx
2020-03/2020-03-16_09-20_Mon.gpx
2020-03/2020-03-18_09-31_Wed.gpx
2020-03/2020-03-19_14-11_Thu.gpx
2020-03/2020-03-20_09-13_Fri.gpx
2020-03/2020-03-22_10-57_Sun.gpx
2020-03/2020-03-23_09-11_Mon.gpx
2020-03/2020-03-24_17-27_Tue.gpx
2020-03/2020-03-25_18-37_Wed.gpx
2020-03/2020-03-26_08-53_Thu.gpx
2020-03/2020-03-28_11-22_Sat.gpx
2020-03/2020-03-30_08-12_Mon.gpx
2020-04/2020-04-01_08-52_Wed.gpx
2020-04/2020-04-03_08-18_Fri.gpx
2020-04/2020-04-05_12-58_Sun.gpx
2020-04/2020-04-07_16-48_Tue.gpx
2020-04/2020-04-08_07-52_Wed.gpx
2020-04/2020-04-10_08-49_Fri.gpx
2020-04/2020-04-11_11-57_Sat.gpx
2020-04/2020-04-12_10-43_Sun_2.gpx
2020-04/2020-04-14_09-02_Tue.gpx
2020-04/2020-04-17_09-15_Fri.gpx
2020-04/2020-04-18_11-42_Sat.gpx
2020-04/2020-04-19_10-24_Sun.gpx
2020-04/2020-04-21_08-45_Tue.gpx
2020-04/2020-04-23_08-51_Thu.gpx
2020-04/2020-04-24_12-40_Fri.gpx
2020-04/2020-04-27_10-31_Mon.gpx
2020-04/2020-04-29_16-14_Wed.gpx
2020-05/2020-05-06_07-15_Wed.gpx
2020-05/2020-05-08_11-54_Fri.gpx
2020-05/2020-05-08_12-36_Fri.gpx
2020-05/2020-05-09_12-41_Sat.gpx
2020-05/2020-05-11_18-27_Mon.gpx
2020-05/2020-05-12_19-26_Tue.gpx
2020-05/2020-05-13_09-01_Wed.gpx
2020-05/2020-05-18_15-52_Mon.gpx
2020-05/2020-05-20_09-04_Wed.gpx
2020-05/2020-05-23_11-16_Sat.gpx
2020-05/2020-05-25_09-09_Mon.gpx
2020-05/2020-05-27_09-08_Wed.gpx
2020-05/2020-05-29_08-40_Fri.gpx
2020-06/2020-06-01_11-14_Mon.gpx
2020-06/2020-06-03_08-50_Wed.gpx
2020-06/2020-06-06_11-21_Sat.gpx
2020-06/2020-06-10_09-00_Wed.gpx
2020-06/2020-06-11_19-53_Thu.gpx
2020-06/2020-06-15_19-19_Mon.gpx
2020-06/2020-06-19_08-46_Fri.gpx
2020-06/2020-06-22_09-36_Mon.gpx
2020-06/2020-06-24_10-18_Wed.gpx
2020-06/2020-06-24_16-35_Wed.gpx
2020-06/2020-06-26_09-31_Fri.gpx
2020-06/2020-06-29_08-49_Mon.gpx
2020-06/2020-06-30_09-13_Tue.gpx
2020-06/2020-06-30_09-55_Tue.gpx
2020-07/2020-07-01_10-51_Wed.gpx
2020-07/2020-07-01_16-04_Wed.gpx
2020-07/2020-07-02_09-29_Thu.gpx
2020-07/2020-07-02_18-26_Thu.gpx
2020-07/2020-07-03_11-38_Fri.gpx
2020-07/2020-07-03_18-00_Fri.gpx
2020-07/2020-07-04_10-57_Sat.gpx
2020-07/2020-07-05_09-43_Sun.gpx
2020-07/2020-07-07_07-50_Tue.gpx
2020-07/2020-07-10_08-41_Fri.gpx
2020-07/2020-07-12_11-20_Sun.gpx
2020-07/2020-07-14_08-48_Tue.gpx
2020-07/2020-07-16_08-51_Thu.gpx
2020-07/2020-07-17_07-25_Fri.gpx
2020-07/2020-07-19_01-24_Sun.gpx
2020-07/2020-07-20_08-54_Mon.gpx
2020-07/2020-07-20_11-12_Mon.gpx
2020-07/2020-07-21_07-05_Tue.gpx
2020-07/2020-07-24_08-26_Fri.gpx
2020-07/2020-07-24_09-53_Fri.gpx
2020-07/2020-07-24_11-18_Fri.gpx
2020-07/2020-07-24_12-38_Fri.gpx
2020-07/2020-07-27_09-06_Mon.gpx
2020-07/2020-07-29_07-26_Wed.gpx
2020-07/2020-07-31_08-51_Fri.gpx
2020-08/2020-08-01_20-01_Sat.gpx
2020-08/2020-08-04_09-24_Tue.gpx
2020-08/2020-08-06_08-36_Thu.gpx
2020-08/2020-08-19_08-48_Wed.gpx
2020-09/2020-09-08_13-52_Tue.gpx
2020-09/2020-09-09_11-31_Wed.gpx
2020-09/2020-09-09_12-18_Wed.gpx
2020-10/2020-10-12_00-14_Mon.gpx
2020-10/2020-10-12_11-17_Mon.gpx
2020-10/2020-10-20_09-41_Tue.gpx
2020-10/2020-10-24_08-55_Sat.gpx
2020-10/2020-10-24_20-08_Sat.gpx
2020-10/2020-10-25_12-56_Sun.gpx
2020-10/2020-10-26_09-32_Mon.gpx
2020-10/2020-10-26_12-27_Mon.gpx
2020-10/2020-10-26_21-16_Mon.gpx
2020-10/2020-10-27_08-58_Tue.gpx
2020-10/2020-10-27_12-32_Tue.gpx
2020-10/2020-10-28_10-36_Wed.gpx
2020-10/2020-10-28_11-56_Wed.gpx
2020-10/2020-10-28_13-00_Wed.gpx
2020-10/2020-10-29_17-23_Thu.gpx
2020-10/2020-10-29_21-20_Thu.gpx
2020-10/2020-10-31_14-37_Sat.gpx
2020-11/2020-11-01_13-00_Sun.gpx
2020-11/2020-11-02_10-23_Mon.gpx
2020-11/2020-11-03_10-01_Tue.gpx
2020-11/2020-11-03_14-17_Tue.gpx
2020-11/2020-11-03_16-30_Tue.gpx
2020-11/2020-11-03_17-25_Tue.gpx
2020-11/2020-11-03_17-44_Tue.gpx
2020-11/2020-11-03_19-28_Tue.gpx
2020-11/2020-11-04_10-34_Wed.gpx
2020-11/2020-11-04_18-22_Wed.gpx
2020-11/2020-11-06_19-50_Fri.gpx
2020-11/2020-11-07_11-12_Sat.gpx
2020-11/2020-11-07_12-53_Sat.gpx
2020-11/2020-11-09_10-48_Mon.gpx
2020-11/2020-11-09_20-38_Mon.gpx
2020-11/2020-11-11_10-13_Wed.gpx
2020-11/2020-11-11_12-50_Wed.gpx
2020-11/2020-11-11_15-13_Wed.gpx
2020-11/2020-11-11_15-58_Wed.gpx
2020-11/2020-11-11_18-54_Wed.gpx
2020-11/2020-11-13_19-44_Fri.gpx
2020-11/2020-11-14_14-11_Sat.gpx
2020-11/2020-11-14_18-58_Sat.gpx
2020-11/2020-11-16_11-06_Mon.gpx
2020-11/2020-11-16_15-11_Mon.gpx
2020-11/2020-11-17_11-30_Tue.gpx
2020-11/2020-11-17_19-21_Tue.gpx
2020-11/2020-11-18_10-05_Wed.gpx
2020-11/2020-11-18_12-40_Wed.gpx
2020-11/2020-11-18_15-36_Wed.gpx
2020-11/2020-11-18_19-58_Wed.gpx
2020-11/2020-11-19_08-35_Thu.gpx
2020-11/2020-11-20_20-13_Fri.gpx
2020-11/2020-11-21_11-07_Sat.gpx
2020-11/2020-11-21_12-18_Sat.gpx
2020-11/2020-11-22_14-41_Sun.gpx
2020-11/2020-11-24_09-08_Tue.gpx
2020-11/2020-11-24_11-17_Tue.gpx
2020-11/2020-11-24_18-20_Tue.gpx
2020-11/2020-11-25_10-10_Wed.gpx
2020-11/2020-11-25_12-06_Wed.gpx
2020-11/2020-11-25_14-07_Wed.gpx
2020-11/2020-11-25_20-14_Wed.gpx
2020-11/2020-11-26_09-04_Thu.gpx
2020-11/2020-11-27_09-13_Fri.gpx
2020-11/2020-11-28_11-31_Sat.gpx
2020-11/2020-11-29_12-25_Sun.gpx
2020-12/2020-12-01_08-19_Tue.gpx
2020-12/2020-12-01_11-11_Tue.gpx
2020-12/2020-12-01_19-02_Tue.gpx
2020-12/2020-12-02_10-08_Wed.gpx
2020-12/2020-12-02_12-42_Wed.gpx
2020-12/2020-12-03_09-02_Thu.gpx
2020-12/2020-12-03_19-49_Thu.gpx
2020-12/2020-12-03_22-08_Thu.gpx
2020-12/2020-12-04_20-30_Fri.gpx
2020-12/2020-12-05_02-14_Sat.gpx
2020-12/2020-12-05_15-05_Sat.gpx
2020-12/2020-12-05_17-32_Sat.gpx
2020-12/2020-12-05_19-52_Sat.gpx
2020-12/2020-12-06_12-39_Sun.gpx
2020-12/2020-12-06_16-46_Sun.gpx
2020-12/2020-12-06_18-08_Sun.gpx
2020-12/2020-12-08_08-46_Tue.gpx
2020-12/2020-12-09_10-16_Wed.gpx
2020-12/2020-12-09_11-46_Wed.gpx
2020-12/2020-12-12_20-44_Sat.gpx
2020-12/2020-12-13_04-28_Sun.gpx
2020-12/2020-12-13_15-01_Sun.gpx
2020-12/2020-12-15_08-45_Tue.gpx
2020-12/2020-12-15_12-11_Tue.gpx
2020-12/2020-12-16_20-02_Wed.gpx
2020-12/2020-12-17_08-10_Thu.gpx
2020-12/2020-12-17_09-45_Thu.gpx
2020-12/2020-12-18_08-52_Fri.gpx
2020-12/2020-12-20_11-26_Sun.gpx
2020-12/2020-12-20_13-22_Sun.gpx
2020-12/2020-12-20_14-04_Sun.gpx
2020-12/2020-12-20_21-45_Sun.gpx
2020-12/2020-12-20_23-31_Sun.gpx
2020-12/2020-12-21_08-53_Mon.gpx
2020-12/2020-12-21_12-56_Mon.gpx
2020-12/2020-12-22_18-14_Tue.gpx
2020-12/2020-12-23_16-13_Wed.gpx
2020-12/2020-12-24_10-45_Thu.gpx
2020-12/2020-12-26_09-13_Sat.gpx
2020-12/2020-12-27_18-18_Sun.gpx
2020-12/2020-12-27_18-53_Sun.gpx
2020-12/2020-12-27_19-24_Sun.gpx
2020-12/2020-12-28_10-18_Mon.gpx
2020-12/2020-12-28_16-40_Mon.gpx
2020-12/2020-12-29_13-17_Tue.gpx
2020-12/2020-12-29_14-00_Tue.gpx
2020-12/2020-12-30_10-15_Wed.gpx
2020-12/2020-12-30_15-41_Wed.gpx
2020-12/2020-12-31_15-15_Thu.gpx
2020-12/2020-12-31_17-21_Thu.gpx
2021-01/2021-01-03_11-21_Sun.gpx
2021-01/2021-01-04_19-39_Mon.gpx
2021-01/2021-01-04_23-59_Mon.gpx
2021-01/2021-01-05_08-43_Tue.gpx
2021-01/2021-01-05_11-16_Tue.gpx
2021-01/2021-01-05_15-46_Tue.gpx
2021-01/2021-01-06_09-35_Wed.gpx
2021-01/2021-01-06_11-24_Wed.gpx
2021-01/2021-01-06_18-47_Wed.gpx
2021-01/2021-01-07_10-03_Thu.gpx
2021-01/2021-01-09_10-10_Sat.gpx
2021-01/2021-01-09_19-09_Sat.gpx
2021-01/2021-01-09_20-29_Sat.gpx
2021-01/2021-01-10_14-11_Sun.gpx
2021-01/2021-01-10_19-41_Sun.gpx
2021-01/2021-01-10_21-02_Sun.gpx
2021-01/2021-01-11_09-02_Mon.gpx
2021-01/2021-01-12_12-05_Tue.gpx
2021-01/2021-01-12_17-59_Tue.gpx
2021-01/2021-01-13_09-41_Wed.gpx
2021-01/2021-01-13_11-37_Wed.gpx
2021-01/2021-01-13_20-57_Wed.gpx
2021-01/2021-01-14_08-46_Thu.gpx
2021-01/2021-01-15_08-26_Fri.gpx
2021-01/2021-01-15_09-30_Fri.gpx
2021-01/2021-01-16_13-40_Sat.gpx
2021-01/2021-01-17_13-27_Sun.gpx
2021-01/2021-01-17_14-55_Sun.gpx
2021-01/2021-01-17_16-45_Sun.gpx
2021-01/2021-01-19_08-55_Tue.gpx
2021-01/2021-01-20_09-46_Wed.gpx
2021-01/2021-01-20_11-36_Wed.gpx
2021-01/2021-01-21_09-00_Thu.gpx
2021-01/2021-01-22_09-07_Fri.gpx
2021-01/2021-01-23_08-19_Sat.gpx
2021-01/2021-01-23_09-42_Sat.gpx
2021-01/2021-01-24_11-39_Sun.gpx
2021-01/2021-01-25_09-56_Mon.gpx
2021-01/2021-01-25_11-03_Mon.gpx
2021-01/2021-01-25_19-53_Mon.gpx
2021-01/2021-01-25_22-54_Mon.gpx
2021-01/2021-01-26_08-46_Tue.gpx
2021-01/2021-01-27_20-19_Wed.gpx
2021-01/2021-01-28_09-08_Thu.gpx
2021-01/2021-01-30_10-54_Sat.gpx
2021-01/2021-01-31_11-24_Sun.gpx
2021-02/2021-02-03_09-43_Wed.gpx
2021-02/2021-02-09_08-11_Tue.gpx
2021-02/2021-02-09_10-47_Tue.gpx
2021-02/2021-02-09_18-57_Tue.gpx
2021-02/2021-02-10_12-39_Wed.gpx
2021-02/2021-02-10_19-48_Wed.gpx
2021-02/2021-02-11_11-09_Thu.gpx
2021-02/2021-02-12_10-56_Fri.gpx
2021-02/2021-02-12_19-45_Fri.gpx
2021-02/2021-02-13_12-02_Sat.gpx
2021-02/2021-02-14_12-08_Sun.gpx
2021-02/2021-02-14_18-33_Sun.gpx
2021-02/2021-02-15_11-00_Mon.gpx
2021-02/2021-02-16_19-33_Tue.gpx
2021-02/2021-02-17_04-19_Wed.gpx
2021-02/2021-02-17_11-53_Wed.gpx
2021-02/2021-02-17_19-52_Wed.gpx
2021-02/2021-02-18_09-11_Thu.gpx
2021-02/2021-02-18_19-35_Thu.gpx
2021-02/2021-02-19_10-15_Fri.gpx
2021-02/2021-02-19_17-29_Fri.gpx
2021-02/2021-02-21_10-31_Sun.gpx
2021-02/2021-02-21_19-27_Sun.gpx
2021-02/2021-02-21_21-08_Sun.gpx
2021-02/2021-02-22_10-00_Mon.gpx
2021-02/2021-02-22_11-03_Mon.gpx
2021-02/2021-02-22_20-33_Mon.gpx
2021-02/2021-02-23_08-31_Tue.gpx
2021-02/2021-02-23_10-45_Tue.gpx
2021-02/2021-02-23_19-24_Tue.gpx
2021-02/2021-02-23_22-03_Tue.gpx
2021-02/2021-02-24_11-15_Wed.gpx
2021-02/2021-02-25_09-03_Thu.gpx
2021-02/2021-02-25_10-25_Thu.gpx
2021-02/2021-02-26_10-21_Fri.gpx
2021-02/2021-02-26_19-15_Fri.gpx
2021-03/2021-03-01_08-43_Mon.gpx
2021-03/2021-03-01_10-38_Mon.gpx
2021-03/2021-03-02_09-54_Tue.gpx
2021-03/2021-03-02_19-15_Tue.gpx
2021-03/2021-03-03_08-34_Wed.gpx
2021-03/2021-03-03_11-35_Wed.gpx
2021-03/2021-03-03_18-15_Wed.gpx
2021-03/2021-03-04_09-26_Thu.gpx
2021-03/2021-03-04_20-04_Thu.gpx
2021-03/2021-03-05_08-34_Fri.gpx
2021-03/2021-03-05_10-22_Fri.gpx
2021-03/2021-03-05_19-03_Fri.gpx
2021-03/2021-03-05_20-19_Fri.gpx
2021-03/2021-03-06_04-31_Sat.gpx
2021-03/2021-03-06_19-15_Sat.gpx
2021-03/2021-03-07_13-56_Sun.gpx
2021-03/2021-03-08_10-57_Mon.gpx
2021-03/2021-03-08_16-14_Mon.gpx
2021-03/2021-03-08_19-12_Mon.gpx
2021-03/2021-03-09_08-49_Tue.gpx
2021-03/2021-03-09_09-40_Tue.gpx
2021-03/2021-03-09_19-46_Tue.gpx
2021-03/2021-03-10_08-55_Wed.gpx
2021-03/2021-03-10_11-22_Wed.gpx
2021-03/2021-03-10_18-34_Wed.gpx
2021-03/2021-03-11_09-01_Thu.gpx
2021-03/2021-03-11_20-25_Thu.gpx
2021-03/2021-03-12_07-35_Fri.gpx
2021-03/2021-03-12_09-18_Fri.gpx
2021-03/2021-03-12_19-59_Fri.gpx
2021-03/2021-03-13_07-53_Sat.gpx
2021-03/2021-03-14_16-21_Sun.gpx
2021-03/2021-03-15_10-24_Mon.gpx
2021-03/2021-03-15_18-53_Mon.gpx
2021-03/2021-03-16_08-50_Tue.gpx
2021-03/2021-03-16_10-05_Tue.gpx
2021-03/2021-03-16_19-37_Tue.gpx
2021-03/2021-03-18_08-36_Thu.gpx
2021-03/2021-03-18_09-40_Thu.gpx
2021-03/2021-03-18_10-35_Thu.gpx
2021-03/2021-03-19_15-31_Fri.gpx
2021-03/2021-03-19_20-34_Fri.gpx
2021-03/2021-03-19_21-07_Fri.gpx
2021-03/2021-03-20_05-15_Sat.gpx
2021-03/2021-03-20_11-30_Sat.gpx
2021-03/2021-03-21_13-23_Sun.gpx
2021-03/2021-03-21_16-46_Sun.gpx
2021-03/2021-03-21_19-51_Sun.gpx
2021-03/2021-03-22_12-55_Mon.gpx
2021-03/2021-03-23_09-03_Tue.gpx
2021-03/2021-03-24_20-10_Wed.gpx
2021-03/2021-03-25_08-46_Thu.gpx
2021-03/2021-03-26_08-59_Fri.gpx
2021-03/2021-03-26_10-31_Fri.gpx
2021-03/2021-03-26_18-43_Fri.gpx
2021-03/2021-03-27_17-12_Sat.gpx
2021-03/2021-03-27_20-10_Sat.gpx
2021-03/2021-03-28_15-05_Sun.gpx
2021-03/2021-03-29_08-54_Mon.gpx
2021-03/2021-03-30_10-04_Tue.gpx
2021-03/2021-03-30_20-00_Tue.gpx
2021-03/2021-03-31_08-25_Wed.gpx
2021-04/2021-04-01_08-45_Thu.gpx
2021-04/2021-04-02_16-09_Fri.gpx
2021-04/2021-04-03_13-34_Sat.gpx
2021-04/2021-04-04_12-00_Sun.gpx
2021-04/2021-04-06_08-35_Tue.gpx
2021-04/2021-04-08_09-34_Thu.gpx
2021-04/2021-04-08_12-42_Thu.gpx
2021-04/2021-04-09_08-30_Fri.gpx
2021-04/2021-04-09_18-44_Fri.gpx
2021-04/2021-04-10_15-13_Sat.gpx
2021-04/2021-04-10_22-42_Sat.gpx
2021-04/2021-04-11_12-26_Sun.gpx
2021-04/2021-04-12_08-50_Mon.gpx
2021-04/2021-04-12_13-15_Mon.gpx
2021-04/2021-04-12_19-49_Mon.gpx
2021-04/2021-04-13_08-47_Tue.gpx
2021-04/2021-04-13_13-20_Tue.gpx
2021-04/2021-04-13_16-36_Tue.gpx
2021-04/2021-04-13_20-54_Tue.gpx
2021-04/2021-04-14_08-46_Wed.gpx
2021-04/2021-04-14_12-26_Wed.gpx
2021-04/2021-04-14_19-59_Wed.gpx
2021-04/2021-04-15_08-41_Thu.gpx
2021-04/2021-04-15_17-51_Thu.gpx
2021-04/2021-04-16_08-42_Fri.gpx
2021-04/2021-04-18_12-11_Sun.gpx
2021-04/2021-04-18_17-49_Sun.gpx
2021-04/2021-04-19_09-13_Mon.gpx
2021-04/2021-04-20_08-40_Tue.gpx
2021-04/2021-04-20_10-57_Tue.gpx
2021-04/2021-04-20_20-15_Tue.gpx
2021-04/2021-04-22_08-41_Thu.gpx
2021-04/2021-04-23_08-43_Fri.gpx
2021-04/2021-04-24_16-55_Sat.gpx
2021-04/2021-04-25_11-50_Sun.gpx
2021-04/2021-04-25_20-27_Sun.gpx
2021-04/2021-04-26_05-29_Mon.gpx
2021-04/2021-04-26_11-49_Mon.gpx
2021-04/2021-04-27_08-38_Tue.gpx
2021-04/2021-04-28_08-40_Wed.gpx
2021-04/2021-04-29_08-38_Thu.gpx
2021-04/2021-04-29_19-29_Thu.gpx
2021-04/2021-04-30_10-14_Fri.gpx
2021-04/2021-04-30_17-36_Fri.gpx
2021-04/2021-04-30_17-52_Fri.gpx
2021-05/2021-05-03_09-49_Mon.gpx
2021-05/2021-05-03_11-33_Mon.gpx
2021-05/2021-05-10_10-21_Mon.gpx
2021-05/2021-05-10_13-38_Mon.gpx
2021-05/2021-05-10_15-09_Mon.gpx
2021-05/2021-05-11_09-49_Tue.gpx
2021-05/2021-05-11_10-18_Tue.gpx
2021-05/2021-05-13_15-08_Thu.gpx
2021-05/2021-05-15_10-00_Sat.gpx
2021-05/2021-05-19_09-05_Wed.gpx
2021-05/2021-05-21_08-50_Fri.gpx
2021-05/2021-05-23_09-44_Sun.gpx
2021-05/2021-05-24_10-27_Mon.gpx
2021-05/2021-05-25_09-10_Tue.gpx
2021-05/2021-05-25_11-45_Tue.gpx
2021-05/2021-05-25_14-25_Tue.gpx
2021-05/2021-05-26_14-52_Wed.gpx
2021-05/2021-05-26_19-10_Wed.gpx
2021-05/2021-05-27_08-44_Thu.gpx
2021-05/2021-05-29_11-01_Sat.gpx
2021-05/2021-05-29_11-11_Sat.gpx
2021-05/2021-05-31_08-57_Mon.gpx
2021-05/2021-05-31_13-37_Mon.gpx
2021-05/2021-05-31_15-33_Mon.gpx
2021-06/2021-06-02_08-52_Wed.gpx
2021-06/2021-06-03_07-25_Thu.gpx
2021-06/2021-06-04_08-49_Fri.gpx
2021-06/2021-06-07_08-45_Mon.gpx
2021-06/2021-06-12_08-19_Sat.gpx
2021-06/2021-06-12_08-57_Sat.gpx
2021-06/2021-06-15_20-34_Tue.gpx
2021-06/2021-06-16_08-40_Wed.gpx
2021-06/2021-06-18_08-45_Fri.gpx
2021-06/2021-06-18_14-04_Fri.gpx
2021-06/2021-06-18_15-44_Fri.gpx
2021-06/2021-06-21_14-01_Mon.gpx
2021-06/2021-06-22_08-21_Tue.gpx
2021-06/2021-06-25_08-50_Fri.gpx
2021-06/2021-06-29_09-08_Tue.gpx
2021-06/2021-06-29_10-34_Tue.gpx
2021-06/2021-06-29_18-22_Tue.gpx
2021-06/2021-06-30_09-52_Wed.gpx
2021-07/2021-07-02_09-03_Fri.gpx
2021-07/2021-07-02_11-27_Fri.gpx
2021-07/2021-07-02_11-56_Fri.gpx
2021-07/2021-07-06_08-24_Tue.gpx
2021-07/2021-07-11_12-17_Sun.gpx
2021-07/2021-07-11_19-10_Sun.gpx
2021-07/2021-07-12_19-02_Mon.gpx
2021-07/2021-07-13_07-28_Tue.gpx
2021-07/2021-07-13_18-24_Tue.gpx
2021-07/2021-07-14_14-38_Wed.gpx
2021-07/2021-07-14_18-48_Wed.gpx
2021-07/2021-07-15_09-38_Thu.gpx
2021-07/2021-07-15_15-07_Thu.gpx
2021-07/2021-07-16_10-22_Fri.gpx
2021-07/2021-07-16_10-51_Fri.gpx
2021-07/2021-07-16_17-29_Fri.gpx
2021-07/2021-07-17_00-37_Sat.gpx
2021-07/2021-07-17_11-19_Sat.gpx
2021-07/2021-07-18_13-58_Sun.gpx
2021-07/2021-07-18_15-24_Sun.gpx
2021-07/2021-07-19_21-45_Mon.gpx
2021-07/2021-07-20_09-38_Tue.gpx
2021-07/2021-07-21_09-31_Wed.gpx
2021-07/2021-07-21_17-46_Wed.gpx
2021-07/2021-07-22_09-43_Thu.gpx
2021-07/2021-07-22_11-47_Thu.gpx
2021-07/2021-07-23_08-31_Fri.gpx
2021-07/2021-07-26_08-56_Mon.gpx
2021-07/2021-07-29_09-26_Thu.gpx
2021-07/2021-07-29_11-08_Thu.gpx
2021-07/2021-07-30_10-22_Fri.gpx
2021-08/2021-08-01_17-48_Sun.gpx
2021-08/2021-08-01_23-19_Sun.gpx
2021-08/2021-08-02_09-10_Mon.gpx
2021-08/2021-08-02_11-53_Mon.gpx
2021-08/2021-08-03_14-05_Tue.gpx
2021-08/2021-08-04_13-19_Wed.gpx
2021-08/2021-08-05_09-30_Thu.gpx
2021-08/2021-08-05_18-00_Thu.gpx
2021-08/2021-08-05_19-19_Thu.gpx
2021-08/2021-08-06_09-06_Fri.gpx
2021-08/2021-08-07_11-34_Sat.gpx
2021-08/2021-08-08_12-10_Sun.gpx
2021-08/2021-08-08_16-40_Sun.gpx
2021-08/2021-08-08_17-48_Sun.gpx
2021-08/2021-08-08_18-25_Sun.gpx
2021-08/2021-08-08_19-19_Sun.gpx
2021-08/2021-08-09_10-05_Mon.gpx
2021-08/2021-08-09_18-58_Mon.gpx
2021-08/2021-08-10_09-43_Tue.gpx
2021-08/2021-08-11_08-27_Wed.gpx
2021-08/2021-08-11_10-18_Wed.gpx
2021-08/2021-08-11_16-50_Wed.gpx
2021-08/2021-08-12_09-39_Thu.gpx
2021-08/2021-08-12_11-17_Thu.gpx
2021-08/2021-08-26_08-10_Thu.gpx
2021-08/2021-08-26_19-46_Thu.gpx
2021-08/2021-08-27_11-03_Fri.gpx
2021-08/2021-08-28_10-13_Sat.gpx
2021-08/2021-08-28_10-19_Sat.gpx
2021-08/2021-08-30_10-20_Mon.gpx
2021-09/2021-09-01_09-46_Wed.gpx
2021-09/2021-09-02_11-16_Thu.gpx
2021-09/2021-09-02_19-08_Thu.gpx
2021-09/2021-09-03_17-24_Fri.gpx
2021-09/2021-09-03_20-05_Fri.gpx
2021-09/2021-09-04_09-27_Sat.gpx
2021-09/2021-09-07_10-11_Tue.gpx
2021-09/2021-09-08_18-54_Wed.gpx
2021-09/2021-09-08_21-07_Wed.gpx
2021-09/2021-09-09_08-36_Thu.gpx
2021-09/2021-09-09_09-40_Thu.gpx
2021-09/2021-09-09_11-15_Thu.gpx
2021-09/2021-09-10_11-02_Fri.gpx
2021-09/2021-09-10_12-20_Fri.gpx
2021-09/2021-09-10_12-23_Fri.gpx
2021-09/2021-09-11_11-23_Sat.gpx
2021-09/2021-09-12_17-04_Sun.gpx
2021-09/2021-09-12_20-08_Sun.gpx
2021-09/2021-09-13_09-00_Mon.gpx
2021-09/2021-09-14_10-14_Tue.gpx
2021-09/2021-09-16_09-19_Thu.gpx
2021-09/2021-09-18_22-16_Sat.gpx
2021-09/2021-09-19_19-17_Sun.gpx
2021-09/2021-09-20_16-15_Mon.gpx
2021-09/2021-09-21_20-24_Tue.gpx
2021-09/2021-09-22_10-47_Wed.gpx
2021-09/2021-09-23_08-23_Thu.gpx
2021-09/2021-09-23_09-41_Thu.gpx
2021-09/2021-09-27_07-59_Mon.gpx
2021-09/2021-09-27_08-22_Mon.gpx
2021-09/2021-09-27_08-30_Mon.gpx
2021-09/2021-09-29_09-36_Wed.gpx
2021-09/2021-09-29_13-35_Wed.gpx
2021-09/2021-09-30_10-35_Thu.gpx
2021-09/2021-09-30_12-31_Thu.gpx
2021-09/2021-09-30_17-59_Thu.gpx
2021-10/
2021-10/2021-10-01_12-10_Fri.gpx
2021-10/2021-10-06_08-58_Wed.gpx
2021-10/2021-10-06_13-37_Wed.gpx
2021-10/2021-10-06_15-23_Wed.gpx
2021-10/2021-10-07_19-57_Thu.gpx
2021-10/2021-10-09_13-38_Sat.gpx
2021-10/2021-10-11_07-51_Mon.gpx
2021-10/2021-10-12_17-13_Tue.gpx
2021-10/2021-10-12_21-03_Tue.gpx
2021-10/2021-10-13_08-59_Wed.gpx
2021-10/2021-10-14_10-37_Thu.gpx
2021-10/2021-10-15_09-00_Fri.gpx
2021-10/2021-10-17_09-34_Sun.gpx
2021-10/2021-10-19_08-49_Tue.gpx
2021-10/2021-10-19_20-16_Tue.gpx
2021-10/2021-10-20_10-09_Wed.gpx
2021-10/2021-10-21_10-29_Thu.gpx
2021-10/2021-10-21_11-57_Thu.gpx
2021-10/2021-10-25_19-10_Mon.gpx
2021-10/2021-10-25_21-25_Mon.gpx
2021-10/2021-10-26_08-56_Tue.gpx
2021-10/2021-10-26_18-04_Tue.gpx
2021-10/2021-10-28_10-34_Thu.gpx
2021-10/2021-10-28_12-33_Thu.gpx
2021-10/2021-10-28_20-34_Thu.gpx
2021-10/2021-10-29_08-30_Fri.gpx
2021-10/2021-10-30_11-06_Sat.gpx
2021-10/2021-10-30_11-57_Sat.gpx
2021-10/2021-10-30_14-32_Sat.gpx
2021-10/2021-10-30_18-33_Sat.gpx
2021-10/2021-10-31_10-30_Sun.gpx
2021-11/
2021-11/2021-11-01_12-02_Mon.gpx
2021-11/2021-11-01_16-47_Mon.gpx
2021-11/2021-11-03_09-39_Wed.gpx
2021-11/2021-11-03_16-56_Wed.gpx
2021-11/2021-11-03_22-23_Wed.gpx
2021-11/2021-11-05_09-43_Fri.gpx
2021-11/2021-11-10_13-58_Wed.gpx
2021-11/2021-11-11_10-34_Thu.gpx
2021-11/2021-11-11_12-24_Thu.gpx
2021-11/2021-11-11_19-42_Thu.gpx
2021-11/2021-11-12_11-00_Fri.gpx
2021-11/2021-11-15_08-39_Mon.gpx
2021-11/2021-11-15_19-31_Mon.gpx
2021-11/2021-11-16_08-34_Tue.gpx
2021-11/2021-11-17_09-50_Wed.gpx
2021-11/2021-11-17_16-34_Wed.gpx
2021-11/2021-11-17_21-05_Wed.gpx
2021-11/2021-11-17_22-51_Wed.gpx
2021-11/2021-11-18_10-58_Thu.gpx
2021-11/2021-11-18_18-20_Thu.gpx
2021-11/2021-11-19_09-34_Fri.gpx
2021-11/2021-11-19_13-17_Fri.gpx
2021-11/2021-11-22_19-42_Mon.gpx
2021-11/2021-11-22_22-37_Mon.gpx
2021-11/2021-11-23_09-35_Tue.gpx
2021-11/2021-11-24_09-17_Wed.gpx
2021-11/2021-11-25_10-42_Thu.gpx
2021-11/2021-11-25_12-10_Thu.gpx
2021-11/2021-11-26_09-34_Fri.gpx
2021-11/2021-11-26_19-38_Fri.gpx
2021-11/2021-11-27_10-11_Sat.gpx
2021-12/
2021-12/2021-12-01_09-19_Wed.gpx
2021-12/2021-12-02_10-40_Thu.gpx
2021-12/2021-12-02_12-02_Thu.gpx
2021-12/2021-12-03_12-26_Fri.gpx
2021-12/2021-12-03_17-07_Fri.gpx
2021-12/2021-12-04_13-27_Sat.gpx
2021-12/2021-12-06_09-02_Mon.gpx
2021-12/2021-12-10_08-21_Fri.gpx
2021-12/2021-12-10_09-28_Fri.gpx
2021-12/2021-12-10_12-33_Fri.gpx
2021-12/2021-12-11_17-01_Sat.gpx

sent 314,939 bytes  received 5,364,990 bytes  2,271,971.60 bytes/sec
total size is 40,339,623  speedup is 7.10

Importing *.gpx files

This takes a while.

# rm "$target_db"
for f in sources/strolling/**/*.gpx; do
    spatialite_xml_load -x "$f" -d strolling.db
done

Selecting some GPX tracks with metadata on individual points

• Note that we ignore `extensions_heading`.
• hdop = horizontal dilution of precision (info)

select
    t.node_id track_id,
    seg.node_id segment_id,
    pt.node_id pt_id,
    time.node_value timestamp,
    AsWkt(ST_GeomFromText(
      'POINT('||pt.lon||' '||pt.lat||')',
      4326
    )) geom,
    ele.node_value elevation,
    speed.node_value speed,
    hdop.node_value hdop
from gpx_trk t
join gpx_trk_trkseg seg
  on t.node_id = seg.parent_id
join gpx_trk_trkseg_trkpt pt
  on seg.node_id = pt.parent_id
join gpx_trk_trkseg_trkpt_time time
  on pt.node_id = time.parent_id
join gpx_trk_trkseg_trkpt_ele ele
  on pt.node_id = ele.parent_id
join gpx_trk_trkseg_trkpt_extensions_speed speed
  on pt.node_id = speed.parent_id
join gpx_trk_trkseg_trkpt_hdop hdop
  on pt.node_id = hdop.parent_id;

echo <<points_query>> | sed 's/;/ limit 10;/' | spatialite -csv strolling.db

Pitalls

My queries are slow, what do I do?

Make sure SQlite uses the correct indices. You can debug this by writing

EXPLAIN QUERY PLAN SELECT …

and then continuing your `SELECT` query like you normally would.