photos_with_apple_metadata (view)

11 rows where albums = "["Photography photo essay", "Public"]"

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: date (date), 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
76		C4D15F2D-8571-447F-B195-05FCDD09A6B5	2020-05-07T16:53:21+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	2255b2c89fd53713fbe7619b0cf93798b616f09ec8f70bf4463571d95ff51456	5833242			1	0	0	0	0	0	0	0
476		6ABAD7FA-24CD-4ED7-BB76-6EECB1CCCACC	2020-05-07T16:52:48+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	f3c765416273a026a566354bccde331bb60228cba6e23452aad503dd270c195d	5120721			1	0	0	0	0	0	0	0
344		B4E86A08-9A42-4CD8-B609-0C5F2FDC1C1A	2020-05-07T16:45:25+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	a70e4feebebb39ea1227431432c17a6bc62ea261912e6aa121142839f1f5d6b3	8385771			1	0	0	0	0	0	0	0
425		B05A77E3-990A-4C34-8524-9B1A407076D4	2020-05-07T16:43:04+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	d840616a2d89956d7bec390b079db7f91a709acf959cf3b9516cb75030ceddb6	6425141			1	0	0	0	0	0	0	0
146		1F2AB404-400E-4CF9-8D71-00BF4D0FD1F6	2020-05-07T01:33:26+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	447a9f051d572fc50f2b2ace488986da0fc75ffe3e43395e8ab79f45a415ae65	6866686			1	0	0	0	0	0	0	0
440		504AF567-DE96-41BB-A7D5-6756ADE5F744	2020-05-07T01:31:13+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	df6d7ede02440bec57c9abd92b5db4679c2fe69c61fd3111f3c3ac86ff40c894	6597345			1	0	0	0	0	0	0	0
363		61139074-357E-4DB6-B179-0DA87E0BA367	2020-05-07T01:27:13+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	b2911b32aeb1fbf89f47f017454427272b2835d6bd54949d14af2f3608509fa2	6572231			1	0	0	0	0	0	0	0
162		0CFDCB94-2637-4C50-A7CF-B8BFE678CEB9	2020-05-07T01:22:04+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	5017ff03465b09ae3448f89fe7236ddab839a700425e1859547a1f3b0333d9ef	7001662			1	0	0	0	0	0	0	0
294		F35A676A-1518-4F62-85F5-21CB76D3EA6B	2020-05-07T01:21:14+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	8d8c87de34258a67e69ff741626a44eae0c5b0d1197c3fa26a7b0216a62a1e86	5689398			1	0	0	0	0	0	0	0
347		74A39A6F-13E4-464D-9597-C6FCC1688D29	2020-05-07T01:11:39+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	a8af36ea5827fa4aa732d7c13d332f1151e79154052e6376489bdeb6c16b0e06	4695872			1	0	0	0	0	0	0	0
231		7078337A-29B6-4536-BB90-A637165ABAE9	2020-05-07T01:09:27+00:00	[ "Photography photo essay", "Public" ]	[]	jpeg	71f4a6598c94b012f415073f1fb941c677733b4574178c2a7664af82a7060ab9	5757727			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;