Using SQL (Snowflake), I want to rank the coordinates for when each motorcycle is stationery (stationery time= time between trips) according to length

So there are two steps one is to filter "has moved"/"stationary" rows, which to be honest I am not 100% if that is what you are presenting in your data so we will start with a filter to get those (within a threshold)

SELECT name, duration, start_lat, start_lon
FROM table
/* where start is within 100m of start */
WHERE haversine(start_lat, start_lon, end_lat,end_lon) > 0.1 

and now we have our "stationary" rows we are want to rank the rows per name to find the longest of each, which can be done via a row_number

SELECT name, duration, start_lat, start_lon
FROM table
/* where start is within 100m of start */
WHERE haversine(start_lat, start_lon, end_lat,end_lon) > 0.1 
QUALIFY row_number() over( partition by name order by duration desc) = 1

which is the same as:

SELECT name, duration, start_lat, start_lon
FROM (
    SELECT name, duration, start_lat, start_lon
        ,row_number() over( partition by name order by duration desc) as rn
    FROM table
    /* where start is within 100m of start */
    WHERE haversine(start_lat, start_lon, end_lat,end_lon) > 0.1 
)
WHERE rn = 1

New facts: If each row is the "moving section" to get the details of the between rows is.

SELECT vehicle_id, 
    lag(time) over (partition by vehicle_id order by time) as stat_start_time,
    time as stat_end_time,
    date_diff('seconds', stat_start_time, stat_end_time) as stat_duration_s,
    lag(end_lat) over (partition by vehicle_id order by time) as stat_start_lat,
    lag(end_lon) over (partition by vehicle_id order by time) as stat_start_lon,
    start_lat as stat_end_lat,
    start_lon as stat_end_lon
FROM table

now we have a table of "stationary" entries, you can do what you wish with it.

thus the longest startionary period per vehicle becomes:

WITH data (vehicle_id, time, start_lat, start_lon, end_lat, end_lon) AS (
  SELECT column1, column2::time, column3, column4, column5, column6  FROM VALUES
    ('Motorcycle  1', '13:12', 2.28079, 77.70193, 2.23239, 33.72323),
    ('Motorcycle  1', '14:40', 2.23239, 33.72323, 9.23079, 78.42890),
    ('Motorcycle  2', '08:34', 9.23079, 78.42890, 8.13433, 12.70871),
    ('Motorcycle  2', '18:20', 8.13433, 12.70871, 7.23578, 99.00093),
    ('Motorcycle  3', '06:18', 7.23578, 99.00093, 2.34079, 75.44866),
    ('Motorcycle  3', '10:00', 2.34079, 75.44866, 1.25459, 17.23253),
    ('Motorcycle  3', '17:54', 1.25459, 17.23253, 8.78088, 99.00123)
)
SELECT vehicle_id,
    stat_duration_s,
    stat_start_lon,
    stat_start_lat
FROM (
    SELECT vehicle_id, 
        lag(time) over (partition by vehicle_id order by time) as stat_start_time,
        time as stat_end_time,
        datediff('seconds', stat_start_time, stat_end_time) as stat_duration_s,
        lag(end_lat) over (partition by vehicle_id order by time) as stat_start_lat,
        lag(end_lon) over (partition by vehicle_id order by time) as stat_start_lon,
        start_lat as stat_end_lat,
        start_lon as stat_end_lon
    FROM data
)
QUALIFY row_number() over( partition by vehicle_id order by stat_duration_s desc nulls last) = 1

gives: