2

After using sum line lengths, is there an automated way to update the resulting polygon layer when more lines are added without needing to delete the layer and re-run the sum line lengths algorithm?

1

You should try a "dynamic query". Depending on how your data are stored u can try either a virtual layer (based on this dynamic query) or a postgis "view" (which is something "dynamic" and re-run each time a change occurs in the underlying tables ...)

Make a quick search about it in here ...

3
  • I'm not sure that I understand what I'm finding under the dynamic query search. I currently have a layer of lines, and a layer of polygons, When I run sum line lengths I get the results I desire, showing lengths and counts of lines per each polygon. I cant however figure out how to get that data without running that algorithm though. Jun 6 '18 at 21:09
  • The sum line lengths tool creates a new layer every time you run it. I don't think you can incorporate it into a dynamic query. However, it should be possible to add virtual fields to the polygon layer that display length and count of lines that intersect each polygon.
    – csk
    Jun 7 '18 at 18:57
  • Hmm, this is more challenging than I first thought. I added a length field to the line layer, and used intersecting_geom_sum() to calculate the total length of all lines that intersect the polygon, but it seems the Sum Line Lengths tool calculates the total length of lines cropped to fit within the polygon.
    – csk
    Jun 7 '18 at 19:28
0

Following snaileater's answer, this is a Virtual Layer syntax to mimic Sum line lengths assuming:

  • the polygon layer name is Polygon and it has unique id field
  • the line layer name is Line

please modify Polygon and Line to your actual layer names.

SELECT 
       Polygon.id AS id, 
       Round(Sum(st_length(st_intersection(Line.geometry, Polygon.geometry))), 5) AS LENGTH, 
       Count(st_intersection(Line.geometry, Polygon.geometry)) AS COUNT,
       Polygon.geometry AS geometry
FROM Line INNER JOIN polygon
ON st_intersects(Line.geometry, Polygon.geometry) 
GROUP BY Polygon.id
ORDER BY Polygon.id

However, I would recommend to stick to Sum line lengths tool, if you have already started building your database with this tool. You will never be satisfied with their consistency (including accuracy and repeatability), if you mix tools for length/area measurements.


EDIT

This is how it looks when your layer name / field name fit into. Also please avoid name starting with numbers (e.g, 161kv).

SELECT 
       County.Cnty_NM AS id, 
       Round(Sum(st_length(st_intersection(161kv.geometry, County.geometry))), 5) AS LENGTH, 
       Count(st_intersection(161kv.geometry, County.geometry)) AS COUNT,
       County.geometry AS geometry
FROM 161kv INNER JOIN County
ON st_intersects(161kv.geometry, County.geometry) 
GROUP BY County.Cnty_NM
ORDER BY County.Cnty_NM
8
  • I'll try the method pointed out here, I do like the sum line lengths for its simplicity but when my line layers change it appears the only way to update that results are deleting and re-running that tool which could be cumbersome down the road. Jun 11 '18 at 15:13
  • I apparently cant seem to get the above syntax to work. My polygon layer is named "County" unique ID field is "Cnty_NM" my line layer is "161kv". I replaced the instances of polygon, ID and Line with my information but it does not seem to yield any results like sum line lengths does. Jun 11 '18 at 21:11
  • @JoshuaLind See an example in the edit.
    – Kazuhito
    Jun 11 '18 at 22:34
  • Kazuhito, Thank you for the edited example. I've updated my layers to not start with numbers per recommendations but my virtual layer returns 0 features. Does that information in your example not go into the query field when trying to add a virtual layer? Jun 26 '18 at 20:39
  • @JoshuaLind Have you imported your layers to start creating virtual layer?
    – Kazuhito
    Jun 26 '18 at 22:26

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.