Possible to get at the automatic image labels?

[simonw]

First up: thank you so much for this library! I've been wanting something like this for years. I'm using this for my own (very early alpha) photos-to-sqlite tool.

One of my favourite features of Apple Photos is the way it runs machine learning on my phone/laptop to identify photos of dogs, cats, beaches etc and add invisible labels to my photos. As a result I can search for "dog" and see all of the photos I've taken of dogs.

I've been trying to figure out where Photos stores those automatically detected labels. I've poked around in the SQLite database for the photo library and I've not found anything there that looks like it's from the machine learning models.

Have you been able to figure out where this stuff is stored? Any chance it could be exposed through osxphotos?

[RhetTbull]

I've not looked for this data (and indeed didn't know Photos did this until you mentioned it). I make heavy use of face detection but haven't tried other image recognition features. I'll take a look -- if I can find where the data is stored I can definitely expose it through osxphotos.

[RhetTbull]

Quick peek at the database points to ZSCENECLASSIFICATION as one possible source of the classification data. However, I can't find any other reference to the ZSCENEIDENTIFIER column values in the database (I'm guessing the identifier is the classified subject in the photo, e.g. "dog") but these values must be looked up elsewhere, perhaps in some other database used by photoanalysisd.

Z_PK,Z_ENT,Z_OPT,ZSCENEIDENTIFIER,ZASSETATTRIBUTES,ZCONFIDENCE
8,49,1,731,5,0.11834716796875
9,49,1,684,6,0.0233648251742125
10,49,1,1702,1,0.026153564453125

[simonw]

I think I may have found it:

Last login: Mon May  4 17:52:43 on ttys023
$ cd /Users/simon/Pictures/Photos\ Library.photoslibrary/database/search/          
$ ls -lah
total 125048
drwxr-xr-x@ 11 simon  staff   352B May  4 17:47 .
drwxr-xr-x@ 12 simon  staff   384B Apr 20 16:46 ..
-rw-r--r--@  1 simon  staff   261B May  4 17:47 graphDataProgress.plist
-rw-r--r--@  1 simon  staff    53M May  4 15:52 psi.sqlite
-rw-r--r--@  1 simon  staff    32K May  4 08:56 psi.sqlite-shm
-rw-r--r--@  1 simon  staff   6.9M May  4 15:52 psi.sqlite-wal
-rw-r--r--@  1 simon  staff    11K Apr 25 15:07 searchMetadata.plist
-rw-r--r--@  1 simon  staff   540B May  4 17:47 searchProgress.plist
-rw-r--r--@  1 simon  staff   454B May  4 17:47 searchSystemInfo.plist
-rw-r--r--@  1 simon  staff   4.3K May  4 17:47 synonymsProcess.plist
-rw-r--r--@  1 simon  staff    23K May  4 15:51 zeroKeywords.data
$ sqlite3 psi.sqlite .schema
CREATE TABLE word_embedding(word TEXT, extended_word TEXT, score DOUBLE);
CREATE INDEX word_embedding_index ON word_embedding(word);
CREATE VIRTUAL TABLE word_embedding_prefix USING fts5(extended_word)
/* word_embedding_prefix(extended_word) */;
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE groups(category INT2, owning_groupid INT, content_string TEXT, normalized_string TEXT, lookup_identifier TEXT, token_ranges_0 INT8, token_ranges_1 INT8, UNIQUE(category, owning_groupid, content_string, lookup_identifier, token_ranges_0, token_ranges_1));
CREATE TABLE assets(uuid_0 INT, uuid_1 INT, creationDate INT, UNIQUE(uuid_0, uuid_1));
CREATE TABLE ga(groupid INT, assetid INT, PRIMARY KEY(groupid, assetid));
CREATE TABLE collections(uuid_0 INT, uuid_1 INT, startDate INT, endDate INT, title TEXT, subtitle TEXT, keyAssetUUID_0 INT, keyAssetUUID_1 INT, typeAndNumberOfAssets INT32, sortDate DOUBLE, UNIQUE(uuid_0, uuid_1));
CREATE TABLE gc(groupid INT, collectionid INT, PRIMARY KEY(groupid, collectionid));
CREATE VIRTUAL TABLE prefix USING fts5(content='groups', normalized_string, category UNINDEXED, tokenize = 'PSITokenizer');
CREATE TABLE IF NOT EXISTS 'prefix_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'prefix_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'prefix_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'prefix_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE lookup(identifier TEXT PRIMARY KEY, category INT2);
CREATE TRIGGER trigger_groups_insert AFTER INSERT ON groups BEGIN INSERT INTO prefix(rowid, normalized_string, category) VALUES (new.rowid, new.normalized_string, new.category); END;
CREATE TRIGGER trigger_groups_delete AFTER DELETE ON groups BEGIN INSERT INTO prefix(prefix, rowid, normalized_string, category) VALUES('delete', old.rowid, old.normalized_string, old.category); END;
CREATE INDEX group_pk ON groups(category, content_string, normalized_string, lookup_identifier);
CREATE INDEX asset_pk ON assets(uuid_0, uuid_1);
CREATE INDEX ga_assetid ON ga(assetid, groupid);
CREATE INDEX collection_pk ON collections(uuid_0, uuid_1);
CREATE INDEX gc_collectionid ON gc(collectionid);

[simonw]

Made a bunch of progress on this in dogsheep/dogsheep-photos#16 but I've got stuck on UUIDs. The psi.sqlite database stores them like this:

I can't work out how to convert those into the standard UUIDs that are stored in the ZUUID columns in the main Photos.sqlite database.

[simonw]

This seems to work, thanks to https://twitter.com/pkqk/status/1257512449575497729

def to_uuid(uuid_0, uuid_1):
    b = uuid_0.to_bytes(8, 'little', signed=True) + uuid_1.to_bytes(8, 'little', signed=True)
    return str(uuid.UUID(bytes=b)).upper()

[RhetTbull]

Looks like @simonw cracked the code on this! I'll work on adding this as a feature to osxphotos in a couple of weeks. From an interface perspective, not sure what to call this. maybe search_terms or image_classification? Interface might be:

# list of all search terms in the database
PhotosDB.search_terms()

# by dict {term: photo_count}
PhotosDB.search_terms_as_dict()

# added to photos()
PhotosDB.photos(search_term=[])

# available by photo
PhotoInfo.search_terms

But, if I can figure out how to add associate confidence value with these, that might be a useful interface. Could return term and confidence as a tuple?

Any thoughts on what would be useful/intuitive?

[simonw]

Personally I like the word "label" to describe what's going on here - it's the output of a machine learning label classification task.

[RhetTbull]

Traveling and don't have my computer but I was able to ssh to my Mac at home via iPad :-) so I played around a bit using @simonw's code to prototype this for osxphotos. Trying to decode the categories in the groups table and so far have this:

• 1 to 12: various parts of the reverse geolocation data (1 is areas of interest and 12 is country)
• 1: area of interest
• 2: street
• 3: appears to be additional city-level/neighborhood info but not sure how this maps into other place data < city
• 5: additional city-level info < city
• 6: city
• 7: county? > city
• 9: sub-administrative area
• 10: state/administrative area name
• 11: state/administrative area abbreviation
• 12: country
• 1014: creation month
• 1015: creation year
• 2016: keyword
• 2017: title
• 2018: description
• 2021: person in image
• 2024: label from ML process
• 2027: meal (e.g. dining, lunch)
• 2029: holiday?
• 2030: season
• 2044: videos
• 2046: live photos
• 2049: time-lapse
• 2053: portrait
• 2054: selfies
• 2055: favorites
• 2056: filename

combining all these into some sort of search method would be handy:

photosdb.search("kids","beach","2019")

Also, trying to use vim over ssh on an iPad keyboard that doesn't have an ESC key = #!@%^!

[RhetTbull]

I'm surprised albums and folders don't appear to be one of the search categories

[RhetTbull]

I like @simonw's suggestion of labels as the name for this data but I also want to expose all the search terms. So thinking of this:

PhotosDB.labels

PhotosDB.labels_as_dict

PhotoInfo.labels
# ^ really a shortcut for PhotoInfo.search_info.labels

PhotoInfo.search_info
# ^ returns a SearchInfo object if you want all the data

SearchInfo.labels
SearchInfo.season
#etc

# also implement __iter__ and __next__ for SearchInfo which would return iterator with all normalized search terms so you could do this:
if "word".lower() in photo.search_info:
	pass

It might be good to also provide a reverse index from label to all photos containing that label to make it fast to search the entire library
e.g. when processing psi.sqlite, also build a _db_label_uuuid dict in form {label: [list of uuids]}

Then add this to PhotosDB:

PhotosDB.photos(labels=[list of normalized terms])

[RhetTbull]

@simonw osxphotos version 0.28.15 now includes properties for PhotoInfo.labels and PhotoInfo.labels_normalized as well as PhotosDB.labels, .labels_normalized, .labels_as_dict, .labels_normalized_as_dict to provide access to the label info. PhotoInfo also includes a new .search_info property which returns a SearchInfo object which will eventually expose all the different categories in psi.sql. Currently it only exposes .labels and .labels_normalized and it's not in the docs yet since it's incomplete.

[simonw]

Amazing! This is such a great project. Thanks very much for this.