Export Coordinates to CSV/KML format

Hey all,
I’m working on a project to keep daily travel logs for work, and I figured I could use the GPS data from my phone, that’s already in HA, to export it to a KML file. I know I can use my existing PHPMyAdmin add-on to export the data based on SQL queries to a CSV, then use a converter to parse it to KML.

I’ve tried creating templates for both lat and long, and combining them to a new device tracker, but the data is still parsed together, as below.

"state_id","domain","entity_id","state","attributes","event_id","last_changed","last_updated","created","context_id","context_user_id"
"20353","device_tracker","device_tracker.my_coords","home","{""source_type"": ""gps"", ""latitude"": 25.57806396484375, ""longitude"": -70.34037622778185, ""gps_accuracy"": 0, ""friendly_name"": ""my_coords""}","24393","2020-03-10 02:19:04","2020-03-10 02:19:04","2020-03-10 02:19:06","0fcb87b99c9e470ab30bffcc469f27f9",NULL
"21456","device_tracker","device_tracker.my_coords","home","{""source_type"": ""gps"", ""latitude"": 25.578079223632812, ""longitude"": -70.34044844154484, ""gps_accuracy"": 0, ""friendly_name"": ""my_coords""}","25563","2020-03-10 02:19:04","2020-03-10 04:27:11","2020-03-10 04:27:11","4f448ca194f6444797529545f6a47b2a",NULL
"21549","device_tracker","device_tracker.my_coords","home","{""source_type"": ""gps"", ""latitude"": 25.578018188476562, ""longitude"": -70.34038697199517, ""gps_accuracy"": 0, ""friendly_name"": ""my_coords""}","25658","2020-03-10 02:19:04","2020-03-10 04:37:51","2020-03-10 04:37:51","aba077ed94a54708a1eb854aff9b4a48",NULL

Is there any way to get this data properly formatted how I’d need it, or am I going about this in a overly complicated manner?

Found a method to do this. It turns out I was over complicating things a bit with SQL.

I ended up using Node-Red and templates to track the locations, and write them to a CSV file on my server. I also wrote a quick CSV2KML Python script that will transpose the CSV into a KML file that I need.

Node-Red flow

Basically, I’m using my phones geocoded location as a trigger. When it changes, I grab the timestamp, latitude, and longitude at that moment, and parse it to a CSV format, then feed it to a file node for writing.

I also added a sub-flow for counting how many records have been written, and notifying me every 100 records.

[{"id":"880a007a.354298","type":"tab","label":"Location Tracking","disabled":false,"info":""},{"id":"4948259d.a34434","type":"debug","z":"880a007a.354298","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1010,"y":80,"wires":[]},{"id":"139874af.04494b","type":"api-render-template","z":"880a007a.354298","name":"Lat/Long","server":"8ffc691d.25e1f8","template":"{{ states('sensor.date_time') }}|{{ state_attr('device_tracker.my_coords','latitude') }}|{{ state_attr('device_tracker.my_coords','longitude') }}","resultsLocation":"payload","resultsLocationType":"msg","templateLocation":"template","templateLocationType":"msg","x":340,"y":140,"wires":[["cf70ebf5.99c72","7b1f1d0c.e91b84"]]},{"id":"1810ed7b.bcb34b","type":"server-state-changed","z":"880a007a.354298","name":"Location Changed?","server":"8ffc691d.25e1f8","version":1,"exposeToHomeAssistant":false,"haConfig":[{"property":"name","value":""},{"property":"icon","value":""}],"entityidfilter":"sensor.davids_iphone_geocoded_location","entityidfiltertype":"exact","outputinitially":true,"state_type":"str","haltifstate":"","halt_if_type":"str","halt_if_compare":"is","outputs":1,"output_only_on_state_change":true,"x":150,"y":60,"wires":[["139874af.04494b"]]},{"id":"cf70ebf5.99c72","type":"file","z":"880a007a.354298","name":"david_location.csv","filename":"/share/nodered/david_location.csv","appendNewline":true,"createDir":true,"overwriteFile":"false","encoding":"none","x":650,"y":140,"wires":[["4948259d.a34434"]]},{"id":"7b1f1d0c.e91b84","type":"counter","z":"880a007a.354298","name":"","init":"0","step":"1","lower":"","upper":"","mode":"increment","outputs":"1","x":580,"y":200,"wires":[["b68c21a6.f483a8","ad13b185.36b53"]]},{"id":"b68c21a6.f483a8","type":"switch","z":"880a007a.354298","name":"","property":"count","propertyType":"msg","rules":[{"t":"gte","v":"100","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":790,"y":200,"wires":[["c3734777.9f772","70251345.3ebe84"]]},{"id":"c3734777.9f772","type":"api-call-service","z":"880a007a.354298","name":"Notify phone","server":"8ffc691d.25e1f8","version":1,"debugenabled":false,"service_domain":"notify","service":"mobile_app_david_phone","entityId":"","data":"{\"message\":\"Time to check logs\"}","dataType":"json","mergecontext":"","output_location":"","output_location_type":"none","mustacheAltTags":false,"x":1050,"y":140,"wires":[[]]},{"id":"af2a18de.f7e998","type":"inject","z":"880a007a.354298","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":360,"y":200,"wires":[["7b1f1d0c.e91b84"]]},{"id":"ad13b185.36b53","type":"debug","z":"880a007a.354298","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"count","targetType":"msg","x":750,"y":280,"wires":[]},{"id":"70251345.3ebe84","type":"change","z":"880a007a.354298","name":"","rules":[{"t":"set","p":"reset","pt":"msg","to":"0","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1040,"y":200,"wires":[["7b1f1d0c.e91b84"]]},{"id":"8ffc691d.25e1f8","type":"server","z":"","name":"Home Assistant","addon":true}]

Python Script

I run this script on my local laptop, where it will grab the latest file via scp, read the CSV data, and parse it into a KML file. It then deletes the local copy of the CSV file to prevent confusion, as well as the remote copy via SSH.

#!/usr/bin/python3
import csv
import time
import os

#Input the file name.
scp = os.system('scp [email protected]:/usr/share/hassio/share/nodered/david_location.csv /data/gdrive/Archive/david_location.csv')
fname = '/data/gdrive/Archive/david_location'
data = csv.reader(open(fname + '.csv'), delimiter = '|')
#Open the file to be written.
timestr = time.strftime("%Y%m%d")
f = open('/data/gdrive/Archive/david_location_{}.kml'.format(timestr), 'w')

#Writing the kml file.
f.write("<?xml version='1.0' encoding='UTF-8'?>\n")
f.write("<kml xmlns='http://earth.google.com/kml/2.1'>\n")
f.write("<Document>\n")
f.write("   <name>" + fname + '{}.kml'.format(timestr) +"</name>\n")
for row in data:
    f.write("   <Placemark>\n")
    f.write("       <name>" + str(row[0]) + "</name>\n")
    #f.write("       <description>" + str(row[0]) + "</description>\n")
    f.write("       <Point>\n")
    f.write("           <coordinates>" + str(row[2]) + "," + str(row[1]) + "</coordinates>\n")
    f.write("       </Point>\n")
    f.write("   </Placemark>\n")
f.write("</Document>\n")
f.write("</kml>\n")
f.close()
os.remove(fname + '.csv')
os.system('ssh [email protected] "rm /usr/share/hassio/share/nodered/david_location.csv"')

Hi I am looking for to log my travels with car. I found your solution.
What is your experience, it is still working?
I am not a programmer, but I use HomeAssitant.
I have an entitiy with Km-s from my Car. (FordPass)
Do you thing is it possibel to write my Km numbers to an Excel with automatic when i leave from home, and get home?