5

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))
FROM "TR_ROAD copy"

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

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

1 Answer 1

7

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

SELECT
    ROW_NUMBER() OVER(ORDER BY name) rownum,
    name,
    st_startpoint(st_line_substring(geometry, 0.5, 1))
FROM
    "test"

with this result:

enter image description here

3
  • Thank you. What if I want to start the row number with 5000 not 1? Should I say RowNum+5000 ?\
    – Case Msee
    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
    Feb 23, 2021 at 5:33
  • 3
    select 'node'||(rowid+4999) as id from ... It is just SQL.
    – user30184
    Feb 23, 2021 at 6:22

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.