This discussion is archived
6 Replies Latest reply: Oct 15, 2013 9:08 AM by matperry RSS

orageo:nearestNeighbor no results

28f0f31d-f7f8-4fcd-a737-4d97bf31ab13 Newbie
Currently Being Moderated

Hi all,

 

I am using Oracle 12c Spatial and Graph and I would like to retrieve the nearest neighbors of a point from a dataset encoded according to GeoSPARQL.

I have created a spatial index on the datatype geo:wktLiteral and I add the appropriate hints to the query so the optimizer pick a plan using the index RDF_V$GEO_IDX. The query I pose is the following:

 

SELECT geo, wkt

FROM TABLE(SEM_MATCH('

     PREFIX geo: <http://www.opengis.net/ont/geosparql#>

     SELECT ?geo ?wkt

     WHERE {

          ?geo geo:asWKT ?wkt.

           FILTER(orageo:nearestNeighbor(?wkt,"POINT(22.39 38.25)"^^geo:wktLiteral,"sdo_num_res=10")).

     }',

SEM_MODELS('geosparqlmd'), null, null, null, null, 'HINT0={ LEADING(?wkt) INDEX(?wkt RDF_V$GEO_IDX) }', null, null));

 

I do not get any error but I do not get any results neither. However, there are a lot of points around POINT(22.39 38.25) in my dataset and if I remove the filter I get results.


Do I use orageo:nearestNeighbor in a wrong way?

 

 

Best regards,

George

  • 1. Re: orageo:nearestNeighbor no results
    matperry Journeyer
    Currently Being Moderated

    Hi George,

     

    Can you please post some of the results you get without the filter? It will be helpful if you use PLUS_RDFT=T in the SEM_MATCH options parameter and select wkt$rdfterm so that we can see all components of the RDF term.

     

    Thanks,

    Matt

  • 2. Re: orageo:nearestNeighbor no results
    28f0f31d-f7f8-4fcd-a737-4d97bf31ab13 Newbie
    Currently Being Moderated

    Hi Matt,

     

    thank you for your answer.

     

    By posing this query:

    SELECT geo, wkt, wkt$rdfterm FROM TABLE(SEM_MATCH('

            PREFIX geo: <http://www.opengis.net/ont/geosparql#>

            SELECT ?geo ?wkt

            WHERE {

                    ?geo geo:asWKT ?wkt.

            }',

    SEM_MODELS('geosparqlmd'), null, null, null, null, 'HINT0={LEADING(?wkt) INDEX(?wkt RDF_V$GEO_IDX)} PLUS_RDFT=T', null, null));

     

    Some of the results I get are the following:

    GEO                                                            WKT                                     WKT$RDFTERM

    --------------------------------------------------------------     ----------------------------------------    ----------------------------------------------------------------------------------------------------------------------

    http://www.geonames.org/geometry_8026       POINT(20.75 38.7)                 "POINT(20.75 38.7)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>

     

     

    http://www.geonames.org/geometry_10782     POINT(20.86667 39.96667)     "POINT(20.86667 39.96667)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>

     

     

    http://www.geonames.org/geometry_1191       POINT(22.05 39.93333)          "POINT(22.05 39.93333)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>

     


    Best regards,

    George

  • 3. Re: orageo:nearestNeighbor no results
    matperry Journeyer
    Currently Being Moderated

    Hi George,

     

    The query and data looks correct. I'll try to reproduce the problem locally with the geonames dataset.

     

    Thanks,
    Matt

  • 4. Re: orageo:nearestNeighbor no results
    matperry Journeyer
    Currently Being Moderated

    George,

     

    orageo:nearestNeighbor behaves as expected in my test (see below). Can you post the commands you used to load the data and create the spatial index.

     

    Thanks,
    Matt

     

    Using data US.zip from http://download.geonames.org/export/dump/

     

    SQL*Loader control file:

     

    LOAD DATA
    CHARACTERSET UTF8
    TRUNCATE
    INTO TABLE GEONAMES_RAW
    FIELDS TERMINATED BY '\t'
    TRAILING NULLCOLS
    (
    GEONAMEID CHAR(4000) NULLIF (NAME="NULL"),
    NAME CHAR(4000) NULLIF (NAME="NULL"),
    ASCIINAME CHAR(4000) NULLIF(ASCIINAME="NULL"),
    ALTERNATENAMES CHAR(4000) NULLIF (ALTERNATENAMES="NULL"),
    LATITUDE CHAR(4000) NULLIF(LATITUDE="NULL"),
    LONGITUDE CHAR(4000) NULLIF(LONGITUDE="NULL"),
    FEATURE_CLASS CHAR(4000) NULLIF(FEATURE_CLASS="NULL"),
    FEATURE_CODE CHAR(4000) NULLIF(FEATURE_CODE="NULL"),
    COUNTRY_CODE CHAR(4000) NULLIF(COUNTRY_CODE="NULL"),
    CC2 CHAR(4000) NULLIF(CC2="NULL"),
    ADMIN1 CHAR(4000) NULLIF(ADMIN1="NULL"),
    ADMIN2 CHAR(4000) NULLIF(ADMIN2="NULL"),
    ADMIN3 CHAR(4000) NULLIF(ADMIN3="NULL"),
    ADMIN4 CHAR(4000) NULLIF(ADMIN4="NULL"),
    POPULATION CHAR(4000) NULLIF(POPULATION="NULL"),
    ELEVATION CHAR(4000) NULLIF(ELEVATION="NULL"),
    DEM CHAR(4000) NULLIF(DEM="NULL"),
    TIMEZN CHAR(4000) NULLIF(TIMEZN="NULL"),
    MOD_DATE CHAR(4000) NULLIF(MOD_DATE="NULL")
    )

     

    Script output:

     

    SQL> set echo on;
    SQL> set serverout on;
    SQL> set timing on;
    SQL> set lines 200 pages 10000;
    SQL> 
    SQL> conn / as sysdba;
    Connected.
    SQL> create user rdfuser identified by rdfuser;
    User created.
    Elapsed: 00:00:01.56
    SQL> grant connect,resource,unlimited tablespace to rdfuser;
    Grant succeeded.
    Elapsed: 00:00:00.03
    SQL> 
    SQL> exec sem_apis.create_sem_network('tbs_3');
    PL/SQL procedure successfully completed.
    Elapsed: 00:00:12.75
    SQL> 
    SQL> conn rdfuser/rdfuser;
    Connected.
    SQL> 
    SQL> -- create table to hold geonames data
    SQL> create table
      2  geonames_raw (
      3    GEONAMEID     VARCHAR2(4000),
      4    NAME          VARCHAR2(4000),
      5    ASCIINAME     VARCHAR2(4000),
      6    ALTERNATENAMES VARCHAR2(4000),
      7    LATITUDE     VARCHAR2(4000),
      8    LONGITUDE     VARCHAR2(4000),
      9    FEATURE_CLASS  VARCHAR2(4000),
     10    FEATURE_CODE     VARCHAR2(4000),
     11    COUNTRY_CODE     VARCHAR2(4000),
     12    CC2          VARCHAR2(4000),
     13    ADMIN1          VARCHAR2(4000),
     14    ADMIN2          VARCHAR2(4000),
     15    ADMIN3          VARCHAR2(4000),
     16    ADMIN4          VARCHAR2(4000),
     17    POPULATION     VARCHAR2(4000),
     18    ELEVATION     VARCHAR2(4000),
     19    DEM          VARCHAR2(4000),
     20    TIMEZN          VARCHAR2(4000),
     21    MOD_DATE     VARCHAR2(4000)
     22  );
    Table created.
    Elapsed: 00:00:00.23
    SQL> 
    SQL> -- load geonames data with sqlldr
    SQL> host sqlldr userid=rdfuser/rdfuser control=geonames.ctl data=US.txt direct=true skip=0 load=100000000 discardmax=1000000 bad=d0.bad discard=d0.rej log=d0.log errors=1000000
    SQL*Loader: Release 12.1.0.1.0 - Production on Mon Oct 14 12:06:15 2013
    Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
    Path used:      Direct, LOAD=100000000
    Load completed - logical record count 2152673.
    Table GEONAMES_RAW:
      2152672 Rows successfully loaded.
    Check the log file:
      d0.log
    for more information about the load.
    SQL> 
    SQL> alter session enable parallel dml;
    Session altered.
    Elapsed: 00:00:00.01
    SQL> 
    SQL> -- create triples and insert into staging table
    SQL> create table
      2  geonames_stable(
      3    rdf$stc_sub  VARCHAR2(4000) NOT NULL,
      4    rdf$stc_pred VARCHAR2(4000) NOT NULL,
      5    rdf$stc_obj  VARCHAR2(4000) NOT NULL
      6  );
    Table created.
    Elapsed: 00:00:00.01
    SQL> 
    SQL> insert /*+ append */ into geonames_stable(rdf$stc_sub, rdf$stc_pred, rdf$stc_obj)
      2  select '<http://www.geonames.org/geometry_'||geonameid||'>',
      3          '<http://www.opengis.net/ont/geosparql#asWKT>',
      4          '"POINT('||trim(longitude)||' '||trim(latitude)||')"^^<http://www.opengis.net/ont/geosparql#wktLiteral>'
      5  from geonames_raw;
    2152672 rows created.
    Elapsed: 00:00:18.51
    SQL> 
    SQL> -- create application table
    SQL> create table geonames_atab(triple sdo_rdf_triple_s);
    Table created.
    Elapsed: 00:00:00.25
    SQL> 
    SQL> -- create model
    SQL> exec sem_apis.create_sem_model('geonames','geonames_atab','triple');
    PL/SQL procedure successfully completed.
    Elapsed: 00:00:03.47
    SQL> 
    SQL> -- bulk load data
    SQL> grant select on geonames_stable to mdsys;
    Grant succeeded.
    Elapsed: 00:00:00.80
    SQL> grant insert on geonames_atab to mdsys;
    Grant succeeded.
    Elapsed: 00:00:00.01
    SQL> 
    SQL> exec sem_apis.bulk_load_from_staging_table('geonames','rdfuser','geonames_stable',flags=>' PARSE MBV_METHOD=SHADOW PARALLEL_CREATE_INDEX PARALLEL=4 ');
    PL/SQL procedure successfully completed.
    Elapsed: 00:04:28.11
    SQL> 
    SQL> -- create spatial index
    SQL> conn / as sysdba;
    Connected.
    SQL> EXECUTE sem_apis.add_datatype_index('http://www.opengis.net/ont/geosparql#wktLiteral',    options=>'TOLERANCE=10 SRID=8307 DIMENSIONS=((LONGITUDE,-180,180) (LATITUDE,-90,90))');
    PL/SQL procedure successfully completed.
    Elapsed: 00:11:44.35
    SQL> 
    SQL> -- run some queries
    SQL> conn rdfuser/rdfuser;
    Connected.
    SQL> 
    SQL> column s$rdfterm format a45;
    SQL> column p$rdfterm format a45;
    SQL> column o$rdfterm format a80;
    SQL> 
    SQL> -- simple query
    SQL> select s$rdfterm, p$rdfterm, o$rdfterm
      2  from table(sem_match(
      3  'SELECT ?s ?p ?o
      4   WHERE { ?s ?p ?o }
      5   LIMIT 10'
      6  ,sem_models('geonames')
      7  ,null,null,null
      8  ,null,' PLUS_RDFT=T '
      9  ));
    S$RDFTERM                      P$RDFTERM                     O$RDFTERM
    --------------------------------------------- --------------------------------------------- --------------------------------------------------------------------------------
    <http://www.geonames.org/geometry_7184618>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-79.84532 40.37332)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_6511783>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-86.6964 36.2241)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4782986>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-79.00196 37.26792)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_7179670>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-90.03046 35.14954)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_5816998>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-104.93164 44.68998)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_5004294>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-86.58177 43.64462)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4708627>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-96.24915 33.69705)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4356124>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-76.75386 39.28816)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4245170>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-88.85464 38.32222)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4900472>    <http://www.opengis.net/ont/geosparql#asWKT>  "POINT(-88.67007 41.38781)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    10 rows selected.
    Elapsed: 00:00:07.09
    SQL> 
    SQL> -- nearestNeighbor
    SQL> select s$rdfterm, o$rdfterm
      2  from table(sem_match(
      3  'SELECT ?s ?o
      4   WHERE
      5   { ?s ogc:asWKT ?o .
      6      FILTER (orageo:nearestNeighbor(?o, "POINT(-88.67007 41.38781)"^^ogc:wktLiteral, "sdo_num_res=10")) }'
      7  ,sem_models('geonames')
      8  ,null,null,null
      9  ,null,' PLUS_RDFT=T '
     10  ));
    S$RDFTERM                      O$RDFTERM
    --------------------------------------------- --------------------------------------------------------------------------------
    <http://www.geonames.org/geometry_4902311>    "POINT(-88.651 41.41273)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4900472>    "POINT(-88.67007 41.38781)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_6298675>    "POINT(-88.68333 41.36667)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4902345>    "POINT(-88.63368 41.39753)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4894812>    "POINT(-88.65313 41.39781)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4889470>    "POINT(-88.67837 41.36357)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4899321>    "POINT(-88.69563 41.39809)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4884055>    "POINT(-88.63535 41.37614)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4905592>    "POINT(-88.67118 41.37198)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    <http://www.geonames.org/geometry_4906650>    "POINT(-88.68285 41.39503)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
    10 rows selected.
    Elapsed: 00:00:02.23
    SQL> 
  • 5. Re: orageo:nearestNeighbor no results
    28f0f31d-f7f8-4fcd-a737-4d97bf31ab13 Newbie
    Currently Being Moderated

    Hi Mat,

     

    to create the semantic model I run the following:

     

    SQL> set echo on;
    SQL> set serverout on;  
    SQL> set timing on;
    SQL> set lines 200 pages 10000;  
    SQL> connect / as sysdba
    Connected.
    SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/md/admin/catsem.sql
    .....
    SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/md/admin/sdordfwm.sql
    .....
    SQL> CREATE TABLESPACE geonames_ts
     DATAFILE '/home/oracle/u01/app/oracle/oradata/geonames_ts.dbf' SIZE 1024M REUSE
     AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
     SEGMENT SPACE MANAGEMENT AUTO;  2    3    4  
    Tablespace created.
    Elapsed: 00:00:05.49
    SQL> CREATE TEMPORARY TABLESPACE geonames_temp_ts 
    TEMPFILE '/home/oracle/u01/app/oracle/oradata/geonames_temp_ts.dbf' SIZE 512M REUSE
    AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;  2    3  
    Tablespace created.
    Elapsed: 00:00:00.02
    SQL> CREATE USER rdf_user IDENTIFIED BY rdf_user
    DEFAULT TABLESPACE geonames_ts
    TEMPORARY TABLESPACE geonames_temp_ts;  2    3  
    User created.
    Elapsed: 00:00:00.02
    SQL> GRANT CREATE SESSION, RESOURCE TO rdf_user;
    Grant succeeded.
    Elapsed: 00:00:00.01
    SQL> ALTER USER rdf_user QUOTA UNLIMITED ON geonames_ts;
    User altered.
    Elapsed: 00:00:00.01
    SQL> GRANT CREATE TABLE TO rdf_user;
    Grant succeeded.
    Elapsed: 00:00:00.00
    SQL> GRANT CREATE ANY DIRECTORY TO rdf_user;
    Grant succeeded.
    Elapsed: 00:00:00.00
    SQL> GRANT DROP ANY DIRECTORY TO rdf_user;
    Grant succeeded.
    Elapsed: 00:00:00.00
    SQL> GRANT CREATE ANY INDEX TO rdf_user; 
    Grant succeeded.
    Elapsed: 00:00:00.00
    SQL> GRANT CREATE ANY INDEXTYPE TO rdf_user;
    Grant succeeded.
    Elapsed: 00:00:00.00
    SQL> EXECUTE SEM_APIS.CREATE_SEM_NETWORK('geonames_ts');
    PL/SQL procedure successfully completed.
    Elapsed: 00:00:06.77
    SQL> CONNECT rdf_user/rdf_user;
    Connected.
    SQL> CREATE TABLE geosparqlmd_t (id NUMBER, triple SDO_RDF_TRIPLE_S);
    Table created.
    Elapsed: 00:00:00.21
    SQL> EXECUTE SEM_APIS.CREATE_SEM_MODEL('geosparqlmd', 'geosparqlmd_t',     'triple');
    PL/SQL procedure successfully completed.
    Elapsed: 00:00:02.27
    SQL> COMMIT;
    Commit complete.
    Elapsed: 00:00:00.00
    SQL> EXIT;
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

     

    Because my whole dataset includes big literals with more than 4000 bytes I load the data using the Jena Adapter for bulk loading as described in Jena Adapter for Oracle Database. Do you need to post this code as well?

     

     

    Finally, I create the spatial index and pose the query:

    SQL> set echo on;  
    SQL> set serverout on;  
    SQL>  set timing on; 
    SQL> set lines 200 pages 10000;  
    SQL> CONNECT rdf_user/rdf_user AS SYSDBA;
    Connected.
    SQL> BEGIN
    SEM_APIS.ADD_DATATYPE_INDEX('http://www.opengis.net/ont/geosparql#wktLiteral',
      tablespace_name=>'GEONAMES_TS',
      options=>'TOLERANCE=0.0001 SRID=8307 DIMENSIONS=((LONGITUDE,-180,180) (LATITUDE,-90,90))', 
      parallel=>'10');
    END;
    /  2    3    4    5    6    7  
    PL/SQL procedure successfully completed.
    SQL> COLUMN DATATYPE FORMAT A50;
    COLUMN INDEX_NAME FORMAT A20;
    COLUMN STATUS FORMAT A20;
    COLUMN TABLESPACE_NAME FORMAT A20;
    SELECT DATATYPE, INDEX_NAME, STATUS, TABLESPACE_NAME FROM MDSYS.SEM_DTYPE_INDEX_INFO;SQL> SQL> SQL> SQL> 
    DATATYPE                                           INDEX_NAME           STATUS               TABLESPACE_NAME
    -------------------------------------------------- -------------------- -------------------- --------------------
    http://xmlns.oracle.com/rdf/geo/WKTLiteral         RDF_V$GEO_IDX        VALID
    http://www.opengis.net/ont/geosparql#wktLiteral    RDF_V$GEO_IDX        VALID
    Elapsed: 00:00:00.00
    SQL> connect rdf_user/rdf_user;
    Connected.
    SQL> SELECT geo, wkt, wkt$rdfterm FROM TABLE(SEM_MATCH('
      PREFIX geo: <http://www.opengis.net/ont/geosparql#>
      SELECT ?geo ?wkt
      WHERE { 
      ?geo <http://www.geonames.org/ontology#asWKT> ?wkt.
      FILTER (orageo:nearestNeighbor(?o, "POINT(-88.67007 41.38781)"^^ogc:wktLiteral, "sdo_num_res=10"))
      }',
    SEM_MODELS('GEOSPARQLMD'), null, null, null, null, 'HINT0={LEADING(?wkt) INDEX(?wkt RDF_V$GEO_IDX)} PLUS_RDFT=T', null, null));  2    3    4    5    6    7    8  
    no rows selected
    SQL> 

     

    Best regards,

    George

  • 6. Re: orageo:nearestNeighbor no results
    matperry Journeyer
    Currently Being Moderated

    Hi George,

     

    The tolerance value you used is a bit too small (0.0001). In general, it should be between 0.05 and 10 for geodetic data.

    See http://docs.oracle.com/cd/E16655_01/appdev.121/e17896/sdo_intro.htm#SPATL450

     

    I would recommend that you drop and re-create the spatial index with a larger tolerance value.

     

    Also, what is the result of the following queries on MDSYS.RDF_VALUE$?

     

    SELECT COUNT(*)

    FROM MDSYS.RDF_VALUE$ v

    WHERE SDO_RDF.getV$GeometryVal(v.value_type,

                                   v.vname_prefix,

                                   v.vname_suffix,

                                   v.literal_type,

                                   v.language_type,

                                   v.long_value,

                                   8307) IS NOT NULL;

     

    SELECT value_id

    FROM MDSYS.RDF_VALUE$ v

    WHERE MDSYS.SDO_NN(

            SDO_RDF.getV$GeometryVal(v.value_type,

                                     v.vname_prefix,

                                     v.vname_suffix,

                                     v.literal_type,

                                     v.language_type,

                                     v.long_value,

                                     8307),

            SDO_RDF.getV$GeometryVal('LIT',

                                     'POINT(-88.67007 41.38781)',

                                     '',

                                     'http://www.opengis.net/ont/geosparql#wktLiteral',

                                     '',

                                     NULL,

                                     8307),

            'sdo_num_res=10') = 'TRUE';

     

    Thanks,
    Matt

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points