Skip to content

Join with SPARQL

Jump to bottom
James Kerr edited this page Oct 2, 2018 · 2 revisions

Here is an example of a service that has a layer that joins a view against SPARQL

{
  "info" : {
    "name" : "AirplaneWatchlist",
    "description" : ""
  },

  "layers" : [
    {
      "id": 0,
      "name" : "Watchlist Planes",
      "decription": "Planes associated with a person or organization on a watchlist",
      "geometryType": "Point",
      "idField": "OBJECTID",
      "displayField": "url",
      "geometryPath": "/geometry",
      "extent" : {
        "xmin" : -160,
        "ymin" : -80,
        "xmax" : 160,
        "ymax" : 80,
        "spatialReference" : {
          "wkid" : 4326,
          "latestWkid" : 4326
        }
      },
      "schema" : "Watchlist",
      "view" : "Airplanes",
      "joins" : [
        {
          "source" : "sparql",
          "query" : "\nPREFIX skos: <http://www.w3.org/2004/02/skos/core#>\nPREFIX foaf: <http://xmlns.com/foaf/0.1/>\nPREFIX org: <http://www.w3.org/ns/org#>\nPREFIX eqp: <https://data.nasa.gov/ontologies/atmonto/equipment#>\nPREFIX wl: <http://marklogic.com/2018/01/watchlist#>\n\nSELECT ?aircraftId ?org1 ?wl ?org2 ?reason\nWHERE {  \n  ?aircraft a eqp:Aircraft.\n  BIND(STR(?aircraft) as ?aircraftId)\n    \n  {\n    ?aircraft eqp:isRegisteredTo ?org1.\n    \n    # Associated via organizational relationship\n    { \n      ?org1 skos:prefLabel ?org1Name.\n      ?org1 wl:onWatchlist ?wl .\n      ?org1 skos:prefLabel ?org1Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which is on the \", ?wlName) as ?reason )\n    } \n    UNION\n    {\n      ?org2 org:hasUnit ?org1.\n      ?org2 wl:onWatchlist ?wl.\n      ?org1 skos:prefLabel ?org1Name.\n      ?org2 skos:prefLabel ?org2Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which is a subsidiary of a company,\", ?org2Name, \", that is on the \", ?wlName) as ?reason )\n    }\n    UNION\n    {\n      ?org1 org:hasUnit ?org2.\n      ?org2 wl:onWatchlist ?wl.\n      ?org1 skos:prefLabel ?org1Name.\n      ?org2 skos:prefLabel ?org2Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which has a subsidiary company,\", ?org2Name, \", that is on the \", ?wlName) as ?reason )\n    }\n  }\n  UNION\n  {\n    # Mentioned in an article that the plane was in a country of interest\n    ?aircraft wl:wasSeenIn ?country.\n    ?country wl:onWatchlist ?wl.\n    ?country skos:prefLabel ?countryName.\n    ?wl skos:prefLabel ?wlName.\n    BIND( CONCAT(\"Aircraft was seen in \", ?countryName, \" which is on the \", ?wlName) as ?reason )\n  }\n}\n",
          "joinOn" : {
            "left" : "IRI",
            "right" : "aircraftId"
          },
          "fields" : {
            "aircraftId" : { "scalarType" : "string" },
            "org1"       : { "scalarType" : "string" },
            "wl"         : { "scalarType" : "string" },
            "reason"     : { "scalarType" : "string" },
            "org2"       : { "scalarType" : "string" }
          }
        }
      ]
    }
  ]
}

To extend this so we can use either a view or a SPARQL query as the starting point, we need to have a generic dataSources property. It would look something like this

{
  "info" : {
    "name" : "AirplaneWatchlist",
    "description" : ""
  },

  "layers" : [
    {
      "id": 0,
      "name" : "Watchlist Planes",
      "decription": "Planes associated with a person or organization on a watchlist",
      "geometryType": "Point",
      "idField": "OBJECTID",
      "displayField": "url",
      "geometryPath": "/geometry",
      "extent" : {
        "xmin" : -160,
        "ymin" : -80,
        "xmax" : 160,
        "ymax" : 80,
        "spatialReference" : {
          "wkid" : 4326,
          "latestWkid" : 4326
        }
      },
      "dataSources" : [
        {
          "source" : "view",
          "schema" : "Watchlist",
          "view" : "Airplanes"
         },
        {
          "source" : "sparql",
          "query" : "\nPREFIX skos: <http://www.w3.org/2004/02/skos/core#>\nPREFIX foaf: <http://xmlns.com/foaf/0.1/>\nPREFIX org: <http://www.w3.org/ns/org#>\nPREFIX eqp: <https://data.nasa.gov/ontologies/atmonto/equipment#>\nPREFIX wl: <http://marklogic.com/2018/01/watchlist#>\n\nSELECT ?aircraftId ?org1 ?wl ?org2 ?reason\nWHERE {  \n  ?aircraft a eqp:Aircraft.\n  BIND(STR(?aircraft) as ?aircraftId)\n    \n  {\n    ?aircraft eqp:isRegisteredTo ?org1.\n    \n    # Associated via organizational relationship\n    { \n      ?org1 skos:prefLabel ?org1Name.\n      ?org1 wl:onWatchlist ?wl .\n      ?org1 skos:prefLabel ?org1Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which is on the \", ?wlName) as ?reason )\n    } \n    UNION\n    {\n      ?org2 org:hasUnit ?org1.\n      ?org2 wl:onWatchlist ?wl.\n      ?org1 skos:prefLabel ?org1Name.\n      ?org2 skos:prefLabel ?org2Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which is a subsidiary of a company,\", ?org2Name, \", that is on the \", ?wlName) as ?reason )\n    }\n    UNION\n    {\n      ?org1 org:hasUnit ?org2.\n      ?org2 wl:onWatchlist ?wl.\n      ?org1 skos:prefLabel ?org1Name.\n      ?org2 skos:prefLabel ?org2Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which has a subsidiary company,\", ?org2Name, \", that is on the \", ?wlName) as ?reason )\n    }\n  }\n  UNION\n  {\n    # Mentioned in an article that the plane was in a country of interest\n    ?aircraft wl:wasSeenIn ?country.\n    ?country wl:onWatchlist ?wl.\n    ?country skos:prefLabel ?countryName.\n    ?wl skos:prefLabel ?wlName.\n    BIND( CONCAT(\"Aircraft was seen in \", ?countryName, \" which is on the \", ?wlName) as ?reason )\n  }\n}\n",
          "joinOn" : {
            "left" : "IRI",
            "right" : "aircraftId"
          },
          "fields" : {
            "aircraftId" : { "scalarType" : "string" },
            "org1"       : { "scalarType" : "string" },
            "wl"         : { "scalarType" : "string" },
            "reason"     : { "scalarType" : "string" },
            "org2"       : { "scalarType" : "string" }
          }
        },
        {
          "source" : "sparql",
          "query" : "\nPREFIX skos: <http://www.w3.org/2004/02/skos/core#>\nPREFIX foaf: <http://xmlns.com/foaf/0.1/>\nPREFIX org: <http://www.w3.org/ns/org#>\nPREFIX eqp: <https://data.nasa.gov/ontologies/atmonto/equipment#>\nPREFIX wl: <http://marklogic.com/2018/01/watchlist#>\n\nSELECT ?aircraftId ?org1 ?wl ?org2 ?reason\nWHERE {  \n  ?aircraft a eqp:Aircraft.\n  BIND(STR(?aircraft) as ?aircraftId)\n    \n  {\n    ?aircraft eqp:isRegisteredTo ?org1.\n    \n    # Associated via organizational relationship\n    { \n      ?org1 skos:prefLabel ?org1Name.\n      ?org1 wl:onWatchlist ?wl .\n      ?org1 skos:prefLabel ?org1Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which is on the \", ?wlName) as ?reason )\n    } \n    UNION\n    {\n      ?org2 org:hasUnit ?org1.\n      ?org2 wl:onWatchlist ?wl.\n      ?org1 skos:prefLabel ?org1Name.\n      ?org2 skos:prefLabel ?org2Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which is a subsidiary of a company,\", ?org2Name, \", that is on the \", ?wlName) as ?reason )\n    }\n    UNION\n    {\n      ?org1 org:hasUnit ?org2.\n      ?org2 wl:onWatchlist ?wl.\n      ?org1 skos:prefLabel ?org1Name.\n      ?org2 skos:prefLabel ?org2Name.\n      ?wl skos:prefLabel ?wlName.\n      BIND( CONCAT(\"Aircraft is registered to \", ?org1Name, \" which has a subsidiary company,\", ?org2Name, \", that is on the \", ?wlName) as ?reason )\n    }\n  }\n  UNION\n  {\n    # Mentioned in an article that the plane was in a country of interest\n    ?aircraft wl:wasSeenIn ?country.\n    ?country wl:onWatchlist ?wl.\n    ?country skos:prefLabel ?countryName.\n    ?wl skos:prefLabel ?wlName.\n    BIND( CONCAT(\"Aircraft was seen in \", ?countryName, \" which is on the \", ?wlName) as ?reason )\n  }\n}\n",
          "joinOn" : {
            "left" : "aircraftId",
            "right" : "acId"
          },
          "fields" : {
            "acId" : { "scalarType" : "string" },
            "foo"       : { "scalarType" : "string" }
          }
        },
        {
          "source" : "view",
          "schema" : "SomeOtherSchema",
          "view" : "SomeOtherView",
          "joinOn" : {
            "left" : "aircraftId",
            "right" : "myId"
          }
        }        
      ]
    }
  ]
}
Clone this wiki locally