PostGIS function to combine LINE geometry together?

Question

(note: the_geom is a geometric value (TYPE: LINESTRING), in which case I read them randomly)

gid | kstart | kend | ctrl_sec_no | the_geom | the_sum_geom 626 | 238 | 239 | 120802 | 123456 | NULL 638 | 249 | 250 | 120802 | 234567 | NULL 4037| 239 | 249 | 120802 | 345678 | NULL 

[Real Practice Description] just skip this for those who are not against the goal

I would like to do 'this' (a set of queries from my last question, the link located at the end of this post) for each row in table B (aka. Land_inventory). These two tables are associated with 'ctrl_sec_no' (the so-called road management section number), which means :: in ONE ctrl_sec_no - 120802 (in fact, it is a road that is equivalent to 3 geometry LINESTRINGs (the_geom) connected together from kstart 238 (start at Kilometer 238) to Kend 250)

[PostGIS Question]

the question arises, how to link these 3 rows {aka gid (626,638,4037) from the table} together and cast to 'the_sum_geom' (originally NULL) using PostGIS functions (whatever). after that we will use this "the_sum_geom" to find the POINT in this LINESTRING geometry

( How to calculate things from many tables using multiple queries? ).

Answer 1

The function you are looking for is ST_Union , you need to use it as a placeholder:

 update mytable set the_sum_geom = ST_LineMerge( ( select ST_Union(the_geom) from mytable where ctrl_sec_no = 120802 ) ) where ctrl_sec_no = 120802; 

With ST_LineMerge you can convert from Multiline to LineString, but there is a caveat if combining multiple lines will not be combined, it will return multi-line without any changes. Refer to the ST_LineMerge docs to understand what ST_LineMerge may or may not do.