cdi query

cdi query

PREFIX dcterms: <http://purl.org/dc/terms/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX rdf-syntax: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX Specification: <http://www.eea.europa.eu/portal_types/Specification#>
PREFIX Data: <http://www.eea.europa.eu/portal_types/Data#>
PREFIX DataFileLink: <http://www.eea.europa.eu/portal_types/DataFileLink#>
PREFIX portal_types: <http://www.eea.europa.eu/portal_types#>
PREFIX Obligations: <http://www.eea.europa.eu/portal_types/Obligations#>
PREFIX rod: <http://rod.eionet.europa.eu/schema.rdf#>
PREFIX schema: <http://schema.org/>
PREFIX sdi-internal: <http://rdfdata.eionet.europa.eu/sdi-internal/schema/>
PREFIX EEAFigure: <http://www.eea.europa.eu/portal_types/EEAFigure#>
PREFIX GISApplication: <http://www.eea.europa.eu/portal_types/GIS%20Application#>
PREFIX DavizVisualization: <http://www.eea.europa.eu/portal_types/DavizVisualization#>
PREFIX Dashboard: <http://www.eea.europa.eu/portal_types/Dashboard#>

SELECT DISTINCT
?sdiUuid
?shortname as ?shortName 
?sdiTitle as ?title
?topicLabel as ?topics
?webmapViewer
?sdiWms as ?webmapService
?staticMapViewer
?interactiveDataViewer
?sdiAbstracts as ?abstract
?data as ?dataUrl
?dataLatestVersion
bif:either(bif:isnull(?isEEACore), 'FALSE', 'TRUE') as ?isCoreFlag ?specificationCodes as ?indicatorCodes 
?specificationTitle as ?indicatorTitle 
?specificationProductID as ?indicatorUrl 
bif:either(bif:isnull(?specificationProductID), 'FALSE', 'TRUE') as ?indicatorFlag 
?instrumentLabel as ?legislativeInstrument 
?instrumentUrl as ?legislativeInstrumentUrl 
bif:either(bif:isnull(?instrumentLabel), 'FALSE', 'TRUE') as ?legislativeInstrumentFlag 
?obligationsTitle as ?rod 
?Obligations as ?rodUrl 
bif:either(bif:isnull(?obligationsTitle), 'FALSE', 'TRUE') as ?rodFlag
bif:substring( ?eeaManagementPlan, 1, 8) as ?managementPlan
bif:replace(?sdiBrowseGraphic,"http:","https:") as ?thumbnailUrl
bif:REPLACE(?sdiCrs, 'urn:ogc:def:crs:', '') as ?crs 
substr(?sdiDatestamp, 1, 10) as ?publicationDate
?contact as ?contactPerson
?sdiInspireThemes as ?inspireThemes
?sdiGemetConcepts as ?gemetConcepts
?sdiStype as ?format
?sdiDistributionFormat as ?distributionFormat
?sdiLinks as ?fileSystemPath
?sdiScaleres as ?spatialResolution
?temporalCoverage 
?sdiLineage as ?lineage
concat('http://sdi.eea.europa.eu/catalogue/srv/eng/catalog.search#/metadata/', 
?sdiUuid) as ?sdiUrl
xsd:integer(substr(?sdiTbegin, 1,4)) as ?tbegin
xsd:integer(substr(?sdiTend, 1,4)) as ?tend
bif:either((xsd:integer(substr(?sdiTend, 1,4)) - xsd:integer(substr(?sdiTbegin, 1,4)) +1 ) > 1, 'TRUE', 'FALSE') as ?timeSeriesAvailability
?sdiPlace as ?placesCovered
?sdiUseLimitation
?referenceFlag
?sdiCustodian as ?contactOrganisation
bif:either(?sdiPublished = 'TRUE', 'FALSE', ?sdiPublished) as ?isRestricted
?associatedResources
xsd:double(?sdiWlon) as ?sdiWlon
xsd:double(?sdiElon) as ?sdiElon
xsd:double(?sdiSlat) as ?sdiSlat
xsd:double(?sdiNlat) as ?sdiNlat
(xsd:double(?sdiWlon)+xsd:double(?sdiElon))/2.0 as ?sdiCentroidLon
(xsd:double(?sdiNlat)+xsd:double(?sdiSlat))/2.0 as ?sdiCentroidLat
bif:either(BOUND(?sdiWlon),  (bif:concat('[[', ?sdiWlon, ',', ?sdiNlat, '],[', ?sdiElon, ',', ?sdiNlat, '],[', ?sdiElon, ',', ?sdiSlat, '],[', ?sdiWlon, ',', ?sdiSlat, '],[', ?sdiWlon, ',', ?sdiNlat, ']]')), '') as ?geoJsonPoly
bif:either(BOUND(?sdiWlon),  (bif:concat('[[', ((xsd:double(?sdiWlon)+xsd:double(?sdiElon))/2.0), ',', ((xsd:double(?sdiNlat)+xsd:double(?sdiSlat))/2.0), ']]')), '') as ?geoJsonPoint
?idpTopics
?restApi
?sdiDatasetId
?productID as ?dataProductID
?status

#?sdiEdition
#?sdiCustodian
#?sdiProtocols
#?sdiDataSrc
#?sdiPublished
#?dataTitle

WHERE
{{
  {
  SELECT DISTINCT
    *
  WHERE {
    ?sdiinternal sdi-internal:UUID ?sdiUuid .
    ?sdiinternal sdi-internal:SDIURL ?data .
    OPTIONAL { ?sdiinternal sdi-internal:dataset_id ?sdiDatasetId } . 
    ?sdiinternal sdi-internal:datestamp ?sdiDatestamp .
    OPTIONAL { ?sdiinternal sdi-internal:crs ?sdiCrs }  .
    ?sdiinternal sdi-internal:title ?sdiTitle .
    ?sdiinternal sdi-internal:abstracts ?sdiAbstracts .
    ?sdiinternal sdi-internal:WLON ?sdiWlon .
    ?sdiinternal sdi-internal:ELON ?sdiElon .
    ?sdiinternal sdi-internal:SLAT ?sdiSlat .
    ?sdiinternal sdi-internal:NLAT ?sdiNlat .
    OPTIONAL { ?sdiinternal sdi-internal:stype ?sdiStype } .  
    OPTIONAL { ?sdiinternal sdi-internal:scaleres ?sdiScaleres } .
    OPTIONAL { ?sdiinternal sdi-internal:tbegin ?sdiTbegin } .
    OPTIONAL { ?sdiinternal sdi-internal:tend ?sdiTend } .
    OPTIONAL { ?sdiinternal sdi-internal:edition ?sdiEdition } .
    OPTIONAL { ?sdiinternal sdi-internal:custodian ?sdiCustodian } .
    OPTIONAL { ?sdiinternal sdi-internal:inspire_themes ?sdiInspireThemes } . 
    OPTIONAL { ?sdiinternal sdi-internal:gemet_concepts ?sdiGemetConcepts } .
    OPTIONAL { ?sdiinternal sdi-internal:protocols ?sdiProtocols } .
    OPTIONAL { ?sdiinternal sdi-internal:links ?sdiLinks } .
    OPTIONAL { ?sdiinternal sdi-internal:lineage ?sdiLineage } .
    OPTIONAL { ?sdiinternal sdi-internal:data_src ?sdiDataSrc } .      
    OPTIONAL { ?sdiinternal sdi-internal:published ?sdiPublished } .
    OPTIONAL { ?sdiinternal sdi-internal:distributionFormat ?sdiDistributionFormat } .
    OPTIONAL { ?sdiinternal sdi-internal:place ?sdiPlace } .
    OPTIONAL { ?sdiinternal sdi-internal:useLimitation ?sdiUseLimitation } .
    OPTIONAL { ?sdiinternal sdi-internal:browseGraphic ?sdiBrowseGraphic } .
    OPTIONAL { ?sdiinternal sdi-internal:shortname ?shortname } .
    OPTIONAL { ?sdiinternal sdi-internal:referenceFlag ?referenceFlag } .
    OPTIONAL { ?sdiinternal sdi-internal:WMS ?sdiWms } .
    OPTIONAL { ?sdiinternal sdi-internal:idpTopics ?idpTopics } .
    OPTIONAL { ?sdiinternal sdi-internal:RESTAPI ?restApi } .
    OPTIONAL { ?sdiinternal sdi-internal:status ?status } .

    graph ?graph { ?data rdf-syntax:type Data:Data }  .
    FILTER (str(?graph) = concat(str(?data), '/@@rdf') )
    ?data dcterms:title ?dataTitle .
    OPTIONAL { ?data Data:contact ?contact } .

    {{
      SELECT DISTINCT 
        ?data
        bif:either(bound(?dataIsReplacedBy),'FALSE', 'TRUE') as ?dataLatestVersion
      WHERE {
        graph ?graph { ?data rdf-syntax:type Data:Data }  .
        OPTIONAL { ?data dcterms:isReplacedBy ?dataIsReplacedBy } .
      }
    }}

    OPTIONAL {{
      SELECT DISTINCT
        ?data
        sql:GROUP_CONCAT(?topicLabel, '|') as ?topicLabel
      WHERE {{ 
        SELECT DISTINCT 
          ?data 
          ?topicLabel
        WHERE {
          graph ?graph { ?data rdf-syntax:type Data:Data }  .
          ?data portal_types:topic ?topic .
          ?topic rdfs:label ?topicLabel .
        }
      }} GROUP BY ?data
    }}

    OPTIONAL { ?data schema:productID ?productID } .
    OPTIONAL { ?data Data:eeaManagementPlan ?eeaManagementPlan } .
 
    OPTIONAL {{
      SELECT DISTINCT
        ?data 
        sql:GROUP_CONCAT(?temporalCoverage, '|') as ?temporalCoverage
      WHERE {{ 
        SELECT DISTINCT ?data ?temporalCoverage 
        WHERE { 
          graph ?graph { ?data rdf-syntax:type Data:Data } .
          ?data Data:temporalCoverage ?temporalCoverage .
        } 
        ORDER BY ?temporalCoverage 
      }} GROUP BY ?data
    }}

    OPTIONAL {{
      SELECT DISTINCT
        ?data
        sql:GROUP_CONCAT_DISTINCT(?relatedItems, '|') as ?associatedResources
      WHERE {{
        SELECT DISTINCT 
          ?data
          ?relatedItems
          ?relatedItemsType
          ?figureType
        WHERE {
          graph ?graph { ?data rdf-syntax:type Data:Data } .
          FILTER (str(?graph) = concat(str(?data), '/@@rdf') )
          ?data Data:relatedItems ?relatedItems .
          ?relatedItems rdf-syntax:type ?relatedItemsType .
        }
        }} 
    }}

    OPTIONAL {{
      SELECT DISTINCT
        ?data
        sql:GROUP_CONCAT_DISTINCT(?relatedItems, '|') AS ?webmapViewer
      WHERE {{
        SELECT DISTINCT 
          ?data
          ?relatedItems
          ?relatedItemsType
          ?figureType
        WHERE {
          graph ?graph { ?data rdf-syntax:type Data:Data } .
          FILTER (str(?graph) = concat(str(?data), '/@@rdf') )
          ?data Data:relatedItems ?relatedItems .
          ?relatedItems rdf-syntax:type GISApplication:GISApplication .
        }
        }} 
    }}

    OPTIONAL {{
      SELECT DISTINCT
        ?data
        sql:GROUP_CONCAT_DISTINCT(?relatedItems, '|') AS ?staticMapViewer
      WHERE {{
        SELECT DISTINCT 
          ?data
          ?relatedItems
          ?relatedItemsType
          ?figureType
        WHERE {
          graph ?graph { ?data rdf-syntax:type Data:Data } .
          FILTER (str(?graph) = concat(str(?data), '/@@rdf') )
          ?data Data:relatedItems ?relatedItems .
          ?relatedItems EEAFigure:figureType ?figureType .
          FILTER (xsd:string(?figureType) = 'map') .
        }
        }} 
    }}

    OPTIONAL {{
      SELECT DISTINCT
        ?data
        sql:GROUP_CONCAT_DISTINCT(?relatedItems, '|') AS ?interactiveDataViewer
      WHERE {{
        SELECT DISTINCT 
          ?data
          ?relatedItems
          ?relatedItemsType
          ?figureType
        WHERE {
          graph ?graph { ?data rdf-syntax:type Data:Data } .
          FILTER (str(?graph) = concat(str(?data), '/@@rdf') )
          ?data Data:relatedItems ?relatedItems .
          ?relatedItems rdf-syntax:type ?relatedItemsType .
          FILTER (?relatedItemsType in (Dashboard:Dashboard, DavizVisualization:DavizVisualization))
        }
        }} 
    }}

    OPTIONAL {{
      SELECT DISTINCT
        ?data
        sql:GROUP_CONCAT(?Obligations, '|') as ?Obligations
        sql:GROUP_CONCAT(?obligationsTitle, '|') as ?obligationsTitle
        sql:GROUP_CONCAT(?instrument, '|') as ?instrument
        sql:GROUP_CONCAT(?instrumentLabel, '|') as ?instrumentLabel
        sql:GROUP_CONCAT(?instrumentUrl, '|') as ?instrumentUrl
        contains(sql:GROUP_CONCAT(?isEEACore , '|'), '1') as ?isEEACore 
      WHERE {{ 
        SELECT DISTINCT 
          ?data ?Obligations ?obligationsTitle ?instrument 
          ?instrumentLabel ?instrumentUrl ?isEEACore
        WHERE {
          graph ?graph { ?data rdf-syntax:type Data:Data } .
          ?data Obligations:Obligations ?Obligations .
          ?Obligations rdfs:label ?obligationsTitle .
          FILTER (!contains(?obligationsTitle, '['))
          ?Obligations rod:instrument ?instrument .
          ?instrument rdfs:label ?instrumentLabel .
          ?instrument rod:instrumentURL ?instrumentUrl .
          ?Obligations rod:isEEACore ?isEEACore .
        }
      }} GROUP BY ?data
    }}

    OPTIONAL {{
      SELECT DISTINCT
        ?data
        sql:GROUP_CONCAT_DISTINCT(?specificationProductID, '|') as ?specificationProductID
        sql:GROUP_CONCAT_DISTINCT(?specificationTitle, '|') as ?specificationTitle
        sql:GROUP_CONCAT_DISTINCT(?specificationCodes, '|') as ?specificationCodes
      WHERE {{ 
        SELECT DISTINCT 
          ?data
          bif:concat('https://www.eea.europa.eu/ds_resolveuid/', ?specificationProductID) as ?specificationProductID
         ?specificationTitle
         ?specificationCodes
        WHERE {
          graph ?graph { ?data rdf-syntax:type Data:Data } .
          FILTER (str(?graph) = concat(str(?data), '/@@rdf') )
          ?specification Specification:relatedItems ?data .
          ?specification rdf-syntax:type Specification:Specification .
          ?specification schema:productID ?specificationProductID .
          OPTIONAL { ?specification dcterms:isReplacedBy ?specificationIsReplacedBy } .
          FILTER (!BOUND( ?specificationIsReplacedBy))
          ?specification dcterms:title ?specificationTitle .
          ?specification Specification:relatedItems ?specificationRelatedItems .
          ?specification Specification:codes ?specificationCodes . 
          #?specificationRelatedItems rdf-syntax:type Data:Data .
          #?specificationRelatedItems schema:productID ?dataProductID .
          FILTER (contains(?specificationCodes, 'CSI') = 0)
        }
      }} GROUP BY ?data
    }}
  }

  } UNION {

    SELECT DISTINCT
      *
    WHERE {
      ?sdiinternal sdi-internal:UUID ?sdiUuid .
      OPTIONAL { ?sdiinternal sdi-internal:SDIURL ?data } .
      OPTIONAL { ?sdiinternal sdi-internal:dataset_id ?sdiDatasetId } . 
      ?sdiinternal sdi-internal:datestamp ?sdiDatestamp .
      OPTIONAL { ?sdiinternal sdi-internal:crs ?sdiCrs }  .
      ?sdiinternal sdi-internal:title ?sdiTitle .
      ?sdiinternal sdi-internal:abstracts ?sdiAbstracts .
      OPTIONAL { ?sdiinternal sdi-internal:stype ?sdiStype } .  
      OPTIONAL { ?sdiinternal sdi-internal:scaleres ?sdiScaleres } .
      OPTIONAL { ?sdiinternal sdi-internal:tbegin ?sdiTbegin } .
      OPTIONAL { ?sdiinternal sdi-internal:tend ?sdiTend } .
      OPTIONAL { ?sdiinternal sdi-internal:edition ?sdiEdition } .
      OPTIONAL { ?sdiinternal sdi-internal:custodian ?sdiCustodian } .
      OPTIONAL { ?sdiinternal sdi-internal:inspire_themes ?sdiInspireThemes } . 
      OPTIONAL { ?sdiinternal sdi-internal:gemet_concepts ?sdiGemetConcepts } .
      OPTIONAL { ?sdiinternal sdi-internal:protocols ?sdiProtocols } .
      OPTIONAL { ?sdiinternal sdi-internal:links ?sdiLinks } .
      OPTIONAL { ?sdiinternal sdi-internal:lineage ?sdiLineage } .
      OPTIONAL { ?sdiinternal sdi-internal:data_src ?sdiDataSrc } .      
      OPTIONAL { ?sdiinternal sdi-internal:published ?sdiPublished } .
      OPTIONAL { ?sdiinternal sdi-internal:distributionFormat ?sdiDistributionFormat } .
      OPTIONAL { ?sdiinternal sdi-internal:place ?sdiPlace } .
      OPTIONAL { ?sdiinternal sdi-internal:useLimitation ?sdiUseLimitation } .
      OPTIONAL { ?sdiinternal sdi-internal:browseGraphic ?sdiBrowseGraphic } .
      OPTIONAL { ?sdiinternal sdi-internal:shortname ?shortname } .
      OPTIONAL { ?sdiinternal sdi-internal:referenceFlag ?referenceFlag } .
      OPTIONAL { ?sdiinternal sdi-internal:WMS ?sdiWms } .
      OPTIONAL { ?sdiinternal sdi-internal:idpTopics ?idpTopics } .
      OPTIONAL { ?sdiinternal sdi-internal:RESTAPI ?restApi } .
      OPTIONAL { ?sdiinternal sdi-internal:status ?status } .
      FILTER NOT EXISTS { ?data rdf-syntax:type Data:Data } 
    }
  }
}}