5

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

  • 2
    The error is due to the space in the column alias Road Location, try Road_Location or putting quotes around it. – M Bain Aug 28 at 1:58
  • @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 Aug 28 at 4:10
  • @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 Aug 28 at 5:02
  • @taras thanks...happy to accept if you post as answer. Why use endpoint (not startpoint) and why (geometry, 0,0.5)? – GeorgeC Aug 28 at 5:24
8

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"
  • 2
    Not OP wanted to say this was an excellent answer and I learned something new about definitions of "middle point". Thank you, Taras! – she_weeds Aug 28 at 9:16
  • @she_weeds, always welcome 🙂 – Taras Aug 28 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.