Extract protocol-buffer–encoded location data from the Photos.app database

example_output.txt

exact_location {
  accuracy: 45
  label: "Burbank Bob Hope Airport"
  address {
    label: "Burbank Bob Hope Airport"
    label: "Burbank Airport"
    label: "Burbank, CA  91505"
    label: "United States"
    full_address {
      country: "United States"
      country_abbreviation: "US"
      region: "California"
      region_abbreviation: "CA"
      county: "Los Angeles"
      locality: "Burbank"
      postal_code: "91505"
      neighborhood: "Vega"
      street_name: "Burbank Airport"
      street_address: "Burbank Airport"
      neighborhood2: "Vega"
    }
  }
  geographical_center {
    latitude: 4630009509478312956
    longitude: 13861400696319760069
  }
  unknown14: 7618
  timezone {
    tz: "America/Los_Angeles"
  }
}
gazeteer_location {
  accuracy: 45
  label: "Burbank Bob Hope Airport"
  bounding_box {
    southwest_latitude: 4630008956154803739
    southwest_longitude: 13861402024557953921
    northeast_latitude: 4630012031409661791
    northeast_longitude: 13861400507689304427
  }
  geographical_center {
    latitude: 4630009578101912279
    longitude: 13861400910226668621
  }
}
gazeteer_location {
  accuracy: 43
  label: "Vega"
  bounding_box {
    southwest_latitude: 4630007706757750864
    southwest_longitude: 13861402135311320382
    northeast_latitude: 4630013024988182082
    northeast_longitude: 13861398203647735064
  }
  geographical_center {
    latitude: 4630010365865929599
    longitude: 13861401106027699295
  }
}
gazeteer_location {
  accuracy: 16
  label: "Burbank"
  bounding_box {
    southwest_latitude: 4630001965273397167
    southwest_longitude: 13861402024557953921
    northeast_latitude: 4630013097636873571
    northeast_longitude: 13861395666763028092
  }
  geographical_center {
    latitude: 4630007513454810608
    longitude: 13861397602304594819
  }
}
gazeteer_location {
  accuracy: 16
  label: "Burbank"
  bounding_box {
    southwest_latitude: 4630001961642369967
    southwest_longitude: 13861402024557953921
    northeast_latitude: 4630011954707730637
    northeast_longitude: 13861398859723684530
  }
  geographical_center {
    latitude: 4630006958175050302
    longitude: 13861400467452456506
  }
}
gazeteer_location {
  accuracy: 4
  label: "Los Angeles"
  bounding_box {
    southwest_latitude: 4629805975682635918
    southwest_longitude: 13861442937016187559
    northeast_latitude: 4630097757006356323
    northeast_longitude: 13861351037336247622
  }
  geographical_center {
    latitude: 4629989243758497250
    longitude: 13861393089416293217
  }
}
gazeteer_location {
  accuracy: 2
  label: "California"
  bounding_box {
    southwest_latitude: 4629775608614036001
    southwest_longitude: 13861827392568615064
    northeast_latitude: 4631109131500574822
    northeast_longitude: 13861103723851560432
  }
  geographical_center {
    latitude: 4630422926863666649
    longitude: 13861536857039067831
  }
}
gazeteer_location {
  accuracy: 1
  label: "United States"
  bounding_box {
    southwest_latitude: 4627595279941274704
    southwest_longitude: 13861851839725873547
    northeast_latitude: 4632147047959197230
    northeast_longitude: 13857783641566184022
  }
  geographical_center {
    latitude: 4630826279266803121
    longitude: 13860016336153235043
  }
}

extract.sh

#!/usr/bin/env bash

# This script extracts a protocol-buffer--encoded location from the Photos.app
# database.
#
# * The query uses `quote` because `reverseLocationData` is binary data, and
#   SQLite won't write the full value to stdout without `quote`'s hex encoding.
# * `cut` trims the leading `X'` that `quote` adds.
# * `xxd` converts the hex data back to binary.
# * `plutil` converts the binary property list data to XML, which is easier to
#   manipulate.
# * `xmllint` extracts the protocol buffer data, which is base64-encoded.
#   `xmllint`'s output includes newlines and leading tabs because that's how
#   `plutil` formats it.
# * `tr` strips the newlines and tabs.
# * `base64` decodes the protocol buffer data to binary.
# * `protoc` decodes the protocol buffer data to plain text.
#
# References:
# * https://stackoverflow.com/a/15451174/672403 exporting binary data from SQLite
# * https://stackoverflow.com/a/33813384/672403 piping stdin to `plutil`

if [ -n "$(which protoc)" ] ; then
  "You need to install the protoc tool. You can run 'brew install protobuf'." 1>&2
  return 1
fi

query="SELECT DISTINCT quote(reverseLocationData) FROM RKVersion WHERE reverseLocationData IS NOT NULL ORDER BY modelId ASC LIMIT 1"
sqlite3 ~/Pictures/Photos\ Library.photoslibrary/database/photos.db "$query" \
| cut -d\' -f2 \
| xxd -r -p \
| plutil -convert xml1 -o - -- - \
| xmllint --xpath "//dict/data/text()" --format - \
| tr -d "\n\t" \
| base64 --decode \
| protoc --decode=LocationHierarchy ./apple_photos_location.proto

LICENSE

MIT License

Copyright (c) 2017-present Jordan Cole

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

photos_location.proto

syntax = "proto3";

message Address {
  message FullAddress {
    reserved 9, 14, 16, 18, 19;
    string country = 1;
    string country_abbreviation = 2;
    string region = 3;
    string region_abbreviation = 4;
    string county = 5;
    string locality = 6;

    // Unclear what the difference is between these.
    string neighborhood = 8;
    repeated string neighborhood2 = 17;

    // @note `street_number` (and `street_address` and `mailing_address`) don't
    //   decode multi-byte Unicode characters correctly. For example, U+2013 EN
    //   DASH becomes the octal-encoded `\342\200\223`.
    string street_number = 11;
    string street_name = 10;
    string street_address = 12;

    string postal_code = 7;
    string postal_code_extension = 13;
    string full_postal_code = 20;
  }

  reserved 1 to 10, 12 to 14;
  repeated string label = 11;
  FullAddress full_address = 15;
}

// @note Latitudes and longitudes are encoded as 2^64 - (n * 10^8) --
//   for example, -121.4206976 becomes 18446744061567482196.
// @see https://www.reddit.com/r/programming/comments/6abmlo/reverse_engineering_apple_location_services/dhdtawt/
message BoundingBox {
  // Must have changed encoding?
  reserved 1 to 4;
  fixed64 southwest_latitude = 5;
  fixed64 southwest_longitude = 6;
  fixed64 northeast_latitude = 7;
  fixed64 northeast_longitude = 8;
}

message LatLon {
  fixed64 latitude = 1;
  fixed64 longitude = 2;
}

// @note These are the fields shared between `ExactLocation` and
//   `GazeteerLocation`. `proto3` doesn't support extending `message`s, so
//   `Location` isn't directly used.
message Location {
  reserved 1, 2, 7, 8, 10, 12, 13, 15, 16, 18;

  // Example `accuracy` values:
  // 1 = country
  // 2 = region
  // 4 = county
  // 16 = city
  // 43 = neighborhood
  // 45 = park, beach
  // 49 = freeway, bridge
  // 57 = exact address, city block
  int32 accuracy = 3;
  string label = 4;
  BoundingBox bounding_box = 5;
  LatLon geographical_center = 9;
  // Known values: 2, 4
  int32 unknown11 = 11;
}

message ExactLocation {
  reserved 1, 2, 7, 8, 10, 12, 13, 15, 16, 18;

  int32 accuracy = 3;
  string label = 4;
  BoundingBox bounding_box = 5;
  Address address = 6;
  LatLon geographical_center = 9;
  int32 unknown11 = 11;
  int32 unknown14 = 14;
  Timezone timezone = 19;
}

message GazeteerLocation {
  reserved 1, 2, 7, 8, 10, 12, 13, 15, 16, 18;

  int32 accuracy = 3;
  string label = 4;
  BoundingBox bounding_box = 5;
  LatLon geographical_center = 9;
  int32 unknown11 = 11;
  // Known values: 7618
  int64 unknown17 = 17;
}

message Timezone {
  string tz = 1;
}

message LocationHierarchy {
  reserved 2;

  ExactLocation exact_location = 1;
  repeated GazeteerLocation gazeteer_location = 3;
}