From: Brian Quinion Date: Fri, 6 Sep 2013 09:33:53 +0000 (+0100) Subject: code for importing values from wikidata X-Git-Tag: v2.2.0~39 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/bd26313d5549529a550b466ef70d106addcfaeef code for importing values from wikidata --- diff --git a/wikidata/create.sql b/wikidata/create.sql new file mode 100644 index 00000000..f6327bd0 --- /dev/null +++ b/wikidata/create.sql @@ -0,0 +1,75 @@ +DROP TABLE entity; +DROP TABLE entity_label; +DROP TABLE entity_description; +DROP TABLE entity_alias; +DROP TABLE entity_link; +DROP TABLE entity_property; + +CREATE TABLE entity ( + entity_id bigint, + title text, + pid bigint, + qid bigint, + datatype text, + CONSTRAINT pk_entity PRIMARY KEY(entity_id) +); + +CREATE TABLE entity_label ( + entity_id bigint, + language text, + label text, + CONSTRAINT pk_entity_label PRIMARY KEY(entity_id,language) +); + +CREATE TABLE entity_description ( + entity_id bigint, + language text, + description text, + CONSTRAINT pk_entity_description PRIMARY KEY(entity_id,language) +); + +CREATE TABLE entity_alias ( + entity_id bigint, + language text, + alias text, + CONSTRAINT pk_entity_alias PRIMARY KEY(entity_id,language,alias) +); + +CREATE TABLE entity_link ( + entity_id bigint, + target text, + value text, + CONSTRAINT pk_entity_link PRIMARY KEY(entity_id,target) +); + +CREATE TABLE entity_link_hit ( + entity_id bigint, + target text, + value text, + hits bigint, + CONSTRAINT pk_entity_link_hit PRIMARY KEY(entity_id,target) +); + +CREATE TABLE link_hit ( + target text, + value text, + hits bigint, + CONSTRAINT pk_link_hit PRIMARY KEY(target,value) +); + +CREATE TABLE entity_property ( + entity_id bigint, + order_id bigint, + pid bigint, + string text, + toqid bigint, + location geometry, + datetime timestamp with time zone, + CONSTRAINT pk_entity_property PRIMARY KEY(entity_id, order_id) +); + +CREATE TABLE import_link_hit ( + target text, + value text, + hits bigint +); diff --git a/wikidata/import.sh b/wikidata/import.sh new file mode 100755 index 00000000..c701b032 --- /dev/null +++ b/wikidata/import.sh @@ -0,0 +1,24 @@ +PSQL=/usr/lib/postgresql/9.2/bin/psql -d wikidata + +cat create.sql | $PSQL + +cat entity.csv | $PSQL -c "COPY entity from STDIN WITH CSV" +cat entity_label.csv | $PSQL -c "COPY entity_label from STDIN WITH CSV" +cat entity_description.csv | $PSQL -c "COPY entity_description from STDIN WITH CSV" +cat entity_alias.csv | $PSQL -c "COPY entity_alias from STDIN WITH CSV" +cat entity_link.csv | $PSQL -c "COPY entity_link from STDIN WITH CSV" +cat entity_property.csv | $PSQL -c "COPY entity_property from STDIN WITH CSV" + +$PSQL -c "create index idx_entity_link_target on entity_link using btree (target,value)" +$PSQL -c "create index idx_entity_qid on entity using btree (qid)" +$PSQL -c "create table property_label_en as select pid,null::text as label from entity where pid is not null" +$PSQL -c "update property_label_en set label = x.label from (select pid,label,language from entity join entity_label using (entity_id) where pid is not null and language = 'en') as x where x.pid = property_label_en.pid" +$PSQL -c "create unique index idx_property_label_en on property_label_en using btree (pid)" +$PSQL -c "alter table entity add column label_en text" +$PSQL -c "update entity set label_en = label from entity_label where entity.entity_id = entity_label.entity_id and language = 'en'" +$PSQL -c "alter table entity add column description_en text" +$PSQL -c "update entity set description_en = description from entity_description where entity.entity_id = entity_description.entity_id and language = 'en'" + +cat totals.txt | $PSQL -c "COPY import_link_hit from STDIN WITH CSV DELIMITER ' '" +$PSQL -c "insert into link_hit select target||'wiki', catch_decode_url_part(value), sum(hits) from import_link_hit group by target||'wiki', catch_decode_url_part(value)" +$PSQL -c "insert into entity_link_hit select entity_id, target, value, coalesce(hits,0) from entity_link left outer join link_hit using (target, value)" diff --git a/wikidata/parse.php b/wikidata/parse.php new file mode 100755 index 00000000..e1b0687f --- /dev/null +++ b/wikidata/parse.php @@ -0,0 +1,188 @@ +#!/usr/bin/php -Cq +') { + $sTitle = substr($sLine, 11, -9); + } + else if (substr($sLine, 0, 8) == ' ') { + $iNS = (int)substr($sLine, 8, -6); + } + else if (substr($sLine, 0, 8) == ' ') { + $iID = (int)substr($sLine, 8, -6); + } + else if (substr($sLine, 0, 33) == ' ') { + if ($iNS == -2) continue; + if ($iNS == -1) continue; + if ($iNS == 1) continue; + if ($iNS == 2) continue; + if ($iNS == 3) continue; + if ($iNS == 4) continue; + if ($iNS == 5) continue; + if ($iNS == 6) continue; + if ($iNS == 7) continue; + if ($iNS == 8) continue; + if ($iNS == 9) continue; + if ($iNS == 10) continue; + if ($iNS == 11) continue; + if ($iNS == 12) continue; + if ($iNS == 13) continue; + if ($iNS == 14) continue; + if ($iNS == 15) continue; + if ($iNS == 121) continue; + if ($iNS == 123) continue; + if ($iNS == 829) continue; + if ($iNS == 1198) continue; + if ($iNS == 1199) continue; + $sText = html_entity_decode(substr($sLine, 33, -8), ENT_COMPAT, 'UTF-8'); + $aArticle = json_decode($sText, true); + + if (array_diff(array_keys($aArticle), array("label", "description", "aliases", "links", "entity", "claims", "datatype")) != array()) { + // DEBUG + var_dump($sTitle); + var_dump(array_keys($aArticle)); + var_dump($aArticle); + exit; + } + + $iPID = $iQID = null; + if ($aArticle['entity'][0] == 'p') { + $iPID = (int)substr($aArticle['entity'], 1); + } else if ($aArticle['entity'][0] == 'q') { + $iQID = (int)substr($aArticle['entity'], 1); + } else { + continue; + } + + echo "."; + + fputcsv($hFileEntity, array($iID,$sTitle,$iPID,$iQID,@$aArticle['datatype'])); + + foreach($aArticle['label'] as $sLang => $sLabel) { + fputcsv($hFileEntityLabel, array($iID,$sLang,$sLabel)); + // echo "insert into entity_label values (".$iID.",'".pg_escape_string($sLang)."','".pg_escape_string($sLabel)."');\n"; + } + + foreach($aArticle['description'] as $sLang => $sLabel) { + fputcsv($hFileEntityDescription, array($iID,$sLang,$sLabel)); + // echo "insert into entity_description values (".$iID.",'".pg_escape_string($sLang)."','".pg_escape_string($sLabel)."');\n"; + } + + foreach($aArticle['aliases'] as $sLang => $aLabels) { + $aUniqueAlias = array(); + foreach($aLabels as $sLabel) { + if (!isset($aUniqueAlias[$sLabel]) && $sLabel) { + fputcsv($hFileEntityAlias, array($iID,$sLang,$sLabel)); + // echo "insert into entity_alias values (".$iID.",'".pg_escape_string($sLang)."','".pg_escape_string($sLabel)."');\n"; + $aUniqueAlias[$sLabel] = true; + } + } + } + + foreach($aArticle['links'] as $sLang => $sLabel) { + fputcsv($hFileEntityLink, array($iID,$sLang,$sLabel)); + // echo "insert into entity_link values (".$iID.",'".pg_escape_string($sLang)."','".pg_escape_string($sLabel)."');\n"; + } + + + if (isset($aArticle['claims'])) { + + foreach($aArticle['claims'] as $iClaim => $aClaim) { + + $bFail = false; + if ($aClaim['m'][0] == 'novalue') continue; + if ($aClaim['m'][0] == 'somevalue') continue; + $iPID = (int)$aClaim['m'][1]; + if ($aClaim['m'][0] != 'value') $bFail = true; + if ($aClaim['m'][2]== 'wikibase-entityid') { + + if ($aClaim['m'][3]['entity-type'] != 'item') $bFail = true; + fputcsv($hFileEntityProperty, array($iID,$iClaim,$iPID,null,$aClaim['m'][3]['numeric-id'],null,null)); + // echo "insert into entity_property values (nextval('seq_entity_property'),".$iID.",".$iPID.",null,".$aClaim['m'][3]['numeric-id'].",null);\n"; + + } elseif ($aClaim['m'][2] == 'globecoordinate') { + + if ($aClaim['m'][3]['globe'] != 'http://www.wikidata.org/entity/Q2') $bFail = true; + fputcsv($hFileEntityProperty, array($iID,$iClaim,$iPID,null,null,"SRID=4326;POINT(".((float)$aClaim['m'][3]['longitude'])." ".((float)$aClaim['m'][3]['latitude']).")",null)); + // echo "insert into entity_property values (nextval('seq_entity_property'),".$iID.",".$iPID.",null,null,ST_SetSRID(ST_MakePoint(".((float)$aClaim['m'][3]['longitude']).", ".((float)$aClaim['m'][3]['latitude'])."),4326));\n"; + + } elseif ($aClaim['m'][2] == 'time') { + // TODO! +/* + if ($aClaim['m'][3]['calendarmodel'] == 'http://www.wikidata.org/entity/Q1985727') { + // Gregorian + if (preg_match('#(\\+|-)0*([0-9]{4})-([0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2})Z#', $aClaim['m'][3]['time'], $aMatch)) { + if ((int)$aMatch[2] < 4700 && ) { + $sDateString = $aMatch[2].'-'.$aMatch[3].($aClaim['m'][3]['timezone']>=0?'+':'').$aClaim['m'][3]['timezone'].($aMatch[1]=='-'?' bc':''); + fputcsv($hFileEntityProperty, array($iID,$iClaim,$iPID,null,null,null,$sDateString)); + } + } else { +// $bFail = true; + } + } elseif ( $aClaim['m'][3]['calendarmodel'] != 'http://www.wikidata.org/entity/Q1985786') { +/ * + // Julian + if (preg_match('#(\\+|-)0*([0-9]{4})-([0-9]{2})-([0-9]{2})T([0-9]{2}:[0-9]{2}:[0-9]{2})Z#', $aClaim['m'][3]['time'], $aMatch)) { +var_dump($aMatch); +exit; +$iDayCount = juliantojd(2, 11, 1732); +var_dump($iDayCount, jdtogregorian($iDayCount)); + } else { + $bFail = true; +exit; + } +exit; +* / + } else { +// $bFail = true; + } +*/ + } elseif ($aClaim['m'][2] == 'string') { + + // echo "insert into entity_property values (nextval('seq_entity_property'),".$iID.",".$iPID.",'".pg_escape_string($aClaim['m'][3])."',null,null);\n"; + fputcsv($hFileEntityProperty, array($iID,$iClaim,$iPID,$aClaim['m'][3],null,null,null)); + + } else { + + $bFail = true; + + } + + // Don't care about sources: if ($aClaim['refs'] != array()) $bFail = true; + + if ($bFail) { + var_dump($sTitle); + var_dump($aClaim); + } else { + // process + } + + } + + } + } + } + fclose($hFile); + fclose($hFileEntity); + fclose($hFileEntityLabel); + fclose($hFileEntityDescription); + fclose($hFileEntityAlias); + fclose($hFileEntityLink); + fclose($hFileEntityProperty); +}