photos_with_apple_metadata (view)

3 rows where persons = "["_UNKNOWN_", "_UNKNOWN_", "_UNKNOWN_"]" sorted by ZWELLCHOSENSUBJECTSCORE

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: date (date), albums (array), persons (array)

rowid	photo	uuid	date	albums	persons	ext	sha256	size	latitude	longitude	favorite	portrait	screenshot	slow_mo	time_lapse	hdr	selfie	panorama	place_city	place_state_province	place_country	ZUUID	ZOVERALLAESTHETICSCORE	ZCURATIONSCORE	ZPROMOTIONSCORE	ZHIGHLIGHTVISIBILITYSCORE	ZBEHAVIORALSCORE	ZFAILURESCORE	ZHARMONIOUSCOLORSCORE	ZIMMERSIVENESSSCORE	ZINTERACTIONSCORE	ZINTERESTINGSUBJECTSCORE	ZINTRUSIVEOBJECTPRESENCESCORE	ZLIVELYCOLORSCORE	ZLOWLIGHT	ZNOISESCORE	ZPLEASANTCAMERATILTSCORE	ZPLEASANTCOMPOSITIONSCORE	ZPLEASANTLIGHTINGSCORE	ZPLEASANTPATTERNSCORE	ZPLEASANTPERSPECTIVESCORE	ZPLEASANTPOSTPROCESSINGSCORE	ZPLEASANTREFLECTIONSSCORE	ZPLEASANTSYMMETRYSCORE	ZSHARPLYFOCUSEDSUBJECTSCORE	ZTASTEFULLYBLURREDSCORE	ZWELLCHOSENSUBJECTSCORE ▼	ZWELLFRAMEDSUBJECTSCORE	ZWELLTIMEDSHOTSCORE
315		9D1D2C1F-5BBF-4142-AABF-36F7DE028FA4	2018-04-22T00:15:18+00:00	[ "Museums", "Public" ]	[ "_UNKNOWN_", "_UNKNOWN_", "_UNKNOWN_" ]	heic	99ce831cc3b5b6b9023d6175b29b6a234da11ec5f93beb38bc24d9d979f4d789	1660836	37.579175	-122.34421666666667	0	0	0	0	0	0	0	0	Burlingame	CA	United States	9D1D2C1F-5BBF-4142-AABF-36F7DE028FA4	0.370849609375	0.5	0.0	0.038931297721298594	0.10000000149011612	-0.0017681121826171875	0.00678253173828125	0.0013561248779296875	0.009999999776482582	-0.24658203125	-0.0560302734375	0.31787109375	0.0022258758544921875	-0.0206451416015625	-0.05078125	-0.2069091796875	-0.0521240234375	0.11614990234375	-0.126220703125	-0.00042724609375	-0.046661376953125	0.0025806427001953125	0.072509765625	0.055511474609375	-0.2056884765625	0.286376953125	-0.03350830078125
94		11842992-CE7D-4301-9925-A29B440C9C0E	2018-04-22T00:11:30+00:00	[ "Museums", "Public" ]	[ "_UNKNOWN_", "_UNKNOWN_", "_UNKNOWN_" ]	heic	29d25816cf8e645abe0cc1d731d8ab025a67e89a5058a98452eb2247900b17b3	1607783	37.57924166666667	-122.34418666666667	0	0	0	0	0	0	0	0	Burlingame	CA	United States	11842992-CE7D-4301-9925-A29B440C9C0E	0.414306640625	0.5	0.0	0.038931297721298594	0.10000000149011612	-0.006641387939453125	0.0281524658203125	0.0015306472778320312	0.009999999776482582	0.0723876953125	-0.04803466796875	0.3828125	0.01132965087890625	-0.0234222412109375	-0.0240478515625	-0.040771484375	-0.181884765625	0.03125	-0.04730224609375	-0.005859375	-0.13427734375	0.002323150634765625	0.06610107421875	0.042755126953125	-0.17333984375	0.408935546875	-0.0148162841796875
256		12AB4E54-7810-428D-A275-7CB631F2E33A	2018-04-22T00:28:38+00:00	[ "Museums", "Public" ]	[ "_UNKNOWN_", "_UNKNOWN_", "_UNKNOWN_" ]	heic	7d55d7d466216fbc80457ea3e500d95d876bd57e356d6cd1b286763c18830a3a	1663615	37.579253333333334	-122.34427833333334	0	0	0	0	0	0	0	0	Burlingame	CA	United States	12AB4E54-7810-428D-A275-7CB631F2E33A	0.4423828125	0.5	0.0	0.038931297721298594	0.10000000149011612	-0.049224853515625	0.0011749267578125	0.00530242919921875	0.009999999776482582	-0.5498046875	-0.0706787109375	-0.1641845703125	0.075439453125	-0.18798828125	-0.06243896484375	-0.378173828125	-0.595703125	0.022674560546875	-0.04718017578125	-0.002899169921875	-0.0088348388671875	0.0022525787353515625	0.0029582977294921875	-0.265625	-0.010894775390625	-0.1243896484375	0.035186767578125

CREATE VIEW photos_with_apple_metadata AS 
    select
        apple_photos.rowid,
        json_object(
            'img_src',
            'https://photos.simonwillison.net/i/' || uploads.sha256 || '.' || uploads.ext || '?w=600'
        ) as photo,
        apple_photos.uuid,
        apple_photos.date,
        apple_photos.albums,
        apple_photos.persons,
        uploads.ext,
        uploads.sha256,
        uploads.size,
        latitude,
        longitude,
        favorite,
        portrait,
        screenshot,
        slow_mo,
        time_lapse,
        hdr,
        selfie,
        panorama,
        place_city,
        place_state_province,
        place_country,
        apple_photos_scores.*
    from
        apple_photos
    join
        uploads on apple_photos.sha256 = uploads.sha256
    left join
        apple_photos_scores on apple_photos.uuid = apple_photos_scores.ZUUID
    order by
        apple_photos.date desc;