Hi,
I created a Virtual Graph for the Foursquare dataset, which is loaded in a PostegreSQL server. Basically, there is a table which contains around 11 million Points of Internets (PoIs). Here is the structure of the table:
CREATE TABLE public.pois
(
poiid text COLLATE pg_catalog."default",
lat numeric,
long numeric,
category text COLLATE pg_catalog."default",
countrycode character(2) COLLATE pg_catalog."default"
)
And here are the auto-generated mappings (ofc in our case we have our internal ontology):
PREFIX :
<http://api.stardog.com/>
PREFIX owl:<http://www.w3.org/2002/07/owl#>
PREFIX rdf:<http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#>
PREFIX stardog:<tag:stardog:api:>
PREFIX xsd:<http://www.w3.org/2001/XMLSchema#>MAPPING
FROM SQL {
SELECT *
FROM "public"."pois"
}
TO {
?subject<http://api.stardog.com/pois#category>?category .
?subject<http://api.stardog.com/pois#countrycode>?countrycode .
?subject<http://api.stardog.com/pois#lat>?lat .
?subject<http://api.stardog.com/pois#long>?long .
?subject<http://api.stardog.com/pois#poiid>?poiid .
?subject rdf:type :pois
} WHERE {
BIND(template("pois{poiid}{lat}{long}{category}{countrycode}") AS ?subject)
BIND(xsd:double(?lat) AS ?lat)
BIND(xsd:double(?long) AS ?long)
}
From Stardog Studio, I am running the following query to retrieve all PoIs , since we need to do further processing on the client side. And this is only one of the simplest queries that we have to run, we need more expensive queries as well, where Joins from different tables and Graphs have to be used:
PREFIX :
<http://api.stardog.com/>
PREFIX owl:<http://www.w3.org/2002/07/owl#>
PREFIX rdf:<http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#>
PREFIX stardog:<tag:stardog:api:>
PREFIX xsd:<http://www.w3.org/2001/XMLSchema#>select * {
GRAPH<virtual://foursquareVG>{
?subject rdf:type :pois;
<http://api.stardog.com/pois#category>?category ;
<http://api.stardog.com/pois#countrycode>?countrycode ;
<http://api.stardog.com/pois#lat>?lat ;
<http://api.stardog.com/pois#long>?long ;
<http://api.stardog.com/pois#poiid>?poiid .
}
}
The query runs for 15 mins and does not retrieve anything. Below is the message that I got after I manually stopped it (killed):
And here is the query plan:
{
"prefixes": {
"": "http://api.stardog.com/",
"stardog": "tag:stardog:api:",
"owl": "http://www.w3.org/2002/07/owl#",
"rdf": "http://www.w3.org/1999/02/22-rdf-syntax-ns#",
"xsd": "XML Schema",
"rdfs": "http://www.w3.org/2000/01/rdf-schema#"
},
"dataset": {},
"plan": {
"label": "Projection(?subject, ?category, ?countrycode, ?lat, ?long, ?poiid)",
"cardinality": 1,
"children": [
{
"label": "VirtualGraphSql<virtual://foursquareVG>[#1]",
"children": [
{
"label": "SELECT "pois"."poiid", "pois"."lat", "pois"."long", "pois"."category", "pois"."countrycode"",
"children":
},
{
"label": "FROM "public"."pois"",
"children":
},
{
"label": "INNER JOIN "public"."pois" AS "pois0" ON "pois"."poiid" = "pois0"."poiid" AND "pois"."lat" = "pois0"."lat" AND "pois"."long" = "pois0"."long" AND "pois"."category" = "pois0"."category" AND "pois"."countrycode" = "pois0"."countrycode"",
"children":
},
{
"label": "INNER JOIN "public"."pois" AS "pois1" ON "pois"."poiid" = "pois1"."poiid" AND "pois"."lat" = "pois1"."lat" AND "pois"."long" = "pois1"."long" AND "pois"."category" = "pois1"."category" AND "pois"."countrycode" = "pois1"."countrycode" AND "pois0"."poiid" = "pois1"."poiid" AND "pois0"."lat" = "pois1"."lat" AND "pois0"."long" = "pois1"."long" AND "pois0"."category" = "pois1"."category" AND "pois0"."countrycode" = "pois1"."countrycode"",
"children":
},
{
"label": "INNER JOIN "public"."pois" AS "pois2" ON "pois"."poiid" = "pois2"."poiid" AND "pois"."lat" = "pois2"."lat" AND "pois"."long" = "pois2"."long" AND "pois"."category" = "pois2"."category" AND "pois"."countrycode" = "pois2"."countrycode" AND "pois0"."poiid" = "pois2"."poiid" AND "pois0"."lat" = "pois2"."lat" AND "pois0"."long" = "pois2"."long" AND "pois0"."category" = "pois2"."category" AND "pois0"."countrycode" = "pois2"."countrycode" AND "pois1"."poiid" = "pois2"."poiid" AND "pois1"."lat" = "pois2"."lat" AND "pois1"."long" = "pois2"."long" AND "pois1"."category" = "pois2"."category" AND "pois1"."countrycode" = "pois2"."countrycode"",
"children":
},
{
"label": "INNER JOIN "public"."pois" AS "pois3" ON "pois"."poiid" = "pois3"."poiid" AND "pois"."lat" = "pois3"."lat" AND "pois"."long" = "pois3"."long" AND "pois"."category" = "pois3"."category" AND "pois"."countrycode" = "pois3"."countrycode" AND "pois0"."poiid" = "pois3"."poiid" AND "pois0"."lat" = "pois3"."lat" AND "pois0"."long" = "pois3"."long" AND "pois0"."category" = "pois3"."category" AND "pois0"."countrycode" = "pois3"."countrycode" AND "pois1"."poiid" = "pois3"."poiid" AND "pois1"."lat" = "pois3"."lat" AND "pois1"."long" = "pois3"."long" AND "pois1"."category" = "pois3"."category" AND "pois1"."countrycode" = "pois3"."countrycode" AND "pois2"."poiid" = "pois3"."poiid" AND "pois2"."lat" = "pois3"."lat" AND "pois2"."long" = "pois3"."long" AND "pois2"."category" = "pois3"."category" AND "pois2"."countrycode" = "pois3"."countrycode"",
"children":
},
{
"label": "INNER JOIN "public"."pois" AS "pois4" ON "pois"."poiid" = "pois4"."poiid" AND "pois"."lat" = "pois4"."lat" AND "pois"."long" = "pois4"."long" AND "pois"."category" = "pois4"."category" AND "pois"."countrycode" = "pois4"."countrycode" AND "pois0"."poiid" = "pois4"."poiid" AND "pois0"."lat" = "pois4"."lat" AND "pois0"."long" = "pois4"."long" AND "pois0"."category" = "pois4"."category" AND "pois0"."countrycode" = "pois4"."countrycode" AND "pois1"."poiid" = "pois4"."poiid" AND "pois1"."lat" = "pois4"."lat" AND "pois1"."long" = "pois4"."long" AND "pois1"."category" = "pois4"."category" AND "pois1"."countrycode" = "pois4"."countrycode" AND "pois2"."poiid" = "pois4"."poiid" AND "pois2"."lat" = "pois4"."lat" AND "pois2"."long" = "pois4"."long" AND "pois2"."category" = "pois4"."category" AND "pois2"."countrycode" = "pois4"."countrycode" AND "pois3"."poiid" = "pois4"."poiid" AND "pois3"."lat" = "pois4"."lat" AND "pois3"."long" = "pois4"."long" AND "pois3"."category" = "pois4"."category" AND "pois3"."countrycode" = "pois4"."countrycode"",
"children":
}
]
}
]
}
}
Of course, when I run this huge query directly on the PostreSQL, it also takes time to finish, maybe similar to Stardog. The normal query which I would expect to be translated from SPARQL-to-SQL (maybe with some tiny additions) is:
SELECT poiid, lat, long, category, countrycode
FROM public.pois;
The above query takes only 16 seconds to be executed.
Considering the runtime transformations of the results to RDF, a kind of tiny delay is acceptable. But the endless execution time makes it impractical to use.
Thank you in advance.
Best,
