Well, you are right, I'll get the desired output using your method but the
question arises then why don't/can't I use the MAX() function here to get the
same result.
Best,
Izhar
________________________________________
From: Lorenz B. [buehm...@informatik.uni-leipzig.de]
Sent: 15 March 2017 07:15
To: users@jena.apache.org
Subject: Re: Complex SPARQL Query
Sure, you don't need grouping here. You could just use ORDER BY
ASC(?nodecount) LIMIT 1, i.e. sorting in the other direction and just
take the first result.
> Hi Lorenz,
>
> Yes, that's true but I can't retrieve all features along with their number of
> sensing nodes. So, when I run one sub-query separately I get the correct
> number of nodes in that feature.
>
> I slightly changed my ontology and then ran the following query against my
> ontology file:
>
> SELECT ?feature (MAX(?nodecount) AS ?featuremaxnodes) WHERE {
>
> {
> SELECT ?feature (COUNT(?node) AS ?nodecount) WHERE {
>
> ?feature rdf:type enviot:Field ;
> ssn:hasDeployment ?node .
> ?node rdf:type enviot:SoilSensingNode .
>
> }
> GROUP BY ?feature
> ORDER BY DESC(?nodecount)
> }
> }
> GROUP BY ?feature
> ORDER BY DESC(?nodecount)
>
> The output of the above query is:
>
>
> feature featuremaxnodes
>
> 1 enviot:A_Hilltop "3"^^xsd:integer
>
> 2 enviot:A_Riverbank "5"^^xsd:integer
>
> 3 enviot:A_Swale "7"^^xsd:integer
>
> Now I am very close to my desired output but still not getting it i.e. I need
> to show the feature having maximum number of nodes. In other words, I need to
> show the following:
>
> feature featuremaxnodes
>
> enviot:A_Swale "7"^^xsd:integer
>
> Any idea how to get the above output?
>
> Many thanks for your time, help and patience.
>
> Best regards,
>
> Izhar
> ________________________________________
> From: Lorenz B. [buehm...@informatik.uni-leipzig.de]
> Sent: 14 March 2017 06:56
> To: users@jena.apache.org
> Subject: Re: Complex SPARQL Query
>
> Just to clarify, each of the sub-SELECT queries returns the number of
> nodes for the features Hilltop, Riverbank and Swale, right? And if you
> run such a SELECT query separately, the number is correct?
>> Dear Lorenz,
>>
>> Apologies for the belated reply, for I was travelling, arrived today.
>>
>> Let me post the whole question again below:
>>
>> I have a question related to SPARQL query which is a bit complex one. I
>> would appreciate if someone could help me out. I have 15 Sensing Nodes with
>> their corresponding Point(longitude latitude) geometries given below:
>>
>> Table 1: Showing results from 1 to 15 of 15. Query took 0.127 s.
>>
>> node gwkt
>>
>> enviot:A0_SoilSensingNode "POINT(-3.783065
>> 53.202158)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:A1_SoilSensingNode "POINT(-3.782898
>> 53.201828)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:A2_SoilSensingNode "POINT(-3.783092
>> 53.202007)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:A3_SoilSensingNode "POINT(-3.781964
>> 53.201765)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:A5_SoilSensingNode "POINT(-3.783055
>> 53.202777)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:A6_SoilSensingNode "POINT(-3.782222
>> 53.202777)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:A7_SoilSensingNode "POINT(-3.781111
>> 53.202777)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:A8_SoilSensingNode "POINT(-3.780833
>> 53.203055)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:A9_SoilSensingNode "POINT(-3.780833
>> 53.202222)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:AA_SoilSensingNode "POINT(-3.783063
>> 53.202309)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:AB_SoilSensingNode "POINT(-3.782638
>> 53.202189)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:AC_SoilSensingNode "POINT(-3.783003
>> 53.202191)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:AD_SoilSensingNode "POINT(-3.782518
>> 53.201841)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:AE_SoilSensingNode "POINT(-3.782024
>> 53.202193)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>> enviot:AF_SoilSensingNode "POINT(-3.782877
>> 53.201788)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>
>> These sensing nodes are deployed in a field having three features named
>> Hilltop (it has 3 nodes), Swale (it has 7 nodes) and Riverbank (it has 5
>> nodes). Each of these features has their own geometries like 'Line String',
>> 'Polygon' and 'Line String' respectively along with their geometric
>> coordinates given below:
>>
>> Table 2: Showing results from 1 to 3 of 3. Query took 0.081 s.
>>
>> feature fgeo
>> fwkt
>> enviot:A_Hilltop enviot:A_HillTopLineGeometry "LINESTRING
>> (-3.780833 53.203055, -3.781111 53.202777,
>>
>> -3.780833 53.202222)
>>
>> "^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>
>>
>> enviot:A_Riverbank enviot:A_RiverbankLineGeometry "LINESTRING (-3.783055
>> 53.202777, -3.783063 53.202309,
>>
>> -3.783065 53.202158, -3.783092 53.202007,
>>
>> -3.782898 53.201828)
>>
>> "^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>
>>
>> enviot:A_Swale enviot:A_SwalePolygonGeometry "POLYGON
>> ((-3.782222 53.202777, -3.782024 53.202193,
>>
>> -3.782638 53.202189, -3.783003 53.202191, -3.781964
>>
>> 53.201765, -3.782518 53.201841, -3.782877 53.201788,
>>
>> -3.782222 53.202777))
>>
>> "^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>
>>
>> NOTE: In the above table (Table 2), the column 'fwkt' has got exactly the
>> same geometry values (Point(long lat) which are the geometry values of
>> individual soil sensing nodes given in column named 'gwkt' in Table 1.
>>
>> Now my question is:
>>
>> Which geographic feature contains the maximum number of soil sensing nodes?
>>
>> The query/answer should retrieve envoi:A_Swale which has 7 soil sensing
>> nodes, which is the maximum.
>>
>> My current query is (indeed not complete and correct) given below:
>>
>> PREFIX geosparql: <http://www.opengis.net/ont/geosparql#>
>> PREFIX sf: <http://www.opengis.net/ont/sf#>
>> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
>> PREFIX enviot:
>> <http://www.environmental-iot.com/enviot_ontology/IotSemanticModel#>
>> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
>> PREFIX DUL: <http://www.loa-cnr.it/ontologies/DUL.owl#>
>> PREFIX ssn: <http://purl.oclc.org/NET/ssnx/ssn#>
>> PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
>>
>> SELECT (MAX(?nodecount) AS ?maxnodecount) WHERE {
>>
>> {
>> SELECT (COUNT(?node) AS ?nodecount) WHERE {
>> ?node rdf:type enviot:SoilSensingNode;
>> geosparql:hasGeometry ?geo .
>> ?geo geosparql:asWKT ?gwkt.
>>
>> FILTER (geof:sfTouches(?gwkt, "POLYGON((-3.782222 53.202777, -3.782024
>> 53.202193, -3.782638 53.202189, -3.783003 53.202191, -3.781964 53.201765,
>> -3.782518 53.201841, -3.782877 53.201788, -3.782222
>> 53.202777))"^^geosparql:wktLiteral))
>> }
>> }
>>
>> {
>> SELECT (COUNT(?node) AS ?nodecount) WHERE {
>> ?node rdf:type enviot:SoilSensingNode;
>> geosparql:hasGeometry ?geo .
>> ?geo geosparql:asWKT ?gwkt.
>>
>> FILTER (geof:sfIntersects(?gwkt, "LINESTRING(-3.783055 53.202777,
>> -3.783063 53.202309, -3.783065 53.202158, -3.783092 53.202007, -3.782898
>> 53.201828)"^^geosparql:wktLiteral))
>> }
>> }
>> {
>> SELECT (COUNT(?node) AS ?nodecount) WHERE {
>> ?node rdf:type enviot:SoilSensingNode;
>> geosparql:hasGeometry ?geo .
>> ?geo geosparql:asWKT ?gwkt.
>>
>> FILTER (geof:sfIntersects(?gwkt, "LINESTRING(-3.780833 53.203055,
>> -3.781111 53.202777, -3.780833 53.202222)"^^geosparql:wktLiteral))
>> }
>> }
>>
>> }
>>
>> If you make two of the sub-queries comments then it retrieves the correct
>> number of Soil Sending Nodes for the one which is filtered but it does not
>> retrieve the max of all these three features (i.e. Hilltop, Swale and
>> Riverbank) having soil sensing nodes which should be 7 in this case.
>>
>> If you need my ontology which contains the data I can send it to you but a)
>> kindly provide me your personal email address and b) it is highly
>> confidential, so, kindly do not disclose/share it to/with other people.
>>
>> Many thanks in advance.
>>
>> Best,
>>
>> Izhar
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> ________________________________________
>> From: Lorenz B. [buehm...@informatik.uni-leipzig.de]
>> Sent: 10 March 2017 07:53
>> To: users@jena.apache.org
>> Subject: Re: Complex SPARQL Query
>>
>> It's almost unreadable in the current formatting...
>>
>> At first, it would be good to see
>>
>> 1. the current SPARQL query
>> 2. minimal sample data
>> 3. the data that you expect to be returned
>>
>> I guess, the pseudo query is (please correct me if I'm wrong)
>>
>> "Which geographic feature contains the maximum number of soil sensing
>> nodes?"
>>
>> And you expect as result
>>
>> envoi:A_Swale
>>
>>
>> With the current formatting issues, it's pretty hard, because I don't
>> know the structure of the data. Once you provide this + the current
>> query, it's more or less using the COUNT aggregate function and probably
>> GROUP BY
>>
>>> Hi all,
>>>
>>>
>>> I have a question related to SPARQL query which is a bit complex one. I
>>> would appreciate if someone could help me out. I have developed an ontology
>>> in Protege in which I have imported the GeoSPARQL Ontology. I have 15
>>> Sensing Nodes with their corresponding Point(longitude latitude) geometries
>>> given below:
>>>
>>>
>>> Table 1: Showing results from 1 to 15 of 15. Query took 0.127 s.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> node
>>>
>>>
>>>
>>> geo
>>>
>>>
>>>
>>> gwkt
>>>
>>>
>>>
>>> 1
>>>
>>>
>>> enviot:A0_SoilSensingNode<http://localhost:7200/resource/enviot/A0_SoilSensingNode>
>>>
>>>
>>> enviot:A0_Geometry<http://localhost:7200/resource/enviot/A0_Geometry>
>>>
>>>
>>> "POINT(-3.783065
>>> 53.202158)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 2
>>>
>>>
>>> enviot:A1_SoilSensingNode<http://localhost:7200/resource/enviot/A1_SoilSensingNode>
>>>
>>>
>>> enviot:A1_Geometry<http://localhost:7200/resource/enviot/A1_Geometry>
>>>
>>>
>>> "POINT(-3.782898
>>> 53.201828)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 3
>>>
>>>
>>> enviot:A2_SoilSensingNode<http://localhost:7200/resource/enviot/A2_SoilSensingNode>
>>>
>>>
>>> enviot:A2_Geometry<http://localhost:7200/resource/enviot/A2_Geometry>
>>>
>>>
>>> "POINT(-3.783092
>>> 53.202007)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 4
>>>
>>>
>>> enviot:A3_SoilSensingNode<http://localhost:7200/resource/enviot/A3_SoilSensingNode>
>>>
>>>
>>> enviot:A3_Geometry<http://localhost:7200/resource/enviot/A3_Geometry>
>>>
>>>
>>> "POINT(-3.781964
>>> 53.201765)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 5
>>>
>>>
>>> enviot:A5_SoilSensingNode<http://localhost:7200/resource/enviot/A5_SoilSensingNode>
>>>
>>>
>>> enviot:A5_Geometry<http://localhost:7200/resource/enviot/A5_Geometry>
>>>
>>>
>>> "POINT(-3.783055
>>> 53.202777)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 6
>>>
>>>
>>> enviot:A6_SoilSensingNode<http://localhost:7200/resource/enviot/A6_SoilSensingNode>
>>>
>>>
>>> enviot:A6_Geometry<http://localhost:7200/resource/enviot/A6_Geometry>
>>>
>>>
>>> "POINT(-3.782222
>>> 53.202777)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 7
>>>
>>>
>>> enviot:A7_SoilSensingNode<http://localhost:7200/resource/enviot/A7_SoilSensingNode>
>>>
>>>
>>> enviot:A7_Geometry<http://localhost:7200/resource/enviot/A7_Geometry>
>>>
>>>
>>> "POINT(-3.781111
>>> 53.202777)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 8
>>>
>>>
>>> enviot:A8_SoilSensingNode<http://localhost:7200/resource/enviot/A8_SoilSensingNode>
>>>
>>>
>>> enviot:A8_Geometry<http://localhost:7200/resource/enviot/A8_Geometry>
>>>
>>>
>>> "POINT(-3.780833
>>> 53.203055)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 9
>>>
>>>
>>> enviot:A9_SoilSensingNode<http://localhost:7200/resource/enviot/A9_SoilSensingNode>
>>>
>>>
>>> enviot:A9_Geometry<http://localhost:7200/resource/enviot/A9_Geometry>
>>>
>>>
>>> "POINT(-3.780833
>>> 53.202222)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 10
>>>
>>>
>>> enviot:AA_SoilSensingNode<http://localhost:7200/resource/enviot/AA_SoilSensingNode>
>>>
>>>
>>> enviot:AA_Geometry<http://localhost:7200/resource/enviot/AA_Geometry>
>>>
>>>
>>> "POINT(-3.783063
>>> 53.202309)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 11
>>>
>>>
>>> enviot:AB_SoilSensingNode<http://localhost:7200/resource/enviot/AB_SoilSensingNode>
>>>
>>>
>>> enviot:AB_Geometry<http://localhost:7200/resource/enviot/AB_Geometry>
>>>
>>>
>>> "POINT(-3.782638
>>> 53.202189)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 12
>>>
>>>
>>> enviot:AC_SoilSensingNode<http://localhost:7200/resource/enviot/AC_SoilSensingNode>
>>>
>>>
>>> enviot:AC_Geometry<http://localhost:7200/resource/enviot/AC_Geometry>
>>>
>>>
>>> "POINT(-3.783003
>>> 53.202191)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 13
>>>
>>>
>>> enviot:AD_SoilSensingNode<http://localhost:7200/resource/enviot/AD_SoilSensingNode>
>>>
>>>
>>> enviot:AD_Geometry<http://localhost:7200/resource/enviot/AD_Geometry>
>>>
>>>
>>> "POINT(-3.782518
>>> 53.201841)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 14
>>>
>>>
>>> enviot:AE_SoilSensingNode<http://localhost:7200/resource/enviot/AE_SoilSensingNode>
>>>
>>>
>>> enviot:AE_Geometry<http://localhost:7200/resource/enviot/AE_Geometry>
>>>
>>>
>>> "POINT(-3.782024
>>> 53.202193)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 15
>>>
>>>
>>> enviot:AF_SoilSensingNode<http://localhost:7200/resource/enviot/AF_SoilSensingNode>
>>>
>>>
>>> enviot:AF_Geometry<http://localhost:7200/resource/enviot/AF_Geometry>
>>>
>>>
>>> "POINT(-3.782877
>>> 53.201788)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>>
>>>
>>> These sensing nodes are deployed in a field which is modelled in the
>>> GeoSPARQL ontologies as 'Field' which is the subClass of Feature. Further,
>>> 'Field' has got three sub-features which are modelled as sub-classes named
>>> Hilltop, Swale and Riverbank having their geometries like 'Line String',
>>> 'Polygon' and 'Line String' respectively along with their geometric
>>> coordinates given below:
>>>
>>>
>>>
>>> Table 2: Showing results from 1 to 3 of 3. Query took 0.081 s.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> feature
>>>
>>>
>>>
>>> fgeo
>>>
>>>
>>>
>>> fwkt
>>>
>>>
>>>
>>> 1
>>>
>>>
>>> enviot:A_Hilltop<http://localhost:7200/resource/enviot/A_Hilltop>
>>>
>>>
>>> enviot:A_HillTopLineGeometry<http://localhost:7200/resource/enviot/A_HillTopLineGeometry>
>>>
>>>
>>> "LINESTRING(-3.780833 53.203055, -3.781111 53.202777, -3.780833
>>> 53.202222)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 2
>>>
>>>
>>> enviot:A_Riverbank<http://localhost:7200/resource/enviot/A_Riverbank>
>>>
>>>
>>> enviot:A_RiverbankLineGeometry<http://localhost:7200/resource/enviot/A_RiverbankLineGeometry>
>>>
>>>
>>> "LINESTRING(-3.783055 53.202777, -3.783063 53.202309, -3.783065 53.202158,
>>> -3.783092 53.202007, -3.782898
>>> 53.201828)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>>
>>> 3
>>>
>>>
>>> enviot:A_Swale<http://localhost:7200/resource/enviot/A_Swale>
>>>
>>>
>>> enviot:A_SwalePolygonGeometry<http://localhost:7200/resource/enviot/A_SwalePolygonGeometry>
>>>
>>>
>>> "POLYGON((-3.782222 53.202777, -3.782024 53.202193, -3.782638 53.202189,
>>> -3.783003 53.202191, -3.781964 53.201765, -3.782518 53.201841, -3.782877
>>> 53.201788, -3.782222
>>> 53.202777))"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
>>>
>>> I
>>>
>>>
>>>
>>> NOTE: In the above table (Table 2), the column 'fwkt' has got exactly the
>>> same geometry values (Point(long lat) which are the geometry values of
>>> individual soil sensing nodes given in column named 'gwkt' in Table 1.
>>>
>>>
>>> Now my question is:
>>>
>>>
>>> Which geographic feature contains the maximum number of soil sensing nodes?
>>>
>>> The query/answer should retrieve envoi:A_Swale which has 7 soil sensing
>>> nodes.
>>>
>>>
>>> I would be really grateful if someone could give me any idea/solution how
>>> to retrieve this?
>>>
>>>
>>> If you require any further information please let me know.
>>>
>>>
>>> Many thanks in advance.
>>>
>>>
>>> Best regards,
>>>
>>>
>>> Izhar
>>>
>>>
>> --
>> Lorenz Bühmann
>> AKSW group, University of Leipzig
>> Group: http://aksw.org - semantic web research center
>>
>>
>>
> --
> Lorenz Bühmann
> AKSW group, University of Leipzig
> Group: http://aksw.org - semantic web research center
>
>
>
--
Lorenz Bühmann
AKSW group, University of Leipzig
Group: http://aksw.org - semantic web research center
