1

I would like to add a reference number to a point table. I would like this value to increase as I move from west to east across my data. So the values read 1 to n from west to east. I have an attribute table on my composer which will contain this reference number, and the name of each point.

The reason for this is so that it is easier to find the points by the reference number on a map. I want to use this reference number as a label for each point. At the moment the reference numbers follow no particular sequence.

Any tips on how I can achieve this in QGIS?

  • I have added an answer, but if you think it needs elaboration then please inform me. Also you do not mention your data source, is this a shapefile or is it some other data source? – ragnvald May 21 '15 at 12:18
  • Since you asked for an answer in QGIS and accepted an answer using PostGIS I suggest you rephrase your question to focus on PostGIS. – ragnvald Jun 9 '15 at 6:22
2

I had to do this just last week, and used a row_number() function ordering by LAT and LON values - however, you will have to venture into SQLite or PostGIS to accomplish it.

So here's what I did:

I needed to select the first 650 kids from North to South and allocate them to a school - this was a hypothetical scenario based on direction and capacity (650 kids fit into a school).

Using SQL Server Spatial, I determined the direction the LAT and LON values increased/decreased from 'right to left' and 'up and down', and how does that correspond to how I would use the row_number function's 'order by' parameter.

I did a little trial and error creating a spatially-enabled view in my database, and brought that into my desktop GIS (In my case ArcMap).

The view held two columns for the X and Y values, which I could use as labels to determine the right/left / up/down, and looked something like this

(SQL Server syntax)

select
table.objectid
, table.geom
, table.geom.STX as LON
, table.geom.STY as LAT

Examining the data on the map, I determined that ordering the LAT (y) values in DESC order, I could assign a row_number from North to South.

My final sql code looked something like this:

    select
    row_number() over (order by table.geom.STY desc) as rownum
, table.objectid
, table.geom
, table.studentnumber
    from table

(I also realized that I didn't need to use the LAT/X value as I would miss kids on the 'right' side of the map - pulling from the North only (LON/Y) worked best)

Then to select the first 650 students from the North that would fit into the school, I wrapped the above into a sub-query, and took the first 650 records:

select * from (
        select
        row_number() over (order by table.geom.STY desc) as rownum
    , table.objectid
    , table.geom
    , table.studentnumber
        from table
) as topStudents

where topStudents.rownum <= '650'

Give or take a few tweaks of the SQL syntax, that is very doable in SQLite and/or PostGIS.

1

I am assuming you are working with a shapefile. But this might just as well work with data in a file geodatabase or other sources.

First of all you will have to establish an east/west value if your file does not have this already.Open the attribute table. Add a field called LON using the field calculator. Pull the value from $x as sin in the figure below. Make sure the new field is a decimal with high enough precission.

Field calculator at work

After this you use the MMQGIS plugin to do your sorting:

enter image description here

Sort by LON:

enter image description here

This will give you the attributes sorted according to LON. In my example it looks like this:

Sorted out

This solution is based on a file with the projection EPSG:4326. Guess it will also work with other projections.

1

Here's another way to number features based on their coordinates. This method doesn't require creating a new shapefile or knowing any Python.

Step 1: Install the Sort and Number plugin

Sort and Number

This plugin sorts and numbers an attribute table.

This plugin adds a new field to an attribute table, where features are numbered depending on multiple sorting criteria.

Step 2: Use the field calculator to add an X coordinate column to your attribute table.

enter image description here

Step 3: Run the Sort and Number plugin with the X coordinate column as the first field. enter image description here

Now you have a new field called "order" which is numbered from west to east. Labeling point features with the order field looks like this: enter image description here

0

If you like running commands at Python console:

  1. Add a numeric field to the attribute table via layer properties (in my example SortId)
  2. Make the layer the active layer
  3. Paste the following commands into the python console:

    aLayer = qgis.utils.iface.activeLayer()  # get active layer
    # create list of tupels of x-coordinate, feature-id
    xpos_id_list = [(feat.geometry().asPoint().x(), feat.id()) for feat in aLayer.getFeatures()]
    xpos_id_list.sort()  # sort list of tupels
    
    i = 1
    fni = aLayer.fieldNameIndex('SortId')  # get fieldindex
    aLayer.startEditing()
    for feat in xpos_id_list:
        aLayer.changeAttributeValue(feat[1], fni, i)  # change field value
        i += 1
    
    aLayer.commitChanges()  # save changed and stop editing
    

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.