I have to build a street network with Postgis that will be used with pgrouting.
I have two tables: 1) the_network (182 lines): is a MULTILINESTRING table. Having columns road_name (varchar) / road_length (numeric)
(numeric) 2) buildings (104 lines): is a POINT table. Having building_id (integer)
The following code creates lines between buildings and the network. The 104 created lines are stored in
buildings_to_network_lines_table which is GEOMETRY type.
SELECT ST_ShortestLine(a.geom, ST_Collect(b.geom)) AS geom, ST_Distance(a.geom, ST_Collect(b.geom)) AS shape_length INTO buildings_to_network_lines_table FROM buildings a, the_network b WHERE ST_Dwithin (a.geom, b.geom, 1000) group by a.gid, a.geom;
Then i just want to create one table named the_network_full that contains all the lines of the_network and buildings_to_network_lines_table tables. I've tried to achieve this by a ST_UNION function :
SELECT ST_Union(a.geom, b.geom) AS the_network_full FROM the_network a, buildings_to_network_lines_table b;
The results looks good but unfortunatelly the resulting the_network_full table (type GEOMETRY) contains 18'928 lines (182 * 104) instead of 182+104 = 286 lines. Any idea of what I am doing wrong ? How would you do to merge these two tables into a new one ?
Any help will be greatly appreciated, Theo