(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? ).
Again, thanks in advanced
The function you are looking for is ST_Union, you need to use it with the aggregate form:
ReplyDeleteupdate mytable set the_sum_geom =
( select ST_Union(the_geom) from mytable where ctrl_sec_no = 120802 )
where ctrl_sec_no = 120802;