Calculate driving distance between origin and destination using longitude and latitude natively in BigQuery?

Jaskeil 0
Viewed 53

I would like to calculate driving distance between two points writing SQL in Google BigQuery. I understand there is a method to calculate linear distance or "bird" miles using the following function: ROUND(ST_DISTANCE(ST_GEOGPOINT(C.LONGITUDE, C.LATITUDE), ST_GEOGPOINT(B.LNG_NBR, B.LAT_NBR))/1609.34,2) AS LINEAR_DIST_MILES

However, I am interested in driving distance instead of a linear distance. Is there a way to do this natively in Google BigQuery without needing to hit a Google Map API? I've also explored some solutions in R but that requires a Google Maps API key.

google-maps google-cloud-platform google-bigquery sql r
1 Answers

You would need two parts

  1. good roads datasets
  2. routing algorithms

BigQuery public datasets includes OpenStreetMaps, which is a reasonable dataset of roads (and other types of information) in most areas. There is also TIGER (bigquery-public-data.geo_us_roads) dataset which is US-specific.

Carto provides a sets of UDFs that can be used for routing. They've published an article how to connect things together:

https://carto.com/blog/how-to-do-route-optimization-at-scale-with-carto-bigquery/

Michael Entin0
Related