5

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...

3
  • 1
    My suggestion is to subselect from FourPoints first, ordered by sequence, and then makeline(geometry) from that using the WITH clause.
    – Taras
    Aug 24, 2023 at 14:14
  • Yes but subselects are a bit beyond my sql skills at the moment. I'll see what I can do...
    – Spacedman
    Aug 24, 2023 at 14:20
  • 1
    slightly off topic, but with PostGIS we could do select st_makeline(geom ORDER BY id) from src;
    – JGH
    Aug 24, 2023 at 20:00

2 Answers 2

5

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)
1
  • Nice, and nice testing framework via command line - this SQL does still work in QGIS virtual layers in case anyone wasn't sure.
    – Spacedman
    Aug 24, 2023 at 14:54
4

This SQL subselect seems to work:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.