photos_on_a_map (view)

4 rows where "date" is on date 2017-07-23

descending

date	latitude	longitude	sha256	ext	popup
2017-07-23T22:20:34+00:00	37.84245	-122.27783333333333	1cfb72be21bffca1c026c49845045769dc2791f7844d85d14e74257325d83b23	jpeg	{ "title": "Taken on 2017-07-23T22:20:34+00:00", "image": "https://photos.simonwillison.net/i/1cfb72be21bffca1c026c49845045769dc2791f7844d85d14e74257325d83b23.jpeg?w=400", "link": "https://photos.simonwillison.net/i/1cfb72be21bffca1c026c49845045769dc2791f7844d85d14e74257325d83b23.jpeg?w=1200" }
2017-07-23T22:13:38+00:00	37.84250333333333	-122.27778666666667	edcd504a5dc67013013c8de46adbc2f586d9d56e572d634d594390fd81b20a95	jpeg	{ "title": "Taken on 2017-07-23T22:13:38+00:00", "image": "https://photos.simonwillison.net/i/edcd504a5dc67013013c8de46adbc2f586d9d56e572d634d594390fd81b20a95.jpeg?w=400", "link": "https://photos.simonwillison.net/i/edcd504a5dc67013013c8de46adbc2f586d9d56e572d634d594390fd81b20a95.jpeg?w=1200" }
2017-07-23T22:11:35+00:00	37.8424	-122.27777166666667	91cd63d05fe337816cae4ca57e9882ccdc5d6f8b921236e703a55e50f116d08d	jpeg	{ "title": "Taken on 2017-07-23T22:11:35+00:00", "image": "https://photos.simonwillison.net/i/91cd63d05fe337816cae4ca57e9882ccdc5d6f8b921236e703a55e50f116d08d.jpeg?w=400", "link": "https://photos.simonwillison.net/i/91cd63d05fe337816cae4ca57e9882ccdc5d6f8b921236e703a55e50f116d08d.jpeg?w=1200" }
2017-07-23T22:07:25+00:00	37.842288333333336	-122.27801333333333	1b68d008a6d555d3a21f96a9ea31e9c1c6513395b93d3349061a5cc8825080b1	jpeg	{ "title": "Taken on 2017-07-23T22:07:25+00:00", "image": "https://photos.simonwillison.net/i/1b68d008a6d555d3a21f96a9ea31e9c1c6513395b93d3349061a5cc8825080b1.jpeg?w=400", "link": "https://photos.simonwillison.net/i/1b68d008a6d555d3a21f96a9ea31e9c1c6513395b93d3349061a5cc8825080b1.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;