Engineering-Tech Wiki

This is an old revision of the document!

• Replace all occurrences of SHP_TABLE (case sensitive, can be part of a word) in the script below with the table name of the imported map from Shapefile (copy all script from below to a text editor and perform that replace);
• Copy/Paste (in Linux: select text and then use middle mouse button or Shift+Insert to paste it in a console) the script line-by-line, follow any severe errors (ignore ones, when it writes that table cannot be dropped when it does not actually exist :) and the like).

Previous solution did not seem to work – the street names and costs get themselves assigned to the wrong links, so there is no relation SHP_TABLE_TOPO.ID↔SHP_TABLE_TOPO.FEATURE.TG_ID=SHP_TABLE_LINK$.LINK_ID – this does not represent actual ID and LINK_ID relation.

-- We have to compare on a geometry-by-geometry basis but in Oracle we cannot compare multi-column values () so we create a signature by creating CBLOB of Well-Known-Text on GEOMETRY:
ALTER TABLE SHP_TABLE ADD WKT;
ALTER TABLE SHP_TABLE_LINK$ ADD WKT;
UPDATE SHP_TABLE t SET WKT=t.GEOMETRY.GET_WKT();
UPDATE SHP_TABLE_LINK$ l SET WKT=l.GEOMETRY.GET_WKT();
-- ! these two UPDATE SQLs now are giving an error, have to deal with:
-- ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
 
 
-- Then will copy values according to comparing WKTs:
 
-- copy/paste everything in one go between BEGIN and termination line with symbol “/” (including):
 
-- for street length and names:
BEGIN
        FOR r IN (SELECT LINK_ID FROM SHP_TABLE_LINK$) LOOP
                UPDATE SHP_TABLE_LINK$ SET (COST, NAME)=(
                  SELECT LENGTH, NAME FROM SHP_TABLE WHERE WKT=(
                    SELECT WKT FROM SHP_TABLE_LINK$ l WHERE LINK_ID = r.LINK_ID)
                  )
                WHERE LINK_ID=r.LINK_ID;
        END LOOP;
END;
/