Extract recorded geolocations of a given tracker

Configuration
1

I’m a newbie to HA.
I have a fon configured to send geolocations via the companion app.
I would like to extract this information directly from the database via SQLite with a statement similar to:

SELECT WhatEverFields FROM WhatEverTable
WHERE entity_id = ‘DEVICE_TRACKER_ID’
AND last_updated BETWEEN ‘START_TIME’ AND ‘END_TIME’;

result should be a table of the geolocations for this specific tracker that are recorded in the given time interval.

Any ideas?

TIA, mpe42

2

I can’t help with the tables and fields but be aware that the SQL sensor state value is limited to a maximum of 255 characters.

4

isn’t there really anyone having a knowledge of the structure of the database?
Is there any documentation available? I can “read” databases if I have an idea what “page”…
any developer?

5

is it possible to contact the maintainer of the MAP card?
This card uses the geolocation information of a device_tracker.
I would like to ask some questions…
Thx, mpe

6
7

It’s not hard. Basically join three tables together - states, states metadata and state attributes. The attributes are stored in JSON. I

8

SELECT field1, field2… from …

a working example of a corresponding SELECT would be very appreciated :slight_smile:

9

crude but works…

SELECT 
	last_updated_ts,
	datetime(last_updated_ts, 'unixepoch', 'localtime') as last_updated,
	states_meta.*,
    state_attributes.shared_attrs
FROM states
LEFT JOIN states_meta ON states.metadata_id = states_meta.metadata_id
LEFT JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id
WHERE (states_meta.entity_id="$DEVICE_TRACKER_ID")
AND last_updated_ts BETWEEN strftime('%s', '$START_TIME', '-7 hours') AND strftime('%s', '$END_TIME', '-7 hours')
order by last_updated_ts ASC;

the remaining work is done by a Bash script that generates a KML-file to be imported in Google Maps…

Thanks to PeteRage for the guidance :slight_smile: