Can someone please explain the steps to take in order to eliminate the parts of these linestrings which jump across large distances (not along with the road network)?
They were created from GPS coordinates with this PostGIS code:
create view tour_bus_position as select tour.id, st_makeline(bus_position."position" order by bus_position.record_time) as tour_bus_position_shape from tour join bus_position on tour.id = bus_position.tour_id where tour.id = bus_position.tour_id and bus_position.accuracy_meters < 100::double precision and tour.state = 6 group by tour.id;
With this result:
Update The next thing I tried is setting a maximum distance between the points ordered by record time:
with point_order as( select tour.id, bus_position."position", bus_position.record_time, row_number() over(partition by tour.id order by max(bus_position.record_time)) as point_order from tour join bus_position on tour.id = bus_position.tour_id where tour.id = bus_position.tour_id and bus_position.accuracy_meters <= 10::double precision and tour.state = 6 and tour.arrival_at_first_stop::date > '2018-04-25' group by tour.id, bus_position."position", bus_position.record_time order by bus_position.record_time asc ) select first_point.id, st_makeline(first_point."position" order by first_point.record_time) as tour_bus_position_shape from point_order as first_point inner join point_order as second_point on first_point.id = second_point.id and first_point.point_order = second_point.point_order-1 where ST_Distance(first_point."position", second_point."position") < 100 group by first_point.id;
With this improved but still not great result:
My goal is to get an accurate measurement of the total distance travelled. With the above result, it takes all of the 'noisy' points and overestimates the distances travelled.
I've tried reducing the
bus_position.accuracy_meters <= 10 which helped quite a lot, but it doesn't help me deal with the points where there are large gaps in between.
I imagine there is some kind of pre-processing I can do to the points so that the linestrings turn out more realistically, or I can clean up the linestrings once they are created.
What is the best way of using PostGIS or QGIS methods?
Maybe adding a maximum distance parameter between the points to be considered in the same linestring?