5

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.

Dugz
  • 61
  • 2
  • 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. – J. Monticolo Nov 19 '19 at 12:44

1 Answers1

6

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)

enter image description here

JGH
  • 41,794
  • 3
  • 43
  • 89
  • copy/pasted your example with only changing the point layers name. Throwing error and is impossible to debug. Any hints? – Bernd V. Dec 04 '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 04 '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 – Bernd V. Dec 04 '19 at 14:23
  • I'm on QGIS-master – Bernd V. Dec 04 '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 04 '19 at 14:34
  • Query execution error on CREATE TEMP VIEW _tview AS select ROW_NUMBER() OVER() as newID from Centroids: 1 - near "(": syntax error – Bernd V. Dec 04 '19 at 14:44
  • Query preparation error on PRAGMA table_info(_tview): no such function: ROW_NUMBER – Bernd V. Dec 04 '19 at 14:45
  • Have to go with the dog. Will try later on another machine. Thanx so far. – Bernd V. Dec 04 '19 at 14:46
  • @BerndV. so the Spatialite driver is too old (help / about. Mine is 4.3) – JGH Dec 04 '19 at 14:47
  • Let us continue this discussion in chat. – Bernd V. Dec 04 '19 at 16:40
  • @BerndV. You can open a new question if you want, the issue not being related with the current topic – JGH Dec 04 '19 at 18:02