Skip to content

Instantly share code, notes, and snippets.

@VladimirAlexiev

VladimirAlexiev/CHIN-restructure.md

Last active March 17, 2024 19:04
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save VladimirAlexiev/cf2de89b692bbc2ae70917aae021ec07 to your computer and use it in GitHub Desktop.
Save VladimirAlexiev/cf2de89b692bbc2ae70917aae021ec07 to your computer and use it in GitHub Desktop.
Download ZIP
Examples of complex SPARQL queries that I've written
Raw
README.md

Examples of complex SPARQL queries that I've written.

Note: originally these used extensions .ru (the W3C mandated extension for SPARQL Update) and .tarql (for TARQL); I renamed them to .sparql for better highlighting on Github.

Dun and Bradstreet Companies

DnB.sparql is a conversion from a DnB tabular data format with 160 columns to a semantic model based on FIBO.

The query is 480 lines, 110 prefixes, 110 binds; and makes 33 nodes, 250 triples.

Wikidata Org Mapping

wd-mapping.rq extracts selected WD data about one organization (denoted ?it) and simplifies it to a "Company Graph" KG format, which is an extension of the one described in:

The euBusinessGraph Ontology: a Lightweight Ontology for Harmonizing Basic Company Information. Roman, D.; Alexiev, V.; Paniagua, J.; Elvesaeter, B.; von Zernichow, B. M.; Soylu, A.; Simeonov, B.; and Taggart, C. Semantic Web - Interoperability, Usability, Applicability (SWJ), 41-68. November 2021. DOI 10.3233/SW-210424

The query is rather complex: 1118 lines, 58 prefixes, 108 binds; 29 nodes, 90 triples.

So it was developed in Literate Programming style in wd-mapping.org, then tangled to wd-mapping.m4 and finally processed with the M4 macro processor.

CHIN Nomenclature Restructuring

CHIN Nomenclature is a thesaurus of museum object types.

The query CHIN-restructure.sparql is the core of an ETL process that converts from an internal (SKOS+custom) to public (SKOS+SKOSXL+schema etc) representaion. It is a complex Federated SPARQL Update of 231 lines, 13.9k chars, 18 prefixes, 50 binds; and makes 18 nodes, 99 triples.. It is described in detail in CHIN-restructure.md, going line by line.

Raw
CHIN-restructure.md

Prefixes

At the beginning the query defines all prefixes it uses, including for individuals like nom:, nomBib:, nomLang:.

The ontology nomo.ttl defines an even wider set of prefixes: when loaded to GraphDB, these become repository namespaces, so they are used in result display and export, which is very useful for the end-user.

Output

Output side of the query:

  • Clears the graph <https://nomenclature.info/nom/thesaurus> The main NOM graph will be overwritten completely
  • Inserts into the same graph a skos:Concept for each group of result rows, according to the semantic mapping model
    • Does not use blank nodes Even subsidiary nodes like schema:StructuredValue (sorting values) have a resolvable URI
    • Uses some fixed values, e.g. dct:type aat:300404672 These are defined in nomo.ttl and commented appropriately, e.g. # inverted form
    • One line can output multiple triples, depending on how many times its variables are bound E.g. schema:image ?imageUri can emit up to 4 images generated from 4 UNION clauses
    • Some variables are "conditional"
      • Example 1: ?topConceptOf is bound to nom: for top-level concepts (PP ensures that), and UNDEF for others The triple skos:topConceptOf ?topConceptOf will be emitted only if the variable is bound
      • Example 2: ?sortEnInvUri is bound only if the concept has an inverted EN label (i.e. ?sortEnInv is bound) When unbound, this whole block (even the triples involving constant values) will not be emitted:
?sortEnInvUri
  a schema:StructuredValue; schema:value ?sortEnInv;
  dct:language nomLang:en; dct:type aat:300404672.

Input

The input part is the WHERE clause and uses a federated query to <repository:nom-internal> This uses GraphDB Internal Federation for speed and security

  • To begin the script iterates over all concepts in the NOM scheme: ?x skos:inScheme nom:
  • Then gets single-valued mandatory props
    • Computes related resolvable URIs values, e.g.:
concat(str(nom:),"level/",str(?level)  # e.g. nom:level/4
concat(str(?x),"-position-en")         # e.g. nom:1234-position-en
  • Then gets single-valued optional props by using optional
  • Then starts a series of union clauses for each multi-valued prop This is very important in order to avoid Cartesian Product, i.e. results rows for every combination of multiple values

The overall structure is:

?x
{
  single valued 1
  ...
  single valued m
} union {
  multi valued 1
  ...
} union {
  multi valued n
}

Description of the multi-valued clauses

prefLabel:

  • Extracts lang(?prefLabel). Converts to lcase for consistency because PP does not support the recommended spelling e.g. en-CA
  • Computes resolvable URIs for the objects of dct:language, skosxl:prefLabel
  • Binds ?prefLabelUriFr, ?prefLabelUriFrCa conditionally on the presence of the respective language By convention, the variable ?UNDEF is never bound
  • Sets up a values table of ?defaultTermContributor ?defaultTermSource per language The respective row is used when ?defaultLang=?prefLabelLang
  • Fetches nomo:Term-Contributor, which could be "CHIN"@es for a label in es Compares to ?prefLabelLang to pick only the right value of nomo:Term-Contributor, which can be multi-valued
    • Uses coalesce(str(?termContributor),?defaultTermContributor) to select either the explicity specified term contributor, or the default term contributor, for that language
    • Looks up this calculated ?termContributorAbbrev as foaf:nick to find the respective ?termContributorUri (which e.g. for "CHIN" is nomAgent:1)
  • Uses similar logic and coalesce() to compute ?termSourceUri No lookup is used here but only concatenation E.g. nomBib:H2E7VWRI is the bibliographic record for Nomenclature, nomBib:HLKYZQAM is the record for AAT, etc
  • If ?prefLabelUriFr (i.e. the language of this prefLabel is fr), maps nomo:French-Term-Gender to a respective value from lexinfo: and uses that as ?frTermGender
  • Does the same for ?frCaTermGender

Inverted-Order-Label:

  • Fetches the language ?invLabelLang
  • Computes the resolvable URIs of that language and of ?invLabelUri (SKOS-XL label)
  • Computes a pair of resolvable URIs ?invLabelUriEn-?prefLabelUriEn or ?invLabelUriEnCa-?prefLabelUriEnCa in order to make the nomo:invertedFormOf label relation between them

altLabel:

  • Fetches the language and computes resolvable URIs for the language
  • Takes the last 6 chars of the MD5 checksum of the label string
  • Computes a label resolvable URI from the concept ID, "-altLabel-", the language and the checksum For example, for the label "kamiit"@iu it computes a resolvable URI like nom:2145-altLabel-iu-f212ae
  • The reason for using a checksum is that there can be multiple altLabels, so it is not enough to use a simpler resolvable URI nom:2145-altLabel-iu as for the previous two labels

hiddenLabel:

  • Computes resolvable URIs using the same logic as for altLabel

definition

  • Fetches the ?definitionLang and computes a language resolvable URI
  • Finds the nomo:Definition-Source matching ?definitionLang, if any E.g. a definition source like "H2E7VWRI"@es will be used for a definition in es
  • Applies coalesce() to use either this ?definitionSource or the default "H2E7VWRI" (which means Nomenclature)
  • Converts it to resolvable URI by prepending the nomBib: namespace
  • Please note that ?definitionUri, ?definitionSourceUri will be bound only if a skos:definition is present Otherwise the specification does not recommend to make parasitic triples involving these resolvable URIs, if the "business payload" (the definition itself) is missing

Images:

  • Fetches up to 4 image resolvable URIs from schema:image, nomo:Image-2, nomo:Image-3, nomo:Image-4
  • Uses the image URL (e.g. https://app.pch.gc.ca/public_info/nomenclature/images/02-00147.jpg) to compute a resolvable URI (e.g. https://nomenclature.info/nom/image/02-00147) for recording triples about that image
    • E.g. in this case the (.*)\\.(.*) regexp part will match (02-00147).(jpg) (parens added for illustration), and the $1 in the replacement will use the base name 02-00147 while omitting the file extension jpg
  • Uses the image resolvable URI to compute an ?imageThumbnail resolvable URI
    • The same regexp part is replaced by $1_t.$2, which inserts _t at the end of the base name
  • Assigns a constant 1..4 to ?imagePosition depending on the union clause
  • Fetches ?imageOriginal, ?imageSource from respective nomo: properties
  • Please note that each of these 4 union clauses bind the same variables Each clause produces one row, so the variables are correlated within the row

Other multivalued props:

  • scopeNote, broader, narrower, exactMatch are merely fetched because they are emitted without modification
  • nomo:Bibliographic-Reference is a Zotero item id like "H2E7VWRI" It is converted to a resolvable URI in the nomBib: namespace

Statistics

Finally, the script records some statistics about execution time. These are useful when doing data work, so the data engineer knows what to expect:

  • Removed 1149220 statements: Update took 40s
  • Added 1149220 statements: Update took 45s

It is curious that deletion takes almost as much time as insertion of new triples. So the first statement (clear graph) takes almost as much as all the rest of the query. This means that most of the time is spent updating GraphDB data structures, whereas fetching and reformatting data is much faster.

Concerns

  • TODO: skos:broaderTransitive: will PP or GDB inference produce that?

Resolved:

  • PP computes skos:topConceptOf for level 1 concepts
  • Canadian lang variants are currently emitted as en-ca, fr-ca (a PP shortcoming) The best practice is to spell the territory component in uppercase: en-CA, fr-CA The ETL script will continue to work if langs in PP are properly cased (en-CA, fr-CA) but will continue to use lowercase lang resolvable URIs. That is a minor cosmetic, so it can remain as is
Raw
CHIN-restructure.sparql
prefix nom: <https://nomenclature.info/nom/>
prefix nomo: <https://nomenclature.info/nom/ontology/>
prefix nomBib: <https://nomenclature.info/nom/biblio/>
prefix nomLang: <https://nomenclature.info/nom/lang/>
prefix aat: <http://vocab.getty.edu/aat/>
prefix cs: <http://purl.org/vocab/changeset/schema#>
prefix dc: <http://purl.org/dc/elements/1.1/>
prefix dct: <http://purl.org/dc/terms/>
prefix dctype: <http://purl.org/dc/dcmitype/>
prefix foaf: <http://xmlns.com/foaf/0.1/>
prefix lexinfo: <http://www.lexinfo.net/ontology/3.0/lexinfo#>
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 schema: <http://schema.org/>
prefix skos: <http://www.w3.org/2004/02/skos/core#>
prefix skosxl: <http://www.w3.org/2008/05/skos-xl#>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
clear silent graph <https://nomenclature.info/nom/thesaurus>;
insert {graph <https://nomenclature.info/nom/thesaurus> {
?x a skos:Concept, schema:CreativeWork;
skos:inScheme nom: ;
schema:mainEntityOfPage ?page ;
schema:isPartOf ?levelUri ;
dc:identifier ?id ;
dct:created ?created ;
dct:modified ?modified ;
skos:notation ?notation ;
skos:topConceptOf ?topConceptOf ;
skos:scopeNote ?scopeNote ;
skos:broader ?broader ;
skos:narrower ?narrower ;
skos:exactMatch ?exactMatch ;
skos:closeMatch ?closeMatch ;
schema:image ?imageUri ; # up to 4
skos:definition ?definitionUri;
skos:prefLabel ?prefLabel; skosxl:prefLabel ?prefLabelUri; schema:name ?prefLabel;
skos:altLabel ?invLabel; skosxl:altLabel ?invLabelUri; schema:alternateName ?invLabel;
skos:altLabel ?altLabel; skosxl:altLabel ?altLabelUri; schema:alternateName ?altLabel;
skos:hiddenLabel ?hiddenLabel; skosxl:hiddenLabel ?hiddenLabelUri;
schema:position ?sortEnUri, ?sortEnInvUri, ?sortFrUri, ?sortEnCaUri, ?sortEnCaInvUri, ?sortFrCaUri;
schema:citation ?bibUri.
?levelUri skos:member ?x.
?sortEnUri a schema:StructuredValue; schema:value ?sortEn; dct:language nomLang:en; dct:type aat:300404671.
?sortEnInvUri a schema:StructuredValue; schema:value ?sortEnInv; dct:language nomLang:en; dct:type aat:300404672.
?sortFrUri a schema:StructuredValue; schema:value ?sortFr; dct:language nomLang:fr; dct:type aat:300404671.
?sortEnCaUri a schema:StructuredValue; schema:value ?sortEnCa; dct:language nomLang:en-ca; dct:type aat:300404671.
?sortEnCaInvUri a schema:StructuredValue; schema:value ?sortEnCaInv; dct:language nomLang:en-ca; dct:type aat:300404672.
?sortFrCaUri a schema:StructuredValue; schema:value ?sortFrCa; dct:language nomLang:fr-ca; dct:type aat:300404671.
?definitionUri a schema:CreativeWork, dctype:Text;
rdf:value ?definition; dct:language ?definitionLangUri; schema:citation ?definitionSourceUri.
?prefLabelUri a skosxl:Label, schema:CreativeWork, dctype:Text; dct:type aat:300404671; # normal form
skosxl:literalForm ?prefLabel; dct:language ?prefLabelLangUri;
dct:contributor ?termContributorUri; schema:citation ?termSourceUri.
?prefLabelUriFr lexinfo:gender ?frGender.
?prefLabelUriFrCa lexinfo:gender ?frCaGender.
?invLabelUri a skosxl:Label, schema:CreativeWork, dctype:Text;
skosxl:literalForm ?invLabel; dct:language ?invLabelLangUri; dct:type aat:300404672. # inverted form
?invLabelUriEn nomo:invertedFormOf ?prefLabelUriEn.
?invLabelUriEnCa nomo:invertedFormOf ?prefLabelUriEnCa.
?altLabelUri a skosxl:Label, schema:CreativeWork, dctype:Text;
skosxl:literalForm ?altLabel; dct:language ?altLabelLangUri.
?hiddenLabelUri a skosxl:Label, schema:CreativeWork, dctype:Text;
skosxl:literalForm ?hiddenLabel; dct:language ?hiddenLabelLangUri.
# up to 4
?imageUri a schema:ImageObject; schema:position ?imagePosition; schema:contentUrl ?image; schema:thumbnailUrl ?imageThumbnail;
schema:creditText ?imageSource; dct:source ?imageOriginal.
}} where {
service <repository:nom-internal> {
?x skos:inScheme nom:
{ # single-valued mandatory props
?x dc:identifier ?id .
?x dct:created ?created .
?x nomo:level ?level .
?x nomo:sortEn ?sortEn .
?x nomo:sortEnInv ?sortEnInv .
?x nomo:sortFr ?sortFr .
bind(iri(concat("https://page.nomenclature.info/parcourir-browse.app?id=",?id)) as ?page)
bind(iri(concat(str(nom:),"level/",str(?level))) as ?levelUri )
bind(iri(concat(str(?x),"-position-en")) as ?sortEnUri )
bind(iri(concat(str(?x),"-position-enInv")) as ?sortEnInvUri )
bind(iri(concat(str(?x),"-position-fr")) as ?sortFrUri )
# single-valued mandatory props that are not yet in the repo, so wrapped as optional
optional {?x nomo:sortEnCa ?sortEnCa
bind(iri(concat(str(?x),"-position-enCa")) as ?sortEnCaUri )}
optional {?x nomo:sortEnCaInv ?sortEnCaInv
bind(iri(concat(str(?x),"-position-enCaInv")) as ?sortEnCaInvUri )}
optional {?x nomo:sortFrCa ?sortFrCa
bind(iri(concat(str(?x),"-position-frCa")) as ?sortFrCaUri )}
# single-valued optional props
optional {?x dct:modified ?modified }
optional {?x skos:notation ?notation }
optional {?x skos:topConceptOf ?topConceptOf }
}
# multi-valued props
union {?x skos:prefLabel ?prefLabel
bind(lcase(lang(?prefLabel)) as ?prefLabelLang )
bind(iri(concat(str(nomLang:),?prefLabelLang)) as ?prefLabelLangUri)
bind(iri(concat(str(?x),"-prefLabel-",?prefLabelLang)) as ?prefLabelUri )
bind(if(?prefLabelLang="fr", ?prefLabelUri,?UNDEF) as ?prefLabelUriFr )
bind(if(?prefLabelLang="fr-ca", ?prefLabelUri,?UNDEF) as ?prefLabelUriFrCa)
optional {
values (?defaultLang ?defaultTermContributor ?defaultTermSource) {
("en" "AASLH" "H2E7VWRI")
("fr" "CHIN" "H2E7VWRI")
("en-ca" "CHIN" "H2E7VWRI")
("fr-ca" "CHIN" "H2E7VWRI")
("iu" "GN" UNDEF )
("iu-latn" "GN" UNDEF )
("es" "GRI" "HLKYZQAM")
("he" "AASLH" "H2E7VWRI")
("he-latn" "AASLH" "H2E7VWRI")
}
filter(?defaultLang=?prefLabelLang)
}
optional {
?x nomo:Term-Contributor ?termContributor
filter(lcase(lang(?termContributor))=?prefLabelLang)
}
bind(coalesce(str(?termContributor),?defaultTermContributor) as ?termContributorAbbrev)
optional {
filter(bound(?termContributorAbbrev))
# This should be present in both "nom-internal" and "nom", see https://gitlab.semantic-web.at/dke/chin/-/issues/54. Don't need SERVICE <repository:nom>
?termContributorUri foaf:nick ?termContributorAbbrev}
optional {
?x nomo:Term-Source ?termSource
filter(lcase(lang(?termSource))=?prefLabelLang)}
bind(iri(concat(str(nomBib:),coalesce(?termSource,?defaultTermSource))) as ?termSourceUri)
optional { # Attributes of ?prefLabelUriFr
filter(bound(?prefLabelUriFr))
optional {
values (?frTermGender ?frGender) {
("f." lexinfo:feminine )
("m." lexinfo:masculine)
}
?x nomo:French-Term-Gender ?frTermGender}}
optional { # Attributes of ?prefLabelUriFrCa
filter(bound(?prefLabelUriFrCa))
optional {
values (?frCaTermGender ?frCaGender) {
("f." lexinfo:feminine )
("m." lexinfo:masculine)
}
?x nomo:French-Canadian-Term-Gender ?frCaTermGender}}}
union {?x nomo:Inverted-Order-Label ?invLabel
bind(lcase(lang(?invLabel)) as ?invLabelLang )
bind(iri(concat(str(nomLang:),?invLabelLang)) as ?invLabelLangUri )
bind(iri(concat(str(?x),"-invLabel-", ?invLabelLang)) as ?invLabelUri )
bind(iri(concat(str(?x),"-prefLabel-",?invLabelLang)) as ?prefLabelUri )
bind(if(?invLabelLang="en", ?invLabelUri, ?UNDEF) as ?invLabelUriEn )
bind(if(?invLabelLang="en", ?prefLabelUri,?UNDEF) as ?prefLabelUriEn )
bind(if(?invLabelLang="en-ca", ?invLabelUri, ?UNDEF) as ?invLabelUriEnCa )
bind(if(?invLabelLang="en-ca", ?prefLabelUri,?UNDEF) as ?prefLabelUriEnCa )}
union {?x skos:altLabel ?altLabel
bind(lcase(lang(?altLabel)) as ?altLabelLang )
bind(iri(concat(str(nomLang:),?altLabelLang)) as ?altLabelLangUri )
bind(md5(str(?altLabel)) as ?md5hash )
bind(substr(?md5hash,strlen(?md5hash)-5) as ?hash ) # take last 6 chars
bind(iri(concat(str(?x),"-altLabel-",?altLabelLang,"-",?hash)) as ?altLabelUri )}
union {?x skos:hiddenLabel ?hiddenLabel
bind(lcase(lang(?hiddenLabel)) as ?hiddenLabelLang )
bind(iri(concat(str(nomLang:),?hiddenLabelLang)) as ?hiddenLabelLangUri )
bind(md5(str(?hiddenLabel)) as ?md5hash )
bind(substr(?md5hash,strlen(?md5hash)-5) as ?hash ) # take last 6 chars
bind(iri(concat(str(?x),"-hiddenLabel-",?hiddenLabelLang,"-",?hash)) as ?hiddenLabelUri )}
union {?x skos:definition ?definition
bind(lcase(lang(?definition)) as ?definitionLang )
bind(iri(concat(str(nomLang:),?definitionLang)) as ?definitionLangUri )
bind(iri(concat(str(?x),"-definition-",?definitionLang)) as ?definitionUri )
optional {
?x nomo:Definition-Source ?definitionSource
filter(lcase(lang(?definitionSource))=?definitionLang)
}
bind(iri(concat(str(nomBib:),coalesce(str(?definitionSource),"H2E7VWRI"))) as ?definitionSourceUri)}
# TODO: refactor the 4 blocks below to 1 block with VALUES
union {?x schema:image ?image
bind(iri(replace(str(?image),"https://app.pch.gc.ca/public_info/nomenclature/images/+(.*)\\.(.*)","https://nomenclature.info/nom/image/$1")) as ?imageUri)
bind(iri(replace(str(?image),"(.*)\\.(.*)","$1_t.$2")) as ?imageThumbnail)
bind(1 as ?imagePosition)
optional {?x nomo:Image-Original-URL ?imageOriginal }
optional {?x nomo:Image-Source ?imageSource }}
union {?x nomo:Image-2 ?image
bind(iri(replace(str(?image),"https://app.pch.gc.ca/public_info/nomenclature/images/+(.*)\\.(.*)","https://nomenclature.info/nom/image/$1")) as ?imageUri)
bind(iri(replace(str(?image),"(.*)\\.(.*)","$1_t.$2")) as ?imageThumbnail)
bind(2 as ?imagePosition)
optional {?x nomo:Image-2-Original-URL ?imageOriginal }
optional {?x nomo:Image-2-Source ?imageSource }}
union {?x nomo:Image-3 ?image
bind(iri(replace(str(?image),"https://app.pch.gc.ca/public_info/nomenclature/images/+(.*)\\.(.*)","https://nomenclature.info/nom/image/$1")) as ?imageUri)
bind(iri(replace(str(?image),"(.*)\\.(.*)","$1_t.$2")) as ?imageThumbnail)
bind(3 as ?imagePosition)
optional {?x nomo:Image-3-Original-URL ?imageOriginal }
optional {?x nomo:Image-3-Source ?imageSource }}
union {?x nomo:Image-4 ?image
bind(iri(replace(str(?image),"https://app.pch.gc.ca/public_info/nomenclature/images/+(.*)\\.(.*)","https://nomenclature.info/nom/image/$1")) as ?imageUri)
bind(iri(replace(str(?image),"(.*)\\.(.*)","$1_t.$2")) as ?imageThumbnail)
bind(4 as ?imagePosition)
optional {?x nomo:Image-4-Original-URL ?imageOriginal }
optional {?x nomo:Image-4-Source ?imageSource }}
union {?x skos:scopeNote ?scopeNote }
union {?x skos:broader ?broader }
union {?x skos:narrower ?narrower }
union {?x skos:exactMatch ?exactMatch }
union {?x skos:closeMatch ?closeMatch }
union {?x nomo:Bibliographic-Reference ?bibKey
bind(iri(concat(str(nomBib:),?bibKey)) as ?bibUri)}
}
};
# Removed 983879 statements. Update took 41s
# Added 983879+30130 statements. Update took 48s
Raw
DnB.sparql
prefix dnb: <http://data.ontotext.com/resource/dnb/>
prefix dnbo: <http://data.ontotext.com/resource/dnbo/>
prefix dct: <http://purl.org/dc/terms/>
prefix owl: <http://www.w3.org/2002/07/owl#>
prefix puml: <http://plantuml.com/ontology#>
prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix rr: <http://www.w3.org/ns/r2rml#>
prefix skos: <http://www.w3.org/2004/02/skos/core#>
prefix sm: <http://www.omg.org/techprocess/ab/SpecificationMetadata/>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix sic: <http://linkedmodel.org/vocab/industry-codes/sic#>
prefix fibo-be-corp-corp: <http://www.omg.org/spec/EDMC-FIBO/BE/Corporations/Corporations/>
prefix fibo-be-fct-fct: <http://www.omg.org/spec/EDMC-FIBO/BE/FunctionalEntities/FunctionalEntities/>
prefix fibo-be-fct-pub: <http://www.omg.org/spec/EDMC-FIBO/BE/FunctionalEntities/Publishers/>
prefix fibo-be-le-cb: <http://www.omg.org/spec/EDMC-FIBO/BE/LegalEntities/CorporateBodies/>
prefix fibo-be-le-fbo: <http://www.omg.org/spec/EDMC-FIBO/BE/LegalEntities/FormalBusinessOrganizations/>
prefix fibo-be-le-lei: <http://www.omg.org/spec/EDMC-FIBO/BE/LegalEntities/LEIEntities/>
prefix fibo-be-le-lp: <http://www.omg.org/spec/EDMC-FIBO/BE/LegalEntities/LegalPersons/>
prefix fibo-be-oac-cctl: <http://www.omg.org/spec/EDMC-FIBO/BE/OwnershipAndControl/CorporateControl/>
prefix fibo-be-oac-cown: <http://www.omg.org/spec/EDMC-FIBO/BE/OwnershipAndControl/CorporateOwnership/>
prefix fibo-be-oac-cpty: <http://www.omg.org/spec/EDMC-FIBO/BE/OwnershipAndControl/ControlParties/>
prefix fibo-be-oac-exec: <http://www.omg.org/spec/EDMC-FIBO/BE/OwnershipAndControl/Executives/>
prefix fibo-be-oac-opty: <http://www.omg.org/spec/EDMC-FIBO/BE/OwnershipAndControl/OwnershipParties/>
prefix fibo-be-plc-plc: <http://www.omg.org/spec/EDMC-FIBO/BE/PrivateLimitedCompanies/PrivateLimitedCompanies/>
prefix fibo-be-ptr-ptr: <http://www.omg.org/spec/EDMC-FIBO/BE/Partnerships/Partnerships/>
prefix fibo-be-sps-sps: <http://www.omg.org/spec/EDMC-FIBO/BE/SoleProprietorships/SoleProprietorships/>
prefix fibo-be-tr-tr: <http://www.omg.org/spec/EDMC-FIBO/BE/Trusts/Trusts/>
prefix fibo-fbc-fct-breg: <http://www.omg.org/spec/EDMC-FIBO/FBC/FunctionalEntities/BusinessRegistries/>
prefix fibo-fbc-fct-fse: <http://www.omg.org/spec/EDMC-FIBO/FBC/FunctionalEntities/FinancialServicesEntities/>
prefix fibo-fbc-fct-mkt: <http://www.omg.org/spec/EDMC-FIBO/FBC/FunctionalEntities/Markets/>
prefix fibo-fbc-fct-ra: <http://www.omg.org/spec/EDMC-FIBO/FBC/FunctionalEntities/RegistrationAuthorities/>
prefix fibo-fbc-fct-rga: <http://www.omg.org/spec/EDMC-FIBO/FBC/FunctionalEntities/RegulatoryAgencies/>
prefix fibo-fbc-fct-usfse: <http://www.omg.org/spec/EDMC-FIBO/FBC/FunctionalEntities/USJurisdiction/USFinancialServicesEntities/>
prefix fibo-fbc-fi-fi: <http://www.omg.org/spec/EDMC-FIBO/FBC/FinancialInstruments/FinancialInstruments/>
prefix fibo-fbc-pas-caa: <http://www.omg.org/spec/EDMC-FIBO/FBC/ProductsAndServices/ClientsAndAccounts/>
prefix fibo-fbc-pas-fpas: <http://www.omg.org/spec/EDMC-FIBO/FBC/ProductsAndServices/FinancialProductsAndServices/>
prefix fibo-fnd-1.1: <http://www.omg.org/spec/EDMC-FIBO/FND/1.1/AboutFND-1.1/>
prefix fibo-fnd-aap-agt: <http://www.omg.org/spec/EDMC-FIBO/FND/AgentsAndPeople/Agents/>
prefix fibo-fnd-aap-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/AgentsAndPeople/AboutAgentsAndPeople/>
prefix fibo-fnd-aap-ppl: <http://www.omg.org/spec/EDMC-FIBO/FND/AgentsAndPeople/People/>
prefix fibo-fnd-acc-4217: <http://www.omg.org/spec/EDMC-FIBO/FND/Accounting/ISO4217-CurrencyCodes/>
prefix fibo-fnd-acc-aeq: <http://www.omg.org/spec/EDMC-FIBO/FND/Accounting/AccountingEquity/>
prefix fibo-fnd-acc-cur: <http://www.omg.org/spec/EDMC-FIBO/FND/Accounting/CurrencyAmount/>
prefix fibo-fnd-acc-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Accounting/AboutAccounting/>
prefix fibo-fnd-agr-agr: <http://www.omg.org/spec/EDMC-FIBO/FND/Agreements/Agreements/>
prefix fibo-fnd-agr-ctr: <http://www.omg.org/spec/EDMC-FIBO/FND/Agreements/Contracts/>
prefix fibo-fnd-agr-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Agreements/AboutAgreements/>
prefix fibo-fnd-arr-arr: <http://www.omg.org/spec/EDMC-FIBO/FND/Arrangements/Arrangements/>
prefix fibo-fnd-arr-cd: <http://www.omg.org/spec/EDMC-FIBO/FND/Arrangements/Codes/>
prefix fibo-fnd-arr-cls: <http://www.omg.org/spec/EDMC-FIBO/FND/Arrangements/ClassificationSchemes/>
prefix fibo-fnd-arr-doc: <http://www.omg.org/spec/EDMC-FIBO/FND/Arrangements/Documents/>
prefix fibo-fnd-arr-id: <http://www.omg.org/spec/EDMC-FIBO/FND/Arrangements/IdentifiersAndIndices/>
prefix fibo-fnd-arr-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Arrangments/AboutArrangements/>
prefix fibo-fnd-dt-bd: <http://www.omg.org/spec/EDMC-FIBO/FND/DatesAndTimes/BusinessDates/>
prefix fibo-fnd-dt-fd: <http://www.omg.org/spec/EDMC-FIBO/FND/DatesAndTimes/FinancialDates/>
prefix fibo-fnd-dt-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/DatesAndTimes/AboutDatesAndTimes/>
prefix fibo-fnd-dt-oc: <http://www.omg.org/spec/EDMC-FIBO/FND/DatesAndTimes/Occurrences/>
prefix fibo-fnd-gao-gl: <http://www.omg.org/spec/EDMC-FIBO/FND/GoalsAndObjectives/Goals/>
prefix fibo-fnd-gao-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/GoalsAndObjectives/AboutGoalsAndObjectives/>
prefix fibo-fnd-gao-obj: <http://www.omg.org/spec/EDMC-FIBO/FND/GoalsAndObjectives/Objectives/>
prefix fibo-fnd-law-cor: <http://www.omg.org/spec/EDMC-FIBO/FND/Law/LegalCore/>
prefix fibo-fnd-law-jur: <http://www.omg.org/spec/EDMC-FIBO/FND/Law/Jurisdiction/>
prefix fibo-fnd-law-lcap: <http://www.omg.org/spec/EDMC-FIBO/FND/Law/LegalCapacity/>
prefix fibo-fnd-law-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Law/AboutLaw/>
prefix fibo-fnd-oac-ctl: <http://www.omg.org/spec/EDMC-FIBO/FND/OwnershipAndControl/Control/>
prefix fibo-fnd-oac-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/OwnershipAndControl/AboutOwnershipAndControl/>
prefix fibo-fnd-oac-oac: <http://www.omg.org/spec/EDMC-FIBO/FND/OwnershipAndControl/OwnershipAndControl/>
prefix fibo-fnd-oac-own: <http://www.omg.org/spec/EDMC-FIBO/FND/OwnershipAndControl/Ownership/>
prefix fibo-fnd-org-fm: <http://www.omg.org/spec/EDMC-FIBO/FND/Organizations/FormalOrganizations/>
prefix fibo-fnd-org-lg: <http://www.omg.org/spec/EDMC-FIBO/FND/Organizations/LegitimateOrganizations/>
prefix fibo-fnd-org-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Organizations/AboutOrganizations/>
prefix fibo-fnd-org-org: <http://www.omg.org/spec/EDMC-FIBO/FND/Organizations/Organizations/>
prefix fibo-fnd-pas-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/ProductsAndServices/AboutProductsAndServices/>
prefix fibo-fnd-pas-pas: <http://www.omg.org/spec/EDMC-FIBO/FND/ProductsAndServices/ProductsAndServices/>
prefix fibo-fnd-pas-psch: <http://www.omg.org/spec/EDMC-FIBO/FND/ProductsAndServices/PaymentsAndSchedules/>
prefix fibo-fnd-plc-adr: <http://www.omg.org/spec/EDMC-FIBO/FND/Places/Addresses/>
prefix fibo-fnd-plc-cty: <http://www.omg.org/spec/EDMC-FIBO/FND/Places/Countries/>
prefix fibo-fnd-plc-fac: <http://www.omg.org/spec/EDMC-FIBO/FND/Places/Facilities/>
prefix fibo-fnd-plc-loc: <http://www.omg.org/spec/EDMC-FIBO/FND/Places/Locations/>
prefix fibo-fnd-plc-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Places/AboutPlaces/>
prefix fibo-fnd-plc-vrt: <http://www.omg.org/spec/EDMC-FIBO/FND/Places/VirtualPlaces/>
prefix fibo-fnd-pty-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Parties/AboutParties/>
prefix fibo-fnd-pty-pty: <http://www.omg.org/spec/EDMC-FIBO/FND/Parties/Parties/>
prefix fibo-fnd-pty-rl: <http://www.omg.org/spec/EDMC-FIBO/FND/Parties/Roles/>
prefix fibo-fnd-qt-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Quantities/AboutQuantities/>
prefix fibo-fnd-qt-qtu: <http://www.omg.org/spec/EDMC-FIBO/FND/Quantities/QuantitiesAndUnits/>
prefix fibo-fnd-rel-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Relations/AboutRelations/>
prefix fibo-fnd-rel-rel: <http://www.omg.org/spec/EDMC-FIBO/FND/Relations/Relations/>
prefix fibo-fnd-utl-alx: <http://www.omg.org/spec/EDMC-FIBO/FND/Utilities/Analytics/>
prefix fibo-fnd-utl-av: <http://www.omg.org/spec/EDMC-FIBO/FND/Utilities/AnnotationVocabulary/>
prefix fibo-fnd-utl-bt: <http://www.omg.org/spec/EDMC-FIBO/FND/Utilities/BusinessFacingTypes/>
prefix fibo-fnd-utl-mod: <http://www.omg.org/spec/EDMC-FIBO/FND/Utilities/AboutUtilities/>
prefix fibo-fnd: <http://www.omg.org/spec/EDMC-FIBO/FND/Foundations/>
prefix fibo-ind-1.0: <http://www.omg.org/spec/EDMC-FIBO/IND/1.0/AboutIND-1.0/>
prefix fibo-ind-ei-ei: <http://www.omg.org/spec/EDMC-FIBO/IND/EconomicIndicators/EconomicIndicators/>
prefix fibo-ind-ei-mod: <http://www.omg.org/spec/EDMC-FIBO/IND/EconomicIndicators/AboutEconomicIndicators/>
prefix fibo-ind-ei-pub: <http://www.omg.org/spec/EDMC-FIBO/IND/EconomicIndicators/EconomicIndicatorPublishers/>
prefix fibo-ind-fx-fx: <http://www.omg.org/spec/EDMC-FIBO/IND/ForeignExchange/ForeignExchange/>
prefix fibo-ind-fx-mod: <http://www.omg.org/spec/EDMC-FIBO/IND/ForeignExchange/AboutForeignExchange/>
prefix fibo-ind-ind-ind: <http://www.omg.org/spec/EDMC-FIBO/IND/Indicators/Indicators/>
prefix fibo-ind-ind-mod: <http://www.omg.org/spec/EDMC-FIBO/IND/Indicators/AboutIndicators/>
prefix fibo-ind-ir-ir: <http://www.omg.org/spec/EDMC-FIBO/IND/InterestRates/InterestRates/>
prefix fibo-ind-ir-mod: <http://www.omg.org/spec/EDMC-FIBO/IND/InterestRates/AboutInterestRates/>
prefix fibo-ind-ir-pub: <http://www.omg.org/spec/EDMC-FIBO/IND/InterestRates/InterestRatePublishers/>
prefix fibo-ind: <http://www.omg.org/spec/EDMC-FIBO/IND/AboutIND/>
prefix lcc-3166-1: <http://www.omg.org/spec/LCC/Countries/ISO3166-1-CountryCodes/>
prefix lcc-cr: <http://www.omg.org/spec/LCC/Countries/CountryRepresentation/>
construct {
?companyUrl
a fibo-fnd-org-fm:FormalOrganization;
fibo-fnd-aap-agt:isIdentifiedBy ?dunsIdUrl, ?nationalIdUrl;
dnbo:formerlyIdentifiedBy ?previousDunsUrl;
fibo-fnd-plc-adr:hasAddress ?phyAddrUrl, ?mailAddrUrl;
dnbo:isOperating ?operating_status_CODE;
dnbo:unreachableByMail ?nixie_IND;
dnbo:unreachableByPhone ?tixie_IND;
dnbo:isDelisted ?delist_IND;
dnbo:favorableBankruptcy ?favorable_BKCY;
dnbo:unfavorableBankruptcy ?unfavorable_BKCY;
dnbo:salesAlert ?sales_ALERT;
dnbo:employeeAlert ?employee_ALERT;
dnbo:executive ?ceoUrl;
fibo-fnd-rel-rel:hasLegalName ?sorted_business_name;
fibo-fnd-rel-rel:hasAlias ?tradestyle_1;
dnbo:register_address_ind ?register_address_IND;
dnbo:countryAccessCode ?country_access_code;
dnbo:phone ?telephone_num;
dnbo:cableTelex ?cable_telex;
dnbo:fax ?fax_num;
dnbo:lineOfBusiness ?lob;
dnbo:primaryLocalActivity ?sicUrlPrim, ?sicExtUrlPrim;
fibo-fnd-rel-rel:isClassifiedBy
?sicUrl1, ?sicUrl2, ?sicUrl3, ?sicUrl4, ?sicUrl5, ?sicUrl6,
?sicExtUrl1, ?sicExtUrl2, ?sicExtUrl3, ?sicExtUrl4, ?sicExtUrl5, ?sicExtUrl6;
fibo-fnd-rel-rel:has ?annualSalesLocalUrl, ?annualSalesUsdUrl, ?netWorthLocalUrl, ?netWorthUsdUrl, ?profitLossLocalUrl, ?profitLossUsdUrl, ?employeesHereUrl, ?employeesTotalUrl;
dnbo:activity_ind ?activity_ind;
dnbo:startDate ?YEAR_started;
dnbo:birthDate ?birth_YEAR;
dnbo:impExpAgent ?imp_exp_agent_code_indUrl;
dnbo:legalStatus ?legal_statusUrl;
dnbo:status ?status_codeUrl;
dnbo:isSubsidiary ?subsidiary_CODE;
dnbo:rating ?rating;
dnbo:full_report_date ?full_report_DATE;
dnbo:last_updt_date ?last_updt_DATE;
dnbo:parent ?hq_par_duns_Url;
dnbo:domesticUltimateAncestor ?dom_ult_duns_Url;
dnbo:globalUltmateAncestor ?gbl_ultduns_Url;
dnbo:globalUltimate_ind ?gbl_ult_IND;
dnbo:globalFamilyMembers ?num_family_members_GLB;
dnbo:globalDiasCode ?global_dias_CODE;
dnbo:globalHierarchyLevel ?global_hierarchy_CODE;
dnbo:familyUpdateDate ?family_update_DATE;
dnbo:update_ind ?update_ind;
dnbo:isManufacturer ?manufac_IND;
dnbo:pers_indc ?pers_INDC;
dnbo:popul_cd ?popul_cd;
dnbo:rec_class_type ?rec_class_type;
dnbo:carr_rt_cd ?carr_rt_cd;
dnbo:nonClassified ?NON_classified;
dnbo:hq_parent_stop_dist_ind ?hq_parent_stop_dist_ind;
dnbo:hq_parent_stop_dist_date ?hq_parent_stop_dist_DATE;
dnbo:report_type ?report_type;
dnbo:case_summ_rpt_type ?case_summ_rpt_type;
dnbo:origin_cd ?origin_cd;
dnbo:credit_line ?credit_line;
dnbo:isMarketableTo ?marketability_IND.
?dunsIdUrl
a fibo-be-le-fbo:OrganizationIdentifier;
fibo-fnd-rel-rel:hasUniqueIdentifier ?duns;
fibo-fnd-rel-rel:isMemberOf ?dunsUrl.
?previousDunsUrl
a fibo-be-le-fbo:OrganizationIdentifier;
fibo-fnd-rel-rel:hasUniqueIdentifier ?previous_duns_new;
fibo-fnd-rel-rel:isMemberOf ?dunsUrl.
?nationalIdUrl
a fibo-be-le-fbo:OrganizationIdentifier;
fibo-fnd-rel-rel:hasUniqueIdentifier ?national_id;
fibo-fnd-rel-rel:isMemberOf ?nationalUrl.
?nationalUrl
a fibo-be-le-fbo:OrganizationIdentificationScheme.
?phyAddrUrl
a fibo-fbc-fct-breg:RegistrationAddress;
dnbo:isAvailable ?available_address_PHY;
fibo-fnd-rel-rel:isClassifiedBy dnb:address-physical;
fibo-fbc-fct-breg:hasAddressLine1 ?phy_addr1;
fibo-fbc-fct-breg:hasAddressLine2 ?phy_addr2;
fibo-fbc-fct-breg:hasPostalCode ?phy_postal_code;
dnbo:hasCity ?phyCityUrl;
dnbo:hasCounty ?phyCountyUrl;
dnbo:hasStateProv ?phyStateProvUrl;
dnbo:hasCountry ?phyCountryUrl;
dnbo:hasContinent ?phyContinentUrl.
?phyCityUrl
a dnbo:City;
fibo-fnd-rel-rel:hasUniqueIdentifier ?phy_city_code;
fibo-fnd-rel-rel:hasName ?phy_city_name;
dnbo:hasCounty ?phyCountyUrl;
dnbo:hasStateProv ?phyStateProvUrl;
dnbo:hasCountry ?phyCountryUrl.
?phyCountyUrl
a dnbo:County;
fibo-fnd-rel-rel:hasUniqueIdentifier ?phy_county_code;
fibo-fnd-rel-rel:hasName ?phy_county_name;
dnbo:hasStateProv ?phyStateProvUrl;
dnbo:hasCountry ?phyCountryUrl.
?phyStateProvUrl
a dnbo:StateProv; # TODO lcc-cr:CountrySubdivision
fibo-fnd-rel-rel:hasUniqueIdentifier ?phy_state_prov_code;
fibo-fnd-rel-rel:hasName ?phy_state_prov;
dnbo:abbrev ?phy_state_prov_abv;
dnbo:hasCountry ?phyCountryUrl. # TODO lcc-cr:isSubdivisionOf, fibo-fnd-rel-rel:isMemberOf
?phyCountryUrl
a dnbo:Country;
fibo-fnd-rel-rel:hasUniqueIdentifier ?phy_country_code;
fibo-fnd-rel-rel:hasName ?phy_country_name;
dnbo:hasContinent ?phyContinentUrl.
?phyContinentUrl
a dnbo:Continent;
fibo-fnd-rel-rel:hasUniqueIdentifier ?phy_continent_code.
# TODO no continent name
?mailAddrUrl
a fibo-fbc-fct-breg:RegistrationAddress;
dnbo:isAvailable ?available_address_MAIL;
fibo-fnd-rel-rel:isClassifiedBy dnb:address-mailing;
fibo-fbc-fct-breg:hasAddressLine1 ?mail_addr1;
fibo-fbc-fct-breg:hasAddressLine2 ?mail_addr2;
fibo-fbc-fct-breg:hasPostalCode ?mail_postal_code;
dnbo:hasCity ?mailCityUrl;
dnbo:hasCounty ?mailCountyUrl;
dnbo:hasStateProv ?mailStateProvUrl;
dnbo:hasCountry ?mailCountryUrl;
dnbo:hasContinent ?mailContinentUrl.
?mailCityUrl
a dnbo:City;
fibo-fnd-rel-rel:hasUniqueIdentifier ?mail_city_code;
fibo-fnd-rel-rel:hasName ?mail_city_name;
dnbo:hasCounty ?mailCountyUrl;
dnbo:hasStateProv ?mailStateProvUrl;
dnbo:hasCountry ?mailCountryUrl.
?mailCountyUrl
a dnbo:County;
fibo-fnd-rel-rel:hasUniqueIdentifier ?mail_county_code;
fibo-fnd-rel-rel:hasName ?mail_county_name;
dnbo:hasStateProv ?mailStateProvUrl;
dnbo:hasCountry ?mailCountryUrl.
?mailStateProvUrl
a dnbo:StateProv; # TODO lcc-cr:CountrySubdivision
fibo-fnd-rel-rel:hasUniqueIdentifier ?mail_state_prov_code;
fibo-fnd-rel-rel:hasName ?mail_state_prov;
dnbo:abbrev ?mail_state_prov_abv;
dnbo:hasCountry ?mailCountryUrl. # TODO lcc-cr:isSubdivisionOf, fibo-fnd-rel-rel:isMemberOf
?mailCountryUrl
a dnbo:Country;
fibo-fnd-rel-rel:hasUniqueIdentifier ?mail_country_code;
fibo-fnd-rel-rel:hasName ?mail_country_name;
dnbo:hasContinent ?mailContinentUrl.
?mailContinentUrl
a dnbo:Continent;
fibo-fnd-rel-rel:hasUniqueIdentifier ?mail_continent_code.
# TODO no continent name
?ceoUrl a dnbo:Executive;
dnbo:name ?ceo_name;
dnbo:title ?ceo_title.
# SIC extension codes
?sicExtUrl1 a skos:Concept, fibo-fnd-arr-cls:IndustrySectorClassifier;
skos:inScheme ?sicExtUrl; fibo-fnd-rel-rel:isMemberOf ?sicExtUrl;
skos:broader ?sicUrl1; sic:code ?sic_with_ext1.
?sicExtUrl2 a skos:Concept, fibo-fnd-arr-cls:IndustrySectorClassifier;
skos:inScheme ?sicExtUrl; fibo-fnd-rel-rel:isMemberOf ?sicExtUrl;
skos:broader ?sicUrl2; sic:code ?sic_with_ext2.
?sicExtUrl3 a skos:Concept, fibo-fnd-arr-cls:IndustrySectorClassifier;
skos:inScheme ?sicExtUrl; fibo-fnd-rel-rel:isMemberOf ?sicExtUrl;
skos:broader ?sicUrl3; sic:code ?sic_with_ext3.
?sicExtUrl4 a skos:Concept, fibo-fnd-arr-cls:IndustrySectorClassifier;
skos:inScheme ?sicExtUrl; fibo-fnd-rel-rel:isMemberOf ?sicExtUrl;
skos:broader ?sicUrl4; sic:code ?sic_with_ext4.
?sicExtUrl5 a skos:Concept, fibo-fnd-arr-cls:IndustrySectorClassifier;
skos:inScheme ?sicExtUrl; fibo-fnd-rel-rel:isMemberOf ?sicExtUrl;
skos:broader ?sicUrl5; sic:code ?sic_with_ext5.
?sicExtUrl6 a skos:Concept, fibo-fnd-arr-cls:IndustrySectorClassifier;
skos:inScheme ?sicExtUrl; fibo-fnd-rel-rel:isMemberOf ?sicExtUrl;
skos:broader ?sicUrl6; sic:code ?sic_with_ext6.
?sicExtUrlPrim a skos:Concept, fibo-fnd-arr-cls:IndustrySectorClassifier;
skos:inScheme ?sicExtUrl; fibo-fnd-rel-rel:isMemberOf ?sicExtUrl;
skos:broader ?sicUrlPrim; sic:code ?sic_with_extPrim.
# amounts
?annualSalesLocalUrl a fibo-fnd-acc-cur:AmountOfMoney;
fibo-fnd-acc-cur:hasAmount ?annual_sales_local_CURR;
fibo-fnd-qt-qtu:hasQuantityKind dnb:quantity-annualSales;
fibo-fnd-acc-cur:hasBaseMoneyUnit ?currencyLocal;
dnbo:estimated ?annual_sales_estUrl;
dnbo:asOfDate ?financial_figures_DATE.
?annualSalesUsdUrl a fibo-fnd-acc-cur:AmountOfMoney;
fibo-fnd-acc-cur:hasAmount ?annual_sales_us_DOLLARS;
fibo-fnd-qt-qtu:hasQuantityKind dnb:quantity-annualSales;
fibo-fnd-acc-cur:hasBaseMoneyUnit ?currencyUsd;
dnbo:estimated ?annual_sales_estUrl;
dnbo:asOfDate ?financial_figures_DATE.
?netWorthLocalUrl a fibo-fnd-acc-cur:AmountOfMoney;
fibo-fnd-acc-cur:hasAmount ?networth_local_CURR;
fibo-fnd-qt-qtu:hasQuantityKind dnb:quantity-netWorth;
fibo-fnd-acc-cur:hasBaseMoneyUnit ?currencyLocal;
dnbo:estimated ?networth_estUrl;
dnbo:asOfDate ?financial_figures_DATE;
dnbo:sign ?networth_local_curr_signUrl.
?netWorthUsdUrl a fibo-fnd-acc-cur:AmountOfMoney;
fibo-fnd-acc-cur:hasAmount ?networth_us_DOLLARS;
fibo-fnd-qt-qtu:hasQuantityKind dnb:quantity-netWorth;
fibo-fnd-acc-cur:hasBaseMoneyUnit ?currencyUsd;
dnbo:estimated ?networth_estUrl;
dnbo:asOfDate ?financial_figures_DATE;
dnbo:sign ?networth_us_dollars_signUrl.
?profitLossLocalUrl a fibo-fnd-acc-cur:AmountOfMoney;
fibo-fnd-acc-cur:hasAmount ?profit_loss_local_CURR;
fibo-fnd-qt-qtu:hasQuantityKind dnb:quantity-profitLoss;
fibo-fnd-acc-cur:hasBaseMoneyUnit ?currencyLocal;
dnbo:estimated ?profit_loss_estUrl;
dnbo:asOfDate ?financial_figures_DATE;
dnbo:sign ?profit_loss_local_curr_signUrl.
?profitLossUsdUrl a fibo-fnd-acc-cur:AmountOfMoney;
fibo-fnd-acc-cur:hasAmount ?profit_loss_us_DOLLARS;
fibo-fnd-qt-qtu:hasQuantityKind dnb:quantity-profitLoss;
fibo-fnd-acc-cur:hasBaseMoneyUnit ?currencyUsd;
dnbo:estimated ?profit_loss_estUrl;
dnbo:asOfDate ?financial_figures_DATE;
dnbo:sign ?profit_loss_us_dollars_signUrl.
?employeesHereUrl a fibo-fnd-qt-qtu:QuantityValue;
fibo-fnd-qt-qtu:hasNumericValue ?employees_HERE;
fibo-fnd-qt-qtu:hasQuantityKind dnb:quantity-employeesHere;
dnbo:arePrincipalsIncluded ?principals_incl_IND.
?employeesTotalUrl a fibo-fnd-qt-qtu:QuantityValue;
fibo-fnd-qt-qtu:hasNumericValue ?employees_TOTAL;
fibo-fnd-qt-qtu:hasQuantityKind dnb:quantity-employeesTotal;
dnbo:arePrincipalsIncluded ?principals_incl_IND.
}
where {
# values ?undef {undef}
bind(1+"undef" as ?undef)
bind(uri(concat(str(dnb:),?duns)) as ?companyUrl)
bind(uri(concat(str(?companyUrl),"/id/duns")) as ?dunsIdUrl)
bind(uri(concat(str(dnb:),"id/duns")) as ?dunsUrl)
bind(if(bound(?previous_duns_new),uri(concat(str(dnb:),"id/duns/previous")),?undef) as ?previousDunsUrl)
bind(uri(concat(str(?companyUrl),"/id/",?national_id_type)) as ?nationalIdUrl)
bind(uri(concat(str(dnb:),"id/",?national_id_type)) as ?nationalUrl)
# Physical address
bind(uri(concat(str(dnb:),"country/",?phy_country_code,"/city/", ?phy_city_code )) as ?phyCityUrl)
bind(uri(concat(str(dnb:),"country/",?phy_country_code,"/county/", ?phy_county_code )) as ?phyCountyUrl)
bind(uri(concat(str(dnb:),"country/",?phy_country_code,"/stateProv/",?phy_state_prov_code)) as ?phyStateProvUrl)
bind(uri(concat(str(dnb:), "country/", ?phy_country_code )) as ?phyCountryUrl)
bind(uri(concat(str(dnb:), "continent/", ?phy_continent_code )) as ?phyContinentUrl)
bind(coalesce(?phy_addr1, ?phy_addr2, ?phy_postal_code, ?phyCityUrl, ?phycountyUrl, ?phyStateProvUrl, ?phyCountryUrl, ?phyContinentUrl) as ?hasPhyAddr)
bind(if(bound(?hasPhyAddr),uri(concat(str(?companyUrl),"/address/physical")),?undef) as ?phyAddrUrl)
# Mailing address
bind(uri(concat(str(dnb:),"country/",?mail_country_code,"/city/", ?mail_city_code )) as ?mailCityUrl)
bind(uri(concat(str(dnb:),"country/",?mail_country_code,"/county/", ?mail_county_code )) as ?mailCountyUrl)
bind(uri(concat(str(dnb:),"country/",?mail_country_code,"/stateProv/",?mail_state_prov_code)) as ?mailStateProvUrl)
bind(uri(concat(str(dnb:), "country/", ?mail_country_code )) as ?mailCountryUrl)
bind(uri(concat(str(dnb:), "continent/", ?mail_continent_code )) as ?mailContinentUrl)
bind(coalesce(?mail_addr1, ?mail_addr2, ?mail_postal_code, ?mailCityUrl, ?mailcountyUrl, ?mailStateProvUrl, ?mailCountryUrl, ?mailContinentUrl) as ?hasMailAddr)
bind(if(bound(?hasMailAddr),uri(concat(str(?companyUrl),"/address/mailing")),?undef) as ?mailAddrUrl)
bind(if(bound(?ceo_name),uri(concat(str(?companyUrl),"/executive/0")),?undef) as ?ceoUrl)
# SIC industrial classification
bind(uri(concat(str(sic:),"Code-",?us_1987_sics1)) as ?sicUrl1)
bind(uri(concat(str(sic:),"Code-",?us_1987_sics2)) as ?sicUrl2)
bind(uri(concat(str(sic:),"Code-",?us_1987_sics3)) as ?sicUrl3)
bind(uri(concat(str(sic:),"Code-",?us_1987_sics4)) as ?sicUrl4)
bind(uri(concat(str(sic:),"Code-",?us_1987_sics5)) as ?sicUrl5)
bind(uri(concat(str(sic:),"Code-",?us_1987_sics6)) as ?sicUrl6)
bind(uri(concat(str(sic:),"Code-",?prim_local_activity_sic)) as ?sicUrlPrim)
# SIC+ext
bind(concat(?us_1987_sics1,?us_1987_sic_ext1) as ?sic_with_ext1)
bind(concat(?us_1987_sics2,?us_1987_sic_ext2) as ?sic_with_ext2)
bind(concat(?us_1987_sics3,?us_1987_sic_ext3) as ?sic_with_ext3)
bind(concat(?us_1987_sics4,?us_1987_sic_ext4) as ?sic_with_ext4)
bind(concat(?us_1987_sics5,?us_1987_sic_ext5) as ?sic_with_ext5)
bind(concat(?us_1987_sics6,?us_1987_sic_ext6) as ?sic_with_ext6)
bind(concat(?prim_local_activity_sic,?prim_local_activity_sic_ext) as ?sic_with_extPrim)
bind(uri(concat(str(dnb:),"sicExt/")) as ?sicExtUrl)
bind(uri(concat(str(?sicExtUrl),?sic_with_ext1)) as ?sicExtUrl1)
bind(uri(concat(str(?sicExtUrl),?sic_with_ext2)) as ?sicExtUrl2)
bind(uri(concat(str(?sicExtUrl),?sic_with_ext3)) as ?sicExtUrl3)
bind(uri(concat(str(?sicExtUrl),?sic_with_ext4)) as ?sicExtUrl4)
bind(uri(concat(str(?sicExtUrl),?sic_with_ext5)) as ?sicExtUrl5)
bind(uri(concat(str(?sicExtUrl),?sic_with_ext6)) as ?sicExtUrl6)
bind(uri(concat(str(?sicExtUrl),?sic_with_extPrim)) as ?sicExtUrlPrim)
# quasi-Boolean
bind(if(?subsidiary_code ="3",true,if(?subsidiary_code ="0",false,?undef)) as ?subsidiary_CODE)
# Booleans
bind(if(?available_address_mail="Y",true,if(?available_address_mail="N",false,?undef)) as ?available_address_MAIL)
bind(if(?available_address_phy ="Y",true,if(?available_address_phy ="N",false,?undef)) as ?available_address_PHY)
bind(if(?delist_ind ="Y",true,if(?delist_ind ="N",false,?undef)) as ?delist_IND)
bind(if(?employee_alert ="Y",true,if(?employee_alert ="N",false,?undef)) as ?employee_ALERT)
bind(if(?favorable_bkcy ="Y",true,if(?favorable_bkcy ="N",false,?undef)) as ?favorable_BKCY)
bind(if(?gbl_ult_ind ="Y",true,if(?gbl_ult_ind ="N",false,?undef)) as ?gbl_ult_IND)
bind(if(?manufac_ind ="Y",true,if(?manufac_ind ="N",false,?undef)) as ?manufac_IND)
bind(if(?marketability_ind ="Y",true,if(?marketability_ind ="N",false,?undef)) as ?marketability_IND)
bind(if(?nixie_ind ="Y",true,if(?nixie_ind ="N",false,?undef)) as ?nixie_IND)
bind(if(?non_classified ="Y",true,if(?non_classified ="N",false,?undef)) as ?NON_classified)
bind(if(?operating_status_code ="Y",true,if(?operating_status_code ="N",false,?undef)) as ?operating_status_CODE)
bind(if(?pers_indc ="Y",true,if(?pers_indc ="N",false,?undef)) as ?pers_INDC)
bind(if(?principals_incl_ind ="Y",true,if(?principals_incl_ind ="N",false,?undef)) as ?principals_incl_IND)
bind(if(?register_address_ind ="Y",true,if(?register_address_ind ="N",false,?undef)) as ?register_address_IND)
bind(if(?sales_alert ="Y",true,if(?sales_alert ="N",false,?undef)) as ?sales_ALERT)
bind(if(?tixie_ind ="Y",true,if(?tixie_ind ="N",false,?undef)) as ?tixie_IND)
bind(if(?unfavorable_bkcy ="Y",true,if(?unfavorable_bkcy ="N",false,?undef)) as ?unfavorable_BKCY)
# Dates can be 4 digits (gYear), 6 digits (gYearMonth), 8 digits (xsd:date)
bind(if(regex(?last_updt_date,"0000$"),strdt(replace(?last_updt_date,"^(....)0000$","$1"),xsd:gYear),
if(regex(?last_updt_date,"00$"), strdt(replace(?last_updt_date,"^(....)(..)00$","$1-$2"),xsd:gYearMonth),
strdt(replace(?last_updt_date,"^(....)(..)(..)$","$1-$2-$3"),xsd:date))) as ?last_updt_DATE)
bind(if(regex(?full_report_date,"0000$"),strdt(replace(?full_report_date,"^(....)0000$","$1"),xsd:gYear),
if(regex(?full_report_date,"00$"), strdt(replace(?full_report_date,"^(....)(..)00$","$1-$2"),xsd:gYearMonth),
strdt(replace(?full_report_date,"^(....)(..)(..)$","$1-$2-$3"),xsd:date))) as ?full_report_DATE)
bind(if(regex(?family_update_date,"0000$"),strdt(replace(?family_update_date,"^(....)0000$","$1"),xsd:gYear),
if(regex(?family_update_date,"00$"), strdt(replace(?family_update_date,"^(....)(..)00$","$1-$2"),xsd:gYearMonth),
strdt(replace(?family_update_date,"^(....)(..)(..)$","$1-$2-$3"),xsd:date))) as ?family_update_DATE)
bind(if(regex(?financial_figures_date,"0000$"),strdt(replace(?financial_figures_date,"^(....)0000$","$1"),xsd:gYear),
if(regex(?financial_figures_date,"00$"), strdt(replace(?financial_figures_date,"^(....)(..)00$","$1-$2"),xsd:gYearMonth),
strdt(replace(?financial_figures_date,"^(....)(..)(..)$","$1-$2-$3"),xsd:date))) as ?financial_figures_DATE)
bind(if(regex(?hq_parent_stop_dist_date,"0000$"),strdt(replace(?hq_parent_stop_dist_date,"^(....)0000$","$1"),xsd:gYear),
if(regex(?hq_parent_stop_dist_date,"00$"), strdt(replace(?hq_parent_stop_dist_date,"^(....)(..)00$","$1-$2"),xsd:gYearMonth),
strdt(replace(?hq_parent_stop_dist_date,"^(....)(..)(..)$","$1-$2-$3"),xsd:date))) as ?hq_parent_stop_dist_DATE)
# Years
bind(strdt(concat(substr("0000",1,4-strlen(?year_started)),?year_started),xsd:gYear) as ?YEAR_started)
bind(strdt(concat(substr("0000",1,4-strlen(?birth_year )),?birth_year ),xsd:gYear) as ?birth_YEAR )
# DUNS numbers. Don't make self-links (MAYBE revise this)
bind(if(?duns=?hq_par_duns_new ,?undef,uri(concat(str(dnb:),?hq_par_duns_new ))) as ?hq_par_duns_Url )
bind(if(?duns=?dom_ult_duns_new,?undef,uri(concat(str(dnb:),?dom_ult_duns_new))) as ?dom_ult_duns_Url)
bind(if(?duns=?gbl_ultduns_new ,?undef,uri(concat(str(dnb:),?gbl_ultduns_new ))) as ?gbl_ultduns_Url )
# convert to xsd:integer
bind(strdt(?global_dias_code , xsd:integer) as ?global_dias_CODE )
bind(strdt(?global_hierarchy_code , xsd:integer) as ?global_hierarchy_CODE )
bind(strdt(?num_family_members_glb , xsd:integer) as ?num_family_members_GLB )
# amounts to xsd:integer
bind(strdt(?annual_sales_local_curr, xsd:integer) as ?annual_sales_local_CURR )
bind(strdt(?annual_sales_us_dollars, xsd:integer) as ?annual_sales_us_DOLLARS )
bind(strdt(?networth_local_curr , xsd:integer) as ?networth_local_CURR )
bind(strdt(?networth_us_dollars , xsd:integer) as ?networth_us_DOLLARS )
bind(strdt(?profit_loss_local_curr , xsd:integer) as ?profit_loss_local_CURR )
bind(strdt(?profit_loss_us_dollars , xsd:integer) as ?profit_loss_us_DOLLARS )
bind(strdt(?employees_here , xsd:integer) as ?employees_HERE )
bind(strdt(?employees_total , xsd:integer) as ?employees_TOTAL )
# estimate URLs
bind(uri(concat(str(dnb:),"estimate/",?annual_sales_ind)) as ?annual_sales_estUrl)
bind(uri(concat(str(dnb:),"estimate/",?networth_ind )) as ?networth_estUrl )
bind(uri(concat(str(dnb:),"estimate/",?loss_indicator )) as ?profit_loss_estUrl )
bind(uri(concat(str(dnb:),"estimate/",?emp_here_ind )) as ?emp_here_estUrl )
bind(uri(concat(str(dnb:),"estimate/",?emp_total_ind )) as ?emp_total_estUrl )
# sign URLs
bind(uri(concat(str(dnb:),"sign/",?networth_local_curr_sign )) as ?networth_local_curr_signUrl )
bind(uri(concat(str(dnb:),"sign/",?networth_us_dollars_sign )) as ?networth_us_dollars_signUrl )
bind(uri(concat(str(dnb:),"sign/",?profit_loss_local_curr_sign)) as ?profit_loss_local_curr_signUrl)
bind(uri(concat(str(dnb:),"sign/",?profit_loss_us_dollars_sign)) as ?profit_loss_us_dollars_signUrl)
# currency URLs
bind(uri(concat(str(dnb:),"currency/20")) as ?currencyUsd)
bind(if(?currency_code="20",?undef,uri(concat(str(dnb:),"currency/", ?currency_code))) as ?currencyLocal)
# amount URLs
bind(if(bound(?annual_sales_local_CURR)&&bound(?currencyLocal),uri(concat(str(?companyUrl),"/annualSalesLocal")),?undef) as ?annualSalesLocalUrl)
bind(if(bound(?annual_sales_us_DOLLARS) ,uri(concat(str(?companyUrl),"/annualSalesUsd" )),?undef) as ?annualSalesUsdUrl )
bind(if(bound(?networth_local_CURR )&&bound(?currencyLocal),uri(concat(str(?companyUrl),"/netWorthLocal" )),?undef) as ?netWorthLocalUrl )
bind(if(bound(?networth_us_DOLLARS ) ,uri(concat(str(?companyUrl),"/netWorthUsd" )),?undef) as ?netWorthUsdUrl )
bind(if(bound(?profit_loss_local_CURR )&&bound(?currencyLocal),uri(concat(str(?companyUrl),"/profitLossLocal" )),?undef) as ?profitLossLocalUrl )
bind(if(bound(?profit_loss_us_DOLLARS ) ,uri(concat(str(?companyUrl),"/profitLossUsd" )),?undef) as ?profitLossUsdUrl )
bind(if(bound(?employees_HERE ) ,uri(concat(str(?companyUrl),"/employeesHere" )),?undef) as ?employeesHereUrl )
bind(if(bound(?employees_TOTAL ) ,uri(concat(str(?companyUrl),"/employeesTotal" )),?undef) as ?employeesTotalUrl )
# other URLs
bind(uri(concat(str(dnb:),"legalStatus/", ?legal_status )) as ?legal_statusUrl)
bind(uri(concat(str(dnb:),"status/", ?status_code )) as ?status_codeUrl)
bind(if(?imp_exp_agent_code_ind="G",?undef,uri(concat(str(dnb:),"impExpAgent/",?imp_exp_agent_code_ind))) as ?imp_exp_agent_code_indUrl)
}
Raw
wd-mapping.org

Wikidata to Company Graph Mapping

Approach

DEM-690: WD-CG tasks.

The overall approach is described in DEM-623 comment (with some additions below):

  • Use the precomputed list of IDs wd-id-org.txt
  • Use entity resolution to request the entities one by one, eg Volkswagen: 
    curl -L http://www.wikidata.org/entity/Q156578.ttl
  • Store these 267k entities to about 1000 folders split out by digit, eg “Q1/5/6/`
  • Use SPARQL “rules” to transform (map) to the desired CG format. There are many tools/libraries that can do that, eg:
    • java: rdf4j or jena in a loop. This is probably the best way since we want to avoid the overhead of starting up the tool
    • cmd: jena “update”, as used in rdf2rml/bin/rdf2rml.ru and rdf2rml.sh
    • perl: RDF SPARQL module
    • N3 Rules (Euler, EulerGUI or EYE: see paper, tutorial, and talk)
  • Write out turtles, or directly load to GraphDB
  • Fetch WD ids of related items, such as persons and universities. This can be done by grepping the org entities and using uniq from the command line, or a simple query in GraphDB.
  • Execute similar SPARQL rules to transform those related items

Literate Programming

The complex part is how to describe the needed SPARQL rules clearly. We attempt to make this mapping specification:

  • Executable, by defining code fragments and macros that generate code
  • Readable, by interspersing explanations with code, and laying out code in an easy to read (pedagogical) way, rather than how the computer wants it.

In particular, for each field we write the input (WHERE) and output (INSERT or CONSTRUCT) next to each other, and then dispatch them to the appropriate part of the query.

We also make the spec modular by:

  • Writing only business-specific rules in the input & output, then adding extra template code to take care of the technical decisions described in the prev section.
  • Writing the query to apply to either Organization or Person root entity, because they are 60% the same. See sec *Person or Organization

M4 Macros

We use the M4 macro processor to piece the query together. This is a superior choice to:
  • cpp, which cannot output multi-line macros.
  • org-mode noweb/tangle which is very slow (see how-to-speed-up-org-babel-tangle) as it opens a separate file for every piece of code (and a separate shell if used; one can spare this with a prop :header-args:sh: :session *sh*)

Compared to the above tools, M4 has a very consistent macro definition and invocation mechanism, but a few quirks that one should know to be able to read the code below:

  • dnl (delete to newline) is used to avoid a newline, and to make macro comments. We use it before macro definition to describe the params
  • ~`…’~ is used to quote some text so it’s not macro-expanded right away
  • (..., ...) is used to group macro params, which are denoted as $1, $2 in the body
  • define(name,body) is used to define macros. Both are usually quoted, so they are not macro-expanded before defining the macro
  • ifelse(expr,test,true,false) emits true if expr=test, otherwise emits false

We also define some macros: some stolen from m4 examples, others borrowed and adapted:

  • nargs(...) returns the number of its arguments
  • foreach(x, (item_1, item_2, ..., item_n), text) iterates x over the items, substituting it in text
  • foreach_sep(x, (item_1, item_2, ..., item_n), text, sep): same but separates the instantiations of text using sep
  • join(sep, args): output args separated by sep

IMPORTANT: divert(-1) starts discarding text (the newlines between macro definitions). We MUST emit divert before the actual text to be output!

divert(-1)

define(`nargs', `$#')

dnl foreach(x, (item_1, item_2, ..., item_n), text)
define(`foreach', `pushdef(`$1')_foreach($@)popdef(`$1')')
define(`_arg1', `$1')
define(`_foreach', `ifelse(`$2', `()', `',
  `define(`$1', _arg1$2)$3`'$0(`$1', (shift$2), `$3')')')

dnl foreach_sep(x, (item_1, item_2, ..., item_n), text, sep)
define(`foreach_sep', `pushdef(`$1')_foreach_sep($@)popdef(`$1')')
define(`_arg1', `$1')
define(`_foreach_sep', `ifelse(`$2', `()', `',
  `define(`$1', _arg1$2)ifelse(nargs$2,1,`$3',`$3$4')`'$0(`$1', (shift$2), `$3', `$4')')')

dnl join(sep, args)
define(`join',
`ifelse(`$#', `2', ``$2'',
  `ifelse(`$2', `', `', ``$2'_')$0(`$1', shift(shift($@)))')')
define(`_join',
`ifelse(`$#$2', `2', `',
  `ifelse(`$2', `', `', ``$1$2'')$0(`$1', shift(shift($@)))')')

An important characteristic of M4 is that it freely mixes M4 macros and target-language keywords. A good example is LANG_ONE in sec *Label Languages. In the excerpt below, the text underlined with ^ is SPARQL, the rest is M4:

define(`LANGS',`en,de,fr,es,it,zh')
bind(coalesce(foreach_sep(`LANG',(LANGS),`$3`_'LANG',`,')) as $3)')
^^^^^^^^^^^^^^                               ^        ^  ^^^^^

The M4 “variable” LANG iterates over LANGS. So if $3=descr, this is expanded to:

bind(coalesce(?descr_en,?descr_de,?descr_fr,?descr_es,?descr_it,?descr_zh)) as ?descr)

SPARQL Mechanics

On the output size, we can use two SPARQL mechanisms:
  • CONSTRUCT, to make triples directly.
  • Update (INSERT), to put the transformed triples to a separate graph, then output only that graph. We have two options:
    • One big query
    • Many simple queries that are run in sequence

For the time being, we use CONSTRUCT since it outputs directly the triples we need and has the same speed.

On the input size, the overall WHERE structure is as follows:

  • A root clause for the main (single-value) props of the item
  • Many UNION clauses for the additional (optional/multi-valued) props

This is required for multi-value props to avoid Cartesian explosion:

  • Both CONSTRUCT and INSERT are instantiated for all solutions of the WHERE, so a Cartesian product will cause many duplicate (unnecessary) output actions
  • Even props that are supposed to be single-value may be multi-value in some cases, so we do it for all.

Because UNION clauses (or indeed any bracketed pattern) doesn’t see outer bindings (see sparql-12#103), we need to repeat the INPUT_root in every UNION clause.

Variable Naming

Variables holding URIs:
  • ?it: input (WD) item (Organization or Person)
  • ?item: output (CG) item
  • ?identXXX: adms:Identifier node for system XXX (eg ?identDBP for <identifier/DBP>) or for WD prop XXX (eg both ?identP6634 and ?identP4264 add Identifiers in <identifier/LI>)

Variables holding literals:

  • ?_xxx: string representing the URI ?xxx
  • ?WD: WD identifier
  • ?idXXX: identifier value for system XXX or WD prop XXX
  • ?labelXXX: label of the item or a sub-resource: single selected (see LANG_ONE()) or muiltiple (see LANG_ALL())

Mapping

Root

The total input file is a Dataset that refers to the input item ?it: that’s how we find it.
  • $1 is extra data to fetch about the dataset, which is used in OUT_root
  • Then we compute the output item (?item) and its URL as string (?_item) to use as prefix of subsidiary URLs. We smash these binds on a single line because they’re repeated in every UNION clause, see next.
dnl $1=extra data to fetch about dataset
define(`INPUT_root',`[a schema:Dataset; schema:about ?it; $1]
  bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)')

Person or Organization

We define a macro to be able to generate 2 different queries: one for persons, another for organizations. (We’re currently working on organizations, so those branches are more developed) 
dnl Set to `' to handle organizations. Set to 1 to handle persons
define(`IS_PERSON',`') 

dnl $1=text if person, $2=text if organization
define(`IF_PERSON',`ifelse(IS_PERSON,1,$1,$2)')

Union Clauses

Every input clause repeats INPUT_root and is emitted in a UNION, where IN_root is the first union clause. 
dnl $1=clause to append to UNION
define(`UNION',`union {INPUT_root()
  $1}')

Root Data

We obtain the following data about the root:
  • Get ?modified from the dataset
  • Compute the WD id
  • Single-value fields such as prefName and descr (but not label, which is multi-valued)
define(`IN_root',
`{INPUT_root(`schema:dateModified ?modified',`bind(strafter(str(?it),str(wd:)) as ?WD)')
  IN_prefName
  IN_descr
  IN_isStateOwned
  IN_isStartup
  IN_isPubliclyTraded
  IN_country
  IN_dates
}')

Then we output it like this:

define(`OUT_root',`
  ?item a IF_PERSON(schema:Person, rov:RegisteredOrganization); skos:notation ?WD; dct:modified ?modified; dct:source <dataset/WD>.
  OUT_prefName
  OUT_descr
  OUT_isStateOwned
  OUT_isStartup
  OUT_isPubliclyTraded
  OUT_country
  OUT_dates
')

Note: in a previous version we computed the type dynamically: Person (mapped from wd:Q5 Human) or Organization (everything else)

bind(if(exists{?it wdt:P31 wd:Q5}, schema:Person, rov:RegisteredOrganization) as ?type)

TODO: update <dataset/WD> in CG-static, describing the date when we crawled it and more accurate volumetrics.

Identifiers

WD is a central authority hub, i.e. a veritable trove of identifiers. This section obtains a large number of identifiers UNION, and the next two get a couple more that are specific. All identifiers are output together.
CodeWD propdescriptionurlTemplateNote
emailP968Email addressmailto:{}
phoneP1329Phone number
websiteP856Official website{}
blogP1581Official blog{}2 map the same
blogP1019Web feed URL{}2 map the same
CBP2087Crunchbase person id/website slughttps://www.crunchbase.com/person/{}2 map the same
CBP2088Crunchbase organization id/website slughttps://www.crunchbase.com/organization/{}2 map the same
Crossref/funderP3153Crossref funder ID
FBP2013Facebook id/website slughttps://www.facebook.com/{}
GLEIP1278Global Legal Entity Identifier
GRIDP2427Global Research Identifier Databasehttps://www.grid.ac/institutes/{}
GS1P3193GS1 Manufacturer code
GitHubP2037GitHub username
ISNIP213International Standard Name Identifierhttp://www.isni.org/{}
InstagramP2003Instagram username
JPP3225JP Corporate Number
KGP646Freebase/Google Knowledge Graphhttp://g.co/kg/{}
LIP2035LinkedIn person website URL (deprecated){}3 map the same
LIP4264LinkedIn organization id/website slughttps://www.linkedin.com/company/{}3 map the same
LIP6634LinkedIn person website id/slughttps://www.linkedin.com/in/{}/3 map the same
MAGP6366Microsoft Academic Graphhttps://academic.microsoft.com/#/detail/{}http://ma-graph.org/entity/{}
OCORPP1320OpenCorporates id
RORP6782Research Organization Registryhttps://ror.org/{}
SubredditP3984Subreddit
TR/permidP3347Thomson Reuters Open PermIDhttps://permid.org/1-{}
TWP2002Twitter id/usernamehttps://twitter.com/{}
US-IRSP1297US IRS Employer Identification
US-SEC-CIKP5531US SEC CIK
VIAFP214Virtual International Authority Filehttps://viaf.org/viaf/{}
YouTubeP2397YouTube channel ID
  • We’ve added all these to CG-static.ttl (TODO: add the urlTemplate for several of them)
  • We use only current identifiers (direct claims wdt:) to avoid eg historic logos and historic phones/emails that are not valid anymore.
  • As per DEM-720, multiple occurrences (eg several VIAF ids or images) are emitted in the same node
  • In some cases several WD props map to the same identifer system. Then the urlTemplate differs between props, but we always normalize to the same value (full URL) by using different prefixes.
  • Because of SOML restrictions, all identifiers are emitted as strings, even those that are URLs.
define(`IN_IDENTIFIER',`UNION(
  values (?prop ?idSystem ?idPrefix) {
    (wdt:P2087 "CB"              "https://www.crunchbase.com/person/")
    (wdt:P2088 "CB"              "https://www.crunchbase.com/organization/")
    (wdt:P3153 "Crossref/funder" "")
    (wdt:P2013 "FB"              "https://www.facebook.com/")
    (wdt:P1278 "GLEI"            "")
    (wdt:P2427 "GRID"            "")
    (wdt:P3193 "GS1"             "")
    (wdt:P2037 "GitHub"          "")
    (wdt:P213  "ISNI"            "")
    (wdt:P2003 "Instagram"       "")
    (wdt:P3225 "JP"              "")
    (wdt:P646  "KG"              "")
    (wdt:P2035 "LI"              "")
    (wdt:P4264 "LI"              "https://www.linkedin.com/company/")
    (wdt:P6634 "LI"              "https://www.linkedin.com/in/")
    (wdt:P6366 "MAG"             "")
    (wdt:P1312 "OCORP"           "")
    (wdt:P6782 "ROR"             "")
    (wdt:P3984 "Subreddit"       "")
    (wdt:P3347 "TR/permid"       "")
    (wdt:P2002 "TW"              "https://twitter.com/")
    (wdt:P1297 "US-IRS"          "")
    (wdt:P5531 "US-SEC-CIK"      "")
    (wdt:P214  "VIAF"            "")
    (wdt:P2397 "YouTube"         "")
    (wdt:P1019 "blog"            "")
    (wdt:P1581 "blog"            "")
    (wdt:P968  "email"           "")
    (wdt:P1329 "phone"           "")
    (ID_image  "image"           "")
    (wdt:P856  "website"         "")
  }
  ?it ?prop ?id.
  bind(concat(?idPrefix,str(?id)) as ?idValue)
  bind(uri(concat(?_item,"/",?idSystem)) as ?ident)
  bind(uri(concat("identifier/",?idSystem)) as ?identSystem))')

All Identifiers are output together:

define(`OUT_IDENTIFIER',`  ?item adms:identifier ?ident.
  ?ident a adms:Identifier; skos:notation ?idValue; cg:identRaw ?idValue; dct:isPartOf ?identSystem; dct:source <dataset/WD>.')

Wikidata Identifier

?WD is already computed, so it’s merely re-bound: 
define(`IN_identWD',`UNION(
  bind(?WD as ?idValue)
  bind(uri(concat(?_item,"/WD")) as ?ident)
  bind(<identifier/WD> as ?identSystem))')

DBpedia Identifier

?DBP id is computed from the English Wikipedia link. 
define(`IN_identDBP',`UNION(
  ?enwp a schema:Article; schema:about ?it; schema:isPartOf <https://en.wikipedia.org/>.
  bind(strafter(str(?enwp), "https://en.wikipedia.org/wiki/") as ?idValue)
  bind(uri(concat(?_item,"/DBP")) as ?ident)
  bind(<identifier/DBP> as ?identSystem))')

Image

DEM-717 Which WD image prop should we use?
  • For people, we use P18 Image, eg see images of Ferdinand Piëch and Elon Musk. Quite often there are several.
    • There’s also P109 Signature, but we don’t use it
  • For companies, we use P154 Logo Image, eg see logos of Volkswagen Group and The Boring Company
    • Often they also have P18 Image, but that usually shows the headquarters or a plant (eg VW Werk Altes Heizkraftwerk WB edit.jpg)
    • Sometimes it shows the logo of a product eg Volkswagen.jpg but it’s not marked as a logo

Please note that WD image props have a URL like this http://commons.wikimedia.org/wiki/Special:FilePath/VWAG-Logo.svg, which is a redirect to the actual image URL eg https://upload.wikimedia.org/wikipedia/commons/9/90/VWAG-Logo.svg.

define(`ID_image',`IF_PERSON(wdt:P18,wdt:P154)')

Label Languages

WD includes labels and descriptions in a gazillion languages. We want to limit to a smaller list. 
define(`LANGS',`en,de,fr,es,it,zh')
define(`LANGS_QUOTED',`foreach_sep(`LANG',(LANGS),`"LANG"',`,')')

LANG_ALL() fetches the above lang variants of a resource.

  • Since WD includes numerous lang variants (eg en-us vs en-gb) with the same value, we use exact match rather than langMatches()
  • We use OPTIONAL so you can put this together with the resource in one clause
dnl $1=resource, $2=prop, $3=var
define(`LANG_ALL',`optional{$1 $2 $3. filter(lang($3) in (LANGS_QUOTED))}')

LANG_ONE fetches one language in the above preference order.

  • We first emit a number of optional clauses “indexed” by lang, eg optional{?it rdfs:label ?lang_en. filter(lang(?lang_en)="en")}
  • Then we coalesce() all these per-lang vars
dnl $1=resource, $2=prop, $3=var
define(`LANG_ONE',
`foreach(`LANG',(LANGS),`
  optional{$1 $2 $3`_'LANG. filter(lang($3`_'LANG)="LANG")}')
  bind(coalesce(foreach_sep(`LANG',(LANGS),`$3`_'LANG',`,')) as $3)')

Labels and Descriptions

We output all pref labels as rov:legalName, all alt labels as skos:altLabel one selected label as skos:prefLabel, one selected description as dct:description 
define(`IN_label',    `UNION(LANG_ALL(?it,rdfs:label,?label))')
define(`IN_altLabel', `UNION(LANG_ALL(?it,skos:altLabel,?altLabel))')
define(`IN_prefName', `LANG_ONE(?it,rdfs:label,?prefName)')
define(`IN_descr',    `LANG_ONE(?it,schema:description,?descr)')
define(`OUT_label',   `  ?item rov:legalName ?label.')
define(`OUT_altLabel',`  ?item skos:altLabel ?altLabel.')
define(`OUT_prefName',`?item skos:prefLabel ?prefName.')
define(`OUT_descr',   `?item dct:description ?descr.')

Concepts

WD items refer to a number of concepts (type, legal form, industry, occupation) that we harvest from the items. We extract single label & description (usually “en”) and put them in a respective ConceptScheme. 
dnl $1=in WD prop, $2=in var, $3=out var
define(`IN_concept',`UNION(
  ?it wdt:$1 $2.
  bind(uri(concat(str(WD:),strafter(str($2),str(wd:)))) as $3)
  LANG_ONE($2,rdfs:label,$3Label)
  LANG_ONE($2,schema:description,$3Descr))')
dnl $1=out var, $2=out prop, $3=concept scheme
define(`OUT_concept',`  ?item $2 $1.
  $1 a skos:Concept; skos:inScheme <$3>; skos:prefLabel $1Label; skos:description $1Descr; dct:source <dataset/WD>.')

define(`IN_type',      `IN_concept(P31,?typ,?type)')
define(`OUT_type',     `OUT_concept(?type,cg:orgType,WD/organization/type)')
define(`IN_legalForm', `IN_concept(P1454,?legal,?legalForm)')
define(`OUT_legalForm',`OUT_concept(?legalForm,rov:orgType,WD/organization/legalForm)')
define(`IN_industry',  `IN_concept(P452,?ind,?industry)')
define(`OUT_industry', `OUT_concept(?industry,rov:orgActivity,WD/industry)')

Dates and Events

Dates

We map two simple dates (we assume they are single-valued). 
dnl $1= ?xxxDate : fetch ?xxxDateTime then convert it to ?xxxDate
define(`DT2DATE',`$1Time bind(strdt(strbefore(str($1Time),"T"),xsd:date) as $1)')

define(`IN_dates',`optional {?it wdt:P571 DT2DATE(?foundedDate)}
  optional {?it wdt:P576 DT2DATE(?dissolvedDate)}')
define(`OUT_dates',`?item schema:dateFounded ?foundedDate.
  ?item schema:dateDissolved ?dissolvedDate.')

Here we truncate a full ~”2009-06-26T00:00:00Z”^^xsd:dateTime~ with fake timestamp to ~”2009-06-26”^^xsd:date~. Extra tasks:

  • TODO: A date ~”2009-01-01”^^xsd:date~ likely has fake month-day. Use the WD “date precision” to calculate the precise truncation, and emit gYearMonth or gYear. This also applies to annual Observations (eg annual turnover).
    • However, this may make faceting & search in GDB harder.
  • TODO: After DEM-727 is worked out, some Significant Events should contribute to these dates

TODO Events

DEM-727

Exchange Listing

Analysis: ./README.md#exchange-listing. We use two statements (events) and their relevant props:
  • P414 Stock Exchange
  • P793 Significant event Q185142 “initial public offering” (which sets the more specific ?exchListingType <..../ipo>)

We mark the target of P414 as cg:StockExchange because we haven’t checked whether they all have an appropriate WD type (to use that to set the CG type).

define(`IN_exchange',`UNION(
  {?it p:P793 ?exchStmt. ?exchStmt ps:P793 wd:Q185142 bind(<transaction/exchangeListing/ipo> as ?exchListingType)}
        union {?it p:P414 ?exchStmt.                  bind(<transaction/exchangeListing>     as ?exchListingType)}
  optional {?exchStmt (ps:P414|pq:P414) ?exch.
    bind(strafter(str(?exch),str(wd:)) as ?exchId)
    bind(uri(concat(str(WD:),?exchId)) as ?exchange)}
  bind(if(exists{?it wdt:P414 ?exch},true,?NULL) as ?isCurrent)
  optional {?exchStmt pq:P249 ?stockSymbol}
  optional {?exchStmt (pq:P580|pq:P585|pq:P571) DT2DATE(?exchStartDate)}
  optional {?exchStmt (pq:P582|pq:P576)         DT2DATE(?exchEndDate)}
  bind(uri(concat(?_item,"/exchangeListing",
    if(bound(?exchId),concat("/",?exchId),""),
    if(bound(?exchStartDate),concat("/",str(?exchStartDate)),""))) as ?exchListing))')
define(`OUT_exchange',`
  ?exchange a cg:StockExchange; dct:source <dataset/WD>.
  ?exchListing a cg:ExchangeListing; cg:agent ?item; cg:exchange ?exchange; cg:stockSymbol ?stockSymbol;
    cg:date ?exchStartDate; cg:dateEnd ?exchEndDate; cg:transactionType ?exchListingType; cg:isCurrent ?isCurrent; dct:source <dataset/WD>.')

TODO Stock Index

Should we map stock (exchange) indexes? They are modeled with ps:P361 Part Of; pq:P580 Start Time. Examples:

Org Relations

Analysis: ./README.md#org-relations. We make a values table similar to the relations table in the analysis:
  • We use one row per prop
  • We render the prop as string, and then bind it to the 3 needed namespaces: wdt: (direct claim), p:/ps: (indirect statement).
  • We use the indirect statement ?stmt to fetch data, and the direct claim only to determine ?isCurrent
define(`IN_org_rels',`UNION(
  values (?relProp ?relType ?relKind) {
    ("P112"  "founder"    "forward"  )
    ("P127"  "owner"      "forward"  )
    ("P1365" "replaces"   "forward"  )
    ("P155"  "follows"    "forward"  )
    ("P1951" "investor"   "forward"  )
    ("P749"  "parent"     "forward"  )
    ("P807"  "forkedFrom" "forward"  )
    ("P1366" "replaces"   "inverse"  )
    ("P156"  "follows"    "inverse"  )
    ("P1830" "owner"      "inverse"  )
    ("P199"  "divisionOf" "inverse"  )
    ("P355"  "parent"     "inverse"  )
    ("P2652" "partner"    "symmetric")
    ("P1889" "different"  "symmetric")
  }
  bind(uri(concat(str(wdt:), ?relProp)) as ?wdtRelProp)
  bind(uri(concat(str(p:),   ?relProp)) as ?pRelProp  )
  bind(uri(concat(str(ps:),  ?relProp)) as ?psRelProp )
  ?it ?pRelProp ?stmt. ?stmt ?psRelProp ?other.
  bind(if(exists{?it ?wdtRelProp ?other},true,?NULL) as ?isCurrent)
  optional {?stmt (pq:P580|pq:P585|pq:P571) DT2DATE(?relStartDate)}
  optional {?stmt (pq:P582|pq:P576)         DT2DATE(?relEndDate)}
  optional {?stmt pq:P1107 ?proportion}

We use ?relKind to indicate the kind of relation (relative to the current item ?it). We want the relation URL to be deterministic so it collapses to the same node no matter whether a forward or inverse prop is found in WD:

  • minor/type/major for asymmetric relations (i.e. it/type/other for forward and other/type/it for inverse)
  • min/type/max for symmetric relations (where min/max are determined by string comparison)

So URLs are allocated depending on relation ?relKind:

  • forward: ag1=?it, ag2=?other, agentMinor=ag1, agentMajor=ag2
  • inverse: ag1=?other, ag2=?it, agentMinor=ag1, agentMajor=ag2
  • symmetric: ag1=min(?it,?other), ag2=max(?it,?other), agent=ag1 & ag2
  bind(strafter(str(?it),   str(wd:)) as ?itId)
  bind(strafter(str(?other),str(wd:)) as ?otherId)
  bind(if(?relKind="forward",?itId,   if(?relKind="inverse",?otherId,if(?itId < ?otherId,?itId,?otherId))) as ?ag1)
  bind(if(?relKind="forward",?otherId,if(?relKind="inverse",?itId,   if(?itId < ?otherId,?otherId,?itId))) as ?ag2)
  bind(uri(concat(str(WD:),?ag1,"/",?relType,"/",?ag2)) as ?orgRel)
  bind(uri(concat("relation/",?relType)) as ?relationType)
  bind(if(?relKind!="symmetric",uri(concat(str(WD:),?ag1)),?NULL) as ?agentMinor)
  bind(if(?relKind!="symmetric",uri(concat(str(WD:),?ag2)),?NULL) as ?agentMajor)
  bind(if(?relKind ="symmetric",uri(concat(str(WD:),?ag1)),?NULL) as ?agent1)
  bind(if(?relKind ="symmetric",uri(concat(str(WD:),?ag2)),?NULL) as ?agent2)
)')

Finally we output the data:

define(`OUT_org_rels',`
  ?orgRel a cg:OrganizationRelation; cg:relationType ?relationType; cg:agentMajor ?agentMajor; cg:agentMinor ?agentMinor; cg:agent ?agent1, ?agent2;
     cg:proportion ?proportion; cg:date ?relStartDate; cg:dateEnd ?relEndDate; cg:isCurrent ?isCurrent; dct:source <dataset/WD>.')

Business Flags

isStateOwned

We set this flag for organizations that have type Q17990971 Public Enterprise, Q2659904 Government Organization, or a subclass thereof.
  • There were various other types scattered around: Central bank, State publisher, transmission system operator, state development institution, national development bank, state railroad, State Agricultural Farm, Regional health authority…
  • Rather than keeping a long list, it’s best to add a second superclass (one of the above two). Example: Q7603018 State Agricultural Farm is now both Farming Business and State-Owned Enterprise.

We can’t check for a superclass in the entity RDF so we have to use a long list from ./types/wd-types-stateOwned.csv. The item may have multiple types: we want to set this flag if one of them indicates state-owned (i.e. a disjunction), and want it to be single-valued. So we compute it independently of “Type”:

define(`IN_isStateOwned',`
  bind(exists{?it wdt:P31 ?type.
    filter(?type in (wd:Q10402097,wd:Q11424358,wd:Q11424434,wd:Q1148315,wd:Q12782082,wd:Q1430364,wd:Q1519138,wd:Q15811601,wd:Q16917889,wd:Q16968466,wd:Q1780371,wd:Q17990971,wd:Q1802186,wd:Q19390308,wd:Q19753703,wd:Q2324556,wd:Q2324813,wd:Q2324835,wd:Q2326317,wd:Q251927,wd:Q270791,wd:Q273615,wd:Q286228,wd:Q2946028,wd:Q2990262,wd:Q30590861,wd:Q3356161,wd:Q3591583,wd:Q485016,wd:Q48814874,wd:Q48815411,wd:Q48815413,wd:Q48851989,wd:Q48851992,wd:Q5189488,wd:Q5589413,wd:Q5834296,wd:Q59603261,wd:Q59603354,wd:Q61819734,wd:Q652812,wd:Q6579042,wd:Q66344,wd:Q6972269,wd:Q7248445,wd:Q7257721,wd:Q7374547,wd:Q7603018,wd:Q1021586,wd:Q10402097,wd:Q10547399,wd:Q10855383,wd:Q11361056,wd:Q11424358,wd:Q1148315,wd:Q11549282,wd:Q11900271,wd:Q12782082,wd:Q1303046,wd:Q1389604,wd:Q1443829,wd:Q15750017,wd:Q15811601,wd:Q16917889,wd:Q16931297,wd:Q16968466,wd:Q1780371,wd:Q17990971,wd:Q1802186,wd:Q19391573,wd:Q2324556,wd:Q2324797,wd:Q2324813,wd:Q2324835,wd:Q2326317,wd:Q24936905,wd:Q2531989,wd:Q270791,wd:Q273615,wd:Q2946028,wd:Q30590861,wd:Q3356161,wd:Q3591583,wd:Q40890107,wd:Q4571771,wd:Q48814874,wd:Q48815411,wd:Q48815413,wd:Q48851989,wd:Q48851992,wd:Q5189477,wd:Q5189488,wd:Q54804183,wd:Q5589413,wd:Q5834296,wd:Q59603261,wd:Q59603354,wd:Q61056688,wd:Q61819734,wd:Q6428421,wd:Q66344,wd:Q6972269,wd:Q7603018,wd:Q835400))}
      as ?isStateOwned)')
define(`OUT_isStateOwned',`?item ebg:isStateOwned ?isStateOwned.')

isPubliclyTraded

We use two criteria (see ./README.md#publicly-traded-criteria)
  • Has link to stock exchange, OR
  • Is one of 6 types that indicate “public company”)
define(`IN_isPubliclyTraded',`bind(exists{?it wdt:P414 []}
  || exists{?it wdt:P31 ?type.
       filter(?type in (wd:Q891723,wd:Q5225895,wd:Q498289,wd:Q166280,wd:Q13641190,wd:Q20746571))}
         as ?isPubliclyTraded)')
define(`OUT_isPubliclyTraded',`?item ebg:isPubliclyTraded ?isPubliclyTraded.')

isStartup

There are two types that indicate startup: Q129238 startup, Q20983877 unicorn: 
define(`IN_isStartup',`bind(exists{?it wdt:P31 ?type.
    filter(?type in (wd:Q129238,wd:Q20983877))}
      as ?isStartup)')
define(`OUT_isStartup',`?item ebg:isStartup ?isStartup.')

Location

Analysis in ./README.md#location.

Country

Analysis in ./README.md#country. We use undivert() (file inclusion) to read a table that maps WD countries to Geonames. 
define(`IN_country',`optional {
    values (?country ?gnCountry) {
undivert(`location/WD-countries-geoname.txt')  }
    ?it wdt:P17 ?country.
    bind(uri(concat(str(GN:),?gnCountry,"/")) as ?geonameCountry)
    bind(uri(concat(str(GN:),?gnCountry,"/WD")) as ?identCountry)
    bind(strafter(str(?country),str(wd:)) as ?idCountry)}')
define(`OUT_country',`?item cg:country ?geonameCountry.
  ?geonameCountry adms:identifier ?identCountry.
  ?identCountry a adms:Identifier; skos:notation ?idCountry; dct:source <dataset/WD>.')

Address

Analysis in ./README.md#address. We map address fields as follows: 
define(`IN_address',`UNION(?it p:P159 ?locStmt.
  ?locStmt a wikibase:BestRank.
  bind(uri(concat(?_item,"/address")) as ?address)
  optional {?locStmt ps:P159 ?location
    LANG_ONE(?location,skos:prefLabel,?locationLabel)}
  optional {?locStmt pq:P6375 ?fullAddress}
  optional {?locStmt pq:P969 ?street}
  optional {?locStmt pq:P669 ?str
    LANG_ONE(?str,skos:prefLabel,?strLabel)}
  optional {?locStmt pq:P670 ?number}
  optional {?locStmt pq:P4856 ?bldg bind(concat("Bldg.",?bldg) as ?building)}
  optional {?locStmt pq:P5423 ?floor}
  optional {?locStmt pq:P281 ?postCode}
  optional {?locStmt pqv:P625 ?geo.
    bind(uri(concat(?_item,"/address/geo")) as ?geoCoord)
    ?geo wikibase:geoLatitude ?lat; wikibase:geoLongitude ?long.
    bind(xsd:decimal(?lat) as ?latitude)
    bind(xsd:decimal(?long) as ?longitude)
    optional {?geo wikibase:geoPrecision ?prec
      bind(xsd:decimal(?prec*111000.0) as ?precision)}})')
define(`OUT_address',`
  ?item org:hasRegisteredSite ?address.
  ?address a locn:Address; cg:addressType <address/headquarters>; wdcg:location ?location; 
    locn:fullAddress ?fullAddress; locn:thoroughfare ?street, ?strLabel; 
    locn:locatorDesignator ?number, ?building, ?floor; locn:postCode ?postCode; schema:geo ?geoCoord.
  ?location a wdcg:Location; gn:name ?locationLabel.
  ?geoCoord a schema:GeoCoordinates; schema:latitude ?latitude; schema:longitude ?longitude; cg:geoPrecision ?precision.')

Remaining tasks:

  • DEM-735 geoPrecision to geoResolution
  • DEM-736 check WD address mapping
  • TODO: bind(... as ?geoCoord) fails on Jena (doesn’t bind anything). Currently we don’t care since it works on rdf4j (GDB). But if issue sparql-12#103 is “fixed” on rdf4j, it might break.

Observations

WD includes relevant observations about organizations, persons and others. To map them (see sec *Observation Mapping), we make a mapping table with 3 props (each uses Pnnn internally):
  • ?obsProp: ~”Pnnn”~, used in the URL
  • ?obsProp1: p: to reach the indirect statement
  • ?obsProp2: psv: to reach the structured value
dnl $1=VALUES body made of multiple OBS
define(`OBSERVATIONS',`values (?obsProp ?obsProp1 ?obsProp2 ?obsType) {$1}')
dnl $1=prop $2=type
define(`OBS',`("$1" p:$1 psv:$1 <observation/$2>)')

There are different kinds of observations:

  • The following (17k) are decimal, converted to millions: 
    define(`OBS_DECIMAL',`OBSERVATIONS(`
    OBS(P2403, totalAssets)
    OBS(P2218, netWorth)
    OBS(P2137, totalEquity)
    OBS(P2226, marketCapitalization)
    OBS(P2284, valuation) # price
    OBS(P2139, totalRevenue)
    OBS(P3362, operatingIncome)
    OBS(P2295, netProfit)')')
  • The following (10k) are converted to integer: 
    define(`OBS_INTEGER',`OBSERVATIONS(`
    OBS(P1128, employees)')')
  • The following are not present in WD:
    • sharePrice
    • fundingRounds
    • fundingTotal
    • fundingDateLast
    • investmentCount
    • investmentTotal

Filtering Observation Values

A few secretive organizations include useless info: 
select ?x ?xLabel ?prop {
  values ?prop {wdt:P2403 wdt:P2218 wdt:P2137 wdt:P2226 wdt:P2139 wdt:P3362 wdt:P2295 wdt:P2284 wdt:P1128}
  ?x ?prop ?y
  filter(!isliteral(?y))
  service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

For example:

  • budget: unknown value; sourcing circumstances: classified
  • employees: unknown value; point in time: unknown value; sourcing circumstances: classified

A few items have a variation on this topic, with a bit of useful info, but we ignore it:

  • Q1005549 Federal Agency for Public Safety Digital Radio (Germany): employees: unknown value; minimum value: 300
  • Q1195162 Der Landbote (newspaper): price: no value; day of week: Wednesday (as opposed to the other days of the week, when it costs an “unknown value” but not zero)

These are “no value” and “unknown value” blank nodes. We filter to isliteral() values only, which happens automatically by the conversions in sec *Observation Mapping

Observation Currency

DEM-732 The model was changed to add cg:currency to decimal observations.

Volkswagen Group has “total assets: 409,732,000,000 euro; point in time 31 December 2016”. This is expressed at 3 levels:

# level 1 (direct claim = wdt:) : amount
wd:Q156578 wdt:P2403 "+409732000000"^^xsd:decimal ;

# level 2 (indirect statement = p: and ps:) : amount and point in time
wd:Q156578 p:P2403 s:Q156578-F7ADF2EA-06A7-467B-AF98-1F849482BD33 .
s:Q156578-F7ADF2EA-06A7-467B-AF98-1F849482BD33 a wikibase:Statement, wikibase:BestRank ;
	wikibase:rank wikibase:PreferredRank ;
	ps:P2403 "+409732000000"^^xsd:decimal ;
	psv:P2403 v:834c21cc073e8d4fff4b8463e05f6271 ;
	pq:P585 "2016-12-31T00:00:00Z"^^xsd:dateTime ;
	pqv:P585 v:80e747dd2faf3349d5cebc85e661f0c1 ;
	prov:wasDerivedFrom ref:f33f976444b390bc5887812aa37006250e713ca1 .

# level 3 (structured value = psv: and pqv:) : currency and precision/range
v:834c21cc073e8d4fff4b8463e05f6271 a wikibase:QuantityValue ;
	wikibase:quantityAmount "+409732000000"^^xsd:decimal ;
	wikibase:quantityUnit <http://www.wikidata.org/entity/Q4916> . # EUR

This query shows that WD uses over 94 currencies for relevant observations. To understand the double brackets (two blank nodes), look at the turtle examples above.

select ?curr ?currLabel ?isoCode (count(*) as ?c) {
  values ?prop {psv:P2403 psv:P2218 psv:P2137 psv:P2226 psv:P2139 psv:P3362 psv:P2295 psv:P2284}
  [?prop [wikibase:quantityUnit ?curr]].
  optional {?curr wdt:P498 ?isoCode}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,fr". }
} group by ?curr ?currLabel ?isoCode order by desc(?c)

There are some units without currency code, but an examination shows they are not important:

  • 1 (unit): this is valid for an integer (eg number of employees) but not quite for the decimal observations
  • penny, shilling, as, gunea, centimo, kopeck: some historic or unimportant observation
  • Simoleon (I guess this is historic)
  • Brazilian real (Q10366257): historic, 1 current real is 2.75 quintillion of this old real
  • Indian anna: historic

To avoid the need to query WD online, we make a mapping table. This uses undivert() which inserts the contents of a file:

define(`CURRENCIES',`values (?curr ?currCode) {
undivert(`WD-currencies.txt')}')

Observation Mapping

The input uses levels 2 and 3 from sec *Observation Currency.
  • It fetches the optional ?obsDate (P585 point in time) and if bound, uses it in the ?obs URL
  • quantityAmount is fetched as ?amt
  • Then the extra clause $2 processes this amount, and if decimal: fetches the currency ?curr and ?currCode
dnl $1=INTEGER/DECIMAL, $2=extra clause
define(`IN_OBS',`UNION(OBS_$1
  ?it ?obsProp1 ?statement.
  optional {?statement pq:P585 DT2DATE(?obsDate)}
  bind(uri(concat(
    ?_item, "/observation/", ?obsProp,
    if(bound(?obsDate),concat("/",str(?obsDate)),""))) as ?obs)
  ?statement ?obsProp2 [wikibase:quantityAmount ?amt].
  $2
)')
define(`IN_OBS_INTEGER',`IN_OBS(INTEGER,
  bind(xsd:integer(?amt) as ?obsInteger))')
define(`IN_OBS_DECIMAL',`IN_OBS(DECIMAL,
  bind(?amt/1000000.0 as ?obsDecimal)
  ?statement ?obsProp2 [wikibase:quantityUnit ?curr].
  ``CURRENCIES'')')

We had to quote ``CURRENCIES''~ twice so the ~undivert() (file inclusion) inside it is executed late, i.e. in the right spot.

The output is simple. Some of the vars below may be unset, then the triple will be skipped:

  • We don’t need two cases because ?obsInteger and ?obsDecimal are exclusive
  • ?currCode will be set in the case ?obsInteger is set
  • ?obsDate is optional
define(`OUT_OBS',
`  ?obs a qb:Observation; qb:dataSet ?item; cg:observationType ?obsType; cg:valueInteger ?obsInteger; cg:valueDecimal ?obsDecimal; cg:currency ?currCode; cg:date ?obsDate; dct:source <dataset/WD>.')

All Inputs/Outputs

This is the concatenation of individual inputs & outputs

define(`IN_all',
`IN_root
IN_identWD
IN_identDBP
IN_IDENTIFIER
IN_label
IN_altLabel
IN_type
IN_legalForm
IN_industry
IN_OBS_INTEGER
IN_OBS_DECIMAL
IN_address
IN_exchange
IN_org_rels')

define(`OUT_all',
`OUT_root
OUT_IDENTIFIER
OUT_label
OUT_altLabel
OUT_type
OUT_legalForm
OUT_industry
OUT_OBS
OUT_address
OUT_exchange
OUT_org_rels')

Prefixes

SPARQL Body

  • divert without arguments resumes output (we had it blocked to skip newlines that are part of macro definitions
  • undivert() reads in the prefixes
divert
undivert(`prefixes.rq')
construct {OUT_all
}

where {IN_all
}
Raw
wd-mapping.sparql
base <https://company-graph.ontotext.com/resource/>
# CG Individuals
prefix CG: <https://company-graph.ontotext.com/resource/CG/> # Company Graph
prefix CB: <https://company-graph.ontotext.com/resource/CB/> # CrunchBase
prefix PDL: <https://company-graph.ontotext.com/resource/PDL/> # People Data Labs
prefix DBP: <https://company-graph.ontotext.com/resource/DBP/> # DBpedia
prefix GN: <http://sws.geonames.org/> # Geonames
prefix WD: <https://company-graph.ontotext.com/resource/WD/> # WikiData
# CG Ontologies
prefix cg: <https://company-graph.ontotext.com/resource/CG/ontology/>
prefix wdcg: <https://company-graph.ontotext.com/resource/WD/ontology/>
prefix adms: <http://www.w3.org/ns/adms#>
prefix apf: <http://jena.apache.org/ARQ/property#>
prefix bo: <http://www.ontotext.com/business-object/>
prefix dbo: <http://dbpedia.org/ontology/>
prefix dc: <http://purl.org/dc/elements/1.1/>
prefix dcat: <http://www.w3.org/ns/dcat#>
prefix dct: <http://purl.org/dc/terms/>
prefix ebg: <http://data.businessgraph.io/ontology#>
prefix gn: <http://www.geonames.org/ontology#>
prefix locn: <http://www.w3.org/ns/locn#>
prefix org: <http://www.w3.org/ns/org#>
prefix owl: <http://www.w3.org/2002/07/owl#>
prefix person: <http://www.w3.org/ns/person#>
prefix puml: <http://plantuml.com/ontology#>
prefix qb: <http://purl.org/linked-data/cube#>
prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix res: <http://www.ontotext.com/business-object/result/>
prefix rov: <http://www.w3.org/ns/regorg#>
prefix schema: <http://schema.org/>
prefix skos: <http://www.w3.org/2004/02/skos/core#>
prefix time: <http://www.w3.org/2006/time#>
prefix void: <http://rdfs.org/ns/void#>
prefix wgs: <http://www.w3.org/2003/01/geo/wgs84_pos#>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
# Wikidata
prefix geo: <http://www.opengis.net/ont/geosparql#>
prefix p: <http://www.wikidata.org/prop/>
prefix pq: <http://www.wikidata.org/prop/qualifier/>
prefix pqn: <http://www.wikidata.org/prop/qualifier/value-normalized/>
prefix pqv: <http://www.wikidata.org/prop/qualifier/value/>
prefix pr: <http://www.wikidata.org/prop/reference/>
prefix prn: <http://www.wikidata.org/prop/reference/value-normalized/>
prefix prov: <http://www.w3.org/ns/prov#>
prefix prv: <http://www.wikidata.org/prop/reference/value/>
prefix ps: <http://www.wikidata.org/prop/statement/>
prefix psn: <http://www.wikidata.org/prop/statement/value-normalized/>
prefix psv: <http://www.wikidata.org/prop/statement/value/>
prefix ref: <http://www.wikidata.org/reference/>
prefix s: <http://www.wikidata.org/entity/statement/>
prefix v: <http://www.wikidata.org/value/>
prefix wd: <http://www.wikidata.org/entity/>
prefix wdno: <http://www.wikidata.org/prop/novalue/>
prefix wdt: <http://www.wikidata.org/prop/direct/>
prefix wdtn: <http://www.wikidata.org/prop/direct-normalized/>
prefix wikibase: <http://wikiba.se/ontology#>
construct {
?item a rov:RegisteredOrganization; skos:notation ?WD; dct:modified ?modified; dct:source <dataset/WD>.
?item skos:prefLabel ?prefName.
?item dct:description ?descr.
?item ebg:isStateOwned ?isStateOwned.
?item ebg:isStartup ?isStartup.
?item ebg:isPubliclyTraded ?isPubliclyTraded.
?item cg:country ?geonameCountry.
?geonameCountry adms:identifier ?identCountry.
?identCountry a adms:Identifier; skos:notation ?idCountry; dct:source <dataset/WD>.
?item schema:dateFounded ?foundedDate.
?item schema:dateDissolved ?dissolvedDate.
?item adms:identifier ?ident.
?ident a adms:Identifier; skos:notation ?idValue; cg:identRaw ?idValue; dct:isPartOf ?identSystem; dct:source <dataset/WD>.
?item rov:legalName ?label.
?item skos:altLabel ?altLabel.
?item cg:orgType ?type.
?type a skos:Concept; skos:inScheme <WD/organization/type>; skos:prefLabel ?typeLabel; skos:description ?typeDescr; dct:source <dataset/WD>.
?item rov:orgType ?legalForm.
?legalForm a skos:Concept; skos:inScheme <WD/organization/legalForm>; skos:prefLabel ?legalFormLabel; skos:description ?legalFormDescr; dct:source <dataset/WD>.
?item rov:orgActivity ?industry.
?industry a skos:Concept; skos:inScheme <WD/industry>; skos:prefLabel ?industryLabel; skos:description ?industryDescr; dct:source <dataset/WD>.
?obs a qb:Observation; qb:dataSet ?item; cg:observationType ?obsType; cg:valueInteger ?obsInteger; cg:valueDecimal ?obsDecimal; cg:currency ?currCode; cg:date ?obsDate; dct:source <dataset/WD>.
?item org:hasRegisteredSite ?address.
?address a locn:Address; cg:addressType <address/headquarters>; wdcg:location ?location;
locn:fullAddress ?fullAddress; locn:thoroughfare ?street, ?strLabel;
locn:locatorDesignator ?number, ?building, ?floor; locn:postCode ?postCode; schema:geo ?geoCoord.
?location a wdcg:Location; gn:name ?locationLabel.
?geoCoord a schema:GeoCoordinates; schema:latitude ?latitude; schema:longitude ?longitude; cg:geoPrecision ?precision.
?exchange a cg:StockExchange; dct:source <dataset/WD>.
?exchListing a cg:ExchangeListing; cg:agent ?item; cg:exchange ?exchange; cg:stockSymbol ?stockSymbol;
cg:date ?exchStartDate; cg:dateEnd ?exchEndDate; cg:transactionType ?exchListingType; cg:isCurrent ?isCurrent; dct:source <dataset/WD>.
?orgRel a cg:OrganizationRelation; cg:relationType ?relationType; cg:agentMajor ?agentMajor; cg:agentMinor ?agentMinor; cg:agent ?agent1, ?agent2;
cg:proportion ?proportion; cg:date ?relStartDate; cg:dateEnd ?relEndDate; cg:isCurrent ?isCurrent; dct:source <dataset/WD>.
}
where {{[a schema:Dataset; schema:about ?it; schema:dateModified ?modified]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
optional{?it rdfs:label ?prefName_en. filter(lang(?prefName_en)="en")}
optional{?it rdfs:label ?prefName_de. filter(lang(?prefName_de)="de")}
optional{?it rdfs:label ?prefName_fr. filter(lang(?prefName_fr)="fr")}
optional{?it rdfs:label ?prefName_es. filter(lang(?prefName_es)="es")}
optional{?it rdfs:label ?prefName_it. filter(lang(?prefName_it)="it")}
optional{?it rdfs:label ?prefName_zh. filter(lang(?prefName_zh)="zh")}
bind(coalesce(?prefName_en,?prefName_de,?prefName_fr,?prefName_es,?prefName_it,?prefName_zh) as ?prefName)
optional{?it schema:description ?descr_en. filter(lang(?descr_en)="en")}
optional{?it schema:description ?descr_de. filter(lang(?descr_de)="de")}
optional{?it schema:description ?descr_fr. filter(lang(?descr_fr)="fr")}
optional{?it schema:description ?descr_es. filter(lang(?descr_es)="es")}
optional{?it schema:description ?descr_it. filter(lang(?descr_it)="it")}
optional{?it schema:description ?descr_zh. filter(lang(?descr_zh)="zh")}
bind(coalesce(?descr_en,?descr_de,?descr_fr,?descr_es,?descr_it,?descr_zh) as ?descr)
bind(exists{?it wdt:P31 ?type.
filter(?type in (wd:Q10402097,wd:Q11424358,wd:Q11424434,wd:Q1148315,wd:Q12782082,wd:Q1430364,wd:Q1519138,wd:Q15811601,wd:Q16917889,wd:Q16968466,wd:Q1780371,wd:Q17990971,wd:Q1802186,wd:Q19390308,wd:Q19753703,wd:Q2324556,wd:Q2324813,wd:Q2324835,wd:Q2326317,wd:Q251927,wd:Q270791,wd:Q273615,wd:Q286228,wd:Q2946028,wd:Q2990262,wd:Q30590861,wd:Q3356161,wd:Q3591583,wd:Q485016,wd:Q48814874,wd:Q48815411,wd:Q48815413,wd:Q48851989,wd:Q48851992,wd:Q5189488,wd:Q5589413,wd:Q5834296,wd:Q59603261,wd:Q59603354,wd:Q61819734,wd:Q652812,wd:Q6579042,wd:Q66344,wd:Q6972269,wd:Q7248445,wd:Q7257721,wd:Q7374547,wd:Q7603018,wd:Q1021586,wd:Q10402097,wd:Q10547399,wd:Q10855383,wd:Q11361056,wd:Q11424358,wd:Q1148315,wd:Q11549282,wd:Q11900271,wd:Q12782082,wd:Q1303046,wd:Q1389604,wd:Q1443829,wd:Q15750017,wd:Q15811601,wd:Q16917889,wd:Q16931297,wd:Q16968466,wd:Q1780371,wd:Q17990971,wd:Q1802186,wd:Q19391573,wd:Q2324556,wd:Q2324797,wd:Q2324813,wd:Q2324835,wd:Q2326317,wd:Q24936905,wd:Q2531989,wd:Q270791,wd:Q273615,wd:Q2946028,wd:Q30590861,wd:Q3356161,wd:Q3591583,wd:Q40890107,wd:Q4571771,wd:Q48814874,wd:Q48815411,wd:Q48815413,wd:Q48851989,wd:Q48851992,wd:Q5189477,wd:Q5189488,wd:Q54804183,wd:Q5589413,wd:Q5834296,wd:Q59603261,wd:Q59603354,wd:Q61056688,wd:Q61819734,wd:Q6428421,wd:Q66344,wd:Q6972269,wd:Q7603018,wd:Q835400))}
as ?isStateOwned)
bind(exists{?it wdt:P31 ?type.
filter(?type in (wd:Q129238,wd:Q20983877))}
as ?isStartup)
bind(exists{?it wdt:P414 []}
|| exists{?it wdt:P31 ?type.
filter(?type in (wd:Q891723,wd:Q5225895,wd:Q498289,wd:Q166280,wd:Q13641190,wd:Q20746571))}
as ?isPubliclyTraded)
optional {
values (?country ?gnCountry) {
(wd:Q29999 "2750405") # Kingdom of the Netherlands
(wd:Q55 "2750405") # Netherlands: constituent country of the Kingdom of the Netherlands (nobody cares about such distinction)
(wd:Q30 "6252001") # United States of America
(wd:Q148 "1814991") # People's Republic of China
(wd:Q145 "2635167") # United Kingdom
(wd:Q183 "2921044") # Germany
(wd:Q668 "1269750") # India
(wd:Q142 "3017382") # France
(wd:Q159 "2017370") # Russia
(wd:Q20 "3144096") # Norway
(wd:Q16 "6251999") # Canada
(wd:Q96 "3996063") # Mexico
(wd:Q34 "2661886") # Sweden
(wd:Q38 "3175395") # Italy
(wd:Q29 "2510769") # Spain
(wd:Q252 "1643084") # Indonesia
(wd:Q213 "3077311") # Czech Republic
(wd:Q36 "798544") # Poland
(wd:Q408 "2077456") # Australia
(wd:Q794 "130758") # Iran
(wd:Q17 "1861060") # Japan
(wd:Q155 "3469034") # Brazil
(wd:Q33 "660013") # Finland
(wd:Q212 "690791") # Ukraine
(wd:Q40 "2782113") # Austria
(wd:Q1028 "2542007") # Morocco
(wd:Q843 "1168579") # Pakistan
(wd:Q35 "2623032") # Denmark
(wd:Q31 "2802361") # Belgium
(wd:Q218 "798549") # Romania
(wd:Q43 "298795") # Turkey
(wd:Q39 "2658434") # Switzerland
(wd:Q184 "630336") # Belarus
(wd:Q805 "69543") # Yemen
(wd:Q664 "2186224") # New Zealand
(wd:Q419 "3932488") # Peru
(wd:Q41 "390903") # Greece
(wd:Q414 "3865483") # Argentina
(wd:Q884 "1835841") # South Korea
(wd:Q258 "953987") # South Africa
(wd:Q869 "1605651") # Thailand
(wd:Q739 "3686110") # Colombia
(wd:Q881 "1562822") # Vietnam
(wd:Q298 "3895114") # Chile
(wd:Q928 "1694008") # Philippines
(wd:Q45 "2264397") # Portugal
(wd:Q889 "1149361") # Afghanistan
(wd:Q865 "1668284") # Taiwan
(wd:Q262 "2589581") # Algeria
(wd:Q37 "597427") # Lithuania
(wd:Q833 "1733045") # Malaysia
(wd:Q717 "3625428") # Venezuela
(wd:Q27 "2963597") # Ireland
(wd:Q28 "719819") # Hungary
(wd:Q232 "1522867") # Kazakhstan
(wd:Q225 "3277605") # Bosnia and Herzegovina
(wd:Q836 "1327865") # Myanmar
(wd:Q211 "458258") # Latvia
(wd:Q1036 "226074") # Uganda
(wd:Q191 "453733") # Estonia
(wd:Q974 "203312") # Democratic Republic of the Congo
(wd:Q399 "174982") # Armenia
(wd:Q916 "3351879") # Angola
(wd:Q215 "3190538") # Slovenia
(wd:Q219 "732800") # Bulgaria
(wd:Q224 "3202326") # Croatia
(wd:Q79 "357994") # Egypt
(wd:Q801 "294640") # Israel
(wd:Q403 "6290252") # Serbia
(wd:Q221 "718075") # North Macedonia
(wd:Q214 "3057568") # Slovakia
(wd:Q851 "102358") # Saudi Arabia
(wd:Q241 "3562981") # Cuba
(wd:Q230 "614540") # Georgia
(wd:Q822 "272103") # Lebanon
(wd:Q858 "163843") # Syria
(wd:Q1009 "2233387") # Cameroon
(wd:Q750 "3923057") # Bolivia
(wd:Q902 "1210997") # Bangladesh
(wd:Q854 "1227603") # Sri Lanka
(wd:Q796 "99237") # Iraq
(wd:Q948 "2464461") # Tunisia
(wd:Q1033 "2328926") # Nigeria
(wd:Q114 "192950") # Kenya
(wd:Q1049 "366755") # Sudan
(wd:Q189 "2629691") # Iceland
(wd:Q786 "3508796") # Dominican Republic
(wd:Q222 "783754") # Albania
(wd:Q712 "2205218") # Fiji
(wd:Q813 "1527747") # Kyrgyzstan
(wd:Q227 "587116") # Azerbaijan
(wd:Q77 "3439705") # Uruguay
(wd:Q924 "149590") # Tanzania
(wd:Q929 "239880") # Central African Republic
(wd:Q1016 "2215636") # Libya
(wd:Q15180 "8354411") # Soviet Union
(wd:Q837 "1282988") # Nepal
(wd:Q736 "3658394") # Ecuador
(wd:Q1029 "1036973") # Mozambique
(wd:Q804 "3703430") # Panama
(wd:Q953 "895949") # Zambia
(wd:Q115 "337996") # Ethiopia
(wd:Q236 "3194884") # Montenegro
(wd:Q423 "1873107") # North Korea
(wd:Q783 "3608932") # Honduras
(wd:Q1008 "2287781") # Ivory Coast
(wd:Q954 "878675") # Zimbabwe
(wd:Q223 "3425505") # Greenland
(wd:Q691 "2088628") # Papua New Guinea
(wd:Q1045 "51537") # Somalia
(wd:Q1032 "2440476") # Niger
(wd:Q912 "2453866") # Mali
(wd:Q774 "3595528") # Guatemala
(wd:Q965 "2361809") # Burkina Faso
(wd:Q842 "286963") # Oman
(wd:Q334 "1880251") # Singapore
(wd:Q229 "146669") # Republic of Cyprus
(wd:Q810 "248816") # Jordan
(wd:Q958 "7909807") # South Sudan
(wd:Q233 "2562770") # Malta
(wd:Q1019 "1062947") # Madagascar
(wd:Q117 "2300660") # Ghana
(wd:Q1246 "831053") # Kosovo
(wd:Q863 "1220409") # Tajikistan
(wd:Q32 "2960313") # Luxembourg
(wd:Q657 "2434508") # Chad
(wd:Q800 "3624060") # Costa Rica
(wd:Q1037 "49518") # Rwanda
(wd:Q1025 "2378080") # Mauritania
(wd:Q1006 "2420477") # Guinea
(wd:Q790 "3723988") # Haiti
(wd:Q971 "2260494") # Republic of the Congo
(wd:Q217 "617790") # Moldova
(wd:Q792 "3585968") # El Salvador
(wd:Q977 "223816") # Djibouti
(wd:Q16957 "8354410") # German Democratic Republic
(wd:Q878 "290557") # United Arab Emirates
(wd:Q734 "3378535") # Guyana
(wd:Q986 "338010") # Eritrea
(wd:Q265 "1512440") # Uzbekistan
(wd:Q819 "1655842") # Laos
(wd:Q711 "2029969") # Mongolia
(wd:Q963 "933860") # Botswana
(wd:Q811 "3617476") # Nicaragua
(wd:Q1020 "927384") # Malawi
(wd:Q967 "433561") # Burundi
(wd:Q1000 "2400553") # Gabon
(wd:Q574 "1966436") # East Timor
(wd:Q424 "1831722") # Cambodia
(wd:Q846 "289688") # Qatar
(wd:Q1041 "2245662") # Senegal
(wd:Q1030 "3355338") # Namibia
(wd:Q685 "2103350") # Solomon Islands
(wd:Q730 "3382998") # Suriname
(wd:Q962 "2395170") # Benin
(wd:Q1044 "2403846") # Sierra Leone
(wd:Q1014 "2275384") # Liberia
(wd:Q766 "3489940") # Jamaica
(wd:Q228 "3041565") # Andorra
(wd:Q733 "3437598") # Paraguay
(wd:Q33946 "8505031") # Czechoslovakia
(wd:Q219060 "6254930") # State of Palestine
(wd:Q778 "3572887") # Bahamas
(wd:Q1007 "2372248") # Guinea-Bissau
(wd:Q874 "1218197") # Turkmenistan
(wd:Q983 "2309096") # Equatorial Guinea
(wd:Q826 "1282028") # Maldives
(wd:Q686 "2134431") # Vanuatu
(wd:Q945 "2363686") # Togo
(wd:Q4628 "2622320") # Faroe Islands
(wd:Q242 "3582678") # Belize
(wd:Q921 "1820814") # Brunei
(wd:Q702 "2081918") # Federated States of Micronesia
(wd:Q1011 "3374766") # Cape Verde
(wd:Q6250 "2461445") # Western Sahara
(wd:Q1027 "934292") # Mauritius
(wd:Q817 "285570") # Kuwait
(wd:Q754 "3573591") # Trinidad and Tobago
(wd:Q1005 "2413451") # Gambia
(wd:Q709 "2080185") # Marshall Islands
(wd:Q398 "290291") # Bahrain
(wd:Q970 "921929") # Comoros
(wd:Q917 "1252634") # Bhutan
(wd:Q1013 "932692") # Lesotho
(wd:Q1039 "2410758") # São Tomé and Príncipe
(wd:Q347 "3042058") # Liechtenstein
(wd:Q238 "3168068") # San Marino
(wd:Q781 "3576396") # Antigua and Barbuda
(wd:Q683 "4034894") # Samoa
(wd:Q710 "4030945") # Kiribati
(wd:Q678 "4032283") # Tonga
(wd:Q695 "1559582") # Palau
(wd:Q235 "2993457") # Monaco
(wd:Q244 "3374084") # Barbados
(wd:Q769 "3580239") # Grenada
(wd:Q36704 "8505035") # Yugoslavia
(wd:Q25230 "3042362") # Guernsey
(wd:Q1050 "934841") # Eswatini
(wd:Q757 "3577815") # Saint Vincent and the Grenadines
(wd:Q1042 "241170") # Seychelles
(wd:Q784 "3575830") # Dominica
(wd:Q760 "3576468") # Saint Lucia
(wd:Q407199 "6254930") # Palestinian territories
(wd:Q672 "2110297") # Tuvalu
(wd:Q785 "3042142") # Jersey
(wd:Q1410 "2411586") # Gibraltar
(wd:Q237 "3164670") # Vatican City
(wd:Q763 "3575174") # Saint Kitts and Nevis
(wd:Q2277 "8354456") # Roman Empire
(wd:Q25 "2634895") # Wales
(wd:Q9648 "3474414") # Falkland Islands
(wd:Q9676 "3042225") # Isle of Man
(wd:Q21203 "3577279") # Aruba
(wd:Q133356 "690791") # Ukrainian Soviet Socialist Republic
(wd:Q26 "2641364") # Northern Ireland
(wd:Q25279 "7626836") # Curaçao
(wd:Q713750 "11612754") # West Germany
(wd:Q26988 "1899402") # Cook Islands
(wd:Q844930 "9962195") # Classical Athens
(wd:Q192184 "3370751") # Saint Helena, Ascension and Tristan da Cunha
(wd:Q697 "2110425") # Nauru
(wd:Q5785 "3580718") # Cayman Islands
(wd:Q23635 "3573345") # Bermuda
(wd:Q18221 "3576916") # Turks and Caicos Islands
(wd:Q8646 "1819730") # Hong Kong
(wd:Q1183 "4566966") # Puerto Rico
(wd:Q25228 "3573511") # Anguilla
(wd:Q34020 "4036232") # Niue
(wd:Q25305 "3577718") # British Virgin Islands
(wd:Q26273 "7609695") # Sint Maarten
(wd:Q23334 "6643410") # Abkhazia
(wd:Q180573 "11608491") # South Vietnam
(wd:Q36678 "285153") # West Bank
(wd:Q37024 "8505033") # Serbia and Montenegro
(wd:Q1202 "2842566") # Saxony
(wd:Q11703 "4796775") # United States Virgin Islands
(wd:Q33788 "2139685") # New Caledonia
(wd:Q13353 "3578097") # Montserrat
(wd:Q25227 "8505032") # Netherlands Antilles
(wd:Q35086 "3474415") # South Georgia and the South Sandwich Islands
(wd:Q17054 "3570311") # Martinique
(wd:Q16641 "5880801") # American Samoa
(wd:Q43448 "1282588") # British Indian Ocean Territory
(wd:Q17012 "3579143") # Guadeloupe
(wd:Q99 "5332921") # California
(wd:Q82112 "3523272") # México
(wd:Q35672 "4030699") # Pitcairn Islands
(wd:Q36823 "4031074") # Tokelau
(wd:Q11196 "3230000") # Republika Srpska
(wd:Q22890 "2646052") # Ireland
(wd:Q5710 "578853") # Republic of Bashkortostan
(wd:Q771 "6254926") # Massachusetts
(wd:Q985 "2953481") # Baden-Württemberg
(wd:Q129003 "1546748") # French Southern and Antarctic Lands
(wd:Q5690 "253394") # Sparta
(wd:Q26253 "2593105") # Madeira
(wd:Q1781 "3054643") # Budapest
(wd:Q5481 "484048") # Republic of Tatarstan
(wd:Q1774 "148729") # Zanzibar
(wd:Q1428 "4197000") # Georgia
(wd:Q3769 "3381670") # French Guiana
(wd:Q1384 "5128638") # New York
(wd:Q1490 "1850144") # Tokyo
(wd:Q176 "6115047") # Quebec
(wd:Q17070 "935317") # Réunion
(wd:Q185944 "1733044") # Kelantan
(wd:Q199841 "8505034") # South Yemen
(wd:Q1055 "2911298") # Hamburg
(wd:Q1194 "2838632") # Schleswig-Holstein
(wd:Q46 "6255148") # Europe
(wd:Q188096 "1733047") # Penang
(wd:Q28208 "6778360") # Goryeo
(wd:Q1208 "2945356") # Brandenburg
(wd:Q1261 "5417618") # Colorado
(wd:Q27561 "7626844") # Caribbean Netherlands
(wd:Q23666 "2648147") # Great Britain
(wd:Q6809 "2050915") # Republic of Buryatia
(wd:Q1186 "1267254") # Kerala
(wd:Q1408 "5101760") # New Jersey
(wd:Q1454 "4482348") # North Carolina
(wd:Q782 "5855797") # Hawaii
(wd:Q812 "4155751") # Florida
(wd:Q980 "2951839") # Bavaria
(wd:Q179029 "1733039") # Sabah
(wd:Q31057 "2155115") # Norfolk Island
(wd:Q25277 "3504558") # Hispaniola
(wd:Q1952 "6091530") # Nova Scotia
(wd:Q3224 "2155400") # New South Wales
(wd:Q5187 "569665") # Chechen Republic
(wd:Q1199 "2905330") # Hesse
(wd:Q1204 "4896861") # Illinois
(wd:Q759 "5090174") # New Hampshire
(wd:Q170462 "1733038") # Sarawak
(wd:Q36074 "2152274") # Queensland
(wd:Q39473 "664661") # Transylvania
(wd:Q39760 "281132") # Gaza Strip
(wd:Q3680 "542415") # Krasnodar Krai
(wd:Q3734 "584222") # Republic of Adygea
(wd:Q5207 "487839") # Stavropol Krai
(wd:Q5267 "554667") # Kabardino-Balkar Republic
(wd:Q5328 "552927") # Karachay-Cherkess Republic
(wd:Q1439 "4736286") # Texas
(wd:Q1456 "4597040") # South Carolina
(wd:Q3041595 "99237") # Republic of Iraq (1958–68)
(wd:Q1509 "4662168") # Tennessee
(wd:Q126125 "3578421") # Saint Martin
(wd:Q12738 "2659495") # Canton of Neuchâtel
(wd:Q3573 "501165") # Rostov Oblast
(wd:Q5466 "567395") # Chuvash Republic
(wd:Q1166 "5001836") # Michigan
(wd:Q1205 "2822542") # Thuringia
(wd:Q1397 "5165418") # Ohio
(wd:Q21 "6269131") # England
(wd:Q189701 "1733036") # Terengganu
(wd:Q17252 "8449601") # Tibet
(wd:Q1951 "5883102") # Alberta
(wd:Q3953 "553972") # Republic of Kalmykia
(wd:Q1191 "1264418") # Maharashtra
(wd:Q1196 "2872567") # Mecklenburg-Vorpommern
(wd:Q1197 "2862926") # Lower Saxony
(wd:Q1221 "5596512") # Idaho
(wd:Q1370 "6254928") # Virginia
(wd:Q1387 "5224323") # Rhode Island
(wd:Q779 "4831725") # Connecticut
(wd:Q816 "5551752") # Arizona
(wd:Q960 "1488873") # Tuva Republic
(wd:Q213467 "1733040") # Perlis
(wd:Q185103 "8378485") # Roman Britain
(wd:Q189710 "1733037") # Selangor
(wd:Q172640 "11608492") # North Vietnam
(wd:Q93366 "3104469") # Biscay
(wd:Q107299 "2641234") # Kingdom of Northumbria
(wd:Q42314 "3042400") # Channel Islands
(wd:Q43266 "3070359") # Moravia
(wd:Q60176 "3514211") # Yucatán
(wd:Q26180 "7610359") # Sint Eustatius
(wd:Q34110 "3522509") # Oaxaca
(wd:Q36687 "2145234") # Victoria
(wd:Q38272 "2654669") # British Isles
(wd:Q41079 "2035607") # Inner Mongolia
(wd:Q14773 "1821275") # Macau
(wd:Q15282 "3522542") # Nuevo León
(wd:Q985531 "281577") # Tulkarm
(wd:Q1904 "6093943") # Ontario
(wd:Q2634 "3172395") # Naples
(wd:Q6320 "1503773") # Khanty-Mansi Autonomous Okrug
(wd:Q6407 "1486462") # Yamalo-Nenets Autonomous Okrug
(wd:Q6838 "7779061") # Zabaykalsky Krai
(wd:Q1061 "1270770") # Gujarat
(wd:Q1180 "1269320") # Jammu and Kashmir
(wd:Q1185 "1267701") # Karnataka
(wd:Q1207 "5690763") # North Dakota
(wd:Q1415 "4921868") # Indiana
(wd:Q1527 "5037779") # Minnesota
(wd:Q824 "5744337") # Oregon
(wd:Q613 "292224") # Dubai
(wd:Q185221 "1733035") # Melaka
(wd:Q191369 "165369") # Ugarit
(wd:Q202311 "8378475") # Egypt
(wd:Q164819 "858895") # Gagauzia
(wd:Q42497 "2770542") # Lower Austria
(wd:Q42880 "2763586") # Tyrol
(wd:Q53079 "4013674") # Coahuila
(wd:Q79920 "3995012") # Nayarit
(wd:Q80245 "3520887") # Quintana Roo
(wd:Q80252 "3983035") # Sinaloa
(wd:Q80269 "3979840") # Zacatecas
(wd:Q25596 "3578422") # Saint Martin
(wd:Q30971 "4030656") # French Polynesia
(wd:Q33935 "293397") # Tel Aviv
(wd:Q34366 "2147291") # Tasmania
(wd:Q35715 "2061327") # South Australia
(wd:Q41470 "126585") # Kurdistan
(wd:Q11972 "2661876") # Aargau
(wd:Q12585 "7730009") # Latin America
(wd:Q12602 "3106654") # Val d'Aran
(wd:Q13373 "6542121") # Lucca
(wd:Q16551 "5242283") # Vermont
(wd:Q16635 "4043988") # Guam
(wd:Q16959 "1529102") # Ürümqi
(wd:Q21195 "2614165") # Scandinavia
(wd:Q22048 "1261029") # Odisha
(wd:Q22424 "1259223") # Punjab
(wd:Q528042 "3202210") # Dalmatia
(wd:Q1558 "4273857") # Kansas
(wd:Q1588 "4331987") # Louisiana
(wd:Q1612 "4099753") # Arkansas
(wd:Q1649 "4544379") # Oklahoma
(wd:Q1965 "6087430") # New Brunswick
(wd:Q2843 "2875601") # Lübeck
(wd:Q3206 "2058645") # Western Australia
(wd:Q5118 "567293") # Republic of Dagestan
(wd:Q5237 "519969") # Republic of North Ossetia-Alania
(wd:Q5446 "529352") # Mari El Republic
(wd:Q5689 "661882") # Åland Islands
(wd:Q6605 "2013162") # Sakha Republic
(wd:Q1159 "1278629") # Andhra Pradesh
(wd:Q1165 "1275715") # Bihar
(wd:Q1198 "2861876") # North Rhine-Westphalia
(wd:Q1206 "2842565") # Saxony-Anhalt
(wd:Q1209 "2944387") # Bremen
(wd:Q1212 "5667009") # Montana
(wd:Q1214 "5843591") # Wyoming
(wd:Q1223 "5815135") # Washington
(wd:Q1371 "4826850") # West Virginia
(wd:Q1391 "4361885") # Maryland
(wd:Q1400 "6254927") # Pennsylvania
(wd:Q1437 "1258899") # Rajasthan
(wd:Q1445 "1255053") # Tamil Nadu
(wd:Q1498 "1253626") # Uttar Pradesh
(wd:Q3125978 "1650535") # Bali
(wd:Q1522 "5481136") # New Mexico
(wd:Q1537 "5279468") # Wisconsin
(wd:Q797 "5879092") # Alaska
(wd:Q60 "5128581") # New York City
(wd:Q173 "4829764") # Alabama
(wd:Q656 "498817") # Saint Petersburg
(wd:Q15 "6255146") # Africa
(wd:Q18 "6255150") # South America
(wd:Q220982 "3370682") # Tristan da Cunha
(wd:Q309272 "8378518") # Mauretania
(wd:Q180103 "143083") # Ardabil
(wd:Q181578 "119505") # Qazvin
(wd:Q183032 "1733049") # Johor
(wd:Q183416 "3578263") # Windward Islands
(wd:Q185111 "3573606") # Tobago
(wd:Q187346 "2501152") # Constantine
(wd:Q187712 "292969") # Abu Dhabi Emirate
(wd:Q188650 "8378511") # Lusitania
(wd:Q188810 "292673") # Sharjah Emirate
(wd:Q188947 "1733048") # Kedah
(wd:Q188953 "1733041") # Perak
(wd:Q189901 "264670") # Argos
(wd:Q200309 "865543") # Samegrelo-Zemo Svaneti
(wd:Q202195 "298846") # Tunceli
(wd:Q202422 "254143") # Salamis Island
(wd:Q204381 "216281") # Goma
(wd:Q160236 "5126698") # Metropolitan Museum of Art
(wd:Q165368 "6290300") # Frankfurt (Main) Hauptbahnhof
(wd:Q165582 "3632306") # Mérida
(wd:Q166919 "6697809") # Thessaly
(wd:Q171334 "687699") # Zaporizhzhya Oblast
(wd:Q171393 "662112") # Wallachia
(wd:Q171852 "698738") # Odessa Oblast
(wd:Q173532 "273203") # Byblos
(wd:Q176081 "142549") # East Azerbaijan Province
(wd:Q95010 "3120935") # Gipuzkoa
(wd:Q125384 "1704846") # Luzon
(wd:Q126148 "2530333") # Tangier
(wd:Q128323 "3573592") # Trinidad
(wd:Q131083 "147435") # Nakhchivan Autonomous Republic
(wd:Q131198 "1547314") # Heard Island and McDonald Islands
(wd:Q131434 "8133605") # Noricum
(wd:Q132931 "570479") # Buynaksk
(wd:Q134386 "126584") # Kurdistan Province
(wd:Q134411 "142550") # West Azarbaijan Province
(wd:Q145507 "551847") # Kaspiysk
(wd:Q145799 "547849") # Kizilyurt
(wd:Q145810 "547840") # Kizlyar
(wd:Q146699 "111453") # Zanjan
(wd:Q147553 "584604") # Zaqatala District
(wd:Q147756 "550478") # Khasavyurt
(wd:Q41967 "2769848") # Upper Austria
(wd:Q42000 "4033649") # Tahiti
(wd:Q43325 "2766823") # Salzburg
(wd:Q43937 "2460837") # Azawad
(wd:Q45693 "615929") # Adjara
(wd:Q45833 "1280239") # Qinghai
(wd:Q46422 "3982846") # Sonora
(wd:Q46475 "4005267") # Guanajuato
(wd:Q51103 "3687926") # Cali
(wd:Q51614 "323835") # Anatolia
(wd:Q56036 "11612751") # West Berlin
(wd:Q57052 "1806222") # Jiangxi
(wd:Q60140 "6269134") # Indian subcontinent
(wd:Q60158 "3527213") # Guerrero
(wd:Q79754 "3520914") # Querétaro
(wd:Q79861 "3995955") # Michoacán
(wd:Q79918 "4011741") # Durango
(wd:Q79952 "4019231") # Aguascalientes
(wd:Q80007 "3516391") # Tamaulipas
(wd:Q80903 "3527115") # Hidalgo
(wd:Q80914 "3516458") # Tabasco
(wd:Q25362 "3578476") # Saint Barthélemy
(wd:Q25396 "7609816") # Bonaire
(wd:Q25409 "724443") # Košice
(wd:Q27433 "7729886") # Central Africa
(wd:Q27449 "9408658") # Southern Europe
(wd:Q28227 "2219875") # Maghreb
(wd:Q28587 "132144") # Hamadan
(wd:Q29971 "3513538") # Leeward Islands
(wd:Q31063 "2078138") # Christmas Island
(wd:Q34374 "6697802") # Crete
(wd:Q34497 "6930057") # Saint Helena
(wd:Q34617 "3424932") # Saint Pierre and Miquelon
(wd:Q35600 "1488441") # Ural Mountains
(wd:Q36004 "1547376") # Cocos (Keeling) Islands
(wd:Q37985 "2774686") # Carinthia
(wd:Q40040 "3665361") # Amazonas
(wd:Q40285 "2082514") # New Guinea
(wd:Q40326 "530936") # Malokarachayevsky District
(wd:Q41183 "170063") # Aleppo
(wd:Q41621 "294801") # Haifa
(wd:Q41705 "1811017") # Fujian
(wd:Q8684 "1835848") # Seoul
(wd:Q8818 "2509954") # Valencia
(wd:Q8828 "2514239") # Majorca
(wd:Q11708 "7729896") # Southeast Asia
(wd:Q11925 "2660522") # Graubünden
(wd:Q11943 "2657895") # canton of Zürich
(wd:Q12172 "2661602") # Basel-Stadt
(wd:Q12225 "6355234") # Murcia
(wd:Q12433 "2658664") # Schwyz
(wd:Q12724 "2658370") # Ticino
(wd:Q13148 "2515812") # La Línea de la Concepción
(wd:Q13895 "1679435") # Zambales
(wd:Q15088 "3108288") # Tarragona
(wd:Q15124 "3181912") # South Tyrol
(wd:Q16162 "2524906") # Province of Cosenza
(wd:Q17269 "1279685") # Tibet
(wd:Q21208 "1808773") # Hebei
(wd:Q22508 "6540935") # Momo
(wd:Q22647 "298885") # Troy
(wd:Q23064 "2653234") # Cheshire
(wd:Q25231 "7521757") # Svalbard
(wd:Q504125 "2618424") # Copenhagen Municipality
(wd:Q505610 "2649298") # Flintshire
(wd:Q590866 "110791") # Tehran Province
(wd:Q664609 "7729891") # Caribbean
(wd:Q669037 "3571219") # West Indies
(wd:Q844295 "148783") # Yamataikoku
(wd:Q851694 "570478") # Buynaksky District
(wd:Q861548 "4155745") # Florida State University
(wd:Q865884 "617264") # Taraclia District
(wd:Q877875 "8378566") # Palmyrene Empire
(wd:Q920396 "3574922") # British West Indies
(wd:Q941045 "4277147") # Peru
(wd:Q1004578 "298088") # Varto
(wd:Q1012828 "305089") # Lice
(wd:Q1015011 "3070678") # Milevsko
(wd:Q1052867 "1574717") # Mekong Delta
(wd:Q1394899 "549104") # Khunzakhsky District
(wd:Q1438410 "482588") # Tlyaratinsky District
(wd:Q1438424 "551375") # Kazbekovsky District
(wd:Q1438467 "583776") # Akhvakhsky District
(wd:Q1438511 "490031") # Shamilsky District
(wd:Q1438521 "557977") # Gunibsky District
(wd:Q1438529 "480801") # Tsuntinsky District
(wd:Q1438662 "550476") # Khasavyurtovsky District
(wd:Q1438994 "535538") # Levashinsky District
(wd:Q1439001 "571887") # Botlikhsky District
(wd:Q1439010 "569892") # Charodinsky District
(wd:Q1439024 "558024") # Gumbetovsky District
(wd:Q1468689 "3620341") # Coco River
(wd:Q1789499 "613582") # Qvareli Municipality
(wd:Q677037 "1254788") # Telangana
(wd:Q812986 "3006978") # La Réorthe
(wd:Q25304 "3876256") # Pichilemu
(wd:Q1581 "4398678") # Missouri
(wd:Q1603 "6254925") # Kentucky
(wd:Q1741 "2761369") # Vienna
(wd:Q1764 "3413829") # Reykjavík
(wd:Q1842 "2960316") # Luxembourg
(wd:Q1914 "552548") # Karelia
(wd:Q2044 "3176959") # Florence
(wd:Q2656 "6542127") # Palermo
(wd:Q2706 "2944368") # Bremerhaven
(wd:Q3001 "3383330") # Paramaribo
(wd:Q3235 "2064513") # Northern Territory
(wd:Q3306 "3703443") # Panama City
(wd:Q3410 "2647043") # Hertfordshire
(wd:Q3503 "2241717") # La concha de tu mamá
(wd:Q3757 "1642673") # Java
(wd:Q3819 "472755") # Volgograd Oblast
(wd:Q3941 "580491") # Astrakhan Oblast
(wd:Q5168 "532096") # Makhachkala
(wd:Q5326 "569154") # Cherkessk
(wd:Q5334 "498671") # Saratov Oblast
(wd:Q5338 "515001") # Orenburg Oblast
(wd:Q5340 "525369") # Republic of Mordovia
(wd:Q5387 "548389") # Kirov Oblast
(wd:Q5684 "98228") # Babylon
(wd:Q5780 "370131") # Meroë
(wd:Q6487 "323777") # Antalya
(wd:Q6543 "1503834") # Republic of Khakassia
(wd:Q6585 "2023468") # Irkutsk Oblast
(wd:Q7788 "2022888") # Khabarovsk Krai
(wd:Q7809 "6457262") # UNESCO
(wd:Q1165546 "3617572") # Mosquito Coast
(wd:Q2128882 "618164") # Cahul District
(wd:Q2426300 "547846") # Kizilyurtovsky District
(wd:Q2651288 "3983630") # Tangamandapiu
(wd:Q7948 "2125072") # Kamchatka Krai
(wd:Q7984 "2126099") # Chukotka Autonomous Okrug
(wd:Q1164 "1278253") # Assam
(wd:Q1171 "1271157") # Goa
(wd:Q1177 "1270101") # Himachal Pradesh
(wd:Q1200 "2847618") # Rhineland-Palatinate
(wd:Q1201 "2842635") # Saarland
(wd:Q1210 "3174618") # Lombardy
(wd:Q1211 "5769223") # South Dakota
(wd:Q1256 "3174725") # Liguria
(wd:Q1280 "3165048") # Umbria
(wd:Q1286 "2661786") # Alps
(wd:Q1356 "1252881") # West Bengal
(wd:Q1449 "6542282") # Genoa
(wd:Q1462 "2523228") # Sardinia
(wd:Q1489 "3527646") # Mexico City
(wd:Q3931110 "3180542") # Royal Palace of Carditello
(wd:Q4148644 "3422723") # Greenland
(wd:Q1519 "292968") # Abu Dhabi
(wd:Q1524 "264371") # Athens
(wd:Q1546 "4862182") # Iowa
(wd:Q1553 "5073708") # Nebraska
(wd:Q724 "4971068") # Maine
(wd:Q788 "2960860") # Arctic Ocean
(wd:Q828 "10861432") # Americas
(wd:Q834 "2658205") # Canton of Valais
(wd:Q891 "520555") # Nizhny Novgorod
(wd:Q987 "1261481") # New Delhi
(wd:Q48 "6255147") # Asia
(wd:Q84 "2643743") # London
(wd:Q100 "4930956") # Boston
(wd:Q220 "3169071") # Rome
(wd:Q239 "2800866") # Brussels
(wd:Q437 "3196359") # Ljubljana
(wd:Q459 "728193") # Plovdiv
(wd:Q643 "3170550") # Po
(wd:Q22 "2638360") # Scotland
(wd:Q211377 "1706802") # Leyte
(wd:Q212938 "7303419") # East Jerusalem
(wd:Q215081 "3624182") # Coco Island
(wd:Q216791 "8378513") # Hispania Tarraconensis
(wd:Q217265 "322954") # Cappadocia
(wd:Q222267 "1268008") # Kanyakumari
(wd:Q238752 "3576390") # Barbuda
(wd:Q18677983 "11071622") # Grand Est
(wd:Q24017189 "6361970") # Cullera
(wd:Q280984 "3562895") # Cueto
(wd:Q304810 "2759836") # Amer
(wd:Q309276 "7422379") # Northeast Greenland National Park
(wd:Q316390 "3576395") # Antigua
(wd:Q322155 "1185128") # Rajshahi
(wd:Q330850 "587010") # Balakan District
(wd:Q368628 "253878") # Sicyon
(wd:Q374794 "2711509") # Gotland Municipality
(wd:Q428980 "5063172") # Mi pito landia
(wd:Q41876 "2984114") # Reims
}
?it wdt:P17 ?country.
bind(uri(concat(str(GN:),?gnCountry,"/")) as ?geonameCountry)
bind(uri(concat(str(GN:),?gnCountry,"/WD")) as ?identCountry)
bind(strafter(str(?country),str(wd:)) as ?idCountry)}
optional {?it wdt:P571 ?foundedDateTime bind(strdt(strbefore(str(?foundedDateTime),"T"),xsd:date) as ?foundedDate)}
optional {?it wdt:P576 ?dissolvedDateTime bind(strdt(strbefore(str(?dissolvedDateTime),"T"),xsd:date) as ?dissolvedDate)}
}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
bind(?WD as ?idValue)
bind(uri(concat(?_item,"/WD")) as ?ident)
bind(<identifier/WD> as ?identSystem)}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
?enwp a schema:Article; schema:about ?it; schema:isPartOf <https://en.wikipedia.org/>.
bind(strafter(str(?enwp), "https://en.wikipedia.org/wiki/") as ?idValue)
bind(uri(concat(?_item,"/DBP")) as ?ident)
bind(<identifier/DBP> as ?identSystem)}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
values (?prop ?idSystem ?idPrefix) {
(wdt:P2087 "CB" "https://www.crunchbase.com/person/")
(wdt:P2088 "CB" "https://www.crunchbase.com/organization/")
(wdt:P3153 "Crossref/funder" "")
(wdt:P2013 "FB" "https://www.facebook.com/")
(wdt:P1278 "GLEI" "")
(wdt:P2427 "GRID" "")
(wdt:P3193 "GS1" "")
(wdt:P2037 "GitHub" "")
(wdt:P213 "ISNI" "")
(wdt:P2003 "Instagram" "")
(wdt:P3225 "JP" "")
(wdt:P646 "KG" "")
(wdt:P2035 "LI" "")
(wdt:P4264 "LI" "https://www.linkedin.com/company/")
(wdt:P6634 "LI" "https://www.linkedin.com/in/")
(wdt:P6366 "MAG" "")
(wdt:P1312 "OCORP" "")
(wdt:P6782 "ROR" "")
(wdt:P3984 "Subreddit" "")
(wdt:P3347 "TR/permid" "")
(wdt:P2002 "TW" "https://twitter.com/")
(wdt:P1297 "US-IRS" "")
(wdt:P5531 "US-SEC-CIK" "")
(wdt:P214 "VIAF" "")
(wdt:P2397 "YouTube" "")
(wdt:P1019 "blog" "")
(wdt:P1581 "blog" "")
(wdt:P968 "email" "")
(wdt:P1329 "phone" "")
(wdt:P154 "image" "")
(wdt:P856 "website" "")
}
?it ?prop ?id.
bind(concat(?idPrefix,str(?id)) as ?idValue)
bind(uri(concat(?_item,"/",?idSystem)) as ?ident)
bind(uri(concat("identifier/",?idSystem)) as ?identSystem)}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
optional{?it rdfs:label ?label. filter(lang(?label) in ("en","de","fr","es","it","zh"))}}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
optional{?it skos:altLabel ?altLabel. filter(lang(?altLabel) in ("en","de","fr","es","it","zh"))}}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
?it wdt:P31 ?typ.
bind(uri(concat(str(WD:),strafter(str(?typ),str(wd:)))) as ?type)
optional{?typ rdfs:label ?typeLabel_en. filter(lang(?typeLabel_en)="en")}
optional{?typ rdfs:label ?typeLabel_de. filter(lang(?typeLabel_de)="de")}
optional{?typ rdfs:label ?typeLabel_fr. filter(lang(?typeLabel_fr)="fr")}
optional{?typ rdfs:label ?typeLabel_es. filter(lang(?typeLabel_es)="es")}
optional{?typ rdfs:label ?typeLabel_it. filter(lang(?typeLabel_it)="it")}
optional{?typ rdfs:label ?typeLabel_zh. filter(lang(?typeLabel_zh)="zh")}
bind(coalesce(?typeLabel_en,?typeLabel_de,?typeLabel_fr,?typeLabel_es,?typeLabel_it,?typeLabel_zh) as ?typeLabel)
optional{?typ schema:description ?typeDescr_en. filter(lang(?typeDescr_en)="en")}
optional{?typ schema:description ?typeDescr_de. filter(lang(?typeDescr_de)="de")}
optional{?typ schema:description ?typeDescr_fr. filter(lang(?typeDescr_fr)="fr")}
optional{?typ schema:description ?typeDescr_es. filter(lang(?typeDescr_es)="es")}
optional{?typ schema:description ?typeDescr_it. filter(lang(?typeDescr_it)="it")}
optional{?typ schema:description ?typeDescr_zh. filter(lang(?typeDescr_zh)="zh")}
bind(coalesce(?typeDescr_en,?typeDescr_de,?typeDescr_fr,?typeDescr_es,?typeDescr_it,?typeDescr_zh) as ?typeDescr)}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
?it wdt:P1454 ?legal.
bind(uri(concat(str(WD:),strafter(str(?legal),str(wd:)))) as ?legalForm)
optional{?legal rdfs:label ?legalFormLabel_en. filter(lang(?legalFormLabel_en)="en")}
optional{?legal rdfs:label ?legalFormLabel_de. filter(lang(?legalFormLabel_de)="de")}
optional{?legal rdfs:label ?legalFormLabel_fr. filter(lang(?legalFormLabel_fr)="fr")}
optional{?legal rdfs:label ?legalFormLabel_es. filter(lang(?legalFormLabel_es)="es")}
optional{?legal rdfs:label ?legalFormLabel_it. filter(lang(?legalFormLabel_it)="it")}
optional{?legal rdfs:label ?legalFormLabel_zh. filter(lang(?legalFormLabel_zh)="zh")}
bind(coalesce(?legalFormLabel_en,?legalFormLabel_de,?legalFormLabel_fr,?legalFormLabel_es,?legalFormLabel_it,?legalFormLabel_zh) as ?legalFormLabel)
optional{?legal schema:description ?legalFormDescr_en. filter(lang(?legalFormDescr_en)="en")}
optional{?legal schema:description ?legalFormDescr_de. filter(lang(?legalFormDescr_de)="de")}
optional{?legal schema:description ?legalFormDescr_fr. filter(lang(?legalFormDescr_fr)="fr")}
optional{?legal schema:description ?legalFormDescr_es. filter(lang(?legalFormDescr_es)="es")}
optional{?legal schema:description ?legalFormDescr_it. filter(lang(?legalFormDescr_it)="it")}
optional{?legal schema:description ?legalFormDescr_zh. filter(lang(?legalFormDescr_zh)="zh")}
bind(coalesce(?legalFormDescr_en,?legalFormDescr_de,?legalFormDescr_fr,?legalFormDescr_es,?legalFormDescr_it,?legalFormDescr_zh) as ?legalFormDescr)}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
?it wdt:P452 ?ind.
bind(uri(concat(str(WD:),strafter(str(?ind),str(wd:)))) as ?industry)
optional{?ind rdfs:label ?industryLabel_en. filter(lang(?industryLabel_en)="en")}
optional{?ind rdfs:label ?industryLabel_de. filter(lang(?industryLabel_de)="de")}
optional{?ind rdfs:label ?industryLabel_fr. filter(lang(?industryLabel_fr)="fr")}
optional{?ind rdfs:label ?industryLabel_es. filter(lang(?industryLabel_es)="es")}
optional{?ind rdfs:label ?industryLabel_it. filter(lang(?industryLabel_it)="it")}
optional{?ind rdfs:label ?industryLabel_zh. filter(lang(?industryLabel_zh)="zh")}
bind(coalesce(?industryLabel_en,?industryLabel_de,?industryLabel_fr,?industryLabel_es,?industryLabel_it,?industryLabel_zh) as ?industryLabel)
optional{?ind schema:description ?industryDescr_en. filter(lang(?industryDescr_en)="en")}
optional{?ind schema:description ?industryDescr_de. filter(lang(?industryDescr_de)="de")}
optional{?ind schema:description ?industryDescr_fr. filter(lang(?industryDescr_fr)="fr")}
optional{?ind schema:description ?industryDescr_es. filter(lang(?industryDescr_es)="es")}
optional{?ind schema:description ?industryDescr_it. filter(lang(?industryDescr_it)="it")}
optional{?ind schema:description ?industryDescr_zh. filter(lang(?industryDescr_zh)="zh")}
bind(coalesce(?industryDescr_en,?industryDescr_de,?industryDescr_fr,?industryDescr_es,?industryDescr_it,?industryDescr_zh) as ?industryDescr)}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
values (?obsProp ?obsProp1 ?obsProp2 ?obsType) {
("P1128" p:P1128 psv:P1128 <observation/employees>)}
?it ?obsProp1 ?statement.
optional {?statement pq:P585 ?obsDateTime bind(strdt(strbefore(str(?obsDateTime),"T"),xsd:date) as ?obsDate)}
bind(uri(concat(
?_item, "/observation/", ?obsProp,
if(bound(?obsDate),concat("/",str(?obsDate)),""))) as ?obs)
?statement ?obsProp2 [wikibase:quantityAmount ?amt].
bind(xsd:integer(?amt) as ?obsInteger)
}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
values (?obsProp ?obsProp1 ?obsProp2 ?obsType) {
("P2403" p:P2403 psv:P2403 <observation/totalAssets>)
("P2218" p:P2218 psv:P2218 <observation/netWorth>)
("P2137" p:P2137 psv:P2137 <observation/totalEquity>)
("P2226" p:P2226 psv:P2226 <observation/marketCapitalization>)
("P2284" p:P2284 psv:P2284 <observation/valuation>) # price
("P2139" p:P2139 psv:P2139 <observation/totalRevenue>)
("P3362" p:P3362 psv:P3362 <observation/operatingIncome>)
("P2295" p:P2295 psv:P2295 <observation/netProfit>)}
?it ?obsProp1 ?statement.
optional {?statement pq:P585 ?obsDateTime bind(strdt(strbefore(str(?obsDateTime),"T"),xsd:date) as ?obsDate)}
bind(uri(concat(
?_item, "/observation/", ?obsProp,
if(bound(?obsDate),concat("/",str(?obsDate)),""))) as ?obs)
?statement ?obsProp2 [wikibase:quantityAmount ?amt].
bind(?amt/1000000.0 as ?obsDecimal)
?statement ?obsProp2 [wikibase:quantityUnit ?curr].
values (?curr ?currCode) {
(wd:Q200294 "AED") # United Arab Emirates dirham
(wd:Q130498 "AMD") # Armenian dram
(wd:Q522701 "ANG") # Netherlands Antillean guilder
(wd:Q199578 "ARS") # Argentine peso
(wd:Q259502 "AUD") # Australian dollar
(wd:Q783373 "AUP") # Australian pound
(wd:Q194453 "BDT") # Bangladeshi taka
(wd:Q172540 "BGN") # Bulgarian lev
(wd:Q10261896 "BRE") # cruzeiro
(wd:Q173117 "BRL") # Brazilian real
(wd:Q201799 "BTN") # Bhutanese ngultrum
(wd:Q160680 "BYN") # Belarusian ruble
(wd:Q275112 "BZD") # Belize dollar
(wd:Q1104069 "CAD") # Canadian dollar
(wd:Q4734 "CDF") # Congolese franc
(wd:Q25344 "CHF") # Swiss franc
(wd:Q39099 "CNY") # renminbi
(wd:Q622599 "CSK") # Czechoslovak koruna
(wd:Q201505 "CUP") # Cuban peso
(wd:Q131016 "CZK") # Czech koruna
(wd:Q16068 "DEM") # Deutsche Mark
(wd:Q25417 "DKK") # Danish krone
(wd:Q189097 "ESP") # peseta
(wd:Q206243 "ETB") # Ethiopian birr
(wd:Q4916 "EUR") # euro
(wd:Q203354 "FIM") # Finnish markka
(wd:Q4592 "FJD") # Fijian dollar
(wd:Q184172 "FRF") # French franc
(wd:Q25224 "GBP") # pound sterling
(wd:Q4608 "GEL") # Georgian lari
(wd:Q1508175 "GHP") # Ghanaian pound
(wd:Q1475316 "GMP") # Gambian pound
(wd:Q213311 "GNF") # Guinean franc
(wd:Q255726 "GQP") # Equatorial Guinean peseta
(wd:Q31015 "HKD") # Hong Kong dollar
(wd:Q26360 "HRK") # Croatian kuna
(wd:Q47190 "HUF") # Hungarian forint
(wd:Q41588 "IDR") # Indonesian Rupiah
(wd:Q1674712 "ILP") # Israeli lira
(wd:Q131309 "ILS") # Israeli new shekel
(wd:Q80524 "INR") # Indian rupee
(wd:Q204992 "ITL") # Italian lira
(wd:Q8146 "JPY") # Japanese yen
(wd:Q202040 "KRW") # South Korean won
(wd:Q201880 "LBP") # Lebanese pound
(wd:Q25627 "LTL") # Lithuanian litas
(wd:Q2086443 "MAF") # Moroccan franc
(wd:Q177875 "MKD") # Macedonian denar
(wd:Q211694 "MWK") # Malawian kwacha
(wd:Q4730 "MXN") # Mexican peso
(wd:Q163712 "MYR") # Malaysian ringgit
(wd:Q200753 "MZN") # Mozambican metical
(wd:Q203567 "NGN") # Nigerian naira
(wd:Q207312 "NIO") # Nicaraguan córdoba
(wd:Q788472 "NLG") # Dutch guilder
(wd:Q132643 "NOK") # Norwegian krone
(wd:Q202895 "NPR") # Nepalese rupee
(wd:Q1472704 "NZD") # New Zealand dollar
(wd:Q17193 "PHP") # Philippine peso
(wd:Q123213 "PLN") # złoty
(wd:Q468474 "PTE") # Portuguese escudo
(wd:Q206386 "QAR") # Qatari riyal
(wd:Q41044 "RUB") # Russian ruble
(wd:Q199857 "SAR") # Saudi riyal
(wd:Q122922 "SEK") # Swedish krona
(wd:Q190951 "SGD") # Singapore dollar
(wd:Q4587 "SLL") # Sierra Leonean leone
(wd:Q193712 "STD") # São Tomé and Príncipe dobra
(wd:Q193712 "STN") # São Tomé and Príncipe dobra
(wd:Q615640 "SUR") # Soviet ruble
(wd:Q177882 "THB") # Thai baht
(wd:Q199886 "TJS") # Tajikistani somoni
(wd:Q4602 "TND") # Tunisian dinar
(wd:Q4613 "TOP") # Tongan paʻanga
(wd:Q1367371 "TPE") # Portuguese Timorese escudo
(wd:Q208526 "TWD") # New Taiwan dollar
(wd:Q81893 "UAH") # hryvnia
(wd:Q4917 "USD") # United States dollar
(wd:Q209272 "UYU") # Uruguayan peso
(wd:Q203757 "VEB") # Venezuelan bolívar
(wd:Q203757 "VEF") # Venezuelan bolívar
(wd:Q207523 "VUV") # Vanuatu vatus
(wd:Q4588 "WST") # Samoan tālā
(wd:Q847739 "XAF") # Central African CFA franc
(wd:Q191511 "XAF") # CFA franc
(wd:Q131723 "XBT") # Bitcoin
(wd:Q221828 "XEU") # European Currency Unit
(wd:Q861690 "XOF") # West African CFA franc
(wd:Q240512 "YER") # Yemeni rial
(wd:Q181907 "ZAR") # South African rand
}
}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
?it p:P159 ?locStmt.
?locStmt a wikibase:BestRank.
bind(uri(concat(?_item,"/address")) as ?address)
optional {?locStmt ps:P159 ?location
optional{?location skos:prefLabel ?locationLabel_en. filter(lang(?locationLabel_en)="en")}
optional{?location skos:prefLabel ?locationLabel_de. filter(lang(?locationLabel_de)="de")}
optional{?location skos:prefLabel ?locationLabel_fr. filter(lang(?locationLabel_fr)="fr")}
optional{?location skos:prefLabel ?locationLabel_es. filter(lang(?locationLabel_es)="es")}
optional{?location skos:prefLabel ?locationLabel_it. filter(lang(?locationLabel_it)="it")}
optional{?location skos:prefLabel ?locationLabel_zh. filter(lang(?locationLabel_zh)="zh")}
bind(coalesce(?locationLabel_en,?locationLabel_de,?locationLabel_fr,?locationLabel_es,?locationLabel_it,?locationLabel_zh) as ?locationLabel)}
optional {?locStmt pq:P6375 ?fullAddress}
optional {?locStmt pq:P969 ?street}
optional {?locStmt pq:P669 ?str
optional{?str skos:prefLabel ?strLabel_en. filter(lang(?strLabel_en)="en")}
optional{?str skos:prefLabel ?strLabel_de. filter(lang(?strLabel_de)="de")}
optional{?str skos:prefLabel ?strLabel_fr. filter(lang(?strLabel_fr)="fr")}
optional{?str skos:prefLabel ?strLabel_es. filter(lang(?strLabel_es)="es")}
optional{?str skos:prefLabel ?strLabel_it. filter(lang(?strLabel_it)="it")}
optional{?str skos:prefLabel ?strLabel_zh. filter(lang(?strLabel_zh)="zh")}
bind(coalesce(?strLabel_en,?strLabel_de,?strLabel_fr,?strLabel_es,?strLabel_it,?strLabel_zh) as ?strLabel)}
optional {?locStmt pq:P670 ?number}
optional {?locStmt pq:P4856 ?bldg bind(concat("Bldg.",?bldg) as ?building)}
optional {?locStmt pq:P5423 ?floor}
optional {?locStmt pq:P281 ?postCode}
optional {?locStmt pqv:P625 ?geo.
bind(uri(concat(?_item,"/address/geo")) as ?geoCoord)
?geo wikibase:geoLatitude ?lat; wikibase:geoLongitude ?long.
bind(xsd:decimal(?lat) as ?latitude)
bind(xsd:decimal(?long) as ?longitude)
optional {?geo wikibase:geoPrecision ?prec
bind(xsd:decimal(?prec*111000.0) as ?precision)}}}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
{?it p:P793 ?exchStmt. ?exchStmt ps:P793 wd:Q185142 bind(<transaction/exchangeListing/ipo> as ?exchListingType)}
union {?it p:P414 ?exchStmt. bind(<transaction/exchangeListing> as ?exchListingType)}
optional {?exchStmt (ps:P414|pq:P414) ?exch.
bind(strafter(str(?exch),str(wd:)) as ?exchId)
bind(uri(concat(str(WD:),?exchId)) as ?exchange)}
bind(if(exists{?it wdt:P414 ?exch},true,?NULL) as ?isCurrent)
optional {?exchStmt pq:P249 ?stockSymbol}
optional {?exchStmt (pq:P580|pq:P585|pq:P571) ?exchStartDateTime bind(strdt(strbefore(str(?exchStartDateTime),"T"),xsd:date) as ?exchStartDate)}
optional {?exchStmt (pq:P582|pq:P576) ?exchEndDateTime bind(strdt(strbefore(str(?exchEndDateTime),"T"),xsd:date) as ?exchEndDate)}
bind(uri(concat(?_item,"/exchangeListing",
if(bound(?exchId),concat("/",?exchId),""),
if(bound(?exchStartDate),concat("/",str(?exchStartDate)),""))) as ?exchListing)}
union {[a schema:Dataset; schema:about ?it; ]
bind(strafter(str(?it),str(wd:))as?WD) bind(concat(str(WD:),?WD)as?_item) bind(uri(?_item)as?item)
values (?relProp ?relType ?relKind) {
("P112" "founder" "forward" )
("P127" "owner" "forward" )
("P1365" "replaces" "forward" )
("P155" "follows" "forward" )
("P1951" "investor" "forward" )
("P749" "parent" "forward" )
("P807" "forkedFrom" "forward" )
("P1366" "replaces" "inverse" )
("P156" "follows" "inverse" )
("P1830" "owner" "inverse" )
("P199" "divisionOf" "inverse" )
("P355" "parent" "inverse" )
("P2652" "partner" "symmetric")
("P1889" "different" "symmetric")
}
bind(uri(concat(str(wdt:), ?relProp)) as ?wdtRelProp)
bind(uri(concat(str(p:), ?relProp)) as ?pRelProp )
bind(uri(concat(str(ps:), ?relProp)) as ?psRelProp )
?it ?pRelProp ?stmt. ?stmt ?psRelProp ?other.
bind(if(exists{?it ?wdtRelProp ?other},true,?NULL) as ?isCurrent)
optional {?stmt (pq:P580|pq:P585|pq:P571) ?relStartDateTime bind(strdt(strbefore(str(?relStartDateTime),"T"),xsd:date) as ?relStartDate)}
optional {?stmt (pq:P582|pq:P576) ?relEndDateTime bind(strdt(strbefore(str(?relEndDateTime),"T"),xsd:date) as ?relEndDate)}
optional {?stmt pq:P1107 ?proportion}
bind(strafter(str(?it), str(wd:)) as ?itId)
bind(strafter(str(?other),str(wd:)) as ?otherId)
bind(if(?relKind="forward",?itId, if(?relKind="inverse",?otherId,if(?itId < ?otherId,?itId,?otherId))) as ?ag1)
bind(if(?relKind="forward",?otherId,if(?relKind="inverse",?itId, if(?itId < ?otherId,?otherId,?itId))) as ?ag2)
bind(uri(concat(str(WD:),?ag1,"/",?relType,"/",?ag2)) as ?orgRel)
bind(uri(concat("relation/",?relType)) as ?relationType)
bind(if(?relKind!="symmetric",uri(concat(str(WD:),?ag1)),?NULL) as ?agentMinor)
bind(if(?relKind!="symmetric",uri(concat(str(WD:),?ag2)),?NULL) as ?agentMajor)
bind(if(?relKind ="symmetric",uri(concat(str(WD:),?ag1)),?NULL) as ?agent1)
bind(if(?relKind ="symmetric",uri(concat(str(WD:),?ag2)),?NULL) as ?agent2)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment