13 Replies Latest reply: Jul 20, 2007 10:32 AM by 588050 RSS

    import Network Model from Shapefile

    588050
      Hello,

      How to import shapefile of roads network (network data model) into oracle spatial? (Oracle+Spatial 10.2g)

      http://www.oracle.com/technology/software/products/spatial/files/shape2sdojava.zip deals only with normal (non-network) spatial data..

      Huge thanks!
        • 1. Re: import Network Model from Shapefile
          150838
          i think shape loader does not support network data loading.

          what you need to do is just place nodes in your base file and load them into network table
          then write procedure to read lines data from geometry table and wright it into network table.

          hope it helps you

          for more details mail me

          Thanks
          Kabeer
          • 2. Re: import Network Model from Shapefile
            588050
            for normal data i have shapefile (.dbf, .prj, .shp, .shx).
            for roads' network i have (.dbf, .sbn, .sbx, .shp, .shx).
            I found on the internet, that .sbn and .sbx are used by ArcGIS to manage the spatial index file.

            I am a rookie here, so could you explain me in detail, whay steps I should make in order to perform this import (there is nothing in the Oracle Spatial db, all I got is this ShapeFile collection).

            Thanks a lot!

            --
            sledge
            • 3. Re: import Network Model from Shapefile
              321596
              It is possible to convert shapefiles with linestrings to networkdata because from all the linestrings you have the start- and endpoints. You can transform it to topologydata and from topologydata to networkdata. This is how I did it.
              Firts of all, load your shapefiles in your database.
              Then create topology with: exec sdo_topo.create_topology
              Insert null-face into topoface$
              EXECUTE sdo_topo.add_topo_geometry_layer
              EXECUTE SDO_TOPO_MAP.CREATE_TOPO_MAP
              EXECUTE SDO_TOPO_MAP.LOAD_TOPO_MAP
              Insert van data in topology table with insert procedure and SDO_TOPO_MAP.CREATE_FEATURE
              CALL SDO_TOPO_MAP.COMMIT_TOPO_MAP();
              EXECUTE SDO_TOPO.INITIALIZE_METADATA;

              Then you can create networktables like this:

              create table YOUR_LINK_TABLE$
              as select edge_id as link_id
              , start_node_id
              , end_node_id
              , geometry
              , 1 as cost
              from YOUR_EDGE_TABLE$

              The same for YOUR_NODES

              insert into user_sdo_network_metadata
              select SDO_NET.VALIDATE_NETWORK('C_REG_NET') from DUAL;

              additional collumns you'll have to add for driving speed, direction, length. You can use spatial operators and functions to do that.

              The parameters for all the procedures you can find in the spatial user guide.
              Some procedures will take some time and system-resources.
              Good luck.

              Wijnand
              • 4. Re: import Network Model from Shapefile
                588050
                Thanks a lot! I created topology successfully and inserted a null face with
                insert into ROADS_INCOMPL_TOPO_FACE$ values(0, null, null, null, null);

                But the following one errors out:
                BEGIN sdo_topo.add_topo_geometry_layer('roads_incompl_topo', 'roads_incomplete', 'GEOMETRY', 'CURVE'); END;

                *
                ERROR at line 1:
                ORA-00904: "A"."GEOMETRY"."TOPOLOGY_ID": invalid identifier
                ORA-06512: at "MDSYS.SDO_TOPO", line 789
                ORA-06512: at "MDSYS.SDO_TOPO", line 994
                ORA-06512: at line 1

                ROADS_INCOMPLETE is my table imported from ShapeFile. It contains a GEOMETRY column which looks like: GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES).
                I tried all topology IDs, no-one worked (although the type in the imported shapefile is linestring(so it's CURVE?)

                further: what is the 'van data' mentioned in your post?

                Thanks you very much for further help-out.

                --
                sledge

                Message was edited by:
                sledge
                • 5. Re: import Network Model from Shapefile
                  150838
                  BEGIN sdo_topo.add_topo_geometry_layer('roads_incompl_topo', 'roads_incomplete', 'FEATURE', 'CURVE');

                  hope it helps

                  Kabeer
                  • 6. Re: import Network Model from Shapefile
                    588050
                    No, it didn't:
                    ORA-00904: "A"."FEATURE": invalid identifier

                    You see, as I have googled so far, my table has to have SDO_TOPO_GEOMETRY column (which then has a TOPOLOGY_ID component), which the table hasn't:

                    SQL> desc roads_incomplete;
                    Name Null? Type
                    ----------------------------------------- -------- ----------------------------
                    CAT NUMBER(38)
                    ID NUMBER
                    FEATTYP NUMBER
                    FT NUMBER
                    F_JNCTID NUMBER
                    F_JNCTTYP NUMBER
                    T_JNCTID NUMBER
                    T_JNCTTYP NUMBER
                    <...>
                    GEOMETRY MDSYS.SDO_GEOMETRY

                    How do I add a FEATURE column to this spatial table?
                    • 7. Re: import Network Model from Shapefile
                      588050
                      Ok, here's what I have done so far (started creating freature table myself), and in the end I get an error:
                      exec sdo_topo.create_topology('roads_incompl_topo', 0.5, null, null, null, null, null);
                      insert into ROADS_INCOMPL_TOPO_FACE$ values(0, null, null, null, null);
                      create table roads_streets (name varchar2(30), feature SDO_TOPO_GEOMETRY); (NOTE this does not have a primary key, because names and/or IDs are repetitious in roads_incomplete:

                      BEGIN
                      FOR road_rec IN (SELECT nome, geometry FROM roads_incomplete) LOOP
                      INSERT INTO roads_streets VALUES(road_rec.nome,
                      SDO_TOPO_MAP.CREATE_FEATURE('ROADS_INCOMPL_TOPO', 'ROADS_STREETS', 'FEATURE',
                      road_rec.geometry));
                      END LOOP;
                      END;
                      /

                      SQL> @for
                      BEGIN
                      *
                      ERROR at line 1:
                      ORA-29532: Java call terminated by uncaught Java exception:
                      oracle.spatial.topo.TopoDataException: table schema, table name or column name
                      not found in topology metadata
                      ORA-06512: at line 3

                      Hope you can help me, I remain;

                      --
                      sledge
                      • 8. Re: import Network Model from Shapefile
                        150838
                        Hi,

                        here is the steps to complete topology

                        -- Deregister feature layers, if they were registered.
                        -- Unioned_counties is a feature layer we will create in the next code example.

                        EXECUTE SDO_TOPO.DELETE_TOPO_GEOMETRY_LAYER ('SAMPLE_8307', 'SAMPLE_TOPO_8307', 'FEATURE');

                        -- Drop the topology if it exists, then create the topology

                        EXECUTE SDO_TOPO.DROP_TOPOLOGY ('SAMPLE_8307');
                        EXECUTE SDO_TOPO.CREATE_TOPOLOGY ('SAMPLE_8307', 0.5);

                        -- Fix incorrectly set bounds
                        --update user_sdo_geom_metadata
                        --set diminfo =
                        --SDO_DIM_ARRAY(
                        -- SDO_DIM_ELEMENT( 'Easting', -180, 180, 0.5),
                        -- SDO_DIM_ELEMENT( 'Northing', -90, 90, 0.5)
                        --)
                        --where table_name like 'SAMPLE_8307_%$';
                        --commit;

                        -- Insert the universal face for an empty topology
                        INSERT INTO SAMPLE_8307_face$ (face_id,
                        boundary_edge_id,
                        island_edge_id_list,
                        island_node_id_list,
                        mbr_geometry)
                        VALUES (-1, NULL, SDO_LIST_TYPE(), SDO_LIST_TYPE(), NULL);
                        commit;

                        -- Create an Oracle table with a feature layer.
                        -- Each feature will represent a county.

                        DROP TABLE SAMPLE_TOPO_8307;
                        CREATE TABLE SAMPLE_TOPO_8307 (id number,
                             type VARCHAR2(1),
                                  feature      SDO_TOPO_GEOMETRY);


                        -- Register feature layer with topology

                        EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('SAMPLE_8307', 'SAMPLE_TOPO_8307', 'FEATURE', 'POLYGON');

                        -- Create updatable TOPO_MAP object and load the whole topology in cache.
                        -- Since we just created the topology, the cache will be empty.

                        EXECUTE SDO_TOPO_MAP.DROP_TOPO_MAP('my_sample_map_cache');
                        EXECUTE SDO_TOPO_MAP.CREATE_TOPO_MAP('sample_8307', 'my_sample_map_cache');
                        EXECUTE SDO_TOPO_MAP.LOAD_TOPO_MAP('my_sample_map_cache', 'true');

                        DECLARE
                        CURSOR c1 IS SELECT fid_, geom FROM SAMPLE_PARCEL;
                        topo_geom SDO_TOPO_GEOMETRY;
                        BEGIN

                        FOR r IN c1 LOOP
                        topo_geom := SDO_TOPO_MAP.CREATE_FEATURE('SAMPLE_8307',
                        'SAMPLE_TOPO_8307',
                        'FEATURE',
                        r.geom);

                        -- Associate topological primitives with features
                        INSERT INTO SAMPLE_TOPO_8307 (id,feature)
                        VALUES (r.fid_, topo_geom);
                        -- Commit so I can watch from other session
                        commit;
                        END LOOP;
                        END;
                        /

                        -- Commit topology changes.

                        EXECUTE SDO_TOPO_MAP.COMMIT_TOPO_MAP;
                        EXECUTE SDO_TOPO_MAP.DROP_TOPO_MAP ('my_sample_map_cache');

                        -- Check how many counties were converted

                        SELECT COUNT(*) FROM SAMPLE_TOPO_8307;

                        -- After an initial bulk load into an empty topology, initialize_metadata

                        EXECUTE SDO_TOPO.INITIALIZE_METADATA ('SAMPLE_8307');


                        hope it helps you

                        Kabeer
                        • 9. Re: import Network Model from Shapefile
                          588050
                          Thanks, guys, everything looks good except for the last command :) I created topology according to mkabeer , however wijnand's last command outputs nothing:
                          SQL> select SDO_NET.VALIDATE_NETWORK('ROADS_INCOMPL') from DUAL;

                          SDO_NET.VALIDATE_NETWORK('ROADS_INCOMPL')
                          --------------------------------------------------------------------------------

                          While a command from a real imported (.dmp) network is ok:
                          SQL> select SDO_NET.VALIDATE_NETWORK('HILLSBOROUGH_NETWORK') from DUAL;

                          SDO_NET.VALIDATE_NETWORK('HILLSBOROUGH_NETWORK')
                          --------------------------------------------------------------------------------
                          TRUE

                          My relevant tables:
                          ROADS_INCOMPL_NODE$
                          ROADS_INCOMPL_EDGE$
                          ROADS_INCOMPL_FACE$
                          ROADS_INCOMPL_HISTORY$
                          ROADS_INCOMPL_TOPO <- the created topology table
                          ROADS_INCOMPLETE <- the one from shapefile
                          HILLSBOROUGH_NETWORK_NODE$
                          HILLSBOROUGH_NETWORK_LINK$
                          HILLSBOROUGH_NETWORK_PATH$
                          HILLSBOROUGH_NETWORK_PLINK$
                          ROADS_INCOMPL_RELATION$

                          >
                          create table YOUR_LINK_TABLE$
                          as select edge_id as link_id
                          , start_node_id
                          , end_node_id
                          , geometry
                          , 1 as cost
                          from YOUR_EDGE_TABLE$
                          The same for YOUR_NODES
                          I created a link$ table successfully, but this was already existing:
                          SQL> desc roads_incompl_node$;
                          Name Null? Type
                          ----------------------------------------- -------- ----------------------------
                          NODE_ID NOT NULL NUMBER
                          EDGE_ID NUMBER
                          FACE_ID NUMBER
                          GEOMETRY SDO_GEOMETRY

                          Thanks, we are close! ;)
                          • 10. Re: import Network Model from Shapefile
                            321596
                            Did you create spatial indexes on your sdo_geometry columns in your network tables. And I don' see a path-table (with index). You don't need paths on forehand but you need the path-table.

                            Wijnand
                            • 11. Re: import Network Model from Shapefile
                              588050
                              Ok, I have just now created a path table (coped schema from hillborough network sample):
                              create table roads_incompl_path$( PATH_ID NUMBER
                              , PATH_NAME VARCHAR2(200)
                              , PATH_TYPE VARCHAR2(200)
                              , START_NODE_ID NUMBER not null
                              , END_NODE_ID NUMBER not null
                              , COST NUMBER
                              , SIMPLE VARCHAR2(1)
                              , PATH_GEOMETRY MDSYS.SDO_GEOMETRY)
                              ;

                              network still does not get validated

                              How would i create spatial indices on sdo_geometry columns in my network tables, if this is the remaining case then?

                              And as why I'm sure the network is really incomplete is that I cannot visualize it with NDM Editor (c00l software), as it does not get listed among networks (means I have to insert some additional metadata like that about topology, as I get just an only HILLSBOROUGH_NETWORK in the list of networks)

                              Thanks for your patience

                              --
                              Message was edited by:
                              sledge
                              • 12. Re: import Network Model from Shapefile
                                321596
                                Spatial indexes are essential for querying spatial data. The create-statement is quite simple:
                                create index table_idx on table(geometry_column) indextype is mdsys.spatial_index

                                But before you can create a spatial index you have to update the metadata also for your network tables (with geometry column).
                                • 13. Re: import Network Model from Shapefile
                                  588050
                                  Ok, putting together your help, I managed to get myself a network model, hurray! But after this script in the end (could you peep through it and tell if anything's missing), VALIDATE_NETWORK gives me this:
                                  SDO_NET.VALIDATE_NETWORK('SHP_TABLE_NET')
                                  --------------------------------------------------------------------------------
                                  No Link Geom Metadata!

                                  How (where) would I insert that Link Geom Metadata? All I found is USER_SDO_GEOM_METADATA, but there is nowhere to insert Link data. Googled, too.

                                  The script, together with instructions, in case you make a nice use of it ;):
                                  * Import a Shapefile into Oracle Spatial as a normal vector map;
                                  * (ISSIT POSSIBLE TO AVOID THIS STEP? PL/SQL .sql file script? But that with DDL restrictions, clumsy variable definitions in DERLARE section etc. drove me nuts and I gave up, just asking users to do simple Find/Replace..):
                                  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).

                                  -- delete any tables existing
                                  EXECUTE SDO_NET.DROP_NETWORK('SHP_TABLE_NET');
                                  DROP TABLE SHP_TABLE_PLINK$;
                                  DROP TABLE SHP_TABLE_EDGE$;
                                  DROP TABLE SHP_TABLE_NODE$;
                                  DROP TABLE SHP_TABLE_LINK$;
                                  DROP TABLE SHP_TABLE_FACE$;

                                  -- delete any existing geometry layer
                                  EXECUTE SDO_TOPO.DELETE_TOPO_GEOMETRY_LAYER('SHP_TABLE', 'SHP_TABLE_TOPO', 'FEATURE');
                                  DROP TABLE SHP_TABLE_TOPO;

                                  -- then drop existing topology
                                  EXECUTE SDO_TOPO.DROP_TOPOLOGY('SHP_TABLE');
                                  -- create new topology
                                  EXECUTE SDO_TOPO.CREATE_TOPOLOGY('SHP_TABLE', 0.5);
                                  -- insert the universal face for an empty topology
                                  INSERT INTO SHP_TABLE_FACE$(face_id, boundary_edge_id, island_edge_id_list, island_node_id_list, mbr_geometry)
                                  VALUES(-1, NULL, SDO_LIST_TYPE(), SDO_LIST_TYPE(), NULL);
                                  -- create an Oracle table with a feature layer
                                  DROP TABLE SHP_TABLE_TOPO;
                                  -- note, do not create unique id (PRIMARY KEY) as the data in teleatlas.dbf is inconsistent
                                  CREATE TABLE SHP_TABLE_TOPO(id NUMBER, feature SDO_TOPO_GEOMETRY);
                                  --CREATE TABLE SHP_TABLE_TOPO(id number, type varchar2(1), feature SDO_TOPO_GEOMETRY);

                                  -- register feature layer with topology
                                  EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('SHP_TABLE', 'SHP_TABLE_TOPO', 'FEATURE', 'CURVE');
                                  -- create updatable TOPO_MAP object and load the whole topology in cache
                                  -- since we just created the topology, the cache will be empty
                                  EXECUTE SDO_TOPO_MAP.DROP_TOPO_MAP('SHP_TABLE_MAP_CACHE');
                                  EXECUTE SDO_TOPO_MAP.CREATE_TOPO_MAP('SHP_TABLE', 'SHP_TABLE_MAP_CACHE');
                                  EXECUTE SDO_TOPO_MAP.LOAD_TOPO_MAP('SHP_TABLE_MAP_CACHE', 'true');

                                  -- copy/paste everything in one go between BEGIN and COMMIT; (including):
                                  -- this one will demand a considerable amount of time
                                  BEGIN
                                  FOR r IN (SELECT id, geometry FROM SHP_TABLE) LOOP
                                  -- associate topological primitives with features
                                  INSERT INTO SHP_TABLE_TOPO(id, feature)
                                  VALUES(r.id,
                                  SDO_TOPO_MAP.CREATE_FEATURE('SHP_TABLE',
                                  'SHP_TABLE_TOPO',
                                  'FEATURE',
                                  r.geometry)
                                  );
                                  END LOOP;
                                  END;
                                  COMMIT;

                                  -- commit topology changes
                                  EXECUTE SDO_TOPO_MAP.COMMIT_TOPO_MAP;
                                  EXECUTE SDO_TOPO_MAP.DROP_TOPO_MAP('SHP_TABLE_MAP_CACHE');

                                  -- check how many primitives were converted (should be the same number as primitives in the DB)
                                  SELECT COUNT(*) FROM SHP_TABLE_TOPO;

                                  -- after an initial bulk load into an empty topology, initialize_metadata
                                  EXECUTE SDO_TOPO.INITIALIZE_METADATA('SHP_TABLE');

                                  -- check here what tables have been created:
                                  SELECT table_name FROM user_tables WHERE table_name LIKE 'SHP_TABLE_%$';

                                  CREATE TABLE SHP_TABLE_LINK$
                                  AS SELECT edge_id AS link_id,
                                  start_node_id,
                                  end_node_id,
                                  geometry
                                  FROM SHP_TABLE_EDGE$;

                                  -- FULL NETWORK:
                                  --CREATE TABLE SHP_TABLE_PATH$(PATH_ID NUMBER,
                                  -- PATH_NAME VARCHAR2(200),
                                  -- PATH_TYPE VARCHAR2(200),
                                  -- START_NODE_ID NUMBER NOT NULL,
                                  -- END_NODE_ID NUMBER NOT NULL,
                                  -- COST NUMBER,
                                  -- SIMPLE VARCHAR2(1),
                                  -- PATH_GEOMETRY MDSYS.SDO_GEOMETRY);
                                  --CREATE TABLE roads_incompl_plink$(path_id number not null, link_id number not null, seq_no number not null);
                                  --INSERT INTO USER_SDO_NETWORK_METADATA(
                                  -- network, network_category, geometry_type,
                                  -- node_table_name, node_geom_column,
                                  -- link_table_name, link_geom_column, link_direction,
                                  -- path_table_name, path_geom_column,
                                  -- path_link_table_name)
                                  -- VALUES(
                                  -- 'SHP_TABLE_NET', 'SPATIAL', 'SDO_GEOMETRY',
                                  -- 'SHP_TABLE_NODE$', 'GEOMETRY',
                                  -- 'SHP_TABLE_LINK$', 'GEOMETRY', 'UNDIRECTED',
                                  -- 'SHP_TABLE_PATH$', 'PATH_GEOMETRY',
                                  -- 'SHP_TABLE_PLINK$');

                                  -- MINIMAL NETWORK:
                                  INSERT INTO USER_SDO_NETWORK_METADATA(
                                  network, network_category, geometry_type,
                                  node_table_name, node_geom_column,
                                  link_table_name, link_geom_column, link_direction)
                                  VALUES(
                                  'SHP_TABLE_NET', 'SPATIAL', 'SDO_GEOMETRY',
                                  'SHP_TABLE_NODE$', 'GEOMETRY',
                                  'SHP_TABLE_LINK$', 'GEOMETRY', 'UNDIRECTED');

                                  SELECT SDO_NET.VALIDATE_NETWORK('SHP_TABLE_NET') FROM DUAL;

                                  COMMIT;