Order matched geometries on line by distance in PostGIS

Question

I want to be able to find Points near (with a given threshold) a given Line-Geometry and Polygons that it crosses. I already have that part of the problem solved, but now I'd like to order the results. Fist match first, last match last.

Here's a sample:

My result should be something like:

--+-------------------------
1 | point A
2 | polygon A
3 | point B
4 | point A

I've tried ST_Line_Locate_Point which seems to do basically that, but this will only give me the closest match, right?

So my problem would be the following. My input line crosses a certain point twice. One time a bit closer on the way back e.g. a bit further away. Is there anything build into PostGIS to help me with that? Or will I have to do the analysis of something like that on my own?

How would you approach this problem?

Answer 1

The repeated passing of the same point is tricky, and you won't get any direct help from PostGIS.

However, you can

• create a sequenced set of minimal components (two-vertice segments) from your LineStrings

  SELECT id,
         seq,
         geom
  FROM   (
      SELECT ln.id,
             dmp.path[1] AS seq,
             ST_MakeLine(
               dmp.geom,
               LEAD(dmp.geom) OVER(PARTITION BY ln.id ORDER BY dmp.path)
             ) AS geom
      FROM   <line_table> AS ln,
             LATERAL ST_DumpPoints(ln.geom) AS dmp
  ) q
  WHERE  geom IS NOT NULL
  ;
  

• find all geometries in range and ORDER BY the sequenced segments and the fraction of line length at which those geometries (for Points) or derived Point geometries (for Polygons) project onto the line segment:

  WITH
    segs AS [MATERIALIZED] (
      <above_query>
    )
  SELECT other.id
  FROM   segs
  JOIN   <other_geometries_table> AS other
    ON   ST_DWithin(segs.geom, other.geom, <threshold>)
  ORDER BY
         segs.id, segs.seq, ST_LineLocatePoint(segs.geom, other.geom)
  ;
  

---

The above is working on Points only. You will have to derive a Point geometry from any crossed Polygon somehow; some options are to use

• the ST_Centroid of the Polygon, after determining if it is crossed
• the ST_Centroid of the ST_Intersection between Polygon and LineString

Using ST_Centroid has the advantage that you can pass in Points, which may make streamlining the query on mixed geometry types easier, if you are indeed using a mixed geometry types table as other.

If not, you can UNION ALL queries on both tables; select all fields (including the ST_LineLocatePoint fraction), and ORDER BY in an outer query:

WITH
  segs AS [MATERIALIZED] (
    <above_query>
  )
SELECT ROW_NUMBER() OVER() AS seq,
       q.id
FROM   (
  SELECT point.id,
         segs.id AS _id,
         segs.seq AS _seq,
         ST_LineLocatePoint(segs.geom, point.geom) AS _frac
  FROM   segs
  JOIN   <point_table> AS point
    ON   ST_DWithin(segs.geom, point.geom, <threshold>)
  UNION ALL
  SELECT polygon.id,
         segs.id AS _id,
         segs.seq AS _seq,
         ST_LineLocatePoint(segs.geom, ST_Centroid(ST_Intersection(segs.geom, polygon.geom))) AS _frac
  FROM   segs
  JOIN   <polygon_table> AS polygon
    ON   ST_Crosses(segs.geom, polygon.geom)
) q
ORDER BY
       _id, _seq, _frac
;

---

Note:

This will double-count point B in your example! And that may or may not be intended: does the line pass point B twice? This is a general question of context and algorithm theory that you will encounter with this task, no matter the methodology. E.g. to exclude point B from getting counted twice, you'd need to add a rule to remove duplicates in adjacent segments.