# Eliminate irrelevant points based on time and distance

I'm collecting raw GPS points from android device for tracking, now when i'm creating a linestring based on the points i collected there are some points which makes no sense (due to GPS fluctuations). I have created a query to select only those coordinates which are satisfying a condition i.e (1.4 meter in 1 sec) which is average human walking distance. But still some points are zigzag. I'm not an expert in gis and also PostGIS

Here is the query i used

SELECT a.*
FROM location_history a
JOIN location_history b
ON (a.id+1 = b.id)
where a.socket_client_id=187 and DATE(a.date_time)='2019-04-17'
and ST_DistanceSphere(ST_SetSrid(ST_MakePoint(a.lng, a.lat), 4326),
ST_SetSrid(ST_MakePoint(b.lng, b.lat), 4326))
<= ((EXTRACT(EPOCH FROM (b.date_time - a.date_time)))*1.4)
order by a.date_time;


Here are some points which are zigzag

• Have you checked the resulting speeds of those instances of zigzag? – underdark Apr 18 at 18:09
• no but according to the theory those points satisfying the fact of 1.4 meter in 1 second. – Arpan Apr 18 at 18:51
• Can you share some sample data? – underdark Apr 18 at 20:51
• What does the output of the query look like? Your approach seems reasonable, but you dont say how it fails. – John Powell Apr 20 at 10:48
• @JohnPowell my objective is to eliminate the point long in distance (due to gps fluctuations) i manage to eliminate some but the query eliminates a pair of coordinates which is also an issue. my query failed as it detects points which can be reached at 1.4m/s but some points pass the condition which resulting some irrelevant linestring forming. – Arpan Apr 20 at 16:01