Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL sample for blog post - 22 Feb 2021
WITH data AS (
SELECT name, loc, resource_id, wkt, capture_date, (LAG(capture_date) OVER (PARTITION BY resource_id ORDER BY capture_date ASC)) AS prev_date,
(LAG(wkt) OVER (PARTITION BY resource_id ORDER BY capture_date ASC)) AS prev_loc
FROM
(SELECT name, resource_id, loc, st_astext(loc) as wkt, capture_date FROM `my_project.my_dataset.geo_sample`
order by capture_date desc) q
ORDER BY capture_date
)
SELECT name, resource_id, loc, capture_date FROM data
WHERE
wkt<>prev_loc
ORDER BY capture_date desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment