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;

How to create a new table containing the black lines (the_network) and the red lines (buildings_to_network_lines_table)

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?

  • Did you want a geometrical union? Seems like you want the union of tables: SELECT geom AS the_network_full FROM the_network UNION SELECT geom AS the_network_full FROM buildings_to_network_lines_table; – César Argul García Apr 9 '14 at 12:14

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
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.