Getting the line middle point with SQL in QGIS

Question

Using SQL in a Virtual Layer is it possible to get the midpoint of a line on the line?

I have tried Finding middle point (midpoint) of line in QGIS? but got an error

I also tried

SELECT road, st_centroid(geometry) as Mid_Point
FROM CSC_Roads
GROUP BY road

but these points are then not always on the line.

I'd also like the final value to be formatted as (x,y)-tuple as I need to display the value in a table.

So this should be

Airport      | 507889.784, 8044651.312 
Allan Howies | 483040.867, 8066200.614

It runs now but the point is not the midpoint but the start of each road (all segments grouped by 'Road').

SELECT Road, AsText(Line_Interpolate_Point(CSC_Roads.geometry, 0.5)) as Road_Location,
       Line_Interpolate_Point(CSC_Roads.geometry, 0.5)
FROM CSC_Roads
GROUP BY road

Answer 1

Let's assume there is a layer called "line" (blue), see image below.

---

So, the vital thing, there must be a distinction between Centroid and Midpoint. There several articles available online, for instance, I personally would consider this one Mathematics: What is the difference between midpoint, center and centroid?.

My personal distinction in terms of the GIS would be

---

For selecting the line's middle point it is not enough just using ST_Centroid(). Because (as described in manual) it is computed as the weighted length of each line segment. Therefore, with the following query

SELECT st_centroid(geometry)
FROM "line"

the line geocentoid is the location of the arithmetic mean of weighted properties, see image below.

So, IMHO the most suitable solution in getting the line's middle point can be achieved by means of ST_Line_Substring(), that produces a part of the input line geometry as a fraction between starting and ending points (0 ≤ fraction ≤ 1).

Thus, with the following query

SELECT ST_EndPoint(ST_Line_Substring(geometry,0,0.5))
FROM "line"

----- where 0 is a fraction where to start
----- and 0.5 is a fraction where to finish

the line middle point is now the middle of the line's original geometry, see image below.

Going further, the same output can be achieved with a slightly adjusted query

SELECT ST_StartPoint(ST_Line_Substring(geometry,0.5,1))
FROM "line"

So, it is possible to use ST_StartPoint() as well, an answer to the question "Why use endpoint (not startpoint) and why (geometry, 0,0.5)?" from comments.

So basically, ST_EndPoint() was used for the starting line segment and ST_StartPoint() for the ending line segment.

---

For extracting line's middle point coorinates, it is possible by the usage of ROUND(), ST_X and ST_Y. Check the following query

SELECT '(' || ROUND(ST_X(ST_StartPoint(ST_Line_Substring(geometry,0.5,1))),2) || ', ' || ROUND(ST_Y(ST_StartPoint(ST_Line_Substring(geometry,0.5,1))),2) || ')' AS XY
FROM "line"

---

References:

• Finding middle point (midpoint) of line in QGIS?