Create a reference number (id) based on x coordinate in QGIS

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?

Answer

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.