# How to number points along a line consecutively

I have a line and along this line I have different points which are max. 20m away from that line. Now I would like to number the points along the line, like shown in the grafic. Any suggestions how to achieve with QGIS or GRASS. • linear referencing pluging could help Mar 26 '14 at 6:45

This should probably get you pretty close to your goal:

``````SELECT  AID,
BID,
theOrder,
rank() over (PARTITION BY AID order by theOrder asc) as rank
FROM    (
SELECT  A."ID" AS AID,
B."ID" AS BID,
ST_line_locate_point(A."Shape", ST_ClosestPoint(A."Shape", B."Shape")) AS theOrder
FROM    tiger."LINES"  AS A
INNER JOIN
tiger."POINTS"  AS B
ON  ST_Distance(A."Shape", B."Shape") <= 20
) AS X
ORDER BY AID, rank;
``````

This should rank your lines ascending by ID, with points then ascending by where they rank, and only using the points that are within 20 feet of the line.

I'd like to note here that if you have a point that is beyond an end or beginning point of a line, but still within 20 feet of the line, you will get multiple ranks of the same number (1 or n). Should this be unwanted behavior, you will need to add distance to the line as a secondary ranking, or exclude ST_Line_Locate_Points of 0 AND 1.

If you want to include the possibility of points that fall exactly on the lines start or end points, then you would use ST_Distance = 0 (as a separate condition).

• In addition to this brilliant answer, for any SQL noobs like myself wishing to achieve the same but with strictly sequential numbering, rather than multiple ranks of the same number for points with the same theOrder value, use `row_number()` instead of `rank()` and perhaps add a distance value (`ST_Distance(A.geom,B.geom) as dist`) and after the `PARTITION BY` clause, order by theOrder and dist. For sequential renumbering that does not restart with each new line, simply drop the partition: use `rank() over (order by theOrder asc)` (or `row_number()`). Mar 31 '19 at 14:49

I would like to share an expansion to/variation to ike's answer (which was indeed suggested in the answer itself).

This can now be used directly within QGIS's DB manager (from QGIS 3.4.2 or QGIS 2.18.26 onwards - using SQLite3 3.25 per Giovanni Zezza's answer to my question here to make use of the `row_number()` or `rank()` window functions)

This query should allow you to renumber points:

• in a continuous sequence (or partitioned)
• based on the closest of multiple line features within 20 map units (changeable)
• ordered by line ID and then distance along line (in direction of line)
• even if the points are at the beginning/end of lines

The virtual layer gets updated immediately whenever you make even unsaved edits to the source point or line layers.

I haven't optimised the query, and in any case, performance can suffer with larger datasets so you should use the query directly in PostGIS instead for such situations.

Also, this hasn't been tested for multilinestring geometries.

# Continuous numbering

``````WITH lines AS
(SELECT geometry, lineid AS lineid FROM line_layer),

points AS
(SELECT geometry, treeid AS pointid FROM point_layer),

located AS
(SELECT points.geometry AS geometry,
lines.lineid AS lineid,
points.pointid AS pointid,
ST_line_locate_point(lines.geometry,
coalesce(ST_ClosestPoint(points.geometry, lines.geometry),
points.geometry)) AS theOrder,
ST_distance(points.geometry, lines.geometry) AS dist
FROM points
INNER JOIN lines
ON ST_Distance(points.geometry, lines.geometry) <= 20
ORDER BY lineid),

located_closest AS
(SELECT pointid,
min(dist) AS mindist
FROM located
GROUP BY pointid)

SELECT located.*,
row_number() OVER (ORDER BY lineid,
theOrder) AS newid
FROM located
INNER JOIN
located_closest
ON located.pointid = located_closest.pointid
WHERE located.dist = located_closest.mindist
``````

# Partitioned numbering

As above but replace

``````   row_number() OVER (ORDER BY lineid,
theOrder) AS newid
``````

with

``````   row_number() OVER (PARTITION BY lineid ORDER BY lineid,
theOrder) AS newid
``````

# Image comparisons (click to enlarge)

Original point data with lines:

Renumbered consecutively (L) and restarting with each new line (R) (PARTITION BY)

Note direction of line B1. Also, point 8 is snapped to the end of line A1:

Numbering instantly updated with unsaved edit to ID (C1 -> A3) (L) and unsaved edit to geometry (A1) (R):

• I tried to use your script in the SQL DB Manager but had some issues. I had to use geom instead of geometry which fixed most errors but now I get the following error. function st_line_locate_point(geometry, geometry) does not exist LINE 11: ST_line_locate_point(lines.geom, HINT: No function matches the given name and argument types. You might need to add explicit type casts. Jan 10 '20 at 18:34
• I got it fixed. Had to use st_linelocatepoint instead of st_line_locate_point. Jan 10 '20 at 19:25

Reverse the operation interpolate desired ranges along segment edges. extrapolate integers from edge location via scripting, specify buffer range from edge, say 20 meters. Just an approach, not a specific answer.