3

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.

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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