9.2 Creating a Spatial Index on Property Graph Data

After adding spatial data to a property graph, you can use OPG_APIS package subprograms to construct SDO_GEOMETRY objects, and then you can create a function-based spatial index on the vertices (VT$) or the edges (VT$) table.

Using the example property graph named test, the following statements add the necessary metadata and create a function-based spatial index.

SQL> -- In the schema that owns the property graph TEST:
SQL> --
SQL> insert into user_sdo_geom_metadata values('TESTVT$',
 'mdsys.opg_apis.get_geometry_from_v_t_cols(v,t)',
 sdo_dim_array(
   sdo_dim_element('Longitude', -180, 180, 0.005),
   sdo_dim_element('Latitude', -90, 90, 0.005)), 8307);

commit;

SQL> -- Create a function-based spatial index
SQL> create index testVTXGEO$
       on testVT$(mdsys.opg_apis.get_geometry_from_v_t_cols(v, t))
       indextype is mdsys.spatial_index_v2
       parameters ('tablespace=USERS')
       parallel 4
       local;

(To create a spatial index on your own property graph, replace the graph name test with the name of your graph.)

If the WKT literals are used in the V column, then replace mdsys.opg_apis.get_geometry_from_v_t_cols with mdsys.opg_apis.get_wktgeometry_from_v_t_cols in the preceding two SQL statements.

Note that the preceding SQL spatial index creation steps are wrapped in convenient Java methods in the OraclePropertyGraph class defined in the oracle.pg.rdbms package:

  /**
   * This API creates a default Spatial index on edges. It assumes that
   * the mdsys.opg_apis.get_geometry_from_v_t_cols(v,t) PL/SQL is going to be used
   * to create a function-based Spatial index. In addition, it adds a predefined
   * value into user_sdo_geom_metadata. To customize, please refer to the dev
   * guide for adding a row to user_sdo_geom_metadata and then creating a 
   * Spatial index manually.
   * Note that, a DDL will be executed so expect an implict commit. If you 
   * have changes that do not want to be persisted, run a rollback before calling
   * this method.
   * @param dop degree of parallelism used to create the Spatial index
   */
  public void createDefaultSpatialIndexOnEdges(int dop);

  /**
   * This API creates a default Spatial index on vertices. It assumes that
   * the mdsys.opg_apis.get_geometry_from_v_t_cols(v,t) PL/SQL is going to be used
   * to create a function-based Spatial index. In addition, it adds a predefined
   * value into user_sdo_geom_metadata. To customize, please refer to the dev
   * guide for adding a row to user_sdo_geom_metadata and then creating a 
   * Spatial index manually.
   * Note that a DDL will be executed so expect an implict commit. If you 
   * have changes that do not want to be persisted, run a rollback before calling
   * this method.
   * @param dop degree of parallelism used to create the Spatial index
   */
  public void createDefaultSpatialIndexOnVertices(int dop);