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