This discussion is archived
10 Replies Latest reply: Aug 27, 2012 8:23 AM by matperry RSS

SEM_MATCH Support for Spatial Queries

user461029 Newbie
Currently Being Moderated
Hi everyone,

I am trying to formulate a SEM_MATCH query in Oracle 11g.2.0.3 on a Windows installation using SPATIAL functions (query and error message attached below). SPATIAL and semantic technologies were enabled according to the installation guide. The semantic network and the semantic model were also created according to the documentation.

The problem seems to be the FILTER expression (which should trivially evaluate to true). If I remove the FILTER, then the remaining part of the query works fine.

Has someone an idea, how I can solve the issue?

Regards,
Michael

Query:

SELECT Distinct lat, lng
FROM TABLE(SEM_MATCH('
Prefix basekb_ns: <http://rdf.basekb.com/ns/>
SELECT ?lat ?lng
WHERE {
?a basekb_ns:m.01sx ?g.
?g basekb_ns:m.01q8 ?lng.
?g basekb_ns:m.01qh ?lat.
FILTER (orageo:withinDistance("POINT(-70 40)"^^orageo:WKTLiteral,
"POINT(-70 40)"^^orageo:WKTLiteral,
100000, "KM"))}',
SEM_Models('freebase_model'),
null,
null,
null));

Error:

Error at line 1:
ora-29532: Java call terminated by uncaught Java exception:
oracle.spatial.rdf.server.RDFException: Unabe to retrieve SRID value for
semantic network
ORA-06512: at "MDSYS.RDF_MATCH_IMPL_T", line 184
ORA-06512: at "MDSYS.RDF_MATCH_IMPL_T", line 1141
ORA-06512: at "MDSYS.RDF_MATCH_IMPL_T", line 379
ORA-06512: at line 1
  • 1. Re: SEM_MATCH Support for Spatial Queries
    matperry Journeyer
    Currently Being Moderated
    Hi Michael,

    Before you can use the spatial operators, you need to create a spatial index as described here:
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#CIHEABGJ

    Also note the recommended use of a LEADING(?var) optimizer hint in the usage notes of many of the spatial operators,
    e.g. http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sem_spatial_orageo.htm#BABGEGBF

    If spatial queries are not performing as expected, there are ways to tune the spatial index, so feel free to post any performance questions you may have.

    Thanks,
    Matt
  • 2. Re: SEM_MATCH Support for Spatial Queries
    user461029 Newbie
    Currently Being Moderated
    Hi Matt,

    thanks a lot. Your answer helped me a great deal.

    Regards,
    Michael
  • 3. Re: SEM_MATCH Support for Spatial Queries
    user461029 Newbie
    Currently Being Moderated
    Hi again,

    by the way: does ORACLE support GeoSPARQL queries and, if yes, do I need to add some special spatial index, well?

    Regards,
    Michael
  • 4. Re: SEM_MATCH Support for Spatial Queries
    matperry Journeyer
    Currently Being Moderated
    Hi Michael,

    The latest Oracle release does not support GeoSPARQL syntax. However, Oracle syntax is very similar. For example, orageo:WKTLiteral is exactly the same format as ogc:wktLiteral (including the default spatial reference system). In addition, the spatial functions available in Oracle provide equivalent functionality to the GeoSPARQL ones (e.g. orageo:relate for testing topological relations).

    Only a single spatial index is needed to enable all of the spatial functions.

    We were heavily involved in developing the GeoSPARQL standard and have not had a release since the standard was finalized.

    Thanks,
    Matt
  • 5. Re: SEM_MATCH Support for Spatial Queries
    user461029 Newbie
    Currently Being Moderated
    Hi Matt,

    thanks again for the quick answer.

    I am encountering another problem with the orageo:distance function. My query returns only results with an unbound value for ?distancefixedpoints. I do not get an error message. Did I used the orageo syntax in a wrong way?

    Regards,
    Michael


    Query:

    select ?l ?distancefixedpoints
    where {
    basekb_ns:budapest basekb_ns:formatlocation ?l.
    bind(orageo:distance(strdt("POINT(1.4839464963944773 103.7065268554687)",orageo:WKTLiteral),strdt("POINT(1.2639464963944773 103.6065268554687)",orageo:WKTLiteral),"unit=KM") as ?distancefixedpoints).
    }
  • 6. Re: SEM_MATCH Support for Spatial Queries
    matperry Journeyer
    Currently Being Moderated
    Hi Michael,

    SPARQL 1.1 features are not currently supported through SEM_MATCH. Are you using Jena Adapter?

    - Matt
  • 7. Re: SEM_MATCH Support for Spatial Queries
    user461029 Newbie
    Currently Being Moderated
    Hi Matt,

    yes, exactly.

    orageo:withinDistance works fine. But orageo:distance does not work at the moment...

    Regards,
    Michael
  • 8. Re: SEM_MATCH Support for Spatial Queries
    matperry Journeyer
    Currently Being Moderated
    Hi Michael,

    This is an unsupported use of orageo:distance. With the current Jena Adapter release, the spatial functions and other Oracle extension functions (e.g. orardf:textContains()) can only be used inside FILTERs. ARQ handles functions that appear in other parts of the query, and ARQ doesn't know how to process these spatial functions.

    As a workaround to project the distance value, you can create a user-defined PL/SQL function to use on projected values in Jena Adapter (see Section 7.7.3):
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sem_jena.htm#CBBBFHGJ

    You could then do a query as follows:

    PREFIX oext: <http://oracle.com/semtech/jena-adaptor/ext/function#>
    SELECT ?l (oext:my_distance("POINT(1.4839464963944773 103.7065268554687)"^^orageo:WKTLiteral, "POINT(1.2639464963944773 103.6065268554687)") as ?object1)
    WHERE { basekb_ns:budapest basekb_ns:formatlocation ?l . }

    The limitation here would be that the second argument must be a constant plain literal (less than 4000 chars) that you assume is a WKTLiteral. The first argument could be a variable.

    The my_distance function could be implemented as follows:

    SQL> create or replace function my_distance(
    rdfvtyp in varchar2,
    rdfltyp in varchar2,
    rdflang in varchar2,
    rdfclob in clob,
    value in varchar2,
    arg in varchar2
    ) return varchar2
    as
    ret_val varchar2(4000);
    geom1 mdsys.sdo_geometry;
    geom2 mdsys.sdo_geometry;
    srid number := 8307;
    tolerance number := 1.0;
    units varchar2(100) := 'unit=KM';
    begin

    geom1 := sem_apis.getV$GeometryVal('LIT', value, '', 'http://xmlns.oracle.com/rdf/geo/WKTLiteral', null, null, srid);
    geom2 := sem_apis.getV$GeometryVal('LIT', arg, '', 'http://xmlns.oracle.com/rdf/geo/WKTLiteral', null, null, srid);

    ret_val := to_char(sdo_geom.sdo_distance(geom1, geom2, tolerance, units));

    return ret_val;

    end;
    /
    2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
    Function created.

    SQL> select my_distance(null,null,null,null,'POINT(1.4839464963944773 103.7065268554687)','POINT(1.2639464963944773 103.6065268554687)') from dual;

    MY_DISTANCE(NULL,NULL,NULL,NULL,'POINT(1.4839464963944773103.7065268554687)','PO
    --------------------------------------------------------------------------------
    12.5801729730273

    1 row selected.

    Hope this helps,
    Matt
  • 9. Re: SEM_MATCH Support for Spatial Queries
    user461029 Newbie
    Currently Being Moderated
    Hi Matt,

    thanks, your answers helped me very much.

    As you have pointed out before, I have run into performance issues. I have only 15million triples in my semantic model.
    Basically, my queries return all locations that are within 50km from a given location:

    SELECT ?city
    WHERE {
    # HINT0={LEADING(?x ?l ?la ?g ?city)}
    basekb_ns:m.02q_q0t basekb_ns:m.01sx ?x.
    ?x basekb_ns:formatlocation ?l.
    ?city basekb_ns:m.01sx ?g.
    ?city rdf:type basekb_ns:m.01m9.
    ?g basekb_ns:formatlocation ?la.
    FILTER (orageo:withinDistance(?la,?l,50, "KM"))
    FILTER(?? != ?city)
    }


    I have tried different variables orders and added semantic network indices. Can you give me a hint on tuning the spatial index?

    Regards,
    Michael
  • 10. Re: SEM_MATCH Support for Spatial Queries
    matperry Journeyer
    Currently Being Moderated
    Hi Michael,

    Probably what's going on here is that the query optimizer doesn't realize that ?l only binds to a single geometry, so it's doing a cartesian join of the two graph patterns and then filtering based on withinDistance.

    If possible, it would be best to do two queries.

    1) get the geometry literal for basekb_ns:m.02q_q0t

    SELECT ?x ?l
    WHERE {
    basekb_ns:m.02q_q0t basekb_ns:m.01sx ?x.
    ?x basekb_ns:formatlocation ?l.
    }

    2) do a within distance query with this geometry literal as the second argument to withinDistance.

    SELECT ?city
    WHERE {
    # HINT0={LEADING(?la)}
    ?city basekb_ns:m.01sx ?g.
    ?city rdf:type basekb_ns:m.01m9.
    ?g basekb_ns:formatlocation ?la.
    FILTER (orageo:withinDistance(?la,"geometry from Q1"^^orageo:WKTLiteral,50, "KM"))
    FILTER(?? != ?city)
    }

    This way the query optimizer should be able to use the Spatial Index.

    Note that Jena Adapter uses a different method for specifying query optimizer hints:
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sem_jena.htm#sthref358

    Also, it's more efficient to use !sameTerm(?x,?y) instead of ?x != ?y.

    If the query optimizer still doesn't use the spatial index, we can manually alter the statistics for the spatial index:

    alter session set current_schema=MDSYS;

    DISASSOCIATE STATISTICS FROM PACKAGES sdo_3gl FORCE;
    DISASSOCIATE STATISTICS FROM PACKAGES prvt_idx FORCE;


    ASSOCIATE STATISTICS WITH PACKAGES sdo_3gl DEFAULT SELECTIVITY 0.001;
    ASSOCIATE STATISTICS WITH PACKAGES prvt_idx DEFAULT SELECTIVITY 0.001;

    An even smaller selectivity value can be used if necessary.

    Hope this helps,
    Matt

Legend

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