photos_with_apple_metadata (view)

11 rows where "date" is on date 2020-05-03 sorted by ZIMMERSIVENESSSCORE

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: albums (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
273		2040FC9F-CAE1-4F99-B671-9E421BD910CA	2020-05-03T01:06:18+00:00	[ "Public" ]	[]	jpeg	837da4d668cb12a9dec96f81736af75d71d12621972a9e025e528d928133e833	4825380			1	0	0	0	0	0	0	0
228		3E1AC4F5-3E6A-4F49-BF1C-E64BCBFB5158	2020-05-03T01:03:57+00:00	[ "Public" ]	[]	jpeg	71ca92f4c7d4521b032a3ef828cc83dec72aba36c64075cfb9512632d7920772	3860130			1	0	0	0	0	0	0	0
104		2921DFAE-9DF7-4586-A8EF-AA2D2A5149C8	2020-05-03T01:03:41+00:00	[ "Public" ]	[]	jpeg	31661d2d425201e4c5b81f154680f0293dc29efef7c5e338fee0f593d3887ee7	3937810			1	0	0	0	0	0	0	0
33		A3F5EB99-DAC8-43D4-90B8-F9EC78342604	2020-05-03T01:02:04+00:00	[ "Public" ]	[]	jpeg	139a18a38b0e84b3e0919d1729ad052a79cc959ad560abd45d777d12c9e6364e	7557213			1	0	0	0	0	0	0	0
192		01F9A7AE-4A1B-47A1-AE0D-9B93B0F88990	2020-05-03T01:01:30+00:00	[ "Public" ]	[]	jpeg	5fd967a5c95505381927b708b0db5d915415e42d0dc27b0d83c39905940a3eb4	5418823			1	0	0	0	0	0	0	0
456		100F0542-94D4-43F8-9C76-7D93589E3E09	2020-05-03T01:00:16+00:00	[ "Public" ]	[]	jpeg	e79a80827b055511dce4c191b1db31255094cbeae7d7be680821b0788dd33dc6	5964031			1	0	0	0	0	0	0	0
194		9D52CBF1-ACD2-45B4-B3EB-A3D37C098D40	2020-05-03T00:59:54+00:00	[ "Public" ]	[]	jpeg	6200b752e427f59e0ec87410470c815297412faae45bc806adad9ffd2dcbfb50	5253392			1	0	0	0	0	0	0	0
40		5F6E36A0-3A6A-457F-8975-5E060E12DEA8	2020-05-03T00:58:55+00:00	[ "Public" ]	[]	jpeg	14cb28c0b20bfb86bdd17a7f6c645ba9f9d2d6c1bd29d1f7ca72cc3b18dbb8db	6881262			1	0	0	0	0	0	0	0
54		50A206BB-03FE-4D52-9C53-F6AA8250E786	2020-05-03T00:57:56+00:00	[ "Public" ]	[]	jpeg	1affd7c600b2972aa1d064003009ee685a52e08e72574537810267f1b73b75ca	7348214			1	0	0	0	0	0	0	0
178		56A360C6-C931-4589-B8A6-D34181EF6C7A	2020-05-03T00:55:00+00:00	[ "Public" ]	[]	jpeg	593c0a341f4c24b76519b19675fb71461c64973032ad9b12cf9ea6a73a0d9eb3	4910868			1	0	0	0	0	0	0	0
449		5976A68A-5495-45EA-B97C-BBB9620DCE11	2020-05-03T00:51:05+00:00	[ "Public" ]	[]	jpeg	e3ce253a5e7e008bda9a02a539602cabb3ac4c124a34c2d49484d6f853edf21a	5656813			1	0	0	0	0	0	0	0

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;