photos_with_apple_metadata (view)

11 rows where "date" is on date 2020-05-03

✎ 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;