Author Topic: Parsing Google JSON  (Read 344 times)

DuncanL

  • Newbie
  • *
  • Posts: 2
Parsing Google JSON
« on: March 19, 2019, 05:41:59 PM »
I have recently downloaded all my photos from Gogole Photos (via Takeout).  This gives me all the images along with JSON files for each one

Have found this info,  it looks like ExifTool will be able to join all that info back up for me.

My issue is how to get the GPS info out of this file format:

{
  "title": "IMG_20190219_101757166_HDR.jpg",
  "description": "",
  "url": "https://lh3.googleusercontent.com/-uAFs2UyPtAb/KTj6OEUcDrV/NNNNNNNNi4V/Z9I66n3GcLVBzzWzVytk1OHeIliUjQ2rtPYNOTNLLPj/s0-d/IMG_20190219_101757166_HDR.jpg",
  "imageViews": "0",
  "creationTime": {
    "timestamp": "1550596613",
    "formatted": "19 Feb 2019, 17:16:53 UTC"
  },
  "modificationTime": {
    "timestamp": "1551774762",
    "formatted": "5 Mar 2019, 08:32:42 UTC"
  },
  "geoData": {
    "latitude": 54.38345692222222,
    "longitude": -12.3228702722222224,
    "altitude": 256.85,
    "latitudeSpan": 0.0,
    "longitudeSpan": 0.0
  },
  "geoDataExif": {
    "latitude": 54.38345692222222,
    "longitude": -12.3228702722222224,
    "altitude": 256.85,
    "latitudeSpan": 0.0,
    "longitudeSpan": 0.0
  },
  "photoTakenTime": {
    "timestamp": "1550571480",
    "formatted": "19 Feb 2019, 10:18:00 UTC"
  }
}


(this has been tweaked to change the actual info...)

This Windows command line should (in theory...) do most of the job,  but I want to get the "photoTakenTime":"timestamp" into the EXIF as the taken time; and the GPS data as well - but I can't find the syntax to get a JSON sub-element.

for %f in (*.jpg) do exiftool -tagsfromfile "%f.json" "-GPSAltitude<geoDataExif.altitude" "-GPSLatitude<geoDataExif.latitude" "-GPSLatitudeRef<geoDataExif.latitude" "-GPSLongitude<geoDataExif.longitude" "-GPSLongitudeRef<geoDataExif.longitude" "-Caption-Abstract<Description" "-ImageDescription<Description" -Description "-DateTimeOriginal<photoTakenTime.timestamp" %f

So the GPS and time inputs are wrong -  can anyone give me clues\pointers as to what they should be?  Thanks!

StarGeek

  • Global Moderator
  • ExifTool Freak
  • *****
  • Posts: 2569
Re: Parsing Google JSON
« Reply #1 on: March 19, 2019, 07:35:30 PM »
As per the edit in my answer there, you should be able to use GeoDataAltitude, GeoDataLatitude, and GeoDataLongitude as the tags to copy from.  So the command would be
exiftool -tagsfromfile "%d/%F.json" "-GPSAltitude<GeoDataAltitude" "-GPSLatitude<GeoDataLatitude" "-GPSLatitudeRef<GeoDataLatitude" "-GPSLongitude<GeoDataLongitude" "-GPSLongitudeRef<GeoDataLongitude" FileOrDir

The timestamp is more complicated, because if I recall correctly, that timestamp is in UTC time (as it shows in the JSON output), but google may not have correctly accounted for the actual timezone of the file.  But the basic command for that would be
exiftool -d %s  -tagsfromfile "%d/%F.json" "-DateTimeOriginal<TAG" FileOrDir

I don't have a takeout json file at the moment with the timestamp in it, so to figure out what to replace TAG with, just run exiftool on the json file with the -s (short) option, e.g. exiftool -s file.json  That will give you a list of all the tag names and values in the json file.

The combined command would be
exiftool -d %s -tagsfromfile "%d/%F.json" "-GPSAltitude<GeoDataAltitude" "-GPSLatitude<GeoDataLatitude" "-GPSLatitudeRef<GeoDataLatitude" "-GPSLongitude<GeoDataLongitude" "-GPSLongitudeRef<GeoDataLongitude" "-DateTimeOriginal<TAG" FileOrDir

A couple things to take note of.  It's been a while since I tested the takeout json, but if I recall correctly, the name of the json file would include the extension of the image file.  So in the case of IMG_20190219_101757166_HDR.jpg, the json file would be IMG_20190219_101757166_HDR.jpg.json.  In that case, you would have to use %F after the -TagsFromFile option, not %f (see the -w (textout) option for the difference).

Also, you don't need to loop this (see Common Mistake #3).  Just pass the files and/or directories directly to exiftool.  Though you probably should add -ext jpg (see -ext (extension) option) to limit the scope to just the image files and not process the json files.

Also, remember that the URL listed in the json file is a direct link to the file, bypassing any privacy settings, making it freely available to anyone who can access that url.
Troubleshooting hints:
* When posting, include your OS, Exiftool version, and type of file you're processing (MP4, JPG, etc).
* Double all percent signs (%) in a Windows batch file.
* If your GPS coords are negative, make sure and set the GpsLatitudeRef and GpsLongitudeRef tags correctly.

StarGeek

  • Global Moderator
  • ExifTool Freak
  • *****
  • Posts: 2569
Re: Parsing Google JSON
« Reply #2 on: March 19, 2019, 08:18:28 PM »
Actually, I just remembered that I had started documenting the json takeout tag names a month ago.  Even though it's very incomplete, I went ahead and made it public.  See here for the list.

The names of the tags are in the parenthesis, so the tag you would copy from would probably be either PhotoTakenTimeTimestamp or ModificationTimeTimestamp.
Troubleshooting hints:
* When posting, include your OS, Exiftool version, and type of file you're processing (MP4, JPG, etc).
* Double all percent signs (%) in a Windows batch file.
* If your GPS coords are negative, make sure and set the GpsLatitudeRef and GpsLongitudeRef tags correctly.

DuncanL

  • Newbie
  • *
  • Posts: 2
Re: Parsing Google JSON
« Reply #3 on: March 19, 2019, 09:23:27 PM »
Thank you.

I didn't realise ExifTool would do the file and directory parsing - so that's very handy - I can just process the entire TakeOut tree.

Ah, so GeoDataAltitude et al are ExifTool mappings from the JSON.  I assumed they were the actual names and that Google had changed the names and formatting since your post!

The image URL in my post was intentionally corrupted - it goes nowhere.

If anyone is interested;  this is what works for me:

exiftool -r -d %s -tagsfromfile "%d/%F.json" "-GPSAltitude<GeoDataAltitude" "-GPSLatitude<GeoDataLatitude" "-GPSLatitudeRef<GeoDataLatitude" "-GPSLongitude<GeoDataLongitude" "-GPSLongitudeRef<GeoDataLongitude" "-DateTimeOriginal<PhotoTakenTimeTimestamp" -ext jpg -overwrite_original <DirToProcess>

(The overwrite_original is safe since I have the original TakeOut zip files if anything goes horribly wrong! ;-) )

Thank you again for the help!