More

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

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


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.


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;