Create points along a line from spreadsheet data in QGIS

Question

I have a sewer network comprising:

• Manholes - a points layer
• Pipes - a polyline layer, with attributes comprising the upstream and downstream manhole

I have a spreadsheet of pipe defects (from a CCTV contractor), where each defect is identified by the following data. Each defect is logged on a separate line with the following attributes:

• Pipe ID
• Starting Manhole
• Ending Manhole
• Distance from Starting manhole
• A range of attributes on observations made

I want to create a set of points for each defect so that I can then analyse the defects. As there can be a huge number of defects, I want to create a workflow to bring the points into QGIS so I can prioritise them.

I can see how to generate points at vertices, crossings or equidistant. However I am not certain where to even start in this task.

Answer 1

Try QGIS' natively supported SpatiaLite SQL; create a Virtual Layer with:

SELECT d.<pipe_id_column>,
       d.<other_columns>,
       ST_Line_Interpolate_Point(p.geometry, <distance_from_start_column> / ST_Length(p.geometry, <TRUE | FALSE>)) AS geometry
FROM   <pipe_layer> AS p
JOIN   <defects_layer> AS d
  ON   p.<id> = d.<pipe_id_column>
;

Here, a Point will be created (interpolated) at the fraction of line length given by the Distance to Starting Manhole divided by total line length (see below).

Fill in <> wrapped identifiers with actual layer/column names; ST_Length accepts a boolean parameter (see below).

The CSV can be loaded into the project or referred to as external data source in the Virtual Layer dialog.

---

This assumes a few things:

• the Starting Manhole always corresponds with the start node of the respective LineString

  If that is not necessarily the case, please edit your question with that info and maybe leave a comment here

• all geometries are in the same CRS

• CRS is geographic (LonLat), e.g. EPSG:4326

  Note:
  When a boolean parameter is passed to ST_Length, the function will calculate length either on a sphere (FALSE; faster, less precise) or a spheroid (TRUE; slower, highest precision), and results are in meter! This needs to match the Distance from Starting Manhole unit.
  If the CRS is projected do not pass the second parameter, to get length calculated using the given CRS and its units.