3

I have a point layer with features that are along a separate line layer, and have calculated the points' distance along the line using line_locate_point(aggregate('Line','collect',$geometry),$geometry). The values are stored in a virtual field named loc in the point layer.

Now I want to be able to sequentially renumber the points along the line based on loc. How can I do this purely in QGIS, without creating a new layer (i.e. so not using something like Sort and Number)?

I tried using scale_linear() but this is not the appropriate tool (see results below - I would like testno to go 1,2,3,4...)

enter image description here

I'm aware there is an easy solution in PostGIS using rank() OVER PARTITION BY but I'm using spatialite and I don't think rank() exists. I can't get it to work.

The quick and dirty alternative is to save loc as a proper field, save as shapefile and renumber appropriately by opening the .dbf in Excel, but I'd prefer to find a solution that works entirely in QGIS without extra plugins if possible.

she_weeds
  • 12,488
  • 1
  • 28
  • 58
  • 1
    Are you in QGIS3 lately? Have you tried Add autoincremental field tool already? – Kazuhito Dec 11 '18 at 15:44
  • Hi Kazuhito, I've been on the Q3 train for a while but did not know of the tool you mentioned. That does the best job for what I'm looking for right now, with the added bonus of grouping by other fields, thank you! (still don't like having to create a new layer, but the neatest solution would probably involve Python) Could you please post your comment as an answer so I can accept it? – she_weeds Dec 11 '18 at 22:29
  • Hi @she_weeds, thanks, will do. By coincidence I was trying to answer another numbering related question Number each value in each section due to other value and struggling with SQL... that's why. – Kazuhito Dec 12 '18 at 10:19

2 Answers2

4

If you are in QGIS 3 (3.0+), there is a new tool Add autoincremental field which will do the job.

Sometimes the output records are ordered in a weird way, so it is better to set Advanced parameters | Sort expression option.

If you are in QGIS 2.18 or earlier versions, common approach would involve SQL such as PostGIS or SpatiaLite. But unfortunately Virtual Layer will not work for your case as the data is stored in a virtual field.

Kazuhito
  • 30,746
  • 5
  • 69
  • 149
  • 1
    Thanks for the answer. I think using the Sort expression option is essential here to allow for sequential numbering by distance. With the SQL answer linked in my original question it is possible to bypass the need for the virtual field, as long as your QGIS version comes with SQLite 3.25, which then makes Virtual Layer is an option. However I've accepted your answer as I was also really looking for a non-SQL approach for my colleagues to use. – she_weeds Dec 12 '18 at 21:44
1

Actually, starting from 3.25 release, SQLite (and thus Spatialite), does have window functions.

As they say: "Support for window functions was added, using the PostgreSQL documentation as the baseline."

The PostGIS solution so may (I did't try it) work with Spatialite too, provided your version of Spatialite was compiled with the 3.25 (or bigger) version of SQLite.

Ciao.

Giovanni Zezza
  • 21
  • 3
  • Well that would explain it, spatialite 5.0.0-beta0 is based off SQLite 3.24. I did notice rank() in the SQLite documentation here https://www.sqlite.org/windowfunctions.html which is why I was confused but it appears this won't be an option for now. Thanks anyway – she_weeds Dec 11 '18 at 21:41
  • 1
    As I understand, Spatialite is rather agnostic about the version of SQLite is linked to, as long it's bigger than 3.7; though maybe the one it was tested against is in fact the 3.24. In any case, QGis 3.4, Windows distribution, is definitely shipped with SQLite 3.25.2. In Linux, it depends on the libsqlite3 system library version you have, I think; it's not difficult to get the one you need. – Giovanni Zezza Dec 12 '18 at 09:42
  • Thanks for that info, I've just updated my QGIS from 3.4.1 to 3.4.2 (and 2.18.25 to 2.18.26) and now I have no problem using rank() in the DB Manager. This certainly makes things a lot easier! I am accepting Kazuhito's answer as the SQL approach is covered by the question I linked to, and it helps to have a non-SQL method since my colleagues don't use it. But thank you for the pointer to upgrade! – she_weeds Dec 12 '18 at 21:33