I have the total number of poles in a location (a sample area) and the road geometry (linestring) of each road in the area. I can calculate the average distance between poles, I want to distribute these total number of poles evenly along the different roads (linestring). I see that ST_LINE_INTERPOLATE_POINT() can help me achieve this but I dont have this function in BigQuery.
Snippest of my road_table is attached here.
My approach is generate interpolated points along the road at regular intervals determined by the raio value that is calculated from array as show below:
SELECT road_geometry, ST_LINE_INTERPOLATE_POINT(road_geometry, ratio) AS interpolated_points
FROM road_table, UNNEST(GENERATE_ARRAY (0,1, 1/(road_length/avg_dist_pole) AS ratio
Hoping to have an output of array of POINT Geography for each record.
But ST_LINE_INTERPOLATE_POINT is not in BQ.
What other approach can I use to achieve this, beside I am not certain if this is the best approach?
BigQuery does not have ST_LINE_INTERPOLATE_POINT but it has ST_LINESUBSTRING which returns line sub-segment. If you take sub-segment from 0 to the given fraction, the end point of this sub-segment will be the point you need.
You can define ST_LINE_INTERPOLATE_POINT like
CREATE TEMP FUNCTION ST_LINE_INTERPOLATE_POINT(line GEOGRAPHY, fraction FLOAT64)
RETURNS GEOGRAPHY
AS (
ST_EndPoint(ST_LineSubString(line, 0, fraction))
);
And call it e.g.
SELECT ST_LINE_INTERPOLATE_POINT(
ST_GeogFromText('LINESTRING(1 1, 10 10)'),
0.9
);
Update: BigQuery got ST_LINEINTERPOLATEPOINT now: