Get position in meters of POI on a polyline

Question

I have a table of POIs which are simple Points I would like to know each time a POI is close to the polyline. I would like to get the number of meters from the start of the polyline each time a POI is close to it.

For example, I expect a result like this :

poi_id	meters_from_start
1	123
1	1481
8	372

As you can see above the same POI can appear multiple times as the polyline can go multiple times near the POI, and in this case I would like to know at which meters from the start.

I tried this but I only get POIs close to the line but without the meters from start.

SELECT * FROM poi 
WHERE ST_Covers(ST_Buffer(ST_LineFromEncodedPolyline('the_polyline'), 0.0002), poi.location)

Here you can see an example polyline that get close to same POI 2 times :

Answer 1

That's a non-trivial thing to do without a broader set of trajectory analytics functionality at hand or in your own development.

However, a natural concept for this and similar, more specific linear reference tasks in SQL is to simulate trajectory traversal through segmentation and windowing:

SELECT
  id,
  distance_to_poi,
  -- this is where we sum up prior length and current partial lenght
  _cumulative_len + _partial_len AS distance_from_start,
  geom
FROM
  (
    SELECT
      -- pick whatever you need from the input tables
      p.id AS poi_id
      t.id AS trajectory_id,
      -- let's get the distance of this approach to the POI, too
      distance_to_poi,
      -- this is the fractional part of the current segment at which we are closest to the POI
      ST_Length(dmp.geom::GEOGRAPHY) * ST_LineLocatePoint(dmp.geom, p.geom) AS _partial_len,
      -- we will also need the cumulative length of all prior segments
      COALESCE(SUM(ST_Length(dmp.geom::GEOGRAPHY)) OVER(ORDER BY dmp.path ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0.0) AS _cumulative_len,
      -- this is where we find out if the current segment is a closest approach
      (
        distance_to_poi <= ST_Distance(LAG(dmp.geom) OVER(ORDER BY dmp.path)::GEOGRAPHY, p.geom::GEOGRAPHY)
        AND
        distance_to_poi <= ST_Distance(LEAD(dmp.geom) OVER(ORDER BY dmp.path)::GEOGRAPHY, p.geom::GEOGRAPHY)
      ) AS _is_closest_approach,
      -- let's also get the actual location of this closest approach
      ST_LineInterpolatePoint(dmp.geom, location_frac) AS geom
    FROM
      <trajectory> AS t
      JOIN
      <poi> AS p ON
        <condition>
      CROSS JOIN LATERAL ST_DumpSegments(t.geom) AS dmp
      CROSS JOIN LATERAL ST_LineLocatePoint(dmp.geom, p.geom) AS location_frac
      CROSS JOIN LATERAL ST_Distance(dmp.geom::GEOGRAPHY, p.geom::GEOGRAPHY) AS distance_to_poi
  ) AS q
WHERE
  _is_closest_approach
  -- it may be a good idea to define a threshold distance until which you
  -- want to consider an approach to be close enough to count in your study
  AND
  distance_to_poi < <threshold>
;