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;

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;

enter image description here

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

  • 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; – cag 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.

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.