SQL Snippet for 2020-03-11 post on geoMusings

vw_stops.sql

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