photos_on_a_map (view)

5 rows where "date" is on date 2017-03-31

descending

date	latitude	longitude	sha256	ext	popup
2017-03-31T00:53:36+00:00	37.76918	-122.46453833333334	a93401fea4842b551132a24a707ae8e30d646d4830c5c32c3ce7c1ddcdd61bbc	jpeg	{ "title": "Taken on 2017-03-31T00:53:36+00:00", "image": "https://photos.simonwillison.net/i/a93401fea4842b551132a24a707ae8e30d646d4830c5c32c3ce7c1ddcdd61bbc.jpeg?w=400", "link": "https://photos.simonwillison.net/i/a93401fea4842b551132a24a707ae8e30d646d4830c5c32c3ce7c1ddcdd61bbc.jpeg?w=1200" }
2017-03-31T00:53:35+00:00	37.76917	-122.46453	4b6d5de8dc4967be44dc73b9c1e8a47dc0b247c0d08b0020ab69947f1441aada	jpeg	{ "title": "Taken on 2017-03-31T00:53:35+00:00", "image": "https://photos.simonwillison.net/i/4b6d5de8dc4967be44dc73b9c1e8a47dc0b247c0d08b0020ab69947f1441aada.jpeg?w=400", "link": "https://photos.simonwillison.net/i/4b6d5de8dc4967be44dc73b9c1e8a47dc0b247c0d08b0020ab69947f1441aada.jpeg?w=1200" }
2017-03-31T00:53:31+00:00	37.769146666666664	-122.46449166666666	ddea960de035d2fc5726dd94a94692f90f7dc60c7e28e19b45fcc765e2d0813c	jpeg	{ "title": "Taken on 2017-03-31T00:53:31+00:00", "image": "https://photos.simonwillison.net/i/ddea960de035d2fc5726dd94a94692f90f7dc60c7e28e19b45fcc765e2d0813c.jpeg?w=400", "link": "https://photos.simonwillison.net/i/ddea960de035d2fc5726dd94a94692f90f7dc60c7e28e19b45fcc765e2d0813c.jpeg?w=1200" }
2017-03-31T00:48:54+00:00	37.7692	-122.46450833333333	fe7c5489ffa8c070183fe2c559c00b6d5880a6b9a9c53f807962166ec96c4375	jpeg	{ "title": "Taken on 2017-03-31T00:48:54+00:00", "image": "https://photos.simonwillison.net/i/fe7c5489ffa8c070183fe2c559c00b6d5880a6b9a9c53f807962166ec96c4375.jpeg?w=400", "link": "https://photos.simonwillison.net/i/fe7c5489ffa8c070183fe2c559c00b6d5880a6b9a9c53f807962166ec96c4375.jpeg?w=1200" }
2017-03-31T00:48:44+00:00	37.76913	-122.46448666666667	c5b53e0765f828354402aad8d9de6be1c70b4c78cc0db97d30f31697b01fb591	jpeg	{ "title": "Taken on 2017-03-31T00:48:44+00:00", "image": "https://photos.simonwillison.net/i/c5b53e0765f828354402aad8d9de6be1c70b4c78cc0db97d30f31697b01fb591.jpeg?w=400", "link": "https://photos.simonwillison.net/i/c5b53e0765f828354402aad8d9de6be1c70b4c78cc0db97d30f31697b01fb591.jpeg?w=1200" }

Advanced export

JSON shape: default, array, newline-delimited

CREATE VIEW photos_on_a_map AS select
      date,
      latitude,
      longitude,
      apple_photos.sha256,
      uploads.ext,
      json_object(
        'title',
        'Taken on ' || date,
        'image',
        'https://photos.simonwillison.net/i/' || uploads.sha256 || '.' || uploads.ext || '?w=400',
        'link',
        'https://photos.simonwillison.net/i/' || uploads.sha256 || '.' || uploads.ext || '?w=1200'
      ) as popup
    from
      apple_photos
      join uploads on apple_photos.sha256 = uploads.sha256
    where
      latitude is not null
    order by
      date desc;