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
foo -- line layer
a.osm_id, -- polygon id
a.geom -- intersection point
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).
here's the results of the query in QGIS, in order of intersection.
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.).