Filter for first geometry that is crossed by a line

Question

I have a query like this

SELECT p.id 
FROM polygons p
INNER JOIN lines l
ON ST_Crosses(p.the_geom), l.the_geom)
WHERE p.id = l.id;

lines can cross more than one polygon. However, I want the inner join only to be performed on the very first polygon that is crossed. How would I do that?

Answer 1

You can do this with what is known as a window function which allows you to partition rows according to some grouping. The idea is that you create a counter, row_number() based on partitioning on the line id, so for each polygon crosses by some line, the row_number will return 1, 2, 3, etc. You use ST_LinelocatePoint to find the distance along the line the first intersection occurs (using ST_PointN(ST_Intersection(a.geom, b.geom), 1)) ie, the first point along the line where an intersection occurs, which will get a 1 from row_number(). Then in the select, you choose WHERE rank = 1 which excludes all the other intersected polygons.

WITH crosses(line_id, poly_id, rank) AS 
   (SELECT 
       a.id, 
       b.id, 
       row_number() OVER(PARTITION by a.id 
                    ORDER BY ST_LinelocatePoint(a.geom,
                         ST_PointN(ST_Intersection(a.geom, b.geom), 1)) ASC)
     FROM 
        lines a, polygons b 
    WHERE ST_Crosses(a.geom, b.geom)
    )
SELECT line_id, poly_id, rank 
  FROM crosses 
  WHERE rank = 1;

EDIT: You can also do this using Postgres's DISTINCT operator, which will return the first distinct combination of the variables in the ON clause.

SELECT DISTINCT ON (a.id) a.id, b.id 
 FROM 
    lines a , polygons b 
 WHERE ST_Crosses (a.geom, b.geom) 
 ORDER BY a.id,  
      ST_LinelocatePoint(a.geom, 
      ST_PointN(ST_Intersection(a.geom, b.geom), 1)) ASC;

As order by ASC is default, you don't really need to put it in either, but I always think it is good to be explicit about these things. Also, note, if you do use an ORDER BY the columns included have to match those in the DISTINCT expression.

While the DISTINCT is shorter then the Window function, I am leaving in the Window function because they are useful more generally, and more flexible, for example, if you wanted the fist two polygons crossed by a line, you would merely have to change the first version to WHERE rank < 3 at the end.