# Filter for first geometry that is crossed by a line

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?

• Do you need to check which is first geometry that is crossed, based on the starting and end point of your linestring? That is, "if I was travelling along this line, when/which is the first polygon that I will cross?". If so, you can adapt the solution below and interpolate the line, checking for crosses. – George Silva Dec 13 '16 at 12:37

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.

• ASC is the default sorting order, not DESC. – Michal Zimmermann Dec 13 '16 at 11:52
• @MichalZimmermann. Thanks. I'm half asleep. Correcting. ASC is also what you need to get the closest one, so good catch. – John Powell Dec 13 '16 at 11:56
• Isn't the result of `ST_Distance` always zero? I think you need to compute the intersection point and use `ST_LineLocatePoint`. – dbaston Dec 13 '16 at 13:17
• @dbaston. You are so correct. I had just come to the same conclusion myself. – John Powell Dec 13 '16 at 13:39
• Sorry, I left the rank = 1 off the final edited query, though this was in the explanation. – John Powell Dec 13 '16 at 14:11