SQL: Calculate distance between two points using coordinates

I’ve got a table with lat and lng coordnates, and need to add the distance into a new column called ‘distance’ in Bigquery.

table

start_lat	end_lat	start_lng	end_lng
41.8964	41.9322	-87.661	-87.6586
41.9244	41.9306	-87.7154	-87.7238
41.903	41.8992	-87.6975	-87.6722

I haven’t a clue how to do it. I saw some examples, but simply couldn’t apply it into this case. Any tip?

Answer

The ST_DISTANCE function will calculate the distance (in meters) between 2 points.

with my_data as (
  select 1 as trip_id, 41.8964 as start_lat, 41.9322 as end_lat, -87.661 as  start_lng, -87.6586 as end_lng union all
  select 2, 41., 41.9306, -87.7154, -87.7238
)
select trip_id, 
  ST_DISTANCE(ST_GEOGPOINT(start_lng, start_lat), ST_GEOGPOINT(end_lng, end_lat)) as distance_in_meters
from my_data

Output:

trip_id	distance_in_meters
1	3985.735019583467
2	103480.52812005761