PostGIS function to connect geometry LINE together?

Question

(note: the_geom is a geometry value (TYPE: LINESTRING), in this case i random them for readability)

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 don't mind the purpose

I would like to do 'this' (a set of queries from my past question, link located on the end of this post) for every row in Table B (aka. land_inventory). These two tables are related by 'ctrl_sec_no' (aka. control section number of a road) which means :: in ONE ctrl_sec_no -- 120802 (in fact, it is a road which is equivalent to 3 geometry LINESTRINGs (the_geom) connected together, from kstart 238 (start at kilometre of 238) to kend 250)

[PostGIS question]

the question is how to connect this 3 lines {aka gid(626,638,4037) from the table} together and result in 'the_sum_geom' (initially NULL) by using PostGIS functions (whatever). after that we will use this 'the_sum_geom' to find the POINT on this geometry LINESTRING

(How calculate things from many tables by using a few queries?).

Francisco Valdez Francisco Valdez · Accepted Answer · 2012-02-09T16:46:23

The function you are looking for is ST_Union, you need to use it with the aggregate form:

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 the multi line cant be merged it will return the multiline without any modification. See the ST_LineMerge docs to understand what ST_LineMerge can or can't do.

PostGIS function to connect geometry LINE together?

1 Answers