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

Question

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.

Answer 1

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)