contsruct query question for sheets

42 views
Skip to first unread message

Bohms, H.M. (Michel)

unread,
Nov 17, 2019, 8:31:15 AM11/17/19
to topbrai...@googlegroups.com

 

PREFIX ss:  <http://www.topbraidcomposer.org/owl/2006/08/spreadsheets.owl#>

PREFIX bsc: <https://w3id.org/def/basicsemantics-owl-complex#>

CONSTRUCT {

?uri a bsc:PropertyDef .

}

WHERE {

    ?namecell a ss:Cell .

    ?namecell ss:row 0 .

    ?namecell ss:cellContents ?name .

BIND (iri(concat("http://www.topbraidcomposer.org/owl/2006/08/spreadsheets.owl#",?name)) AS ?uri )

}

 

Works fine for getting more semantic data after xlsx import like:

But if the second and third row are special too like giving (optionally) datatype and unit) how can I best combine?

 

Like to have forinstance as result:  ss:shear_modulus bsc:hasUnit Pa

 

Is there some smart way to get to the value of row2/3 or can I make a next query that is sequential to this one?

 

Thx for advice, Michel

 

 

 

 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

T +31888663107
M +31630381220
E michel...@tno.nl

Location

 

This message may contain information that is not intended for you. If you are not the addressee or if this message was sent to you by mistake, you are requested to inform the sender and delete the message. TNO accepts no liability for the content of this e-mail, for the manner in which you use it and for damage of any kind resulting from the risks inherent to the electronic transmission of messages.

 

 

 

 

Bohms, H.M. (Michel)

unread,
Nov 18, 2019, 3:35:12 AM11/18/19
to topbrai...@googlegroups.com

 

More in general: if there are more patterns around for getting the cell contents in some semantics properties for rows/datapoints...that would be very welcome!

 

First I thought it would be easy but I am struggling a bit extracting via sparql constructs the row data becoming different instances involving “row-1 definitions” just knowing the row and column of a specific cell. Guess I just miss a general pattern walking the rows (as instances f datapoints)  and next the columns (as triples for those instances referencing the first row) ....

 

Thx Michel

--
You received this message because you are subscribed to the Google Groups "TopBraid Suite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to topbraid-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/0f3fd26f32ce47ba85ffd41f0abdf41c%40tno.nl.

Irene Polikoff

unread,
Nov 18, 2019, 11:27:50 PM11/18/19
to topbrai...@googlegroups.com
Michel,

General pattern for this is to use SPARQL.

It is hard to reply more specifically without looking at your spreadsheet and I do not have time right now to loo at it.

If you are processing a cell with row X, column Y and your row 1 contains names of the properties that you will use to create the URIs of the properties, then you can always do

?dataCell ss:row ?x
?dataCell ss :column ?y.
?dataCell ss:cellContents ?dataValue
?propertyCell ss:row 1.
?propertyCell ss :column ?y.
?propertyCell ss:cellContents ?propertyValue
FILTER (?x > 1)

Further, if your data is tabular and the first row represents properties, you can just double click on the spreadsheet to open. And avoid going through this type of import. 

There are different importers for spreadsheets. This white paper may be of use https://www.topquadrant.com/download/using-topbraid-to-work-with-spreadsheet-data/?wpdmdl=15930&refresh=5dd36e53b83261574137427, although it is a bit outdated. 

On Nov 18, 2019, at 3:35 AM, 'Bohms, H.M. (Michel)' via TopBraid Suite Users <topbrai...@googlegroups.com> wrote:

 
More in general: if there are more patterns around for getting the cell contents in some semantics properties for rows/datapoints...that would be very welcome!
 
First I thought it would be easy but I am struggling a bit extracting via sparql constructs the row data becoming different instances involving “row-1 definitions” just knowing the row and column of a specific cell. Guess I just miss a general pattern walking the rows (as instances f datapoints)  and next the columns (as triples for those instances referencing the first row) ....
 
Thx Michel
 
Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

This message may contain information that is not intended for you. If you are not the addressee or if this message was sent to you by mistake, you are requested to inform the sender and delete the message. TNO accepts no liability for the content of this e-mail, for the manner in which you use it and for damage of any kind resulting from the risks inherent to the electronic transmission of messages.
 
Van: 'Bohms, H.M. (Michel)' via TopBraid Suite Users <topbrai...@googlegroups.com> 
Verzonden: Sunday, November 17, 2019 2:31 PM
Aan: topbrai...@googlegroups.com
Onderwerp: [topbraid-users] contsruct query question for sheets
 
 
CONSTRUCT {
?uri a bsc:PropertyDef .
}
WHERE {
    ?namecell a ss:Cell .
    ?namecell ss:row 0 .
    ?namecell ss:cellContents ?name .
BIND (iri(concat("http://www.topbraidcomposer.org/owl/2006/08/spreadsheets.owl#",?name)) AS ?uri )
}
 
Works fine for getting more semantic data after xlsx import like:
<image002.jpg>
But if the second and third row are special too like giving (optionally) datatype and unit) how can I best combine?
 
Like to have forinstance as result:  ss:shear_modulus bsc:hasUnit Pa
 
Is there some smart way to get to the value of row2/3 or can I make a next query that is sequential to this one?
 
Thx for advice, Michel
 
 
 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

This message may contain information that is not intended for you. If you are not the addressee or if this message was sent to you by mistake, you are requested to inform the sender and delete the message. TNO accepts no liability for the content of this e-mail, for the manner in which you use it and for damage of any kind resulting from the risks inherent to the electronic transmission of messages.
 
 
-- 
You received this message because you are subscribed to the Google Groups "TopBraid Suite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to topbraid-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/0f3fd26f32ce47ba85ffd41f0abdf41c%40tno.nl.

-- 
You received this message because you are subscribed to the Google Groups "TopBraid Suite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to topbraid-user...@googlegroups.com.

Bohms, H.M. (Michel)

unread,
Nov 19, 2019, 3:12:52 AM11/19/19
to topbrai...@googlegroups.com, Bektas, K.E. (Esra)

Hi Irene, see after >

 

Michel,

 

General pattern for this is to use SPARQL.

 

It is hard to reply more specifically without looking at your spreadsheet and I do not have time right now to loo at it.

 

> I have a simple sheet with 3 special rows on top:

> 1. attribuut-name

> 2. optional unit

> 3. optional datatype

 

If you are processing a cell with row X, column Y and your row 1 contains names of the properties that you will use to create the URIs of the properties, then you can always do

 

?dataCell ss:row ?x

?dataCell ss :column ?y.

?dataCell ss:cellContents ?dataValue

?propertyCell ss:row 1.

?propertyCell ss :column ?y.

?propertyCell ss:cellContents ?propertyValue

FILTER (?x > 1)

 

> ok!, guess my pattern would be like:

 

?dataCell ss:row ?x

?dataCell ss :column ?y.

?dataCell ss:cellContents ?dataValue

?propertyCell ss:row 1.

?propertyCell ss :column ?y.

?propertyCell ss:cellContents ?propertyValue

?unitCell ss:row 1.

?unitCell ss :column ?y.

?unitCell ss:cellContents ?unitValue

?datatypeCell ss:row 1.

?datatypeCell ss :column ?y.

?datatypeCell ss:cellContents ?datatypeValue

 

FILTER (?x > 3)

 

 

Further, if your data is tabular and the first row represents properties, you can just double click on the spreadsheet to open. And avoid going through this type of import. 

 

> I actually found the semantic table option but indeed sparql might be more flexible to cope with the special rows (and with alternative ways of instantiating involving “complex properties”).

 

There are different importers for spreadsheets. This white paper may be of use https://www.topquadrant.com/download/using-topbraid-to-work-with-spreadsheet-data/?wpdmdl=15930&refresh=5dd36e53b83261574137427, although it is a bit outdated. 

 

> I guess your (total) query above is somehow part of the semantic table import under the hood? Is it somewhere to be found explicitly/complete? Despite your hints I struggle with the actual construct part (instantiation of the row-instances).

 

> Thanks a lot, also for your advice so far! Michel

Richard Cyganiak

unread,
Nov 19, 2019, 4:11:16 AM11/19/19
to topbraid-users list, Bektas, K.E. (Esra)

On 19 Nov 2019, at 08:12, 'Bohms, H.M. (Michel)' via TopBraid Suite Users <topbrai...@googlegroups.com> wrote:

?dataCell ss:row ?x
?dataCell ss :column ?y.
?dataCell ss:cellContents ?dataValue
?propertyCell ss:row 1.
?propertyCell ss :column ?y.
?propertyCell ss:cellContents ?propertyValue
?unitCell ss:row 1.
?unitCell ss :column ?y.
?unitCell ss:cellContents ?unitValue
?datatypeCell ss:row 1.
?datatypeCell ss :column ?y.
?datatypeCell ss:cellContents ?datatypeValue
 
FILTER (?x > 3)

This looks good, except you need 1/2/3 for the three ss:row patterns instead of the 1/1/1 you have there.

Despite your hints I struggle with the actual construct part (instantiation of the row-instances).
 
Try doing a simple SELECT first, with this graph pattern instead of the ... part:

    SELECT ?x ?propertyValue ?dataValue ?unitValue ?datatypeValue
    WHERE {
        ...
    }

Each result row should now have all the ingredients that you need to construct one triple.

The next step is to turn it into a CONSTRUCT:

    CONSTRUCT {
        ?s ?p ?o
    }
    WHERE {
        ...
        BIND (subjectExpr AS ?s)
        BIND (predicateExpr AS ?p)
        BIND (objectExpr AS ?o)
    }

But the three xxxExpr parts still need to be replaced with real expressions that create the appropriate RDF nodes. Something like:

    subjectExpr: IRI(CONCAT(STR(<http://example.com/item/>, STR(?x)))
    predicateExpr: IRI(CONCAT(STR(<http://example.com/property/>, STR(?propertyValue)))
    objectExpr: STRDT(?dataValue, xsd:string)

These should work as a starting point, but the real expressions are probably going to be more complicated than that. The objectExpr will need to do something appropriate with ?unitValue and ?datatypeValue.

If the expressions become too complicated, it might be best to turn the expressions into SPIN functions. That way, the complexity is contained in one place, and the SPIN function can be re-used in other queries.

If extra triples are needed in the output, like an rdf:type triple for each subject, then it's probably easiest to make a separate query that produces only those extra triples.

Hope that helps,
Richard

Bohms, H.M. (Michel)

unread,
Nov 19, 2019, 4:28:24 AM11/19/19
to topbrai...@googlegroups.com, Bektas, K.E. (Esra)

Thx Richard

We will experiment!

Greetings Michel

 

 

 

 

 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

T +31888663107
M +31630381220
E michel...@tno.nl

Location

 

This message may contain information that is not intended for you. If you are not the addressee or if this message was sent to you by mistake, you are requested to inform the sender and delete the message. TNO accepts no liability for the content of this e-mail, for the manner in which you use it and for damage of any kind resulting from the risks inherent to the electronic transmission of messages.

--

You received this message because you are subscribed to the Google Groups "TopBraid Suite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to topbraid-user...@googlegroups.com.

Bohms, H.M. (Michel)

unread,
Nov 20, 2019, 8:34:57 AM11/20/19
to topbrai...@googlegroups.com

Hmmmm

 

 

PREFIX ss:  <http://www.topbraidcomposer.org/owl/2006/08/spreadsheets.owl#>

PREFIX bsc: <https://w3id.org/def/basicsemantics-owl-complex#>

CONSTRUCT {

?uri a bsc:PropertyDef .

}

WHERE {

?dataCell ss:row ?x .

?dataCell ss:column ?y .

?dataCell ss:cellContents ?dataValue .

?propertyCell ss:row 0 .

?propertyCell ss:column ?y .

?propertyCell ss:cellContents ?property .

?unitCell ss:row 1 .

?unitCell ss:column ?y .

OPTIONAL { ?unitCell ss:cellContents ?unit }

?datatypeCell ss:row 2 .

?datatypeCell ss:column ?y .

OPTIONAL {?datatypeCell ss:cellContents ?datatype }

 

BIND (iri(concat("http://www.topbraidcomposer.org/owl/2006/08/spreadsheets.owl#",?property)) AS ?uri )

FILTER ( ?x > 2)}

 

Gives me only instances for property names (first row, row 0) for which there are units defined in second row 1.

I thought that putting optional there would give them all.....

Adding OPTIONAL also for the general ?dataCell ss:cellContents ?dataValue . does not help

 

Where is my thinking wrong?

 

Thx Michel

 

 

 

 

 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

T +31888663107
M +31630381220
E michel...@tno.nl

Location

 

This message may contain information that is not intended for you. If you are not the addressee or if this message was sent to you by mistake, you are requested to inform the sender and delete the message. TNO accepts no liability for the content of this e-mail, for the manner in which you use it and for damage of any kind resulting from the risks inherent to the electronic transmission of messages.

 

 

 

 


Verzonden: Tuesday, November 19, 2019 10:11 AM
Aan: topbraid-users list <topbrai...@googlegroups.com>
CC: Bektas, K.E. (Esra) <esra....@tno.nl>

--

You received this message because you are subscribed to the Google Groups "TopBraid Suite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to topbraid-user...@googlegroups.com.

Richard Cyganiak

unread,
Nov 20, 2019, 8:46:12 AM11/20/19
to topbraid-users list
I think the OPTIONAL needs to surround all three lines:

OPTIONAL {
    ?unitCell ss:row 1 .
    ?unitCell ss:column ?y .
    ?unitCell ss:cellContents ?unit
}

and same for the datatype.

Richard


Bohms, H.M. (Michel)

unread,
Nov 20, 2019, 8:56:35 AM11/20/19
to topbrai...@googlegroups.com

 

That’s it 😊 thx.

Still trying to understand why...because they are “bound together”?

 

 

 

 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

T +31888663107
M +31630381220
E michel...@tno.nl

Location

 

Richard Cyganiak

unread,
Nov 20, 2019, 9:06:30 AM11/20/19
to topbraid-users list

On 20 Nov 2019, at 13:56, 'Bohms, H.M. (Michel)' via TopBraid Suite Users <topbrai...@googlegroups.com> wrote:

That’s it 😊 thx.
Still trying to understand why...because they are “bound together”?

If a cell is empty, no triples are produced at all for the cell: neither ss:row nor ss:column nor ss:cellContents.

Your version would work if only the ss:cellContents triple was missing for such cells.

But because all three are missing, they all three need to be marked as optional.

This will match all persons that have a first name and last name, but skip any that have no first name or no first name or neither:

    ?person a :Person.
    ?person :firstName ?fn.
    ?person :lastName ?ln.

This will match all persons that have a last name. If the person has a first name, that name will also be returned. Persons without last name will be skipped:

    ?person a :Person.
    OPTIONAL { ?person :firstName ?fn. }
    ?person :lastName ?ln.

This will match all persons. If the person has a first name, then the first name will also be returned. If the person has a last name, that will also be returned.

    ?person a :Person.
    OPTIONAL { ?person :firstName ?fn. }
    OPTIONAL { ?person :lastName ?ln. }

This will match all persons. If the person has a first *and* last name, then *both* names will be returned. If the person has only a first name, or only a last name, then no name will be returned:

    ?person a :Person.
    OPTIONAL {
        ?person :firstName ?fn.
        ?person :lastName ?ln.
    }

Richard




Irene Polikoff

unread,
Nov 20, 2019, 5:35:00 PM11/20/19
to topbrai...@googlegroups.com
Because if row 1/column y cell is empty, you do not have triples that match this graph pattern:

?unitCell ss:row 1 .
?unitCell ss:column ?y .

You have multiple triples that match

?x ss:row 1 pattern

And you have multiple triples that match

?x ss:column ?y  pattern

You do not have any triples that match both.


If you think of your data as a graph and understand query as a graph pattern matching process, this becomes quite clear.  It helps to draw out the data in a graph.

The question goes back to understanding fundamentals of RDF graph data model. It is hard (and I would not advise) to move to more complex topics of OWL and SHACL (or even RDFS) until RDF is fully understood.

In trainings, I typically spend significant time just on RDF, then followed by SPARQL - because one can’t do SPARQL until they understand RDF. Conversely, if they do understand RDF, SPARQL should be fairly easy.

Only after that, it is possible to successfully move on and master other topics.


On Nov 20, 2019, at 8:56 AM, 'Bohms, H.M. (Michel)' via TopBraid Suite Users <topbrai...@googlegroups.com> wrote:

 
That’s it 😊 thx.
Still trying to understand why...because they are “bound together”?
 
Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

Bohms, H.M. (Michel)

unread,
Nov 21, 2019, 2:45:17 AM11/21/19
to topbrai...@googlegroups.com

Hi Irene, Richard

 

Thx for the explanations!

 

I had not realised that with the sheet-import empty cells were not generated at all!

 

Greetings Michel

 

 

 

 

 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

T +31888663107
M +31630381220
E michel...@tno.nl

Location

 

Irene Polikoff

unread,
Nov 21, 2019, 8:13:43 AM11/21/19
to topbrai...@googlegroups.com
You can always import data and look at it as you develop queries.

The pattern Richard recommended - starting with SELECT is almost always the way to go. Then, if a query does not return what you expect if you can’t quite understand why by looking at data, simplify it systematically by removing one triple match at a time until the issue becomes clear.



On Nov 21, 2019, at 2:45 AM, 'Bohms, H.M. (Michel)' via TopBraid Suite Users <topbrai...@googlegroups.com> wrote:

Hi Irene, Richard

 

Thx for the explanations!

 

I had not realised that with the sheet-import empty cells were not generated at all!

 

Greetings Michel

 

 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

T +31888663107
M +31630381220
E michel...@tno.nl

Location

 

Bohms, H.M. (Michel)

unread,
Nov 21, 2019, 8:41:42 AM11/21/19
to topbrai...@googlegroups.com

 

That is exactly what I now do, by select variants making sure that I can get all the data

Now I start systematically constructing new triples, involving constructed URIs, based on it.

 

thx

 

 

 

 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

T +31888663107
M +31630381220
E michel...@tno.nl

Location

 

Bohms, H.M. (Michel)

unread,
Nov 22, 2019, 10:30:02 AM11/22/19
to topbrai...@googlegroups.com

Hi Richard,

 

Suppose I want to generate for each sheet cell found (?dataCell binding) a unique uri.

How would I do that? Kind of spif:buildURI?

 

Clearly the red is not the way, no instances of bsc:Property are generated:

Guess I need some function in between for ?dataCell ?

?uri1 a bsc:PropertyDef .

?uri2 a bsc:Property}

WHERE {

?dataCell ss:row ?x .

?dataCell ss:column ?y .

?dataCell ss:cellContents ?dataValue .

?propertyCell ss:row 0 .

?propertyCell ss:column ?y .

?propertyCell ss:cellContents ?property .

OPTIONAL { ?unitCell ss:row 1 .

?unitCell ss:column ?y .

?unitCell ss:cellContents ?unit . }

OPTIONAL { ?datatypeCell ss:row 2 .

?datatypeCell ss:column ?y .

?datatypeCell ss:cellContents ?datatype }

BIND (iri(concat("http://www.topbraidcomposer.org/owl/2006/08/spreadsheets.owl#",?property)) AS ?uri1 ) .

BIND (iri(concat("http://www.topbraidcomposer.org/owl/2006/08/spreadsheets.owl#",?dataCell)) AS ?uri2 )

FILTER ( ?x > 2)}

 

Thx for the tip; guess the last tip I need to make my further mapping complete...

michel

 

 

 

Dr. ir. H.M. (Michel) Böhms
Senior Data Scientist

T +31888663107
M +31630381220
E michel...@tno.nl

Location

 

cid:image001.gif@01D5A151.BADA4FB0

Tim Smith

unread,
Nov 22, 2019, 11:06:51 AM11/22/19
to topbrai...@googlegroups.com
In your example, ?datacell is already a bnode or URI.  To use concat(), you would need to convert ?datacell to a string representation.

To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/3722a8f1b89b447f95902aff5110b6a0%40tno.nl.

Irene Polikoff

unread,
Nov 22, 2019, 11:33:22 AM11/22/19
to topbrai...@googlegroups.com
Correct.

In RDF, only URI or blank nodes can be subjects of triples.

If you have something like

?dataCell ss:cellContents ?dataValue .



It means that ?dataCell is either a URI or a blank node. There is no need to see the data to know this.

I am pretty sure it is a URI. Since Michel has the data, he could check and see what it is.

One could convert ?dataCell into a string and then use that string to create a new URI. In the context of the BIND statement in this query, however, it does not make a sense.

What may make sense is generating a new URI in a different namespace or of a different form. Then converting ?dataCell to a string may be useful or may be not. It would only be useful if one wanted to use parts of the ?dataCell to create a new URI.

If there is a new URI, then any triples that were referring to ?dataCell uris  would need to be copied to refer to the new URI - that is if one wanted to use them with the new URI.

Reply all
Reply to author
Forward
0 new messages