Geography function over a column

Question

I am trying to use the st_makeline() function in order to create lines for every points and the next one in a single column. Do I need to create another column with the 2 points already ?

with t1 as(
SELECT *, ST_GEOGPOINT(cast(long as float64) , cast(lat as float64))  geometry FROM `my_table.faissal.trajets_flix` 
where id = 1
order by index_loc
)

select index_loc geometry
from t1

Here are the results

Thanks for your help

Answer 1

Consider also below simple and cheap option

select st_geogfromtext(format('linestring(%s)', 
    string_agg(long || ' ' || lat order by index_loc))
  ) as path
from `my_table.faissal.trajets_flix`
where id = 1             

if applied to sample data in your question - output is

which is visualized as

Answer 2

You seems to want to write this code:

• https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_makeline

WITH t1 as (
  SELECT *, ST_GEOGPOINT(cast(long as float64), cast(lat as float64)) geometry 
    FROM `my_table.faissal.trajets_flix` 
-- WHERE id = 1
)
SELECT id, ST_MAKELINE(ARRAY_AGG(geometry ORDER BY index_loc)) traj 
  FROM t1
 GROUP BY id;

with output:

When visualized on the map.