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))
with this result:
You actually seems to try to do something called map-matching. I don't know at wich scale do you want to use your algorithm, but if you just group GPS track by cluster or even snap it on the closest road you will face a lot of problems with real life data. GPS data can easily be noisy, especially in urban environnement, where there is a lot of chances to ...
A solution using PostGIS alone.
The Steps are as follows:
Generate clusters from the points
Get the centre of each point cluster
Generate a line that connects each cluster centre point (will also approximate the centreline of the route)
Create sampling points at 20m intervals along the line (allowing for 10m radius buffer from each sampling point)
Get an ...
The solution presented here consists basicaly of the following steps:
Project the points to the line layer
Create a new point layer with points at a regular interval along the line
For each point of step 2, find the closest point from step 1: this one is the "cluster center": the point that remains and that gets the mean value of the other points ...
I have tried with your dataset as per https://lists.osgeo.org/pipermail/grass-user/2021-February/082215.html, it does work. The step which you are missing is to first set the computational region to the dataset (https://grasswiki.osgeo.org/wiki/Computational_region).
Since I agree that the current error message is unhelpful, I have proposed a modification (...