Calculating mileage in SQL query

1

Hi, I'm working on a SQL project and developed a query to calculate the distance between trip commutes. I'm trying to determine how to calculate mileage for the distance column. Can anyone assist me with this issue?

--Find the average distance between each station, if the each trip starts at one station and ends at a different station. 
SELECT 
  id, 
  b.user_type, 
  name AS start_station, 
  Sum(
    sqrt(
      Power(
        (
          Select 
            latitude 
          from 
            capitalbikeshare_stations 
          WHERE 
            capitalbikeshare_stations.id = B.end_station_id
        )- S.latitude, 
        2
      )+ power(
        (
          Select 
            longitude 
          from 
            capitalbikeshare_stations 
          Where 
            capitalbikeshare_stations.id = B.end_station_id
        )- S.longitude, 
        2
      )
    )
  ) / Count(*) AS distance 
FROM 
  capitalbikeshare_stations as S 
  Inner Join capitalbikeshare_2019 as B On S.id = B.start_station_id 
Where 
  start_station_id <> end_station_id 
GROUP BY 
  id, 
  b.user_type 
ORDER BY 
  distance DESC;
3

To get a good answer you will need to post consumable test data and NOT images! (ie DDL and Inserts)

My approach would be to convert the latitude and longitude to a spatial data type and then do the calculation.

1 Like