photos_on_a_map (view)

5 rows where "date" is on date 2017-06-16

descending

date	latitude	longitude	sha256	ext	popup
2017-06-16T01:28:19+00:00	37.772616666666664	-122.45991333333333	5dd80ab326459d68c5d9acc8f7094aa4761b9f98ff7f774a3c8b1c25cbf49f83	jpeg	{ "title": "Taken on 2017-06-16T01:28:19+00:00", "image": "https://photos.simonwillison.net/i/5dd80ab326459d68c5d9acc8f7094aa4761b9f98ff7f774a3c8b1c25cbf49f83.jpeg?w=400", "link": "https://photos.simonwillison.net/i/5dd80ab326459d68c5d9acc8f7094aa4761b9f98ff7f774a3c8b1c25cbf49f83.jpeg?w=1200" }
2017-06-16T00:56:17+00:00	37.76925	-122.46415	d553261ef8ec0fc2d81eb04e584491dfcba6d4d58b8455cc741c362ec21eea22	jpeg	{ "title": "Taken on 2017-06-16T00:56:17+00:00", "image": "https://photos.simonwillison.net/i/d553261ef8ec0fc2d81eb04e584491dfcba6d4d58b8455cc741c362ec21eea22.jpeg?w=400", "link": "https://photos.simonwillison.net/i/d553261ef8ec0fc2d81eb04e584491dfcba6d4d58b8455cc741c362ec21eea22.jpeg?w=1200" }
2017-06-16T00:55:34+00:00	37.76930333333333	-122.46441666666666	be2e31a4d8ecf12e6631c7fbd0a093a446a7d23ef5dbeb412c41c14907357d16	jpeg	{ "title": "Taken on 2017-06-16T00:55:34+00:00", "image": "https://photos.simonwillison.net/i/be2e31a4d8ecf12e6631c7fbd0a093a446a7d23ef5dbeb412c41c14907357d16.jpeg?w=400", "link": "https://photos.simonwillison.net/i/be2e31a4d8ecf12e6631c7fbd0a093a446a7d23ef5dbeb412c41c14907357d16.jpeg?w=1200" }
2017-06-16T00:54:59+00:00	37.76912	-122.46459166666666	85c32ecbb8b5b4e75514799212c4bdd8c51469269e931a1fb6aa53f87dc92676	jpeg	{ "title": "Taken on 2017-06-16T00:54:59+00:00", "image": "https://photos.simonwillison.net/i/85c32ecbb8b5b4e75514799212c4bdd8c51469269e931a1fb6aa53f87dc92676.jpeg?w=400", "link": "https://photos.simonwillison.net/i/85c32ecbb8b5b4e75514799212c4bdd8c51469269e931a1fb6aa53f87dc92676.jpeg?w=1200" }
2017-06-16T00:54:53+00:00	37.76909666666667	-122.46451666666667	318022d1b2b2e9804d550276366c3b8ad3a61fe5efaf79af0c59d0ae647ff195	jpeg	{ "title": "Taken on 2017-06-16T00:54:53+00:00", "image": "https://photos.simonwillison.net/i/318022d1b2b2e9804d550276366c3b8ad3a61fe5efaf79af0c59d0ae647ff195.jpeg?w=400", "link": "https://photos.simonwillison.net/i/318022d1b2b2e9804d550276366c3b8ad3a61fe5efaf79af0c59d0ae647ff195.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;