CODEWITHSUNDEEP
pythonsparqlrdflib
Spyros Ligouras
Spyros Ligouras

Reputation: 39

SPARQL query returns more rows than expected

I'm trying to convert the following Turtle RDF into csv. I just want to keep the values of hasPlaceName, hasICAOcode and hasWKT for each object:

:Place_Oberschleissheim_Airport a :Civil_Aerodrome
 ;  :hasPlaceName "OBERSCHLEISSHEIM"
 ;    :hasICAOcode "EDNX"
 ;  :elevationOfPlace "487.68"^^unit:meters
 ;  :hasGeometry :geom_11_55916690826416_48_239166259765625
 ;  :Aerodrome_serves_City :City_OBERSCHLEISSHEIM
 .  :City_OBERSCHLEISSHEIM a :City
 .  :geom_11_55916690826416_48_239166259765625 a :Geometry
 ;  :hasWKT "POINT (11.55916690826416 48.239166259765625)" .

:Place_Oberschleissheim_Airport a :Civil_Aerodrome
 ;  :hasPlaceName "OBERSCHLEISSHEIM"
 ;    :hasICAOcode "EDMX"
 ;  :elevationOfPlace "487.68"^^unit:meters
 ;  :hasGeometry :geom_11_565555572509766_48_23805618286133
 ;  :Aerodrome_serves_City :City_OBERSCHLEISSHEIM
 .  :City_OBERSCHLEISSHEIM a :City
 .  :geom_11_565555572509766_48_23805618286133 a :Geometry
 ;  :hasWKT "POINT (11.565555572509766 48.23805618286133)" .

This is my SPARQL query:

SELECT DISTINCT ?icao ?name ?wkt
WHERE {
    [] a :Civil_Aerodrome ;
        :hasPlaceName ?name ;
        :hasICAOcode ?icao ;
        :hasGeometry/:hasWKT ?wkt .
}

But I'm getting four records instead of two:

(u'EDNX', 'u'OBERSCHLEISSHEIM', 'u'POINT (11.565555572509766 48.23805618286133)')
(u'EDMX', 'u'OBERSCHLEISSHEIM', 'u'POINT (11.565555572509766 48.23805618286133)')
(u'EDNX', 'u'OBERSCHLEISSHEIM', 'u'POINT (11.55916690826416 48.239166259765625)')
(u'EDMX', 'u'OBERSCHLEISSHEIM', 'u'POINT (11.55916690826416 48.239166259765625)')

I think I'm missing something in the SPARQL syntax. Any clues towards the right direction would be greatly appreciated.

Upvotes: 2

Views: 140

Answers (1)

Jeen Broekstra
Jeen Broekstra

Reputation: 22053

Your SPARQL query is correct, and so is the result. The problem is in your data.

You have defined a single airport resource, :Place_Oberschleissheim_Airport. However, you give this single airport two different icao codes, and two different sets of coordinates.

Then you ask a query which asks for all distinct combinations of icao, placename, and coordinate-sets for any airport. There's only one airport, but it has two icaos, and two coordinate-sets, so there's four unique results to your query: 

icao code 1, coordinate 1
icao code 2, coordinate 1
icao code 1, coordinate 2
icao code 2, coordinate 2

The solution is to make sure that your data uses different identifiers for different airports (or more generally, to make sure that coordinates and icao codes are unique per airport).

Alternatively, if the data really must stay this way, and you want your query to return unique results per airport, you will have to ask yourself which coordinate and which icao code it should return.

If the answer is "just pick one of each, I don't care which", you can do the following

SELECT DISTINCT (SAMPLE(?icao) as ?code) ?name (sample(?wkt) as ?coord)
WHERE {
    ?airport a :Civil_Aerodrome ;
        :hasPlaceName ?name ;
        :hasICAOcode ?icao ;
        :hasGeometry/:hasWKT ?wkt .
} group by ?airport ?name

result:

Evaluating SPARQL query...
+------------------------+------------------------+------------------------+
| code                   | name                   | coord                  |
+------------------------+------------------------+------------------------+
| "EDNX"                 | "OBERSCHLEISSHEIM"     | "POINT (11.55916690826416 48.239166259765625)"|
+------------------------+------------------------+------------------------+
1 result(s) (13 ms)

If on the other hand you want both back, you either have to accept that you get a few more rows, or you can stick the alternatives together into one line:

SELECT DISTINCT (GROUP_CONCAT(DISTINCT ?icao) as ?codes) ?name (GROUP_CONCAT(DISTINCT ?wkt) as ?coords)
WHERE {
    ?airport a :Civil_Aerodrome ;
        :hasPlaceName ?name ;
        :hasICAOcode ?icao ;
        :hasGeometry/:hasWKT ?wkt .
} group by ?airport ?name

result:

Evaluating SPARQL query...
+------------------------+------------------------+------------------------+
| codes                  | name                   | coords                 |
+------------------------+------------------------+------------------------+
| "EDNX EDMX"            | "OBERSCHLEISSHEIM"     | "POINT (11.55916690826416 48.239166259765625) POINT (11.565555572509766 48.23805618286133)"|
+------------------------+------------------------+------------------------+
1 result(s) (2 ms)

Upvotes: 3

Related Questions