Creating IDs for middle point of line

Question

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

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

Answer 1

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: