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

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.

example

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

desired_output

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

result

• M Bain: The error is due to the space in the column alias Road Location, try Road_Location or putting quotes around it.
• GeorgeC: @MBain Thanks -it runs now but it creates a point for the start of the road and not the mid-point? also do you know how to get the text to just have the x,y value? see updated question.
• GeorgeC: @Taras That worked for me {SELECT Road, ST_EndPoint(ST_Line_Substring(geometry,0,0.5)), concat((ST_X(ST_EndPoint(ST_Line_Substring(geometry,0,0.5)))),',',ST_Y(ST_EndPoint(ST_Line_Substring(geometry,0,0.5)))) as Mid_Point from CSC_Roads group by road} Now I just need to format the number to 2 decimal places.
• GeorgeC: @taras thanks...happy to accept if you post as answer. Why use endpoint (not startpoint) and why (geometry, 0,0.5)?

Answer 1

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

input

---

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

idea

---

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.

result_1

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.

result_2

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?

• she_weeds: Not OP wanted to say this was an excellent answer and I learned something new about definitions of "middle point". Thank you, Taras!
• Taras: @she_weeds, always welcome 🙂