Cleaning GPS tracks from large jump / outliers

Question

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?

Answer 1

while inserting data in table you can have two check 1)You can create a buffer (st_buffer) around your road network and check if point intersects (st_intersects) your buffered area. 2) The distance between last recorded gps coordinates of that point and new gps coordinates is not more than 100m (ST_DISTANCE). Only then you insert that record in you DB. This can be done by having a before insert trigger in postgres.