Automatically numbering points East-West & North-south on QGIS?

I have a dataset over with points every 100m over several km.

I want to number points so that they follow an 's' shaped path.

For example:

1 2 3 4 5

10 9 8 7 6

11 12 13 14 15

20 19 18 17 16

I can easily sort by X ascending using the sort & number plugin, but I haven't figured out how to alternate between ascending and descending on alternate rows.

• You can maybe make (with selection) two data sets, one for odd rows, another for even rows (something like `(\$y / 100) % 2`), and with the odd data set number with X ascending and the even data set, X descending. Nov 19 '19 at 12:44

You can achieve this using a virtual layer

The idea would be to order the row by Y, then to compute the `dense_rank` according to this 1st sort (i.e. all rows having the same value gets the same rank), then so apply a different sort direction on the X coordinate whether the dense_rank is odd or even.

At last, you would generate a new ID based on the row ID.

Let's note that it is highly probable that the X and Y coordinates should not be used directly, but rather with a small tolerance (that is, 2 points that are a micro-meter away from each other should be considered the same). Even if the points where created by a script, the storage model (floating point) could add noise to the coordinates.

In the example below, I have used a tolerance of 0.02 degrees. You will have to adjust this value for your data. I have left the dense rank (`r`) value as it is helpful to identify the suitable tolerance.

Go the the menu `layer / add layer / add-edit virtual layer` and enter the following query.

``````select *, ROW_NUMBER() OVER() as newID
from (
SELECT *,DENSE_RANK() OVER (ORDER BY round( ST_Y(geometry)/0.02) desc) as r
FROM myPointLayer
ORDER BY round(ST_Y(geometry)/0.02)  DESC,
CASE (DENSE_RANK() OVER (ORDER BY  round( ST_Y(geometry)/0.02) desc) % 2)
WHEN 1 THEN round( ST_X(geometry)/0.02) END ASC,
CASE (DENSE_RANK() OVER (ORDER BY round( ST_Y(geometry)/0.02) desc) % 2)
WHEN 0 THEN round( ST_X(geometry)/0.02) END DESC
)
``````

A few more nerdy explanations:

The ordering statements for both ascending and descending direction are always used, but we use X or Null based on the dense_rank, effectively using only one direction per row.

We compute the new_id in an outer query, so it makes uses of the inner query `order by`. Because we use the `case` in the `order by`, we can't simply create the rowid in the inner query (the entire inner query `order by` clause should be put inside the over clause of `ROW_NUMBER() OVER()` to have the same effect)

• copy/pasted your example with only changing the point layers name. Throwing error and is impossible to debug. Any hints? Dec 4 '19 at 14:13
• @BerndV. what is the error? If I remember right, the window functions were added recently to the underlying Spatialite driver, make sure you are using a recent version of QGIS (the answer works under 3.4)
– JGH
Dec 4 '19 at 14:19
• Query execution error on CREATE TEMP VIEW _tview AS select *, ROW_NUMBER() OVER() as newID from ( SELECT *,DENSE_RANK() OVER (ORDER BY round( ST_Y(geometry)/0.02) desc) as r FROM Centroids ORDER BY round(ST_Y(geometry)/0.02) DESC, CASE (DENSE_RANK() OVER (ORDER BY round( ST_Y(geometry)/0.02) desc) % 2) WHEN 1 THEN round( ST_X(geometry)/0.02) END ASC, CASE (DENSE_RANK() OVER (ORDER BY round( ST_Y(geometry)/0.02) desc) % 2) WHEN 0 THEN round( ST_X(geometry)/0.02) END DESC ): 1 - near "(": syntax error Dec 4 '19 at 14:23
• I'm on QGIS-master Dec 4 '19 at 14:24
• @BerndV. I don't know. You can try re-building the query step by step to find out where it fails, starting with `select ROW_NUMBER() OVER() as newID from Centroids`
– JGH
Dec 4 '19 at 14:34