17

Field calculator has a great function under "Records" called "$rownum" which returns (assigns) the number of the current row.

Is there a way to make it work together with sorting? Lets say I want to sort table by column "length" and get the order (sequence) of lines by length from shortest to longest.

I know I can use some office spreadsheet software easily for this to sort by column and populate new column with order (sequence). But I would prefer to do the whole job in QGIS.

Update: As workaround there is also How to change the order of features in a shapefile? - using mmqgis plugin and function modify/sort and after that use "$rownum" on the new saved file.

25

So, I've coded a plugin (Sort and Number) to solve your issue. It allows you to select up to 3 fields and order your attribute table according to these fields. Then, it numbers the attribute table in a new field (named "order" by default), starting from 1.

enter image description here

| improve this answer | |
  • It looks awesome. I am going to test this plugin now, thank you. – Miro May 3 '16 at 23:25
  • When installed for the first time in 2.8 it came up with some error but after test in 2.14 and back in 2.8 it works excellent. Thank you. – Miro May 3 '16 at 23:41
  • @Miro: you're welcome. I hope it will be useful for other people too. – ArMoraer May 4 '16 at 6:04
  • @ArMoraer - Great plugin! – Joseph May 4 '16 at 9:29
  • 1
    Can you recode this for Bonn 3 -> i loved this tool! – lowsparked Sep 12 '18 at 22:18
9

I'm not sure you can with field calculator, but you can use a virtual layer with a query like :

SELECT 
    * ,
    (SELECT count(*) FROM your_layer AS b WHERE a.length >= b.length) AS length_position
FROM your_layer AS a 
ORDER BY a.length
| improve this answer | |
  • Thank you, I still work with long term release 2.8 so it was my first time I discovered virtual layer in 2.14. Apart it was very very very slow it works. Generally this is acceptable though I am going to wait a bit more for some other answers if any. – Miro May 3 '16 at 7:24
1

For a simple sort (by one column) you can use QGIS expressions in the Field Calculator.

From QGIS 3.8 the aggregate() function has allowed for the use of an order field (which can be different from the aggregated field).

This means you can use something like the following expression to:

  1. concatenate all the $id values into a comma-separated list, ordered by ORDER_FIELD
  2. Convert the ordered list into an array
  3. Look up the position, in the array, of the current feature's $id value (converted to string) (starts from 0 - add a +1 at the end so it starts from 1)

Expression:

array_find(
    string_to_array(
        concatenate(expression:=to_string($id),
                    concatenator:=',',
                    order_by:="ORDER_FIELD"),
        ','),
    to_string($id))
+1

Example: enter image description here


Other notes:

  • $id is always unique and sequential and recalculated even after features are added/removed so you don't need to rely on a separate unique ID column.

  • If ORDER_FIELD has numbers in a text column or an alphanumeric combination (e.g. '1', '2', '3', '3A', '4') you can use on-the-fly conversion (using to_int("ORDER_FIELD")) or zero-padding (e.g. lpad("ORDER_FIELD",3,'0')) to ensure it sorts in the correct order.

  • To reverse the order use array_reverse() between array_find() and string_to_array()

  • To order by multiple fields you could concatenate them in the order_by parameter (e.g. "textfield"||"ORDER_FIELD") - just be sure to zero-pad any numeric fields so that it sorts by A01,A02....A11,B1, not A1,A11,A12...A2,B1

| improve this answer | |
  • I don't understand what is happening in your GIF. Before you apply your code, your table appears to be unsorted. After the code, the table is sorted by the field NUM. Was that your intention? If so, where in your code is the field NUM specified? When I apply your code with a shapefile, the output sorting is by FID. I don't mean to sound unappreciative, because I think you may be on to something important: what if I want to sequentially number a new field based on the current table order, even if the table is unsorted? That is, I want to "freeze" the order as currently displayed. – Stu Smith May 3 at 16:28
  • Sorry, I should have been more consistent or explicit with the example. The table was originally sorted by an id column (irrelevant, really), then I created a new column order with sequential numbers (1-13) based on the num field (=ORDER_FIELD in the code). Then I sorted the table by order to show that num has been ordered as well. Your attribute table would almost always be sorted, if not by some field or combination of fields, then the underlying sequence of data in the file (which is the "default/original sorting", if you like - I think this order is accessed using $id). – she_weeds May 3 at 21:54
  • Unlike Excel, GIS formats rely on database formats. You can't really "lock" the sequence the data except as how it was inserted. You use data attributes to order the records as needed (and the original sequence is itself stored as an auto incrementing integer, hidden or otherwise). In Excel the sequence you see is part of the data and can be changed around arbitrarily by moving/inserting rows in between existing ones. If your attribute table order in qgis cannot be explained by the data, and it isn't the original sequence in the shapefile itself, then there's no way to renumber except manually – she_weeds May 3 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.