Skip to content

Instantly share code, notes, and snippets.

@VladimirAlexiev

VladimirAlexiev/CHIN-restructure.md

Last active June 10, 2026 11:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save VladimirAlexiev/cf2de89b692bbc2ae70917aae021ec07 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
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.sparql 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 Emacs Orgmode 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
#+startup: showeverything
#+options: ':nil *:t -:t ::t <:t H:5 \n:nil ^:{} anchor:t arch:headline author:t
#+options: broken-links:nil c:nil creator:nil d:(not "LOGBOOK") date:t e:t email:nil f:t
#+options: inline:t num:nil p:nil pri:nil prop:nil stat:t tags:t tasks:t tex:t
#+options: timestamp:nil title:t toc:5 todo:t |:t
#+title: Wikidata to Company Graph Mapping
#+date: <2021-11-04>
#+author: Vladimir Alexiev
#+email: vladimir.alexiev@ontotext.com
#+language: en
#+select_tags: export
#+exclude_tags: noexport
#+creator: Emacs 26.1 (Org mode 9.2.2)
* Approach
:PROPERTIES:
:CUSTOM_ID: approach
:END:
Overall approach:
- Select organization types using a whitelist (eg Enterprise, Business) and a blacklist (eg School, Church), and subclass querying
- Save a precomputed list org 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 ([[https://sourceforge.net/projects/eulersharp/files/eulersharp/][Euler]], [[http://eulergui.sourceforge.net/][EulerGUI]] or [[https://github.com/josd/eye/][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
:PROPERTIES:
:CUSTOM_ID: literate-programming
:END:
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
:PROPERTIES:
:CUSTOM_ID: m4-macros
:END:
We use the [[https://www.gnu.org/savannah-checkouts/gnu/m4/manual/m4-1.4.18/m4.html][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 [[https://emacs.stackexchange.com/questions/53417/how-to-speed-up-org-babel-tangle][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 [[https://github.com/Starlink/m4/tree/master/examples][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!
#+begin_src m4 :tangle yes
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($@)))')')
#+end_src
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:
#+begin_src m4
define(`LANGS',`en,de,fr,es,it,zh')
bind(coalesce(foreach_sep(`LANG',(LANGS),`$3`_'LANG',`,')) as $3)')
^^^^^^^^^^^^^^ ^ ^ ^^^^^
#+end_src
The M4 "variable" ~LANG~ iterates over ~LANGS~. So if ~$3=descr~, this is expanded to:
#+begin_src sparql
bind(coalesce(?descr_en,?descr_de,?descr_fr,?descr_es,?descr_it,?descr_zh)) as ?descr)
#+end_src
** SPARQL Mechanics
:PROPERTIES:
:CUSTOM_ID: sparql-mechanics
:END:
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 [[https://github.com/w3c/sparql-12/issues/103][sparql-12#103]]),
we need to repeat the ~INPUT_root~ in every UNION clause.
** Variable Naming
:PROPERTIES:
:CUSTOM_ID: variable-naming
:END:
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
:PROPERTIES:
:CUSTOM_ID: mapping
:header-args:m4: :tangle yes
:END:
** Root
:PROPERTIES:
:CUSTOM_ID: root
:END:
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.
#+begin_src m4
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)')
#+end_src
*** Person or Organization
:PROPERTIES:
:CUSTOM_ID: person-or-organization
:END:
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)
#+begin_src m4
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)')
#+end_src
*** Union Clauses
:PROPERTIES:
:CUSTOM_ID: union-clauses
:END:
Every input clause repeats ~INPUT_root~ and is emitted in a UNION,
where ~IN_root~ is the first union clause.
#+begin_src m4
dnl $1=clause to append to UNION
define(`UNION',`union {INPUT_root()
$1}')
#+end_src
*** Root Data
:PROPERTIES:
:CUSTOM_ID: root-data
:END:
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)
#+begin_src m4
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
}')
#+end_src
Then we output it like this:
#+begin_src m4
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
')
#+end_src
Note: in a previous version we computed the type dynamically:
~Person~ (mapped from ~wd:Q5~ Human) or ~Organization~ (everything else)
#+begin_src sparql
bind(if(exists{?it wdt:P31 wd:Q5}, schema:Person, rov:RegisteredOrganization) as ?type)
#+end_src
TODO: update ~<dataset/WD>~ in CG-static, describing the date when we crawled it and more accurate volumetrics.
** Identifiers
:PROPERTIES:
:CUSTOM_ID: identifiers
:END:
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.
| Code | WD prop | description | urlTemplate | Note |
|-----------------|---------|------------------------------------------|--------------------------------------------|-------------------------------|
| email | P968 | Email address | mailto:{} | |
| phone | P1329 | Phone number | | |
| website | P856 | Official website | {} | |
| blog | P1581 | Official blog | {} | 2 map the same |
| blog | P1019 | Web feed URL | {} | 2 map the same |
| CB | P2087 | Crunchbase person id/website slug | https://www.crunchbase.com/person/{} | 2 map the same |
| CB | P2088 | Crunchbase organization id/website slug | https://www.crunchbase.com/organization/{} | 2 map the same |
| Crossref/funder | P3153 | Crossref funder ID | | |
| FB | P2013 | Facebook id/website slug | https://www.facebook.com/{} | |
| GLEI | P1278 | Global Legal Entity Identifier | | |
| GRID | P2427 | Global Research Identifier Database | https://www.grid.ac/institutes/{} | |
| GS1 | P3193 | GS1 Manufacturer code | | |
| GitHub | P2037 | GitHub username | | |
| ISNI | P213 | International Standard Name Identifier | http://www.isni.org/{} | |
| Instagram | P2003 | Instagram username | | |
| JP | P3225 | JP Corporate Number | | |
| KG | P646 | Freebase/Google Knowledge Graph | http://g.co/kg/{} | |
| LI | P2035 | LinkedIn person website URL (deprecated) | {} | 3 map the same |
| LI | P4264 | LinkedIn organization id/website slug | https://www.linkedin.com/company/{} | 3 map the same |
| LI | P6634 | LinkedIn person website id/slug | https://www.linkedin.com/in/{}/ | 3 map the same |
| MAG | P6366 | Microsoft Academic Graph | https://academic.microsoft.com/#/detail/{} | http://ma-graph.org/entity/{} |
| OCORP | P1320 | OpenCorporates id | | |
| ROR | P6782 | Research Organization Registry | https://ror.org/{} | |
| Subreddit | P3984 | Subreddit | | |
| TR/permid | P3347 | Thomson Reuters Open PermID | https://permid.org/1-{} | |
| TW | P2002 | Twitter id/username | https://twitter.com/{} | |
| US-IRS | P1297 | US IRS Employer Identification | | |
| US-SEC-CIK | P5531 | US SEC CIK | | |
| VIAF | P214 | Virtual International Authority File | https://viaf.org/viaf/{} | |
| YouTube | P2397 | YouTube 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 [[https://ontotext.atlassian.net/browse/DEM-720][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.
#+begin_src m4
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))')
#+end_src
All Identifiers are output together:
#+begin_src m4
define(`OUT_IDENTIFIER',` ?item adms:identifier ?ident.
?ident a adms:Identifier; skos:notation ?idValue; cg:identRaw ?idValue; dct:isPartOf ?identSystem; dct:source <dataset/WD>.')
#+end_src
*** Wikidata Identifier
:PROPERTIES:
:CUSTOM_ID: wikidata-identifier
:END:
~?WD~ is already computed, so it's merely re-bound:
#+begin_src m4
define(`IN_identWD',`UNION(
bind(?WD as ?idValue)
bind(uri(concat(?_item,"/WD")) as ?ident)
bind(<identifier/WD> as ?identSystem))')
#+end_src
*** DBpedia Identifier
:PROPERTIES:
:CUSTOM_ID: dbpedia-identifier
:END:
~?DBP~ id is computed from the English Wikipedia link.
#+begin_src m4
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))')
#+end_src
*** Image
:PROPERTIES:
:CUSTOM_ID: image
:END:
[[https://ontotext.atlassian.net/browse/DEM-717][DEM-717]]
Which WD image prop should we use?
- For people, we use P18 Image, eg see images of [[https://www.wikidata.org/wiki/Q78713#P18][Ferdinand Piëch]] and [[https://www.wikidata.org/wiki/Q317521#P18][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 [[https://www.wikidata.org/wiki/Q156578#P154][Volkswagen Group]] and [[https://www.wikidata.org/wiki/Q28874479#P154][The Boring Company]]
- Often they also have P18 Image, but that usually shows the headquarters or a plant (eg [[http://commons.wikimedia.org/wiki/Special:FilePath/VW%2520Werk%2520Altes%2520Heizkraftwerk%2520WB%2520edit.jgp][VW Werk Altes Heizkraftwerk WB edit.jpg]])
- Sometimes it shows the logo of a product eg [[http://commons.wikimedia.org/wiki/Special:FilePath/Volkswagen.jpg][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~.
#+begin_src m4
define(`ID_image',`IF_PERSON(wdt:P18,wdt:P154)')
#+end_src
** Label Languages
:PROPERTIES:
:CUSTOM_ID: label-languages
:END:
WD includes labels and descriptions in a gazillion languages.
We want to limit to a smaller list.
#+begin_src m4
define(`LANGS',`en,de,fr,es,it,zh')
define(`LANGS_QUOTED',`foreach_sep(`LANG',(LANGS),`"LANG"',`,')')
#+end_src
~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
#+begin_src m4
dnl $1=resource, $2=prop, $3=var
define(`LANG_ALL',`optional{$1 $2 $3. filter(lang($3) in (LANGS_QUOTED))}')
#+end_src
~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
#+begin_src m4
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)')
#+end_src
** Labels and Descriptions
:PROPERTIES:
:CUSTOM_ID: labels-and-descriptions
:END:
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~
#+begin_src m4
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.')
#+end_src
** Concepts
:PROPERTIES:
:CUSTOM_ID: concepts
:END:
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~.
#+begin_src m4
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)')
#+end_src
** Dates and Events
:PROPERTIES:
:CUSTOM_ID: dates-and-events
:END:
*** Dates
:PROPERTIES:
:CUSTOM_ID: dates
:END:
We map two simple dates (we assume they are single-valued).
#+begin_src m4
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.')
#+end_src
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 [[https://ontotext.atlassian.net/browse/DEM-727][DEM-727]] is worked out, some Significant Events should contribute to these dates
*** TODO Events
:PROPERTIES:
:CUSTOM_ID: events
:END:
[[https://ontotext.atlassian.net/browse/DEM-727][DEM-727]]
** Exchange Listing
:PROPERTIES:
:CUSTOM_ID: exchange-listing
:END:
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).
#+begin_src m4
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>.')
#+end_src
** TODO Stock Index
:PROPERTIES:
:CUSTOM_ID: stock-index
:END:
Should we map stock (exchange) indexes? They are modeled with ps:P361 Part Of; pq:P580 Start Time.
Examples:
- [[https://www.wikidata.org/wiki/Q156578#P361][Volkswagen Group part of DAX]]; start time 1 Jul 1988
- [[https://www.wikidata.org/wiki/Q30285363#P361][Sirma Group part of SOFIX]]; start time 7 Sep 2017
** Org Relations
:PROPERTIES:
:CUSTOM_ID: org-relations
:END:
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~
#+begin_src m4
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}
#+end_src
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~
#+begin_src m4
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)
)')
#+end_src
Finally we output the data:
#+begin_src m4
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>.')
#+end_src
** Business Flags
:PROPERTIES:
:CUSTOM_ID: business-flags
:END:
*** isStateOwned
:PROPERTIES:
:CUSTOM_ID: isstateowned
:END:
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: [[https://www.wikidata.org/wiki/Q7603018][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":
#+begin_src m4
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.')
#+end_src
*** isPubliclyTraded
:PROPERTIES:
:CUSTOM_ID: ispubliclytraded
:END:
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")
#+begin_src m4
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.')
#+end_src
*** isStartup
:PROPERTIES:
:CUSTOM_ID: isstartup
:END:
There are two types that indicate startup: Q129238 startup, Q20983877 unicorn:
#+begin_src m4
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.')
#+end_src
** Location
:PROPERTIES:
:CUSTOM_ID: location
:END:
Analysis in [[./README.md#location]].
*** Country
:PROPERTIES:
:CUSTOM_ID: country
:END:
Analysis in [[./README.md#country]].
We use ~undivert()~ (file inclusion) to read a table that maps WD countries to Geonames.
#+begin_src m4
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>.')
#+end_src
*** Address
:PROPERTIES:
:CUSTOM_ID: address
:END:
Analysis in [[./README.md#address]]. We map address fields as follows:
#+begin_src m4
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.')
#+end_src
Remaining tasks:
- [[https://ontotext.atlassian.net/browse/DEM-735][DEM-735]] geoPrecision to geoResolution
- [[https://ontotext.atlassian.net/browse/DEM-736][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 [[https://github.com/w3c/sparql-12/issues/103][sparql-12#103]] is "fixed" on rdf4j, it might break.
** Observations
:PROPERTIES:
:CUSTOM_ID: observations
:END:
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
#+begin_src m4
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>)')
#+end_src
There are different kinds of observations:
- The following (17k) are decimal, converted to millions:
#+BEGIN_SRC m4
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)')')
#+end_src
- The following (10k) are converted to integer:
#+begin_src m4
define(`OBS_INTEGER',`OBSERVATIONS(`
OBS(P1128, employees)')')
#+END_SRC
- The following are not present in WD:
- sharePrice
- fundingRounds
- fundingTotal
- fundingDateLast
- investmentCount
- investmentTotal
*** Filtering Observation Values
:PROPERTIES:
:CUSTOM_ID: filtering-observation-values
:END:
A few secretive organizations include useless info:
#+begin_src sparql
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". }
}
#+end_src
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:
- [[https://www.wikidata.org/wiki/Q1005549][Q1005549]] Federal Agency for Public Safety Digital Radio (Germany): employees: unknown value; minimum value: 300
- [[https://www.wikidata.org/wiki/Q1195162][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
:PROPERTIES:
:CUSTOM_ID: observation-currency
:END:
[[https://ontotext.atlassian.net/browse/DEM-732][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:
#+begin_src Turtle
# 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
#+end_src
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.
#+begin_src sparql
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)
#+end_src
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:
#+begin_src m4
define(`CURRENCIES',`values (?curr ?currCode) {
undivert(`WD-currencies.txt')}')
#+end_src
*** Observation Mapping
:PROPERTIES:
:CUSTOM_ID: observation-mapping
:END:
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~
#+begin_src m4
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'')')
#+end_src
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
#+begin_src m4
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>.')
#+end_src
** All Inputs/Outputs
:PROPERTIES:
:CUSTOM_ID: all-inputs-outputs
:END:
This is the concatenation of individual inputs & outputs
#+name: IN_all
#+begin_src m4
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')
#+end_src
** Prefixes
:PROPERTIES:
:CUSTOM_ID: prefixes
:END:
** SPARQL Body
:PROPERTIES:
:CUSTOM_ID: sparql-body
:END:
- ~divert~ without arguments resumes output (we had it blocked to skip newlines that are part of macro definitions
- ~undivert()~ reads in the prefixes
#+begin_src m4
divert
undivert(`prefixes.rq')
construct {OUT_all
}
where {IN_all
}
#+end_src
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