How to merge linestrings that don't touch based on common name?

Question

I had a table of road line strings that contained duplicate road names with different geoms for different line segments. I extracted the duplicates to another table. I now want to join these segments under one street name, gid and merged geom in a different table and am a little stuck.

same street name,  gid   , distinct geom
same street name, dif gid, distinct geom

Any help appreciated.

Chris

When I first was thinking about this I wasn't taking into account needing a function that could take two geometries, among other shortfalls.

Moving along a little bit:

select T1.*, ST_Touches(T1.geom,T2.geom) from road_dups T1, road_dups T2
where T1.sld_name=T2.sld_Name order by sld_name desc;

Tells me in most cases start and end points don't touch at 0 feet (this is 32011 state plane in feet). Same results with St_Intersect:

select T1.*, ST_Intersects((ST_Buffer(T1.geom,0.0)),ST_Buffer(T2.geom,0.0)))from
road_dups T1, road_dups T2 where T1.sld_name=T2.sld_Name order by sld_name desc;

Now my start/ends are intersecting:

selectT1.*,ST_Intersects((ST_Buffer(T1.geom,0.1)),ST_Buffer(T2.geom,0.1)))from
road_dups T1, road_dups T2 where T1.sld_name=T2.sld_Name order by sld_name desc;

"WHITTREDGE RD" t
"WHITTREDGE RD" f
"WHITTREDGE RD" f
"WHITTREDGE RD" t

My process right now is clunky:

• Identify if a road table has these twice named roads with minor gaps
• Pull em out to another table and join them, I' now thinking St_Union
• Delete the dup records in the original table
• update the original table with the unioned linestrings
• process into topology

The holy grail would be to do this in place on the original table in one pass. I hope this is clearer. When my brain froze, well, it froze.

Chris

so pacofvf can see how I am mangling his suggested function:

CREATE OR REPLACE FUNCTION process_elim_dup_roads() 
RETURNS integer AS
$BODY$
DECLARE
rec record;
BEGIN
DELETE FROM T3;
FOR rec IN SELECT T1.sld_name as sld_name, T1.gid as gid, T1.route_subt, T1.road_num, T2.* as T2, ST_Union(ST_Buffer(T1.geom,0.1),ST_Buffer(T2.geom,0.1)) AS geom WHERE T1.sld_name=T2.sld_Name LOOP
    INSERT INTO T3 (sld_name, gid,route_subt, road_num, geom) VALUES (rec.sld_name, rec.gid, rec.route_subt, rec.road_num , rec.geom);
   --END IF;
END LOOP;
DELETE FROM T1;
FOR rec IN SELECT sld_name, ST_Multi(ST_Union(geom)) AS geom from T3 GROUP BY sld_name, gid, route_subt, road_num.. LOOP  -- we move to single geom in this ST_Union?
    INSERT INTO T1 (sld_name, gid, route_subt, road_num, geom) VALUES (rec.sld_name, rec.gid, rec.route_subt, rec.road_num, rec.geom);
 -- END IF;
END LOOP;
RETURN
END;
$BODY$
 LANGUAGE 'plpgsql' IMMUTABLE STRICT;

currently chokes at: ERROR: syntax error at or near "SELECT" LINE 14: FOR rec IN SELECT .sld_name, ST_Multi(ST_Union(geom)...

so I'm head scratching there. Took out a couple of unnesessary spaces before the first LOOP and now runs to END with ERROR: syntax error at or near "END" LINE 17: END;

Previous testing showed that ST_Union(geom,geom) returned equal geoms from ST_Equal, though different order from ST_OrderingEquals. I'm not sure that order matters that much at this point, but may just be a matter of retaining the max(gid) geom of any of the duplicates and tossing the rest.

per Pacofvf's suggestion - ie. test the parts by running each select. This is the first select:

SELECT T1.sld_name as sld_name, T1.gid as gid, T1.route_subt, T1.road_num,
ST_Union(ST_Buffer(T1.geom,0.1),ST_Buffer(T2.geom,0.1)) AS geom
FROM summit_roads as t1, summit_roads as t2
WHERE T1.sld_name=T2.sld_Name order by sld_name desc;

sld_name  gid route_subt road_num geom

"YALE ST";91518;7;"";"snip" --geom is snip
"WOODMERE DR";95961;7;"";"snip"
"WOODLAND AVE";95766;7;"";"snip"
"WOODFERN RD";96069;7;"";"snip"
"WOODCROFT RD";103401;7;"";"snip"
"WINCHIP RD";89078;7;"";"snip"
"WINCHESTER RD";103385;7;"";"snip"
"WILLIAMS ST";95881;7;"";"snip"
"WILDWOOD LA";96155;7;"";"snip"
"WHITTREDGE RD";95993;7;"";"snip"
"WHITTREDGE RD";95993;7;"";"snip"
"WHITTREDGE RD";95702;7;"";"snip"
"WHITTREDGE RD";95702;7;"";"snip"
"WHITESELL CT";95703;7;"";"snip"

So, change here is getting rid of the T2.* and using a FROM. Now, let's understand, I really know nothing about creating functions, but I think they are magical. When I read them and try to figure out what they are doing...I continue to think they are magical.

There are circumstances in a SELECT where one doesn't need a FROM clause, though I needed one to get this query to run as a stand alone. Because I think functions are magic, I wondered if the resulting function, - process_elim_dup_roads(some_table) creates an implied FROM clause, at least once functioning. I don't know. For the moment I'm going to plug a working query that includes a FROM back into the function body and test the next select.

Answer 1

It looks like you need a third table with the same schema than the T1 table and a function to do that. Assuming that the t1's srid is 95702 and t2's srid is 95993:

CREATE OR REPLACE FUNCTION my_merge_function() 
  RETURNS integer AS
$BODY$
DECLARE
    rec record;
BEGIN
        DELETE FROM T3;
        FOR rec IN SELECT T1.sld_name as sld_name, .... , ST_Union(T1.geom,ST_Transform(T2.geom,95702)) AS geom FROM T1,T2 WHERE T1.sld_name=T2.sld_Name LOOP
            INSERT INTO T3 (sld_name, ....., geom) VALUES (rec.sld_name, ..... , rec.geom);
        END LOOP;
        DELETE FROM T1;
        FOR rec IN SELECT sld_name, ST_Multi(ST_Union(geom)) AS geom from T3 GROUP BY sld_name, .... LOOP
            INSERT INTO T1 (sld_name, ....., geom) VALUES (rec.sld_name, ..... , rec.geom);
        END LOOP;
        RETURN
    END;
    $BODY$
      LANGUAGE 'plpgsql' IMMUTABLE STRICT;

I don't know what you meant with process into topology, but with this you are half way through.