photos_with_apple_metadata (view)

12 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: albums, longitude, place_city, place_state_province, place_country, ZCURATIONSCORE, ZHIGHLIGHTVISIBILITYSCORE, ZBEHAVIORALSCORE, ZFAILURESCORE, ZIMMERSIVENESSSCORE, ZINTERACTIONSCORE, ZLOWLIGHT, ZNOISESCORE, ZPLEASANTPOSTPROCESSINGSCORE, ZSHARPLYFOCUSEDSUBJECTSCORE, date (date), albums (array)

rowid 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
9 D1B958E3-2990-4F12-BE97-5C1C32830D2E 2021-12-30T17:58:41+00:00 ["2021 in pictures"] [] HEIC 8b4cd18bdaf58d926273787ab6bab546b654224a6689e198f87a688647c7971e 1761684 52.499478333333336 13.461246666666666 0 0 0 0 0 0 0 0 Berlin Berlin Germany D1B958E3-2990-4F12-BE97-5C1C32830D2E 0.5361328125 0.6 0.0 0.04656488550755814 0.10000000149011612 -0.000244140625 0.06103515625 0.001708984375 0.019999999552965164 -0.146728515625 -0.013916015625 0.3779296875 0.001953125 -0.01025390625 0.010009765625 0.0595703125 0.1103515625 0.223388671875 0.162841796875 0.015380859375 -0.003662109375 0.00390625 0.17724609375 0.083251953125 0.108642578125 0.376708984375 0.002685546875
12 D17CDA85-5AF6-4C88-AB58-C998896EF05E 2021-12-28T16:04:07+00:00 ["2021 in pictures"] [] HEIC c7b5bd358966f1e8523b2591460cd3acb4ae2e5b945c402d5d77d870bb449e93 1379522 51.567611666666664 7.614541666666667 0 0 0 0 0 0 0 0 Kamen North Rhine-Westphalia Germany D17CDA85-5AF6-4C88-AB58-C998896EF05E 0.291015625 0.6 0.0 0.038931297721298594 0.10000000149011612 -0.0048828125 -0.003662109375 0.002685546875 0.03999999910593033 -0.411376953125 -0.03662109375 0.06591796875 0.004150390625 -0.06787109375 -0.081298828125 -0.26416015625 -0.41357421875 0.12939453125 -0.099365234375 -0.000732421875 -0.023193359375 0.0029296875 0.0048828125 -0.013671875 -0.198486328125 -0.034423828125 -0.046875
6 F45CFBA3-B3BF-4435-A3C9-8C4EA628E128 2021-11-29T18:41:37+00:00 ["2021 in pictures"] [] HEIC 7cf9cdc460fe7f62c219bee673a79299b7eafa4cf3559e8b0a9751f5209f00e1 1603263 52.499471666666665 13.461246666666666 0 0 0 0 0 0 0 0 Berlin Berlin Germany F45CFBA3-B3BF-4435-A3C9-8C4EA628E128 0.48046875 0.5 0.0 0.038931297721298594 0.10000000149011612 -0.000732421875 0.03369140625 0.002685546875 0.11999999731779099 -0.10693359375 -0.031494140625 0.35009765625 0.00146484375 -0.01416015625 -0.00732421875 -0.072021484375 0.03759765625 0.196044921875 0.076416015625 0.017822265625 -0.016357421875 0.005126953125 0.088623046875 0.04833984375 0.033203125 0.309326171875 -0.002197265625
11 582D091A-7EC1-48A5-ABF8-407D1A566861 2021-09-10T19:06:48+00:00 ["2021 in pictures"] [] HEIC c6f7309ff11938885c14c4b36e5e11508399e55f050d3c4cf02b820f4c6c897d 2013613 48.711358329999996 -3.8066333299999995 0 0 0 0 0 0 0 0 Plougasnou Brittany France 582D091A-7EC1-48A5-ABF8-407D1A566861 0.68798828125 0.5 0.0 0.043556628791430525 0.7067856788635254 -0.000244140625 0.122314453125 0.06005859375 0.03999999910593033 0.1875 -0.013427734375 0.252685546875 0.00732421875 -0.00537109375 -0.019287109375 0.270263671875 0.115234375 0.047119140625 0.16845703125 0.04150390625 0.01123046875 0.00537109375 0.007080078125 0.00048828125 0.03515625 0.03515625 0.035888671875
2 E07E1130-5B2F-4170-9B15-0C671658BC1F 2021-09-03T18:47:26+00:00 ["2021 in pictures"] [] HEIC 34495368bab4f6da4fa225950d3a0c931e717dfbb4532675f49500ca48d2f7bc 1600182 48.71195332999999 -3.807203329999999 0 0 0 0 0 0 0 0 Plougasnou Brittany France E07E1130-5B2F-4170-9B15-0C671658BC1F 0.65966796875 0.5 0.0 0.04355195505928447 0.7058928608894348 -0.000732421875 0.1015625 0.25390625 0.03999999910593033 0.070068359375 -0.017333984375 0.114013671875 0.00537109375 -0.007080078125 -0.01904296875 0.351318359375 0.114013671875 0.06298828125 0.24755859375 0.049072265625 0.02294921875 0.010009765625 0.005859375 0.003173828125 0.007568359375 -0.0234375 0.03271484375
4 5C4176F5-D2A3-42CC-8209-B1B3FD1AFA2A 2021-08-29T07:03:32+00:00 ["Frankreich - Balkon", "2021 in pictures"] [] HEIC 3aa6c9cd3dcc0abd2a26bc0e7c90f67c96cb31df3479b9331438df67ccab1127 2241928 48.71063667 -3.80465333 0 0 0 0 0 0 0 0 Plougasnou Brittany France 5C4176F5-D2A3-42CC-8209-B1B3FD1AFA2A 0.56201171875 0.6 0.0 0.05121183213386827 0.7087500095367432 -0.000244140625 0.0732421875 0.022216796875 0.2199999988079071 0.067626953125 -0.022705078125 0.256103515625 0.001708984375 -0.007080078125 -0.010009765625 0.24560546875 0.112548828125 0.5595703125 0.139892578125 0.009521484375 0.015869140625 0.04736328125 0.01806640625 0.00341796875 0.052490234375 0.1572265625 0.007568359375
5 0483D9A5-1462-434F-B3E5-FF569122061A 2021-07-20T16:48:16+00:00 ["2021 in pictures"] [] HEIC 3e449940062e1c2e740351d912c587618b5d8126cd7cc79061e5e826d0a72ebd 1623519 52.518769999999996 13.37585833 0 0 0 0 0 0 0 0 Berlin Berlin Germany 0483D9A5-1462-434F-B3E5-FF569122061A 0.48876953125 0.6 0.0 0.8099236641335124 0.10000000149011612 -0.0009765625 0.0146484375 0.033203125 0.019999999552965164 -0.20068359375 -0.044189453125 -0.00439453125 0.008544921875 -0.013427734375 -0.05029296875 -0.012939453125 -0.202392578125 0.179443359375 0.1484375 0.00390625 -0.002197265625 0.017822265625 0.00390625 -0.004150390625 -0.000732421875 -0.051513671875 0.02734375
8 A22D1A8D-B448-4888-A0B2-1F3E66D9FCD5 2021-04-17T16:37:18+00:00 ["2021 in pictures"] [] HEIC 8637e3598b48740f6b80f7f12cfc7ee8ced7444fcfe5e306cc0b8ed1da6c5617 2001624 52.49595333 13.462820000000002 0 0 0 0 0 0 0 0 Berlin Berlin Germany A22D1A8D-B448-4888-A0B2-1F3E66D9FCD5 0.63623046875 0.6 0.0 0.8130199217614327 0.5067856907844543 -0.000244140625 0.086181640625 0.11328125 0.009999999776482582 0.09228515625 -0.036865234375 0.277099609375 0.001220703125 -0.005859375 -0.027587890625 0.218017578125 0.073974609375 0.260986328125 0.134033203125 0.019287109375 0.124755859375 0.0224609375 0.00439453125 0.0009765625 -0.0078125 -0.0537109375 0.01123046875
1 5AD2CE08-609F-47CB-91F3-15D4051F009F 2021-03-07T13:50:07+00:00 ["2021 in pictures"] [] HEIC 2a61e08dcea8dd51a5941ab3e7878125841295ce6e01e255a38725dab401ba91 2880406 52.49956333 13.461336669999998 0 0 0 0 0 0 0 0 Berlin Berlin Germany 5AD2CE08-609F-47CB-91F3-15D4051F009F 0.6201171875 0.5 0.0 0.038931297721298594 0.1007142886519432 0.0 0.043212890625 0.006103515625 0.03999999910593033 0.328857421875 -0.028564453125 -0.0283203125 0.0048828125 -0.004150390625 0.004150390625 0.5205078125 0.256103515625 0.311767578125 0.459228515625 0.00390625 0.004638671875 0.263916015625 0.158203125 0.011962890625 0.089599609375 0.7158203125 0.03125
7 CDF9A542-7D46-4EF0-96AA-25674749C731 2021-02-16T11:31:03+00:00 ["2021 in pictures"] [] HEIC 83601fb1a1a0b64ad9683c2fd1eebf7e4f6b5ef96ecb2c21e1062812b3ff95b4 1186484 52.498333329999994 13.45882833 0 0 0 0 0 0 0 0 Berlin Berlin Germany CDF9A542-7D46-4EF0-96AA-25674749C731 0.6044921875 0.5 0.0 0.04201824610469906 0.5049999952316284 -0.000732421875 0.071044921875 0.20703125 0.019999999552965164 0.082275390625 -0.03466796875 0.07275390625 0.001953125 -0.01025390625 -0.019775390625 0.272216796875 0.0537109375 0.129150390625 0.228759765625 0.046875 0.00927734375 0.0263671875 0.0048828125 0.000244140625 0.013671875 -0.02490234375 0.015625
3 48051C79-D67C-4218-B30B-0C1D901EE7B0 2021-02-12T12:06:57+00:00 ["2021 in pictures"] [] HEIC 3905f6e755225129b71cab739ba52e830a0e4969a5604da0f5423d1f1d55526f 1830983 52.49947833 13.455220000000002 0 0 0 0 0 0 0 0 Berlin Berlin Germany 48051C79-D67C-4218-B30B-0C1D901EE7B0 0.55615234375 0.5 0.0 0.04201079870908315 0.5042856931686401 -0.000244140625 0.083740234375 0.052001953125 0.019999999552965164 -0.2216796875 -0.0869140625 0.094970703125 0.00244140625 -0.002197265625 -0.061279296875 -0.1005859375 0.141357421875 0.097412109375 0.2294921875 0.012451171875 0.00244140625 0.012451171875 0.005615234375 0.000732421875 -0.002685546875 -0.142333984375 0.008056640625
10 BD92A771-54FC-493B-B091-51B2E28BD4DF 2021-01-18T12:27:11+00:00 ["2021 in pictures"] [] HEIC a26836fc703091b2f92301aa2ab1565490e89c0948025a8e743d43b4465ecf88 2377022 52.49784167 13.460325000000001 0 0 0 0 0 0 0 0 Berlin Berlin Germany BD92A771-54FC-493B-B091-51B2E28BD4DF 0.6884765625 0.6 0.0 0.04656488550755814 0.10000000149011612 -0.000244140625 0.069091796875 0.1806640625 0.07999999821186066 0.07763671875 -0.015625 0.1689453125 0.001708984375 -0.00634765625 0.01025390625 0.400146484375 0.1904296875 0.74560546875 0.32470703125 0.039794921875 0.027587890625 0.11328125 0.01171875 0.00537109375 0.00439453125 0.01708984375 0.010986328125

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

CREATE VIEW photos_with_apple_metadata AS 
    select
        apple_photos.rowid,
        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;