Joining two Tables with Ontop

[David Großmann]

Hi at all,

I try to join two tables via ontop and grab some information using the following sparql query:

PREFIX : <http://www.semanticweb.org/grossmann/ontologies/kis-ontology#>

SELECT  * {

  ?code a :ICD10GM_Element ; :meddok_DOKUTYPEN ?typ ; :meddok_EPSNR ?epi; :meddok_CODE_GES ?c ; :eps_AUFNR ?auf ; ?EPSNR ?e. ?code a :Aufnahmediagnose .

}

LIMIT 50

The data in my database (the language is german) is in two tables, first one is P_PVMDA_MEDDOK_AUFENTH aka meddok and second one is P_PVEPS_EPISODE aka eps. Both tables have a PPVEPS_EPSNR, so I created three data properties EPSNR with two sup properties (eps_EPSNR, meddok_EPSNR) (see epsnrONTOP-Screenshot)

 

My mappings are 

ICD10GM_Element

<:kis/P_PVMDA_MEDDOK_AUFENTH/{PPVMDA_DOKU_NR}> a :ICD10GM_Element ; :meddok_CODE_GES {PSTDCO_CODE_GES} ; :meddok_BEHNR {PPVBZT_BEHNR} ; :meddok_DOKUTYPEN {PSTDTY_DOKUTYP} ; :meddok_WERK_GES {PSTDWE_WERK_GES} ; :meddok_EPSNR {PPVEPS_EPSNR} . 

select * from kis.P_PVMDA_MEDDOK_AUFENTH where ALLMND_MANDANT = 'BWZK'

Fall

<:kis/P_PVEPS_EPISODE/{PPVEPS_EPSNR}> a :Fall ; :eps_AKTBEHNR {PPVEPS_AKTBEHNR} ; :eps_ARCHIV_KZN {PPVEPS_ARCHIV_KZN} ; :eps_AUFNR {PPVEPS_AUFNR} ; :eps_AUFNR_MC {PPVEPS_AUFNR_MC} ; :eps_DATBIS {PPVEPS_DATBIS}^^xsd:int ; :eps_DATVON {PPVEPS_DATVON}^^xsd:int ; :eps_EPSNR {PPVEPS_EPSNR} ; :eps_EPSSTAT {PPVEPS_EPSSTAT} ; :eps_FREIGABE_KZN {PPVEPS_FREIGABE_KZN} ; :eps_MEDFREIGABE {PPVEPS_MEDFREIGABE}^^xsd:int ; :eps_MEDFREIGABE_DATUM {PPVEPS_MEDFREIGABE_DATUM}^^xsd:dateTime ; :eps_PATNR {PPVPAT_PATNR} ; :eps_ZEITBIS {PPVEPS_ZEITBIS}^^xsd:int ; :eps_ZEITVON {PPVEPS_ZEITVON}^^xsd:int . 

select * from kis.P_PVEPS_EPISODE

But my query results are empty, so I'm missing a crucial part. What am I doing wrong? Do I need to use the three data properties or are even two enough?

[David Großmann]

I forgot to say, thanks for your help.

[tir]

Hi David,

you are performing a SPARQL join over the ?code variable, and such a join is non-empty *only if* the URIs being joined coincide (nothing different from a SQL join).

By looking at your mappings, that is, assuming that property :eps_AUFNR is not defined elsewhere, such a condition can never be satisfied:

URIs coming from ICD10GM_Element will always start with :kis/P_PVMDA_MEDDOK_AUFENTH/, whereas those coming from Fall will always start with :kis/P_PVEPS_EPISODE/.

If I got your question correctly, what you want to do instead is to perform a join over the "PPVEPS_EPSNR" value. Therefore, do a join over that value instead, e.g.

SELECT * WHRE {
?code a :ICD10GM_Element ;
        :meddok_EPSNR ?epi;
?fall a :Fall ;
      :eps_EPSNR ?epi ;
      :eps_AUFNR ?auf .
}

Note that I have omitted a portion of your SPARQL query (e.g., you did not provide the mapping for :Aufnahmediagnose).

Best, Davide.

--
Please follow our guidelines on how to report a bug https://ontop-vkg.org/community/contributing/bug-report
---
You received this message because you are subscribed to the Google Groups "ontop4obda" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ontop4obda+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ontop4obda/f665c328-e948-4266-9718-9c64d09705f0n%40googlegroups.com.

[tir]

There were some typos in my previous answer, the query should be:

SELECT * WHERE {
?code a :ICD10GM_Element ;
      :meddok_EPSNR ?epi .

?fall a :Fall ;
      :eps_EPSNR ?epi ;
      :eps_AUFNR ?auf .
}

Best.

[David Großmann]

Hello,

thank you very much that worked perfectly fine. 

Do I have to map all my values because of OWL 2 QL? So if I want the ontolgy to understand my C10.0 has a meddok_CODE_GES = "C10.0" I have to map the c10.0?

And what about subclasses? My C10.0 and many others is a subclass of :KrebsregisterDiagnoseRLP, do I have to map this relationship too?

[David Großmann]

I just ask because I want to map more than 1000 items and I wonder if it's going to cost a lot of time to run a query.

[Benjamin Cogrel]

Hi David,

Axioms like "C10.0 subClassOf meddok_CODE_GEST only {"C10.0"}" are outside OWL2QL and are therefore ignored by Ontop. However, axioms like "C10.0 subClassOf C10" are supported.

If you want to say that all instances of the class C10.0 have a property meddok_CODE_GEST and "C10.0" as literal value, then you need to write at least one mapping entry for that.

You don't need to write any additional mapping for making sure that instances of C10.0 appears as instances of C10 in the RDF graph. Ontop infers it automatically.

As for performance, I don't really see what could cause an issue.

Best,

Benjamin

--

Please follow our guidelines on how to report a bug https://ontop-vkg.org/community/contributing/bug-report
---
You received this message because you are subscribed to the Google Groups "ontop4obda" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ontop4obda+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/ontop4obda/4c28f4c7-43b2-4327-8a80-ab8054188882n%40googlegroups.com.