How to merge a start, undefined number of midpoints, and an endpoint together?-CodePudding

My goal is to merge together a start point that will always exist, any existing midpoints from a different table(could be from 0-100), and an end point that will always exist.

The tables are formatted so that table one has the "lines" without midpoints with: unique indentifier, start point, endpoint. Table two has the unique identifier for matching purposes, one point, and an ascending index to identify order.

    st_removerepeatedpoints(
        st_snaptogrid(
            st_setsrid(
                st_makeline(
                    st_makepoint(underground."X Coordinate"::double precision, underground."Y Coordinate"::double precision), 
                    st_dump(SELECT st_makepoint(midpoint.x_coordinate::double precision, midpoint.y_coordinate::double precision) AS geom FROM midpoint 
                        WHERE midpoint.element_name::text = underground."Element Name"::text
                        ORDER BY midpoint.indx), 
                    st_makepoint(underground."X2 Coordinate"::double precision, underground."Y2 Coordinate"::double precision)
                )
            , 32038)
        , 0.001::double precision)
    ) AS geom,

I've tried various functions but I've been unable to get the "midpoint" into a format that works well with stmakeline. stdump seems like a possible solution but i have been unable to get it to work, and i have not found any solutions from users with similar issues.

CodePudding user response：

Try to use this:

ST_MakeLine(ARRAY(SELECT geom FROM mid_point))

And ST_AddPoint() to add start and end points.