3

I have a polygon table that sometimes overlaps itself and has an integer column called rank

Then I have a line table

Basically I want to know what's the length of lines that are contained within each polygon. Problem is that because of the overlap, segments are being counted double for multiple polygons, making the lines look a lot lengthier than they actually are

Query used was

update polygontable a set length_of_lines = 
(select sum(st_length(st_intersection(a.geom,b.geom))) 
from linetable b where   st_intersects(b.geom, a.geom))

To avoid having lines look a lot lengthier, how to attribute the length of the lines according to the rank of the polygons?

Example: if a portion of the line is being accounted for polygon 1, it shouldn't be counted for polygon 2. In other words, if a line has 9 meters, 3 meters purely inside polygon 1, 3 meters in the area overlapping polygon 1 and 2, and 3 meters purely inside polygon 2 - How to update the table so that it attributes 6 meters to polygon1 and only 3 meters to polygon 2 (because the overlapping bit should be only accounted for polygon 1)

  • does it happen to be the case that there are only ever no more than two polygons overlapping? it's 'fairly' simple for that case, but the effort grows kinda exponentially with each additional overlap... – ThingumaBob Aug 30 at 20:26
  • @ThingumaBob nope, sometimes 3 polygons overlap, even if just a little bit – Luffydude Sep 2 at 12:59
2

Try this:

UPDATE  polygontable AS p
   SET  length_of_lines = ST_Length(
                            ST_Intersection(
                              (SELECT ST_Union(geom) FROM linetable WHERE ST_Intersects(p.geom, geom)),
                                COALESCE(
                                  ST_Difference(
                                    p.geom,
                                    (SELECT ST_Union(geom) AS geom FROM polygontable WHERE rank < p.rank)
                                  ),
                                  p.geom
                                )
                              )
                            )
;

The idea is to produce either the ST_Difference from each p.geom and the ST_Union of all polygons that have a higher rank, or use p.geom itself if highest rank already, and calculate the ST_Length of the intersecting lines (collected as ST_Union to get an aggregate length of all lines that intersect each polygon).

Needless to say, the two ST_Union may take a heavy toll on execution time for very large tables...


Validate with:

SELECT  p.id,
        (ST_Dump(
          ST_Intersection(
            (SELECT ST_Union(geom) FROM linetable WHERE ST_Intersects(p.geom, geom)),
            COALESCE(
              ST_Difference(
                p.geom,
                (SELECT ST_Union(geom) AS geom FROM polygontable WHERE rank < p.rank)
              ),
              p.geom
            )
          ) 
        )).geom
FROM    polygontable AS p
;
  • I tested this on a few cases of three and four sequentially overlapping polygons. Can't say if it covers all edge cases though; curious to hear your results. – ThingumaBob Sep 3 at 10:27
  • SQL Error [22023]: ERROR: Only lon/lat coordinate systems are supported in geography. Both tables have less than 500 rows. It actually worked fine for 2 other line tables with a few rows but been going over half an hour with a 10m row dataset. Thanks dude! – Luffydude Sep 3 at 14:35
  • 1
    @Luffydude ah, okay, just delete ::GEOGRAPHY; I used that to get meter at best precision, it's a habit. I expect it to take a while for that large a table. With this query there is not much to tune except the mandatory index. – ThingumaBob Sep 3 at 14:49
2

This became convoluted but it seems to work. I use dumprings to create lines of the polygons, use these line to split line input table, give id to each new line, join these to polygons by st_within (had to use st_line_substring to shorten the lines for some reason all would not fit within when they should) sort by rank and select distinct on id of split lines to select lowest rank line and join result back.

UPDATE somepolygontable set linelength = sub2.sum
FROM (
SELECT id, sum(linelength)
FROM(
SELECT DISTINCT ON (lineid)
       id, st_length(geom) linelength
FROM   (

SELECT poly.id, poly.rank, line2.lineid, line2.geom
FROM somepolygontable poly
LEFT join (

SELECT row_number() over() lineid, st_line_substring(geom,0.0001, 0.9999) geom 
FROM(
(SELECT (st_dump(st_split(line.geom, splitline.geom))).geom geom
FROM somelinetable line, 
(SELECT st_multi(st_union(borders.geom)) geom
FROM
(SELECT st_exteriorring(sp.geom) geom
FROM(
    SELECT (st_dumprings(singleparts.geom)).* 
    FROM (SELECT (st_dump(poly.geom)).geom geom
    FROM somepolygontable poly) singleparts
    ) as sp) borders) splitline)) line) line2

ON st_within(line2.geom,poly.geom)) line3
ORDER  BY lineid, id, rank) sub
GROUP BY id) sub2
where somepolygtable.id = sub2.id

enter image description here

  • Ermmmmm looks like it's so convoluted that I'm getting the following error SQL Error [08006]: An I/O error occurred while sending to the backend. An I/O error occurred while sending to the backend. An I/O error occurred while sending to the backend. – Luffydude Sep 2 at 14:01

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.