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,
   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 
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:

enter image description here

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?

  • Some more information about your point data would be helpful. How far apart are the points recorded (spatially and timewise)? Are they ordered? Does each trip have its own ID?
    – Erik
    Commented Sep 20, 2019 at 7:31
  • @Erik They are recorded every 5 seconds and are ordered just by the time that they were recorded. Each trip does have its own ID. Commented Sep 20, 2019 at 7:33
  • If there is a new point every 5 seconds you could set a maximum distance depending on how far it is possible to get in 5 seconds (100 meters?)
    – Dirk
    Commented Sep 20, 2019 at 8:03
  • @DirkB. Thank you, this is what I was just testing out but I don't know the code in PostGIS. Starting by putting it in the 'where' clause. Commented Sep 20, 2019 at 8:04
  • 1
    I believe that your question fits better for the signal processing forum dsp.stackexchange.com. Some examples of the methods can be seen in ocefpaf.github.io/python4oceanographers/blog/2015/03/16/…. PostGIS and QGIS are probably not optimal tools for signal processing but perhaps they can be made to do for example rolling median filtering. Visvalingam-Whyatt simplification is certainly worth a try bost.ocks.org/mike/simplify and postgis.net/docs/ST_SimplifyVW.html.
    – user30184
    Commented Sep 20, 2019 at 12:28

1 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.

  • Thanks for the asnwer. I had the same thought, but didn't like the buffering method because the points could still be within the buffer of a road even if it's not the right road. The road network is very big and the area is quite dense with them. I will try using the before insert trigger. Commented Sep 20, 2019 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.