Translating Database Values to RDF Terms

Translating using R2RML view

One can utilize the power of R2RML views (allowed by our SQL-based approach) to specify the desired translation of database values (composed possibly by transforming, in SQL, values from one or more columns of one or more tables) to RDF terms. This technique supports: partial mapping, translating to any type of RDF term (including IRIs and literals), many-to-one mapping (and even many-to-many mapping if the need arises).

Example 1: TriplesMap involving partial-mapping translation of combined values of two encoded columns, MAKE (numeric code) and MODEL (3-letter code), in table SCOTT.CARTABLE to more meaningful literals:

x:CarTriplesMap
 rr:logicalTable [ rr:sqlQuery "
   select t.*, 
   (CASE t.make || '.' || t.model 
     when '1.MUS' then 'FORD-MUSTANG'
     when '2.VOL' then 'CHEVROLET-VOLT'
     when '3.ODY' then 'HONDA-ODYSSEY'
     when '3.ACC' then 'HONDA-ACCORD'
     ELSE               (t.make || '.' || t.model)
   END) makemodel 
   from scott.carTable t
   " ] ;

 rr:subjectMap    [ rr:template "http://example.org/cars/LICENSEPLATE" ] ;

 rr:predicateObjectMap [ rr:predicate car:makeModel ;  rr:objectMap [ rr:column "MAKEMODEL" ] ] .

Note that if one wants error to be returned when no translation has been explicitly specified, then the ELSE part of the CASE statement above can be altered as follows (or any other variation of the same idea):

     ELSE               (1/0)

Example 2: TriplesMap involving partial-mapping translation of combined values of two columns, CITY (varchar2) and STATE (2-letter code), in table SCOTT.USA_CITIES to IRIs.

There are about 30,000 cities and we want only a few well-known cities to map to their special URLs as shown in the (CASE statement based) translation below. All other cities use the generic translated form: http://www.city.{CITY}.{STATE}.us.

x:CityStateTriplesMap
 rr:logicalTable  [ rr:sqlQuery "
   select t.*,
     (CASE 'http://www.city.' || utl_url.escape(CITY) || '.' || utl_url.escape(STATE) || '.us'
        when 'http://www.city.New%20York.NY.us'            then 'http://www.nyc.gov'
        when 'http://www.city.Boston.MA.us'                then 'http://www.cityofboston.gov'
        when 'http://www.city.Atlanta.GA.us'               then 'http://www.atlantaga.gov'
        when 'http://www.city.Miami.FL.us'                 then 'http://www.miamigov.com'
        when 'http://www.city.Dallas.TX.us'                then 'http://www.dallascityhall.com'
        when 'http://www.city.Los%20Angeles.CA.us'         then 'http://www.lacity.org'
        when 'http://www.city.San%20Francisco.CA.us'       then 'http://www.sfgov.org'
        when 'http://www.city.Seattle.WA.us'               then 'http://www.seattle.gov'
        when 'http://www.cityofchicago.org'                then 'http://www.seattle.gov'
        ELSE 'http://www.city.' || utl_url.escape(CITY) || '.' || utl_url.escape(STATE) || '.us'
      END) cityState
     from SCOTT.USA_CITIES t 
   " ];

 rr:subjectMap    [ rr:column "CITYSTATE" ] ;

 rr:predicateObjectMap [ rr:predicate cs:city ;        rr:objectMap [ rr:column "CITY" ] ] ;
 rr:predicateObjectMap [ rr:predicate cs:state ;       rr:objectMap [ rr:column "STATE" ] ] ;
 rr:predicateObjectMap [ rr:predicate cs:lat ;         rr:objectMap [ rr:column "LATITUDE" ] ] ;
 rr:predicateObjectMap [ rr:predicate cs:long ;        rr:objectMap [ rr:column "LONGITUDE" ] ] .

Note that if one wants error to be returned when no translation has been explicitly specified, then the ELSE part of the CASE statement above can be altered as follows (or any other variation of the same idea):

     ELSE               (1/0)

Translating using R2RML-native scheme (rr:translationMap, rr:fromTerm, rr:toTerm)

If R2RML view based approach to translation is deemed inadequate for some reason, the R2RML-native scheme illustrated below may be considered. This scheme does not suffer from any restrictions and results in compact specification of the translation information. It allows: partial mapping, mapping to any type of RDF term (IRI or literal), many-to-one mapping (may even allow many-to-many if the need arises).

Example 3: (Same scenario as Example 2 above) TriplesMap involving partial-mapping translation of combined values of two columns, CITY (varchar2) and STATE (2-letter code), in table SCOTT.USA_CITIES to IRIs.

There are about 30,000 cities and we want only a few well-known cities to map to their special URLs as shown in the (CASE statement based) translation below. All other cities use the generic translated form: http://www.city.{CITY}.{STATE}.us.

x:CityStateTriplesMap
 rr:logicalTable  [ rr:tableName "SCOTT.USA_CITIES" ] ;

 rr:subjectMap    [ rr:template "http://www.city.{CITY}.{STATE}.us" ; 
                    rr:translationScheme x:myTranslationScheme ] ;

 rr:predicateObjectMap [ rr:predicate cs:city ;  rr:objectMap [ rr:column "CITY" ] ] ;
 rr:predicateObjectMap [ rr:predicate cs:state ; rr:objectMap [ rr:column "STATE" ] ] ;
 rr:predicateObjectMap [ rr:predicate cs:lat ;   rr:objectMap [ rr:column "LATITUDE" ] ] ;
 rr:predicateObjectMap [ rr:predicate cs:long ;  rr:objectMap [ rr:column "LONGITUDE" ] ] ;

 x:myTranslationScheme rr:translationMap
  [ rr:toTerm <http://www.nyc.gov> ;            rr:fromTerm <http://www.city.New%20York.NY.us> ] ;
  [ rr:toTerm <http://www.cityofboston.gov> ;   rr:fromTerm <http://www.city.Boston.MA.us> ] ;
  [ rr:toTerm <http://www.atlantaga.gov> ;      rr:fromTerm <http://www.city.Atlanta.GA.us> ] ;
  [ rr:toTerm <http://www.miamigov.com> ;       rr:fromTerm <http://www.city.Miami.FL.us> ] ;
  [ rr:toTerm <http://www.dallascityhall.com> ; rr:fromTerm <http://www.city.Dallas.TX.us> ] ;
  [ rr:toTerm <http://www.lacity.org> ;         rr:fromTerm <http://www.city.Los%20Angeles.CA.us> ] ;
  [ rr:toTerm <http://www.sfgov.org> ;          rr:fromTerm <http://www.city.San%20Francisco.CA.us> ] ;
  [ rr:toTerm <http://www.seattle.gov> ;        rr:fromTerm <http://www.city.Seattle.WA.us> ] ;
  [ rr:toTerm <http://www.cityofchicago.org> ;  rr:fromTerm <http://www.city.Chicago.IL.us> ] .