Replicating the ST_LineLocatePoint function from PostGIS in Google BigQuery

PostGIS has a function ST_LineLocatePoint which is really useful in multiple use cases including snapping lines to roads (common in mobility use cases, or for cleaning GPS data) as I've previously shown here. Unfortunately Google BigQuery does not yet have this function, however it is easy to replicate!

ST_LineLocatePoint returns the distance of the closest point along a line as a fraction of it's length as shown in the diagram above. To replicate this, we can use the following SQL:

To make this more repeatable, we can turn it into a persistent UDF (User Defined Function) ready for re-use:

Which can then be used like any other function:

Code available as a Github Gist here - enjoy!