photos_on_a_map (view)

5 rows where "date" is on date 2016-11-05

descending

date	latitude	longitude	sha256	ext	popup
2016-11-05T21:21:05+00:00	37.82716333333333	-122.52745833333333	5f9968989d0aad8a9dd9c6aabd5e495444a44a8714a1fa3296a96132b67de178	jpeg	{ "title": "Taken on 2016-11-05T21:21:05+00:00", "image": "https://photos.simonwillison.net/i/5f9968989d0aad8a9dd9c6aabd5e495444a44a8714a1fa3296a96132b67de178.jpeg?w=400", "link": "https://photos.simonwillison.net/i/5f9968989d0aad8a9dd9c6aabd5e495444a44a8714a1fa3296a96132b67de178.jpeg?w=1200" }
2016-11-05T20:55:46+00:00	37.828136666666666	-122.52736666666667	d966b699790953594f2be6cc03c5e5b3d478ebf27122037a7dc022db7642411e	jpeg	{ "title": "Taken on 2016-11-05T20:55:46+00:00", "image": "https://photos.simonwillison.net/i/d966b699790953594f2be6cc03c5e5b3d478ebf27122037a7dc022db7642411e.jpeg?w=400", "link": "https://photos.simonwillison.net/i/d966b699790953594f2be6cc03c5e5b3d478ebf27122037a7dc022db7642411e.jpeg?w=1200" }
2016-11-05T20:55:42+00:00	37.827938333333336	-122.52742	90f7eaebeafeb0eeb14cdc064d541761d82d969c173687df7d0bd3551f1ae240	jpeg	{ "title": "Taken on 2016-11-05T20:55:42+00:00", "image": "https://photos.simonwillison.net/i/90f7eaebeafeb0eeb14cdc064d541761d82d969c173687df7d0bd3551f1ae240.jpeg?w=400", "link": "https://photos.simonwillison.net/i/90f7eaebeafeb0eeb14cdc064d541761d82d969c173687df7d0bd3551f1ae240.jpeg?w=1200" }
2016-11-05T20:50:06+00:00	37.82793	-122.52746666666667	679f1f8f0d139dba297bf0d5ecadcaed7259d6c812e6a825e2d3999cc301d5b3	jpeg	{ "title": "Taken on 2016-11-05T20:50:06+00:00", "image": "https://photos.simonwillison.net/i/679f1f8f0d139dba297bf0d5ecadcaed7259d6c812e6a825e2d3999cc301d5b3.jpeg?w=400", "link": "https://photos.simonwillison.net/i/679f1f8f0d139dba297bf0d5ecadcaed7259d6c812e6a825e2d3999cc301d5b3.jpeg?w=1200" }
2016-11-05T20:47:12+00:00	37.82842	-122.527275	7c8518ac608ee1cec1d652018536ba1e77fd2434536d5016ef825e0990b80bb6	jpeg	{ "title": "Taken on 2016-11-05T20:47:12+00:00", "image": "https://photos.simonwillison.net/i/7c8518ac608ee1cec1d652018536ba1e77fd2434536d5016ef825e0990b80bb6.jpeg?w=400", "link": "https://photos.simonwillison.net/i/7c8518ac608ee1cec1d652018536ba1e77fd2434536d5016ef825e0990b80bb6.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;