[protege-discussion] populate my individuals from database : protege-discussion Digest, Vol 76, Issue 26

[Timothy Redmond]

On 11/23/2012 04:45 AM, alaa hedib wrote:
> thanks Timothy and Sluka
> its really smart way , i already think in it , but to write it as java 
> code.
> please Sluka if you can send to my sample sheet from your work .
>

Writing some custom code to translate a database or a spreadsheet into 
OWL should not be too difficult.  To read the database you can use jdbc 
[1] and there are a couple of good options for reading excel 
spreadsheets in java (e.g. [2] but I believe apache has an option as 
well).  To write to the OWL ontology I would recommend the OWL api.  The 
functions shouldCreatePropertyAssertions and shouldCreateClassAssertions 
from [4] show how to add statements that individuals have a particular 
type and particular property values.

The hard work of course is making this more than a custom solution to 
work with one particular database or spreadsheet.  So you are forced to 
choose between

 1. writing and maintaining a graphical (or text based) program
    (possibly a Protege plugin) such as datamaster or mapping master or
 2. writing a one-off solution for one particular problem.

I have done the second option in the past but it must have been before 
mapping master came out because I hope that I would have used mapping 
master if it was there.  If you are considering the first option then I 
would think that exploring the Protege 3 solutions would be less work.

-Timothy

[1] http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
[2] http://jexcelapi.sourceforge.net/
[3] http://owlapi.sourceforge.net/
[4] http://owlapi.svn.sourceforge.net/viewvc/owlapi/v3/trunk/contract/src/test/java/org/coode/owlapi/examples/Examples.java?view=markup

>
>  Alaa
>
>
> --- On *Tue, 11/13/12, Timothy Redmond /<tredmond at stanford.edu>/*wrote:
>
>
>     From: Timothy Redmond <tredmond at stanford.edu>
>     Subject: Re: [protege-discussion] populate my individuals from
>     database : protege-discussion Digest, Vol 76, Issue 26
>     To: protege-discussion at lists.stanford.edu
>     Date: Tuesday, November 13, 2012, 12:23 PM
>
>
>     This is interesting and it is useful to know that you succeeded in
>     making this work.  Hacky but I like it.
>
>     There is one possible gotcha and if you know the right technique
>     it would be nice to know the workaround.  If some of the
>     characters are non-ascii then windows might use the wrong
>     character encoding on the paste.  We want it to use the UTF-8
>     encoding and windows tends to use some windows specific encoding.
>     Often this might not be a problem but occasionally people have had
>     trouble with the cut and paste.  If the basic text editor was
>     smart enough there is a chance that it could be part of the solution.
>
>     -Timothy.
>
>
>     On 11/13/12 12:10 PM, Sluka, James Patrick wrote:
>     >> can any one help me to find efficient  way to populate my ontology
>     >> individuals from database table or speared-sheet
>     >
>     > How often do you need to do this?
>     > --------------------------------
>     >
>     > IF this is a "one-of" process, that is you've got a big list of
>     individuals and you just need to import it one time, then you can
>     pretty much do it using Excel and a bit of cutting and pasting in
>     a text editor.
>     >
>     > I've had to do this a few months ago; take a basic structure in
>     Excel that has classes, subclasses, definitions, external links,
>     edit dates etc. and convert it into an OWL-2 ontology.
>     >
>     > To do it, I built a very simple OWL-2 in Protege 4 with the
>     basic types, qualities, links etc. and saved the ontology as RDF/XML.
>     >
>     > I then examined that file and extracted the syntax for, for
>     example, creating a class (subClass of xxxx, definition etc.)
>     >
>     > In Excel you can use a spreadsheet cell to create a class (or
>     individual) where the details for the class (individual) are
>     values of other columns that are inserted into the correct RDF/XML
>     syntax.
>     >
>     > To create many individuals it is likely that the syntax is
>     exactly the same for all of them, just the details (name etc.)
>     change. So the same Excel formula can be used for all the
>     individuals. Write the formula once then paste it down an entire
>     column.
>     >
>     > Here is the Excel formula I used to create classes, one class
>     per row, info for the class from several cells in the same Excel row;
>     > ="<owl:Class rdf:about=""&CBO_0_9;"&Classes!E6&""">
>     >   <rdfs:label>"&Classes!E6&"</rdfs:label>
>     >   <rdfs:subClassOf rdf:resource=""&CBO_0_9;"&Classes!F6&"""/>
>     >
>     <rdfs:comment>"&Classes!L6&"</rdfs:comment>"&C14&$C$1&B14&K14&L14&M14&"
>     > </owl:Class>"
>     >
>     > (In the above, Excel cell references were to another sheet in
>     the workbook named "Classes".)
>     >
>     > You can put <cr>'s into an Excel cell with <alt><enter> and
>     that's how I got the line breaks in the formula above. The formula
>     above gives a cell with the text contents of (again, this is for
>     one row of the Excel table and is creating an owl class for that row);
>     >
>     > "<owl:Class rdf:about=""&CBO_0_9;PhysicalEntityType"">
>     >   <rdfs:label>PhysicalEntityType</rdfs:label>
>     >   <rdfs:subClassOf rdf:resource=""&CBO_0_9;CBO_Object""/>
>     >   <rdfs:comment>A PhysicalEntityType is a CBO:Class
>     (BFO:snap:quality) class of physical objects. For example, a Cell,
>     a basement membrane of a diffusible Molecule.</rdfs:comment>
>     >   <dc:date rdf:datatype=""&xsd;date"">21 July 2011</dc:date>
>     >   <dc:creator>jps</dc:creator>
>     >   <rdfs:seeAlso>BFO:snap:quality</rdfs:seeAlso>
>     <rdfs:isDefinedBy>urn:miriam:dummy:1</rdfs:isDefinedBy>
>     <rdfs:isDefinedBy>urn:miriam:dummy:2</rdfs:isDefinedBy>
>     <rdfs:isDefinedBy>urn:miriam:dummy:3</rdfs:isDefinedBy>
>     > </owl:Class>"
>     >
>     > I then copied the column with the results from Excel into a
>     basic text editor and removed the extra quote marks. (Single " are
>     removed and "" is converted into ").
>     >
>     > I then pasted that big chunk of XML into the original template
>     OWL-2 file. If everything is done right that file can be opened in
>     Protege2 and will be a valid OWL2 ontology. It took a few tries to
>     get the syntax and quote marks correct so save your Excel
>     worksheet so you can go back and fix any problems with the formulas.
>     >
>     > This is a pretty crude hack but if you only need to do it once
>     (or perhaps only occasionally) it is probably easier than writing
>     a Protege plugin to import from an Excel file.
>     >
>     > Jim Sluka
>     > Indiana Univ.
>     >
>     >
>     >
>     > _______________________________________________
>     > protege-discussion mailing list
>     > protege-discussion at lists.stanford.edu
>     </mc/compose?to=protege-discussion at lists.stanford.edu>
>     > https://mailman.stanford.edu/mailman/listinfo/protege-discussion
>     >
>     > Instructions for unsubscribing:
>     http://protege.stanford.edu/doc/faq.html#01a.03
>
>     _______________________________________________
>     protege-discussion mailing list
>     protege-discussion at lists.stanford.edu
>     </mc/compose?to=protege-discussion at lists.stanford.edu>
>     https://mailman.stanford.edu/mailman/listinfo/protege-discussion
>
>     Instructions for unsubscribing:
>     http://protege.stanford.edu/doc/faq.html#01a.03
>
>
>
> _______________________________________________
> protege-discussion mailing list
> protege-discussion at lists.stanford.edu
> https://mailman.stanford.edu/mailman/listinfo/protege-discussion
>
> Instructions for unsubscribing: http://protege.stanford.edu/doc/faq.html#01a.03

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.stanford.edu/pipermail/protege-discussion/attachments/20121126/0803097a/attachment.html>