9

For each street I have a list of house numbers in a column of a CSV file and I need to sort them numerically:

House numbers are separated by -.

name list sorted_list
street A 5/A-5-4-8-3-6-9-7-1-10-7/B-2-7/A 1,2,3,4,5,5/A,6,7,7/A,7/B,8,9,10
street B 15-1-2/AX-2-3 1,2,2/AX,3,15

How can I get the sorted_list column?

I used this expression, but I find it complicated and slow;

array_to_string(
array_foreach(
    array_sort(
        with_variable('lista',
            string_to_array("sorted_list" ,'-'),
            array_foreach(generate_series(0, array_length(@lista)-1),
            lpad(regexp_substr( (@lista[@element]),'(\\d+)'),3,'0')
            ||'|'||
            if(regexp_substr((@lista[@element]),'([a-zA-Z/]+)') !='',
               regexp_substr((@lista[@element]),'([a-zA-Z/]+)'),
               ' ')
            ||'|'||
            @lista[@element]))),
regexp_replace( @element,'^.+\\|(.+)$','\\1'))
)

Is there an easier way to use expressions?


After a few days of study, my request is to obtain the natural sorting as per Kadir's solution, but using the QGIS core expressions; but I think it is impossible and that the way through code is the only and fastest in terms of performance.

Here is a desired example:

solution value
list 1-1/A-10-1r-2/10-10/1A2-10/A-1000/C1-SNC - to order
kadir 1,1/A,1r,2/10,10,10/1A2,10/A,1000/C1,SNC
my SNC,1,1r,1/A,2/10,10,10/1A2,10/A,1000/C1
sort -V 1,1r,1/A,2/10,10,10/1A2,10/A,1000/C1,SNC
sort -n SNC,1,1/A,1r,2/10,10,10/1A2,10/A,1000/C1
natsort 1,1/A,1r,2/10,10,10/1A2,10/A,1000/C1,SNC

sort -V and sort -n are the linux system solutions
natsort is python module and cli to run natural sort processing

4 Answers 4

12

Create a new function using Function Editor.

enter image description here

Copy/paste the following script:

from qgis.core import *
from qgis.gui import *
import re

def natural_sort_key(s):
    ns = re.compile('([0-9]+)')
    return [int(t) if t.isdigit() else t.lower() for t in re.split(ns, s)]  

@qgsfunction(args='auto', group='Custom', usesgeometry=False)
def sort_my_addresses(field, feature, parent):
    _list = feature[field].split("-")
    _list.sort(key=natural_sort_key)
     
    return ','.join(_list)

Click on "Save and Load Functions" button. Use this expression:

sort_my_addresses('list')

Note: the field parameter name (list here) for sort_my_addresses should be between single quotes ('), not double quotes ("). For example, 'field_name', not "field_name".

enter image description here

Reference for natural_sort_key method: How to sort a list containing alphanumeric values?

1
  • 2
    good solution, but I am looking for a simple solution through the use of QGIS core functions, THANK YOU
    – pigreco
    Jan 16 at 8:49
6

Two approaches that resolve different parts of the problem and could maybe help finding an easier solution. Approach 3 solves the problem, based on approach 2, but is similarily complex as your initial expression.

Approach 1: array_prioritize

Integer-only elements will be sorted correctly, combined alpha-numerical strings will be put at the end:

array_to_string (
    array_prioritize(
        string_to_array("list" ,'-'), 
        generate_series(1,1000)
    )
)

enter image description here

Approach 2: using map() functions

Correct sorting of integer only and mixed alpha-numerical values, but only of the same length of the numerical part - so refining this to run separately for single-digit, two-digit and three-digit values should return the correct order:

array_to_string (
    array_prioritize(
        map_akeys(
            hstore_to_map( 
                array_to_string(
                    array_foreach (
                        string_to_array("list" ,'-'),
                        @element  ||  '=>'  ||  regexp_substr( @element, ('\\d+'))
                    )
                )
            )
        ), 
        array (1,1000)
    )
)

enter image description here

Approach 3: running approach 2 separately for single/two digit values

Adapting approach 2 from above to take into consideration only single digit house numbers, this expression return the correct alpha-numerical sort order. Change 1 to 2 for two digit values (and so on) in line 10 and concatenate the resulting strings:

array_to_string (
    array_prioritize(
        map_akeys(
            hstore_to_map( 
                array_to_string(
                    array_foreach (
                        array_foreach (
                            string_to_array("list" ,'-'),
                            if (
                                length (regexp_substr( @element, ('\\d+'))) =1,  -- no. of digits
                                @element, ''
                            )
                        ),
                        @element  ||  '=>'  ||  regexp_substr( @element, ('\\d+'))
                    )
                )
            )
        ), 
        array (1,1000)
    )
)

To avoid repaeting the above expression for each length (no. o digits), again add an array_for_each() function. Line 3 defines the no. of digits for which the expression should run - here for 1- and 2-digit lengths:

array_to_string (
    array_foreach (
        array(1,2), -- no. of digits to take into consideration
        with_variable(
            'digits',
            @element,
            array_to_string (
                array_prioritize(
                    map_akeys(
                        hstore_to_map( 
                            array_to_string(
                                array_foreach (
                                    array_foreach (
                                        string_to_array("list" ,'-'),
                                        if (
                                            length (regexp_substr( @element, ('\\d+'))) = @digits,
                                            @element, ''
                                        )
                                    ),
                                    @element  ||  '=>'  ||  regexp_substr( @element, ('\\d+'))
                                )
                            )
                        )
                    ), 
                    array (1,1000)
                )
            )
        )
    )
)

enter image description here

And, changing line 3 either to array(1,2,3), or to generate_series(1,3,1), three-digit values are also sorted correctly: enter image description here

5
  • 2
    in case you use array (1,3) in line three, it would not consider the two-digit numbers :-(
    – pigreco
    Jan 16 at 12:56
  • You're right, once again! To include two-digit numbers, it must be either array(1,2,3), or generate_series(1,3). I updated the solution accordingly.
    – Babel
    Jan 16 at 13:52
  • 1
    I did a test with my data, my expression takes 125 seconds, yours double; I also notice that it adds a comma to the end or the beginning of the string. :-(
    – pigreco
    Jan 16 at 16:45
  • 1
    Yeah, not a real improvement... Thanks for the feedback. No easy solution available, it seems (no one that improves your expression).
    – Babel
    Jan 16 at 16:48
  • 2
    thanks for your time :-)
    – pigreco
    Jan 16 at 16:50
5

A slightly different approach to solving the problem from the solutions till now proposed is the following expression:

array_to_string(
    array_foreach(
        array_sort(
            array_foreach(
                string_to_array(
                    replace('5/A-5-16/B-4-18-15-22-3-14-12-6-16/A-16/FG-9-11-13-7-21-1-19-10-7/A-17-123-20','-',',')),
                lpad(@element,((max_length(regexp_substr(@element,'(\\d+)')))+3)+length(regexp_replace(@element,'\\d+','')),0)
            )   
        ),
    regexp_replace(regexp_replace(@element, '^00',''),'^0',''))
)


With this expression, both elements with one or more letters and numbers with more than two-digit are processed and ordered correctly.

enter image description here

1
  • 2
    I tried the expression with my dataset, my expression takes 125 seconds, yours after 10 minutes I stopped it, it seems too slow.Also it doesn't handle 5/1-5/A cases correctly, I know it's not in the example. Thank you
    – pigreco
    Jan 17 at 8:19
3

A slightly different approach, converting the characters to its ascii code and adding it as decimals to the house numbers, creating decimal numbers that are then sorted and afterwards coverted back to the initial format:

array_to_string (
    array_foreach (
        array_sort (
            array_foreach (
                string_to_array (list,'-'),
                to_real (
                    concat (
                        regexp_substr (
                            @element,   
                            ('\\d+')
                        ),  
                        '.' ,
                        ascii(
                            regexp_substr (
                                replace (@element, '/',''),
                                ('\\D+')
                            )
                        )
                    )
                )
            )
        ),
        floor(@element)  || 
        if (@element > floor (@element), '/', '') || 
        char (100*round (@element -floor(@element),2))
    )
)

enter image description here

8
  • 2
    unfortunately it doesn't handle house numbers with multiple letters, see 2/AX which becomes just 2/A or 29/BAC in` 29/B`
    – pigreco
    Jan 16 at 8:46
  • 1
    the problem is in the ascii and char functions which return the value of only one character
    – pigreco
    Jan 16 at 8:54
  • I see. This could be adressed somehow, but would make this rather complex expression even more complex. You have a working expression and are looking for an easier one, so that makes no sense.
    – Babel
    Jan 16 at 9:10
  • 2
    If there is not in the core functions, it is the right time to make one. @Babel is right: "your example is tricky enough". I think the best option is to build a new function for your particular problem. Jan 16 at 9:28
  • 2

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.