This discussion is archived
13 Replies Latest reply: Jul 20, 2007 8:32 AM by 588050 RSS

import Network Model from Shapefile

588050 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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;