How to find records using a distance filter, in a table of Points?

Question

My table fields are: POSIC (id, geom, dt_inclusion). DB is PostGIS. Data comes from a GPS Source, then i can create a route ordening the points by Date_Inclusion.

So, lets say i have registries A,B,C and D, with a start point A and end point D. With distance from each other: [A->B 250meters] , [B->C 250meters] and [C->D 600meters].

I need a query that with a 1 KM filter, would return points A, B and C, the last 1KM covered from point A.

Im trying to do this using recursive a query. Execute a st_distance from A->B and save the result, check if its greater than 1KM, if not, execute another st_distance, from B->C + (last_result) and check it again. But maybe there is a better aproach. Or any help on this recursive query would be great too.

Answer 1

I can't help you with a recursive query. Instead you can use two window functions.

• In the inner query dist the distance between two neighbour points are calculated.
• In the inner query sum the total distance from point A to a the actual point is calculated.
• The last step is to filter out points with a distance over 1km.

The query:

select *  
from   (  
         select *,
                sum(distance) over (partition by id order by dt_inclusion) as sum
         from   (
                  select *,
                         ST_Distance(geom, LAG(geom) over (partition by id order by dt_inclusion)) as distance
                  from   posic
                ) dist 
       ) sum
where coalesce(sum, 0) <= 1000
order by dt_inclusion;