CODEWITHSUNDEEP
sqlgoogle-bigquery
Alex
Alex

Reputation: 1537

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

Upvotes: 3

Views: 1336

Answers (2)

Francois BAPTISTE
Francois BAPTISTE

Reputation: 163

Now that native ST_PointN and ST_NUMPOINTS are available we can use direct transformation without parsing strings:

WITH linestring AS (
    SELECT ST_GeogFromText('linestring(1 1, 2 1, 3 2, 3 3)') g
)
SELECT ST_PointN(g, idx) mypoint
FROM linestring,
unnest(GENERATE_ARRAY(1,ST_NUMPOINTS(g))) idx

this one produces array

Row     mypoint
1       POINT(1 1)
2       POINT(2 1)
3       POINT(3 2)
4       POINT(3 3)

Upvotes: 5

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

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)

Upvotes: 5

Related Questions