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
row_number() OVER(PARTITION by a.id
ORDER BY ST_LinelocatePoint(a.geom,
ST_PointN(ST_Intersection(a.geom, b.geom), 1)) ASC)
lines a, polygons b
WHERE ST_Crosses(a.geom, b.geom)
SELECT line_id, poly_id, rank
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
lines a , polygons b
WHERE ST_Crosses (a.geom, b.geom)
ORDER BY a.id,
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.