Recovering un-uploaded observations from dead Android phone

Fortunately even though the screen was dead it is possible to connect to the phone with adb and pull the iNaturalist app data from /data/data/org.inaturalist.android

In particular the inaturalist.db database contained in the databases directory is what is useful. This is an sqlite3 database and the following two tables are what I needed: observations, observation_photos. If there had been sounds then observation_sounds would also have been useful.

The date field is in Unix timestamp with milliseconds format which needed to be converted to local date/time format, and the _id from the observations table needs to be matched with _observation_id from the observation_photos table. The tables are connected using a join.

The relevant fields necessary to create an observation are:
observations.observed_on
observations.latitude
observations.longitude
observations.species_guess
observations.description
observation_photos.original_photo_filename

Finally, I also only wanted the observations that were made from 12/26/2019 onward. These records are filtered using a WHERE clause.

Here is the actual command:

SELECT observations._id, 
observations.observed_on, 
datetime(ROUND(observations.observed_on / 1000), 'unixepoch') AS isodate, 
observations.latitude, 
observations.longitude, 
observations.species_guess, 
observations.description, 
observation_photos.original_photo_filename 
FROM observations 
JOIN observation_photos ON observations._id=observation_photos._observation_id 
WHERE isodate > '2019-12-25 23:59:59';

Here is the output:

264|1577387141872|2019-12-26 19:05:41|37.9038325792225|-122.571555711329|asteraceae||/storage/7672-BA7A/DCIM/Camera/20191226_120451.jpg
265|1577388307000|2019-12-26 19:25:07|37.8969966246212|-122.569546736777|Angiospermae||/storage/7672-BA7A/DCIM/Camera/20191226_122507.jpg
266|1577388787000|2019-12-26 19:33:07|37.896361120291|-122.566486671567|asteraceae||/storage/7672-BA7A/DCIM/Camera/20191226_123307.jpg
267|1577388922000|2019-12-26 19:35:22|37.8961060703826|-122.566429004073|asteraceae||/storage/7672-BA7A/DCIM/Camera/20191226_123522.jpg
268|1577389296000|2019-12-26 19:41:36|37.8960055317941|-122.563664652407|Angiospermae||/storage/7672-BA7A/DCIM/Camera/20191226_124136.jpg
269|1577390172000|2019-12-26 19:56:12|37.8937439064166|-122.564252726734|angiosperms||/storage/7672-BA7A/DCIM/Camera/20191226_125613.jpg
270|1577390730000|2019-12-26 20:05:30|37.8923648840782|-122.567022442818|Angiospermae|Tastes good|/storage/7672-BA7A/DCIM/Camera/20191226_130531.jpg
271|1577392176000|2019-12-26 20:29:36|37.8967288774207|-122.575220279396|Angiospermae||/storage/7672-BA7A/DCIM/Camera/20191226_132936.jpg
272|1577392301000|2019-12-26 20:31:41|37.8974127949668|-122.574964463711|gilled mushrooms||/storage/7672-BA7A/DCIM/Camera/20191226_133142.jpg
273|1577996487000|2020-01-02 20:21:27|37.8002776774237|-122.479512691498|grasses||/storage/7672-BA7A/DCIM/Camera/20200102_132127.jpg
274|1577996858000|2020-01-02 20:27:38|37.8011270032275|-122.479240782559|asteraceae||/storage/7672-BA7A/DCIM/Camera/20200102_132738.jpg
275|1577997057000|2020-01-02 20:30:57|37.8014438421677|-122.479247152805|Hedgenettles|Hairy leaves and stems leaves have lemon-y scent|/storage/7672-BA7A/DCIM/Camera/20200102_133057.jpg

Based on this I recovered all the information necessary to create these 12 un-uploaded observations using the pictures which I also saved from the phone.

Publicado el enero 16, 2020 11:47 TARDE por xpacifica