photos_with_apple_metadata (view)

2 rows where "date" is on date 2017-12-16, longitude = "-0.1321805" and place_state_province = "England"

✎ 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
173		4E152F37-5FF2-43B0-BB98-FE9B6802BF55	2017-12-16T11:51:14+00:00	[ "Public" ]	[]	heic	57da25cee1c32557167b6d5458ac3ea56f683150c2611d6d8ad6a9b6dbb27276	2072848	51.503303333333335	-0.1321805	1	0	0	0	0	0	0	0	London	England	United Kingdom	4E152F37-5FF2-43B0-BB98-FE9B6802BF55	0.5703125	0.75	0.0	0.05801526718694745	0.10000000149011612	-0.0008358955383300781	0.032989501953125	0.00313568115234375	0.009999999776482582	-0.45654296875	-0.043121337890625	0.239501953125	0.0038547515869140625	-0.00919342041015625	-0.02197265625	-0.1068115234375	0.0599365234375	0.09857177734375	0.10504150390625	0.00670623779296875	-0.011199951171875	0.004467010498046875	0.097900390625	0.051239013671875	0.05975341796875	0.29052734375	-8.392333984375e-05
415		48EECE84-27A5-4AEC-9B09-6173F8700CEA	2017-12-16T11:51:09+00:00	[ "Public" ]	[]	heic	d1f5ff1dc55f467cc316d31897840e7ab50a1894823780b56c36e9ee6bda5c08	1806032	51.503303333333335	-0.1321805	0	0	0	0	0	0	0	0	London	England	United Kingdom	48EECE84-27A5-4AEC-9B09-6173F8700CEA	0.58349609375	0.5	0.0	0.04200794951606343	0.10000000149011612	-0.004520416259765625	0.0024871826171875	0.00269317626953125	0.019999999552965164	-0.498291015625	-0.06878662109375	0.1837158203125	0.006931304931640625	-0.023101806640625	-0.06317138671875	-0.370361328125	-0.158447265625	0.11920166015625	-0.0765380859375	-0.003047943115234375	-0.0631103515625	0.0030155181884765625	0.021575927734375	0.007659912109375	-0.190673828125	-0.0775146484375	-0.033233642578125

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;