5

Using 3.22.13, consider a non-spatial GeoPackage attribute table containing one text field, named "field_a". This table has the following form:

fid field_a
1  B
2  A
3  X
4  C

How can I sort the table so that "field_a" is now permanently sorted in the same ascending order as the "fid", as shown below?

fid field_a
1  A
2  B
3  C
4  X

If this sorting requires exporting to a new table, that is acceptable.

I have tried the Order by expression tool with Expression = "field_a". Unfortunately, it did not change the sort order in any way.

Edit:

After further review, I've been able to accomplish this task by:

  1. Exporting the table as a .csv to Excel, without the "fid" field
  2. Sorting the .csv within Excel by field_a, then saving
  3. Add the re-sorted .csv to QGIS
  4. Export the .csv as a GeoPackage table

Although this works, it is a little kludgy. I'm looking for a more efficient way to do this sorting, preferably within QGIS. Also, I found no plugins that fit this need.

Improve this question

3 Answers 3

Reset to default
7

You can use GeoPandas to sort the field "field_a" in ascending order and save it back to the GeoPackage with a new name.

Here is a sample code:

import geopandas as gpd

# update the path to select the geopackage file
gpkg = r'F:\Ahmad\Test\Test4\Geopackage\Test.gpkg'

# Update layer name with the table name
gdf = gpd.read_file(gpkg, layer='polygon_grid', driver="GPKG")  

# sort field_a in ascending order
gdf = gdf.sort_values(['FiledName']) # change FiledName to field_a in your case

# write the table back to the geopackage with a new name
gdf.to_file(gpkg, layer='polygon_grid_sorted', driver="GPKG")

Here is the output:

enter image description here

Improve this answer
7

One can try the setSortExpression() and setSortOrder() methods of the QgsAttributeTableConfig class.

from qgis.core import QgsProject

def sorting_table_by_column(layer_name: str, column_name: str, sort_order: int) -> None:
    """
    Sets permanent sorting by a column in the attribute table
    Parameters:
    ==========
    :param layer_name: name of the layer
    :param column_name: name of the target column
    :param sort_order: ascending(0) or descending(1) sorting order
    """

    layer = QgsProject.instance().mapLayersByName(layer_name)[0]

    layer_attr_table_config = layer.attributeTableConfig()
    layer_attr_table_config.setSortExpression(column_name)
    layer_attr_table_config.setSortOrder(sort_order)

    layer.setAttributeTableConfig(layer_attr_table_config)
    
    return
    
sorting_table_by_column('points', "String", 0)

Initial input:
input

Change the parameters of the function in the last line. Press Run script run script and get the output that will look like this:

output

References:

Improve this answer
6

Create a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer... with this query, than make it permanent/export/save it:

select *
from my_layer
order by field_a

Be aware: if the initial layer contains a "fid" field, be sure to either create a new unique identifier field (see here) to get the id in the same order as field_a. The other option: exclude the initial "fid" from the query. In this case, you can't select all (select *), but have to list all fields you want to export one by one (there is no simple way to select all, but exclude one field).

If you have just two fields ("fid" and "field_a", as in your example), you could use select field_a, geometry from my_layer order by field_a. Like this, the missing "fid" field will be automatically created when you save the layer as GeoPackage.

Improve this answer
2
  • This was the simplest and easiest of the answers that were kindly offered. However, there is one important qualification here: when exporting the virtual layer via Export > Save features as..., uncheck the fid field from the Select fields to export and their export options section of the Save Vector Layer as... window. This forces the target fid to be recalculated (I think!), so that field_a and fid are both sorted in the same ascending order (which was a requirement of my OP). P.S., I am also eagerly awaiting the book :) Commented Dec 19, 2022 at 19:53
  • You're right, see updated answer. Commented Dec 19, 2022 at 20:17

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.