Skip to content

Instantly share code, notes, and snippets.

@geobabbler
Created March 11, 2021 12:01
Show Gist options
  • Save geobabbler/d592cd8d7a48c33a0a55d3982046c2e5 to your computer and use it in GitHub Desktop.
Save geobabbler/d592cd8d7a48c33a0a55d3982046c2e5 to your computer and use it in GitHub Desktop.
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