Differences

This shows you the differences between two versions of the page.

--- contrib:add_names_of_streets_et_al_from_shapefile_to_oracle_spatial [2007/07/24 20:26] – created sleleiva
+++ contrib:add_names_of_streets_et_al_from_shapefile_to_oracle_spatial [2007/07/25 18:19] – sleleiva
@@ Line 1: / Line 1: @@
   * 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.
 <code oracle8>
--- copy/paste everything in one go between BEGIN and termination line with symbol “/” (including):
+-- 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
--- for street length:
-BEGIN
-        FOR r IN (SELECT LINK_ID FROM SHP_TABLE_LINK$) LOOP
-                UPDATE SHP_TABLE_LINK$ SET COST=(SELECT LENGTH FROM SHP_TABLE WHERE ID=(SELECT ID FROM SHP_TABLE_TOPO t WHERE t.FEATURE_TG_ID = r.LINK_ID)) WHERE LINK_ID=r.LINK_ID;
-        END LOOP;
-END;
-/
--- for street names:
+-- 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 LINK_NAME=(SELECT NAME FROM SHP_TABLE WHERE ID=(SELECT ID FROM SHP_TABLE_TOPO t WHERE t.FEATURE_TG_ID = r.LINK_ID)) WHERE LINK_ID=r.LINK_ID;
+                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;
 /
 </code>