I have line layer as shown here:
enter image description here

I created middle point of line by using the below query in virtual layer.

SELECT ST_StartPoint(ST_Line_Substring(geometry, 0.5, 1))

The problem is it only creates the middle point without creating data (IDs) for middlepoints(nodes).

enter image description here

Is there are way to create unique ids for each middle point as sample is shown in last image

enter image description here

  • Why not to use the id from the line layer? or you particularly need a new one?
    – Taras
    Commented Oct 6, 2021 at 5:40

1 Answer 1


You can use the SQLite function row_number() for that, see this example:

    ROW_NUMBER() OVER(ORDER BY name) rownum,
    st_startpoint(st_line_substring(geometry, 0.5, 1))

with this result:

enter image description here

  • Thank you. What if I want to start the row number with 5000 not 1? Should I say RowNum+5000 ?\
    – Case Msee
    Commented Feb 23, 2021 at 5:32
  • Secondly, the rownum only add integers. what if i want to use ids with integers and strings as shown in image in question?
    – Case Msee
    Commented Feb 23, 2021 at 5:33
  • 3
    select 'node'||(rowid+4999) as id from ... It is just SQL.
    – user30184
    Commented Feb 23, 2021 at 6:22

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.