Skip to content

Instantly share code, notes, and snippets.

@endolith
Created September 9, 2022 01:08
Show Gist options
  • Save endolith/8c5baeb032e1310aa795b526da6fe3a5 to your computer and use it in GitHub Desktop.
Save endolith/8c5baeb032e1310aa795b526da6fe3a5 to your computer and use it in GitHub Desktop.
Maprika sqlite database to GPX track converter
"""
Written by GPT3 from this English prompt (with one tweak to the prompt, to get
the timestamp parameter to not error out, and PEP8 fixes):
----
1. Open a sqlite database with a name like `2022-07-17 17.48.19.db` or
`2021-04-11 08.44.16.db`
2. Inside is a table called `locations`, with headers [LOCTYPE INTEGER,
TIMESTAMP BIGINT, LATITUDE REAL, LONGITUDE REAL,ALTITUDE REAL, ACCURACY
REAL, SPEED REAL, BEARING REAL, LOCNAME VARCHAR]. Each row contains a GPS
measurement. The timestamps are stored using the unix epoch format.
3. Extract the timestamp, latitude, and longitude for all the rows from the
`locations` table.
4. Using the gpxpy library, create a .gpx file
5. Convert the timestamps (such as 1043011384999) into GPX format (Coordinated
Universal Time (UTC) using ISO 8601 format).
6. Insert the rows into the GPX file as a track.
7. Save the .gpx file with the same base name as the .db file.
"""
import sqlite3
import gpxpy
import gpxpy.gpx
import os
import datetime
def convert_db_to_gpx(db_file):
"""
Convert a sqlite database file to a gpx file.
"""
# Open the database file
conn = sqlite3.connect(db_file)
c = conn.cursor()
# Extract the data from the database
c.execute("SELECT * FROM locations")
rows = c.fetchall()
# Create a GPX file
gpx = gpxpy.gpx.GPX()
# Create a GPX track
gpx_track = gpxpy.gpx.GPXTrack()
gpx.tracks.append(gpx_track)
# Create a GPX segment
gpx_segment = gpxpy.gpx.GPXTrackSegment()
gpx_track.segments.append(gpx_segment)
# Add the data to the GPX segment
for row in rows:
gpx_segment.points.append(gpxpy.gpx.GPXTrackPoint(
row[2], row[3], time=datetime.datetime.fromtimestamp(row[1]/1000)))
# Save the GPX file
gpx_file = open(db_file.replace('.db', '.gpx'), 'w')
gpx_file.write(gpx.to_xml())
gpx_file.close()
if __name__ == "__main__":
# Loop through all the .db files in the current directory
for file in os.listdir('.'):
if file.endswith('.db'):
convert_db_to_gpx(file)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment