Given a query that calculates distances of ST_INTERSECTION of a linestring with many polygons (stored in the polygon_column in the PolygonTable):

        po.polygon_column,ST_GeomFromText('LINESTRING(-122 48,-122 38,-122 37,...)', 4326))::geography)) 
  AS route_length, po.polygon_name 
FROM PolygonTable po 
GROUP by po.polygon_name;

The results are returned ordered by the PolygonTable polygon_name. How can I order the results in the order the line intersects them?

That is, if I have polygons A,B,C,D, and the line string goes through them in the order of B,A,C,D, I'd like my results to be

  route_length     | polygon_name 
 123.391961613962 |        B
 321.511826869426 |        A
  123.69306414636 |        C
  321.69306414636 |        D
  • ORDER BY route_length DESC
    – WKT
    Commented Apr 18, 2018 at 19:32
  • 1
    Hi. That's not it. Sorry if the explanation is not clear: I need it ordered by the order the line goes through polygons, not by the route length
    – ddgd
    Commented Apr 18, 2018 at 19:43

1 Answer 1


Here's a possible solution. It's basic and won't catch all the possible edge-cases, but it's a start :-)

I've only tested it on a simple use case (one line passing through multiple buildings) using QGIS Db Manager, a database layer (from postgres) and a scratch line layer.

QGIS uses spatialite to do these because I'm mixing postgres and non-postgres sources. The postgis-only version should be very similar (the functions in spatialite and postgis are very similar, just change the names in most cases)

The approach is :-

  • find a point of intersection between your line and each polygon
  • order them in ascending order of distance from the start of your line.


with a as (
        poly.osm_id, -- your polygon ID
            ), -- first geometry
            1 -- first point
        as geom 
        foo -- line layer 
        intersects(poly.geometry, foo.geometry) 
    a.osm_id, -- polygon id
    a.geom     -- intersection point
    a, foo
    by length(make_line(st_pointn(foo.geometry,1), a.geom)) asc;

Here's the line (layer 'foo') , red stars show where it intersects. Labels are the polygon IDs (I'm using data from OSM here).

enter image description here

here's the results of the query in QGIS, in order of intersection.

enter image description here

There appears to be a different number of stars (5) to the number of results (7); that's because the start point is actually in 3 different overlapping polygons, so the first point appears 3 times in the same place).

This is just a starter; it won't catch a lot of edge cases (I haven't tried it with more complex geometries, holes, overlapping and invalid geometries etc.).

  • That's an interesting approach, thanks! I haven't managed to make it work yet, but at least I have a direction to move towards
    – ddgd
    Commented Apr 19, 2018 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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