2

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

SELECT 
  SUM(
    ST_LENGTH(
      ST_INTERSECTION(
        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 Apr 18 '18 at 19:32
  • 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 Apr 18 '18 at 19:43
4

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 (
    select 
        poly.osm_id, -- your polygon ID
        st_pointn(
            st_geometryn(
                intersection(poly.geometry,foo.geometry),1
            ), -- first geometry
            1 -- first point
        )
        as geom 
    from 
        poly, 
        foo -- line layer 
    where 
        intersects(poly.geometry, foo.geometry) 
)
select 
    a.osm_id, -- polygon id
    a.geom     -- intersection point
from
    a, foo
order 
    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 Apr 19 '18 at 13:17

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.