QGIS – Ordering Points When Creating Line in Virtual Layer

polyline-creationqgissqlverticesvirtual-layer

I've got a QGIS point geometry layer with four point features and a numeric field sequence. I want to create a virtual layer joining these points into a line geometry layer (which will have one feature) in order of increasing sequence.

I can make a virtual layer with a line feature from the points using:

select makeline(geometry) as geom
from FourPoints

and I get three sides of a square, joined in the order I created them (temporary scratch layer, points). But I can't see how to order it by attribute. Trying:

select makeline(geometry) as geom
from FourPoints
order by sequence

makes no difference. Do I need to do a subselect from FourPoints first, ordered by sequence, and then makeline(geometry) from that? How? My SQL-fu is weak.

Making new real layers isn't an option, since I will be editing the point layer and I'd like the line layer to update magically, also I have more than one "sequence" column and I'd like to have multiple virtual layers, for different sequence columns.

If easier, I could renumber the sequence variable to be strictly incrementing by 1 every time, so then its a case of joining the point with sequence value i to that with sequence value i+1. Not sure if that could then be done with a geometry generator within a point layer…

Best Answer

You resolved your problem already but here is a full test case with data. Save the GeoJSON as "seq.json" and run the ogrinfo commands for testing.

{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "properties": { "seq1":1, "seq2":1 }, "geometry": {"type":"Point","coordinates":[-10,8]} },
{ "type": "Feature", "properties": { "seq1":2, "seq2":2 }, "geometry": {"type":"Point","coordinates":[-7,0.0]} },
{ "type": "Feature", "properties": { "seq1":3, "seq2":4 }, "geometry": {"type":"Point","coordinates":[0.0,0.0]} },
{ "type": "Feature", "properties": { "seq1":4, "seq2":3 }, "geometry": {"type":"Point","coordinates":[3,8]} }
]
}

ogrinfo -dialect sqlite -sql "select makeline(geometry) from (select geometry from seq order by seq1)" seq.json
 LINESTRING (-10 8,-7 0,0 0,3 8)

ogrinfo -dialect sqlite -sql "select makeline(geometry) from (select geometry from seq order by seq2)" seq.json
 LINESTRING (-10 8,-7 0,3 8,0 0)
Related Question