SQL : Select longest and unique route

Question

I have a table that contains the route's GPS traces and I want to select only unique and longest routes.

Table looks like:

route_id        gpstrace
route1      [[long,lat], [long,lat],... ]
route2      [[long,lat], [long,lat],... ]
route3      [[long,lat], [long,lat],... ]
route19     [[long,lat], [long,lat],... ]

In route_id column there is possibility that route1, route2 and route3 are same but route1 is little bit longer than route2 and route3. So I want to return route1. Similarly I want to return all unique and longest route from the table.

I tried:

https://gis.stackexchange.com/questions/182246/sql-server-select-all-polygons-nested-within-a-larger-polygon

https://gis.stackexchange.com/questions/96037/sql-geometry-query-similar-to-select-by-centroid

Can anyone help me with SQL query?

Answer 1

You can use a recursive CTE in MySQL:

with recursive cte(r, d, c, gpstrace) as (
    select t.route_id, 6371*acos(cos(radians(json_extract(t.gpstrace, '$[0][0]')))
     * cos(radians(json_extract(t.gpstrace, '$[1][0]')))
     * cos(radians(json_extract(t.gpstrace, '$[0][1]') - json_extract(t.gpstrace, '$[1][1]')))
     + sin(radians(json_extract(t.gpstrace, '$[0][0]')))
     * sin(radians(json_extract(t.gpstrace, '$[1][0]')))), 1, t.gpstrace from testtable t
    union all
    select c.r, c.d + 6371*acos(cos(radians(json_extract(c.gpstrace, concat('$[', c.c, '][0]'))))
      * cos(radians(json_extract(c.gpstrace, concat('$[', c.c+1, '][0]'))))
      * cos(radians(json_extract(c.gpstrace, concat('$[', c.c, '][1]')) - json_extract(c.gpstrace, concat('$[', c.c+1, '][1]'))))
      + sin(radians(json_extract(c.gpstrace, concat('$[', c.c, '][0]'))))
      * sin(radians(json_extract(c.gpstrace, concat('$[', c.c+1, '][0]'))))), c.c + 1, c.gpstrace
    from cte c
    where c.c + 1 < json_length(c.gpstrace) 
),
results(r, d) as (select r, d from cte where c + 1 >= json_length(gpstrace))
select * from results r where r.d = (select max(r1.d) from results r1);

See demo.