Using ST_Union to merge tables containing lines?

Question

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)

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 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 look good but unfortunately the resulting "the_network_full" table (type GEOMETRY) contains 18'928 lines (182*104) instead of 182+104=286 lines.

How can I merge these two tables into a new one?

Answer 1

You implicitly CROSS JOINed the tables so you got a cartesian product. Since you don't want to merge the geometries together you should use the UNION ALL which is an entirely different construct:

SELECT a.geom
FROM  the_network a
UNION ALL
SELECT b.geom
FROM buildings_to_network_lines_table b

Now that you have a correct SELECT query you can use it in INSERT ... SELECT to put the data in new table.