Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
2  
Welcome to gis.stackexchange! It would be easier to answer your question if you could provide some more information, like the tables you are currently using and the table you want to create. –  underdark Dec 10 '11 at 18:17
    
"WHITTREDGE RD";95702;"geom" "WHITTREDGE RD";95993;"geom" Pretty much the same as the abstracted above. These are one road so just looking to make one geom with one name –  Chris English Dec 11 '11 at 0:00
    
You will need to perform an aggregate based on an attribute. Most GIS packages (QGIS?) and databases (experience with Oracle Spatial) can perform this operation. If you provide the forum with the RDBMS you are using, we will be able to give you a better idea of what you can achieve. Another practice with Geodatabases, is to separate your Attribute data from your Geometries, and combine them using views. The advantage is being able to manipulate your data more easily. –  OptimizePrime Dec 12 '11 at 16:29
    
Postgis 2.0/postgresql 9.1 - the above #'s after WHITTREDGE RD are distinct gid rather than SRID –  Chris English Dec 12 '11 at 17:09
    
There is a syntax error near "id, route_subt, road_num.. LOOP -- we move to" extra dots, why dont you try each select query? –  pacofvf Dec 13 '11 at 15:45
show 3 more comments

1 Answer

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.

share|improve this answer
    
I was working on a WITH approach but I'll give this a try and report. –  Chris English Dec 12 '11 at 17:10
    
I was getting beaten up by WITH –  Chris English Dec 12 '11 at 17:18
    
I just realized you still have duplicated records, I've modified the second loop query to join road's records, you just have to put in the group by clause the other columns names, except the geom column. –  pacofvf Dec 12 '11 at 17:24
    
if I comment out the first END IF; runs to: syntax error at or near "SELECT" LINE 14: FOR rec IN SELECT sld_name, ST_Multi(ST_Union(geom)).. –  Chris English Dec 12 '11 at 18:08
    
sorry, copy paste error :s, just delete both END IF; –  pacofvf Dec 12 '11 at 18:16
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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