Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Snippet for 2020-03-11 post on geoMusings
SELECT
ARRAY_TO_STRING(ARRAY_AGG(CONCAT(st_x(q.loc),',',st_y(q.loc))), ';') AS coords
FROM (
WITH
DATA AS (
SELECT
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
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
'Sample Asset' AS name,
resource_id,
loc,
capture_date
FROM
DATA
WHERE
wkt<>prev_loc
ORDER BY
capture_date ASC) q
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment