# 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?

• 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? Commented May 21, 2015 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. Commented Jun 9, 2015 at 6:22

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.

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

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:

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.

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.

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

Sort by LON:

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

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

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
``````