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)