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?

  • 1
    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 
       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 
  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 
    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.

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.