5

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.

enter image description here

meles
  • 323
  • 2
  • 10

3 Answers3

4

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:

enter image description here enter image description here

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:

enter image description here enter image description here

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

enter image description here enter image description here

Community
  • 1
she_weeds
  • 12,488
  • 1
  • 28
  • 58
  • 2
    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.

     – Andrew Jan 10 '20 at 18:34
  • 2
    I got it fixed. Had to use st_linelocatepoint instead of st_line_locate_point. – Andrew Jan 10 '20 at 19:25
3

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).

ike
  • 903
  • 6
  • 17
  • 1
    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()).

     – she_weeds Mar 31 '19 at 14:49
0

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.

lewis
  • 1,507
  • 10
  • 18