Using GeoSPARQL with Dremio

[Frank Nebel]

Hello community,

I tried to get GeoSPARQL to work with Ontop. More precisely, I am using GraphDB 10.7.1 and its Ontop integration.

If this should be a bug, I am happy to register it. But before proceeding there, I wanted to make sure that it is not a simple oversight on my side.

I have a table with some cities retrieved from Wikidata. The columns are id (e.g. http://www.wikidata.org/entity/Q3922), label and geocoordinate (e.g. ( Point(8.883333333 51.93777778)).

This data is maintained in Dremio and I correctly configured Dremio when setting up the GraphDB repository.

I then defined the following mappings:

- id to geo:Geometry

- label to rdfs:label

- humanSettlementHasGeoCoordinate to geo:asWKT

More precisely:

@prefix data: <http://mydata.example.org/> .
@prefix geo:  <http://www.opengis.net/ont/geosparql#> .
@prefix rdf:  <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .
@prefix voc:  <http://vocabulary.example.org/> .
@prefix xsd:  <http://www.w3.org/2001/XMLSchema#> .

[ <http://www.w3.org/ns/r2rml#logicalTable>
          [ <http://www.w3.org/ns/r2rml#tableName>
                    "\"lenses\"."\"X.Y.Z\"".\"HumanSettlement\"" ];
  <http://www.w3.org/ns/r2rml#predicateObjectMap>
          [ <http://www.w3.org/ns/r2rml#objectMap>
                    [ <http://www.w3.org/ns/r2rml#column>
                              "\"humanSettlementHasGeoCoordinate\"";
                      <http://www.w3.org/ns/r2rml#datatype>
                              geo:wktLiteral;
                      <http://www.w3.org/ns/r2rml#termType>
                              <http://www.w3.org/ns/r2rml#Literal>
                    ];
            <http://www.w3.org/ns/r2rml#predicateMap>
                    [ <http://www.w3.org/ns/r2rml#constant>
                              geo:asWKT ]
          ];
  <http://www.w3.org/ns/r2rml#subjectMap>
          [ <http://www.w3.org/ns/r2rml#column>
                    "\"id\"";
            <http://www.w3.org/ns/r2rml#termType>
                    <http://www.w3.org/ns/r2rml#IRI>
          ]
] .

[ <http://www.w3.org/ns/r2rml#logicalTable>
          [ <http://www.w3.org/ns/r2rml#tableName>
                    "\"lenses\"."\"X.Y.Z\"".\"HumanSettlement\"" ];
  <http://www.w3.org/ns/r2rml#subjectMap>
          [ <http://www.w3.org/ns/r2rml#class>
                    geo:Geometry;
            <http://www.w3.org/ns/r2rml#column>
                    "\"id\"";
            <http://www.w3.org/ns/r2rml#termType>
                    <http://www.w3.org/ns/r2rml#IRI>
          ]
] .

[ <http://www.w3.org/ns/r2rml#logicalTable>
          [ <http://www.w3.org/ns/r2rml#tableName>
                    "\"lenses\"."\"X.Y.Z\"".\"HumanSettlement\"" ];
  <http://www.w3.org/ns/r2rml#predicateObjectMap>
          [ <http://www.w3.org/ns/r2rml#objectMap>
                    [ <http://www.w3.org/ns/r2rml#column>
                              "\"label\"";
                      <http://www.w3.org/ns/r2rml#datatype>
                              xsd:string;
                      <http://www.w3.org/ns/r2rml#termType>
                              <http://www.w3.org/ns/r2rml#Literal>
                    ];
            <http://www.w3.org/ns/r2rml#predicateMap>
                    [ <http://www.w3.org/ns/r2rml#constant>
                              rdfs:label ]
          ];
  <http://www.w3.org/ns/r2rml#subjectMap>
          [ <http://www.w3.org/ns/r2rml#column>
                    "\"id\"";
            <http://www.w3.org/ns/r2rml#termType>
                    <http://www.w3.org/ns/r2rml#IRI>
          ]
] .
 

Next, I executed the following query:

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof:<http://www.opengis.net/def/function/geosparql/>
PREFIX uom:<http://www.opengis.net/def/uom/OGC/1.0/>

SELECT ?dist WHERE {
    <http://www.wikidata.org/entity/Q9918> geo:asWKT ?xWKT .
<http://www.wikidata.org/entity/Q10686> geo:asWKT ?yWKT .
    BIND(geof:distance(?xWKT,?yWKT, uom:metre) AS ?dist)      
}

This gives me an error: Query evaluation error: VALIDATION ERROR: No match found for function signature ST_DISTANCESPHERE(<CHARACTER>, <CHARACTER>)

After checking all the geo spatial functions that are supported in Dremio, this is no surprise. There is no ST_DISTANCESPHERE (with two parameters). Instead there is GEO_DISTANCE (with four parameters).

My assumption. Dremio`s geo spatial functions have changed and this has not been adapted within Ontop (in the mapping process). Is this correct, or did I do something wrong? If yes, is there an easy way to fix it (some config-file)?

Thanks!

Best

Frank

[Benjamin Cogrel]

Hi Frank,

Thanks for your question.

The function ST_DISTANCESPHERE comes with the default function factory of Ontop and is made available to all the SQL dialects by default.

We didn't implement anything Dremio-specific for geospatial support so far.

Unfortunately, Dremio is missing support for the GEOMETRY data type on which our current implementation of GeoSPARQL is based. 

It requires to directly deal with latitude and longitude values instead of handling arbitrary geometry objects.

This looks at first glance a bit tricky to implement, especially if the DB value directly comes as a WKT text string, potentially describing an arbitrary geometry.

I am not a specialist of the geospatial part, perhaps my colleagues can comment more on this.

Best,

Benjamin

--
Please follow our guidelines on how to report a bug https://ontop-vkg.org/community/contributing/bug-report
---
You received this message because you are subscribed to the Google Groups "ontop4obda" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ontop4obda+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/ontop4obda/a23efb1a-bed0-4e3d-8ff4-2fc7b4f05ccbn%40googlegroups.com.

[Frank Nebel]

Hello Benjamin, 

thank your for your analysis. This seems to be more complicated than anticipated. Do you have in mind, which classes would have to be modified/extended to make it work? Just as an orientation, if I find someone who could contribute to your project.

Best

Frank

[Benjamin Cogrel]

Hi Frank,

Here is the feedback I collected:

The only function that could be conceivably supported with limitations is indeed geof:distance with input unit degree, output unit metre. But given how GEO_DISTANCE is defined in Dremio:

• You would need to assume/enforce all data are POINT geometries
• Coordinate order is crucial. Since there is no GEOMETRY column, there is no guarantee that it is POINT(longitude, latitude). You need to ensure/enforce this too.
• Maybe then you need to extract the longitude and latitude for each point e.g.:
• CAST(SPLIT_PART(SPLIT_PART('POINT(8.883333333 51.93777778)', '(', 2), ' ', 2) AS FLOAT) AS latitude
• CAST(SPLIT_PART(SPLIT_PART('POINT(8.883333333 51.93777778)', '(', 2), ' ', 1) AS FLOAT) AS longitude

We think you would need to override getDBSTDistanceSphere for Dremio to get a custom serialization for the translation above instead of using the serialization ST_DISTANCESPHERE. 

Dremio GEO_DISTANCE only works with input unit DEGREE, output unit METRE so again that is the only use case (nothing else from GeoSPARQL) with very limited robustness. 

On the other native Dremio functions, GEO_BEYOND and GEO_NEARBY are effectively filters over geof:distance so have no GeoSPARQL counterparts (you could even replicate them with GEO_DISTANCE), neither do the 2 GEOHASH functions.

Best,

Benjamin

To view this discussion visit https://groups.google.com/d/msgid/ontop4obda/66f6f4c9-4e19-46a7-989e-e422fd5e28cen%40googlegroups.com.

[Frank Nebel]

Hello Benjamin,

thank you for pointing out this potential solution. As you said, it would be quite limited as a solution. I will have to see how to proceed. But adapting DremioDBFunctionSymbolFactory.java that inherits from  AbstractSQLDBFunctionSymbolFactory.java is at least an option.

Best

Frank