4

I have line layer as shown in image 1. 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).

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

enter image description here

enter image description here

enter image description here

6

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 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 at 5:33
  • 2
    select 'node'||(rowid+4999) as id from ... It is just SQL. – user30184 Feb 23 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.