photos_with_apple_metadata (view)

5 rows where "date" is on date 2016-11-05 sorted by ZPROMOTIONSCORE

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: persons, hdr, ZHIGHLIGHTVISIBILITYSCORE, ZBEHAVIORALSCORE, ZINTERACTIONSCORE, 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
191		25F1BCBC-3562-408E-9680-6FCEF1B81AA2	2016-11-05T21:21:05+00:00	[ "Museums", "Public" ]	[]	jpeg	5f9968989d0aad8a9dd9c6aabd5e495444a44a8714a1fa3296a96132b67de178	2555246	37.82716333333333	-122.52745833333333	0	0	0	0	0	0	0	0	Sausalito	CA	United States	25F1BCBC-3562-408E-9680-6FCEF1B81AA2	0.50244140625	0.5	0.0	0.038931297721298594	0.10000000149011612	-0.0023326873779296875	0.005535125732421875	0.004665374755859375	0.019999999552965164	-0.384033203125	-0.036773681640625	0.181396484375	0.0038852691650390625	-0.02947998046875	-0.0823974609375	-0.1904296875	-0.1136474609375	0.1702880859375	-0.056884765625	0.001659393310546875	-0.056396484375	0.006587982177734375	0.01467132568359375	-0.00415802001953125	-0.0748291015625	0.009765625	-0.01593017578125
432		69C46054-1D8F-4FD6-ABC7-2369C79921F4	2016-11-05T20:55:46+00:00	[ "Museums", "Public" ]	[ "_UNKNOWN_" ]	jpeg	d966b699790953594f2be6cc03c5e5b3d478ebf27122037a7dc022db7642411e	2299000	37.828136666666666	-122.52736666666667	0	0	0	0	0	0	0	0	Sausalito	CA	United States	69C46054-1D8F-4FD6-ABC7-2369C79921F4	0.4814453125	0.5	0.0	0.038931297721298594	0.10000000149011612	-0.0009508132934570312	0.047760009765625	0.05499267578125	0.009999999776482582	-0.06640625	-0.043701171875	0.10211181640625	0.0026111602783203125	-0.004505157470703125	-0.045806884765625	0.0196533203125	0.0323486328125	0.05194091796875	0.1417236328125	0.0133056640625	0.00213623046875	0.00971221923828125	0.00661468505859375	-0.0017147064208984375	-0.00029754638671875	-0.0811767578125	0.0098724365234375
302		2DA58E9D-038C-49B2-ABCB-2185E57E8F68	2016-11-05T20:55:42+00:00	[ "Museums", "Public" ]	[]	jpeg	90f7eaebeafeb0eeb14cdc064d541761d82d969c173687df7d0bd3551f1ae240	2520930	37.827938333333336	-122.52742	0	0	0	0	0	0	0	0	Sausalito	CA	United States	2DA58E9D-038C-49B2-ABCB-2185E57E8F68	0.31591796875	0.5	0.0	0.038931297721298594	0.503017008304596	-0.001956939697265625	0.0648193359375	0.0282745361328125	0.009999999776482582	0.2083740234375	-0.04913330078125	-0.018768310546875	0.05291748046875	-0.01306915283203125	-0.023773193359375	0.1820068359375	0.0557861328125	0.120849609375	0.4013671875	0.07073974609375	0.00934600830078125	0.0231475830078125	0.0257568359375	0.020965576171875	0.044036865234375	0.0660400390625	0.041961669921875
206		07509762-A841-4CEB-AA42-B65439E9C3DD	2016-11-05T20:50:06+00:00	[ "Museums", "Public" ]	[]	jpeg	679f1f8f0d139dba297bf0d5ecadcaed7259d6c812e6a825e2d3999cc301d5b3	3572478	37.82793	-122.52746666666667	0	0	0	0	0	0	0	0	Sausalito	CA	United States	07509762-A841-4CEB-AA42-B65439E9C3DD	0.6259765625	0.5	0.0	0.8053737856049574	0.10000000149011612	-0.0006985664367675781	0.037078857421875	0.00669097900390625	0.009999999776482582	0.080078125	-0.0257110595703125	0.2083740234375	0.0021152496337890625	-0.009674072265625	-0.003387451171875	0.104736328125	0.0789794921875	0.060516357421875	0.1619873046875	0.01324462890625	-0.003513336181640625	0.0063629150390625	0.08209228515625	0.025970458984375	0.15087890625	0.417236328125	0.03619384765625
255		EECDD2BE-E93F-4830-8435-68681476E833	2016-11-05T20:47:12+00:00	[ "Museums", "Public" ]	[]	jpeg	7c8518ac608ee1cec1d652018536ba1e77fd2434536d5016ef825e0990b80bb6	1551766	37.82842	-122.527275	0	0	0	0	0	1	0	0	Sausalito	CA	United States	EECDD2BE-E93F-4830-8435-68681476E833	0.432861328125	0.5	0.0	0.038931297721298594	0.10000000149011612	-0.0020198822021484375	0.009552001953125	0.00595855712890625	0.009999999776482582	-0.31591796875	-0.06304931640625	0.0941162109375	0.01016998291015625	-0.0251312255859375	-0.0953369140625	-0.33154296875	-0.14111328125	0.1279296875	-0.02105712890625	0.0037994384765625	-0.0222930908203125	0.00626373291015625	0.01073455810546875	-0.00830078125	-0.09222412109375	-0.0933837890625	-0.0088958740234375

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;