Extract n coordinates from Line – Bigquery

I want to extract n coordinates (latitude, longitude) from a geography object (a line) I have on big query.

Is there a way to specify how many st_geogpoint I want to obtain? (which can be more or less than the st_geogpoint used to create the line with st_makeline)

Example:

LINESTRING(-115.2893119 36.218517, -115.2892195 36.2184946, -115.2879825 36.2184996, -115.2871506 36.2185021, -115.2870766 36.2185255)  

I want to be able to extract n (where n>=2) st_geogpoint from that line. Is it possible?

Output expected if n=2

[POINT(-115.2893119 36.218517), POINT(-115.2870766 36.2185255)]

Output expected if n=10

[POINT(-115.2893119 36.218517),
POINT_2,
POINT_3,
POINT_4,
POINT_5,
POINT_6,
POINT_7,
POINT_8,
POINT_9,
POINT(-115.2870766 36.2185255) ]

I can’t give the example of points in between the first and last, because I’m expecting them to be extracted from the LINE according to the value of n

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  SAFE.ST_GEOGFROMTEXT(CONCAT('POINT(', point, ')')) geo_object
FROM `project.dataset.table`,
UNNEST(REGEXP_EXTRACT_ALL(ST_ASTEXT(geo_object), r'[^,()]+')) point WITH OFFSET pos
WHERE pos BETWEEN 1 AND 3

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT ST_GEOGFROMTEXT('LINESTRING(-115.2893119 36.218517, -115.2892195 36.2184946, -115.2879825 36.2184996, -115.2871506 36.2185021, -115.2870766 36.2185255)') geo_object
)
SELECT 
  SAFE.ST_GEOGFROMTEXT(CONCAT('POINT(', point, ')')) geo_object
FROM `project.dataset.table`,
UNNEST(REGEXP_EXTRACT_ALL(ST_ASTEXT(geo_object), r'[^,()]+')) point WITH OFFSET pos
WHERE pos BETWEEN 1 AND 3   

with result

Row geo_object   
1   POINT(-115.2893119 36.218517)    
2   POINT(-115.2892195 36.2184946)   
3   POINT(-115.2879825 36.2184996)     

obviously by adjusting 1 and 3 in below line you ncan control the number of points to output

WHERE pos BETWEEN 1 AND 3   

In case when you expect output in same row as an array – see below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT ST_GEOGFROMTEXT('LINESTRING(-115.2893119 36.218517, -115.2892195 36.2184946, -115.2879825 36.2184996, -115.2871506 36.2185021, -115.2870766 36.2185255)') geo_object
)
SELECT 
  ARRAY(
    SELECT SAFE.ST_GEOGFROMTEXT(CONCAT('POINT(', point, ')'))
    FROM UNNEST(REGEXP_EXTRACT_ALL(ST_ASTEXT(geo_object), r'[^,()]+')) point WITH OFFSET pos
    WHERE pos BETWEEN 1 AND 3  
    AND NOT point IS NULL
  ) geo_objects
FROM `project.dataset.table`

this one produces array

Row geo_objects  
1   POINT(-115.2893119 36.218517)    
    POINT(-115.2892195 36.2184946)   
    POINT(-115.2879825 36.2184996)