How to create lines from ordered groupbyed points in BigQuery?-CodePudding

Reproducible setup

I have an (example) BigQuery table, project.table.geodata:

CREATE OR REPLACE TABLE `project.table.geodata` AS (
  SELECT ST_GEOGPOINT(70., 12.) AS geometry, 'a' AS loc, 1 as timestamp
   UNION ALL
  SELECT ST_GEOGPOINT(70., 14.) AS geometry, 'a' AS loc, 2 as timestamp
   UNION ALL
  SELECT ST_GEOGPOINT(72., 14.) AS geometry, 'a' AS loc, 3 as timestamp
    UNION ALL
  SELECT ST_GEOGPOINT(55., 24.) AS geometry, 'b' AS loc, 4 as timestamp
    UNION ALL
  SELECT ST_GEOGPOINT(55., 26.) AS geometry, 'b' AS loc, 5 as timestamp
    UNION ALL
  SELECT ST_GEOGPOINT(57., 26.) AS geometry, 'b' AS loc, 8 as timestamp
)

Visualization:

Wanted outcome

I would like to group together the points having the same loc and create a line from them, ordering them by increasing timestamp.

Manually creation of expected output:

CREATE OR REPLACE TABLE `project.table.lines` AS (
  SELECT ST_GeogFromText('LINESTRING(70 12, 70 14, 72 14)') as line
    UNION ALL
  SELECT ST_GeogFromText('LINESTRING(55 24, 55 26, 57 26)') as line
)

looks like:

The lines have the GEOGRAPHY datatype.

What I have tried

I think I should GROUP BY column loc, sort within each group by timestamp, and then create the linestring from the resulting arrays.

I came up with:

select st_makeline(array_agg(geometry)) as lines from `project.table.geodata` group by loc;

but this does not guarantee that the order of points is increasing with the value of their associated timestamp value within the line.

Question

What SQL query produces the desired output?

CodePudding user response：

You seem to be close the the answer. Just adding ORDER BY clause in your ARRAY_AGG will work as you want.

WITH geodata AS (
  SELECT ST_GEOGPOINT(70., 12.) AS geometry, 'a' AS loc, 1 as timestamp
   UNION ALL
  SELECT ST_GEOGPOINT(70., 14.) AS geometry, 'a' AS loc, 2 as timestamp
   UNION ALL
  SELECT ST_GEOGPOINT(72., 14.) AS geometry, 'a' AS loc, 3 as timestamp
    UNION ALL
  SELECT ST_GEOGPOINT(55., 24.) AS geometry, 'b' AS loc, 4 as timestamp
    UNION ALL
  SELECT ST_GEOGPOINT(55., 26.) AS geometry, 'b' AS loc, 5 as timestamp
    UNION ALL
  SELECT ST_GEOGPOINT(57., 26.) AS geometry, 'b' AS loc, 8 as timestamp
)
SELECT ST_MAKELINE(ARRAY_AGG(geometry ORDER BY timestamp)) AS lines 
  FROM geodata GROUP BY loc;

output:
 --------------------------------- 
|              lines              |
 --------------------------------- 
| LINESTRING(70 12, 70 14, 72 14) |
| LINESTRING(55 24, 55 26, 57 26) |
 ---------------------------------