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.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
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).
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
rank() window functions)
This query should allow you to renumber points:
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.
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
As above but replace
row_number() OVER (ORDER BY lineid, theOrder) AS newid
row_number() OVER (PARTITION BY lineid ORDER BY lineid, theOrder) AS newid
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):