data-sources/wikipedia-wikidata/import_wikidata.sh

   1 #!/bin/bash
   2
   3 psqlcmd() {
   4      psql --quiet wikiprocessingdb
   5 }
   6
   7 mysql2pgsqlcmd() {
   8      ./mysql2pgsql.perl /dev/stdin /dev/stdout
   9 }
  10
  11 download() {
  12      echo "Downloading $1"
  13      wget --quiet --no-clobber --tries 3
  14 }
  15
  16 # languages to process (refer to List of Wikipedias here: https://en.wikipedia.org/wiki/List_of_Wikipedias)
  17 # requires Bash 4.0
  18 readarray -t LANGUAGES < languages.txt
  19
  20
  21
  22 echo "====================================================================="
  23 echo "Download wikidata dump tables"
  24 echo "====================================================================="
  25
  26 # 114M  wikidatawiki-latest-geo_tags.sql.gz
  27 # 1.7G  wikidatawiki-latest-page.sql.gz
  28 # 1.2G  wikidatawiki-latest-wb_items_per_site.sql.gz
  29 download https://dumps.wikimedia.org/wikidatawiki/latest/wikidatawiki-latest-geo_tags.sql.gz
  30 download https://dumps.wikimedia.org/wikidatawiki/latest/wikidatawiki-latest-page.sql.gz
  31 download https://dumps.wikimedia.org/wikidatawiki/latest/wikidatawiki-latest-wb_items_per_site.sql.gz
  32
  33
  34
  35
  36 echo "====================================================================="
  37 echo "Import wikidata dump tables"
  38 echo "====================================================================="
  39
  40 echo "Importing wikidatawiki-latest-geo_tags"
  41 gzip -dc wikidatawiki-latest-geo_tags.sql.gz          | mysql2pgsqlcmd | psqlcmd
  42
  43 echo "Importing wikidatawiki-latest-page"
  44 gzip -dc wikidatawiki-latest-page.sql.gz              | mysql2pgsqlcmd | psqlcmd
  45
  46 echo "Importing wikidatawiki-latest-wb_items_per_site"
  47 gzip -dc wikidatawiki-latest-wb_items_per_site.sql.gz | mysql2pgsqlcmd | psqlcmd
  48
  49
  50
  51
  52
  53
  54 echo "====================================================================="
  55 echo "Get wikidata places from wikidata query API"
  56 echo "====================================================================="
  57
  58 echo "Number of place types:"
  59 wc -l wikidata_place_types.txt
  60
  61 while read F  ; do
  62     echo "Querying for place type $F..."
  63     wget --quiet "https://query.wikidata.org/bigdata/namespace/wdq/sparql?format=json&query=SELECT ?item WHERE{?item wdt:P31*/wdt:P279*wd:$F;}" -O $F.json
  64     jq -r '.results | .[] | .[] | [.item.value] | @csv' $F.json >> $F.txt
  65     awk -v qid=$F '{print $0 ","qid}' $F.txt | sed -e 's!"http://www.wikidata.org/entity/!!' | sed 's/"//g' >> $F.csv
  66     cat $F.csv >> wikidata_place_dump.csv
  67     rm $F.json $F.txt $F.csv
  68 done < wikidata_place_types.txt
  69
  70
  71
  72
  73 echo "====================================================================="
  74 echo "Import wikidata places"
  75 echo "====================================================================="
  76
  77 echo "CREATE TABLE wikidata_place_dump (
  78         item        text,
  79         instance_of text
  80       );"  | psqlcmd
  81
  82 echo "COPY wikidata_place_dump (item, instance_of)
  83       FROM '/srv/nominatim/Nominatim/data-sources/wikipedia-wikidata/wikidata_place_dump.csv'
  84       DELIMITER ','
  85       CSV
  86       ;"  | psqlcmd
  87
  88 echo "CREATE TABLE wikidata_place_type_levels (
  89         place_type text,
  90         level      integer
  91       );" | psqlcmd
  92
  93 echo "COPY wikidata_place_type_levels (place_type, level)
  94       FROM '/srv/nominatim/Nominatim/data-sources/wikipedia-wikidata/wikidata_place_type_levels.csv'
  95       DELIMITER ','
  96       CSV
  97       HEADER
  98       ;" | psqlcmd
  99
 100
 101
 102
 103 echo "====================================================================="
 104 echo "Create derived tables"
 105 echo "====================================================================="
 106
 107 echo "CREATE TABLE geo_earth_primary AS
 108       SELECT gt_page_id,
 109              gt_lat,
 110              gt_lon
 111       FROM geo_tags
 112       WHERE gt_globe = 'earth'
 113         AND gt_primary = 1
 114         AND NOT(    gt_lat < -90
 115                  OR gt_lat > 90
 116                  OR gt_lon < -180
 117                  OR gt_lon > 180
 118                  OR gt_lat=0
 119                  OR gt_lon=0)
 120       ;" | psqlcmd
 121
 122 echo "CREATE TABLE geo_earth_wikidata AS
 123       SELECT DISTINCT geo_earth_primary.gt_page_id,
 124                       geo_earth_primary.gt_lat,
 125                       geo_earth_primary.gt_lon,
 126                       page.page_title,
 127                       page.page_namespace
 128       FROM geo_earth_primary
 129       LEFT OUTER JOIN page
 130                    ON (geo_earth_primary.gt_page_id = page.page_id)
 131       ORDER BY geo_earth_primary.gt_page_id
 132       ;" | psqlcmd
 133
 134 echo "ALTER TABLE wikidata_place_dump
 135       ADD COLUMN ont_level integer,
 136       ADD COLUMN lat numeric(11,8),
 137       ADD COLUMN lon numeric(11,8)
 138       ;" | psqlcmd
 139
 140 echo "UPDATE wikidata_place_dump
 141       SET ont_level = wikidata_place_type_levels.level
 142       FROM wikidata_place_type_levels
 143       WHERE wikidata_place_dump.instance_of = wikidata_place_type_levels.place_type
 144       ;" | psqlcmd
 145
 146 echo "CREATE TABLE wikidata_places
 147       AS
 148       SELECT DISTINCT ON (item) item,
 149                                 instance_of,
 150                                 MAX(ont_level) AS ont_level,
 151                                 lat,
 152                                 lon
 153       FROM wikidata_place_dump
 154       GROUP BY item,
 155                instance_of,
 156                ont_level,
 157                lat,
 158                lon
 159       ORDER BY item
 160       ;" | psqlcmd
 161
 162 echo "UPDATE wikidata_places
 163       SET lat = geo_earth_wikidata.gt_lat,
 164           lon = geo_earth_wikidata.gt_lon
 165       FROM geo_earth_wikidata
 166       WHERE wikidata_places.item = geo_earth_wikidata.page_title
 167       ;" | psqlcmd
 168
 169
 170
 171
 172 echo "====================================================================="
 173 echo "Process language pages"
 174 echo "====================================================================="
 175
 176
 177 echo "CREATE TABLE wikidata_pages (
 178         item          text,
 179         instance_of   text,
 180         lat           numeric(11,8),
 181         lon           numeric(11,8),
 182         ips_site_page text,
 183         language      text
 184       );" | psqlcmd
 185
 186 for i in "${LANGUAGES[@]}"
 187 do
 188    echo "CREATE TABLE wikidata_${i}_pages AS
 189          SELECT wikidata_places.item,
 190                 wikidata_places.instance_of,
 191                 wikidata_places.lat,
 192                 wikidata_places.lon,
 193                 wb_items_per_site.ips_site_page
 194          FROM wikidata_places
 195          LEFT JOIN wb_items_per_site
 196                 ON (CAST (( LTRIM(wikidata_places.item, 'Q')) AS INTEGER) = wb_items_per_site.ips_item_id)
 197          WHERE ips_site_id = '${i}wiki'
 198            AND LEFT(wikidata_places.item,1) = 'Q'
 199          ORDER BY wikidata_places.item
 200          ;" | psqlcmd
 201
 202    echo "ALTER TABLE wikidata_${i}_pages
 203          ADD COLUMN language text
 204          ;" | psqlcmd
 205
 206    echo "UPDATE wikidata_${i}_pages
 207          SET language = '${i}'
 208          ;" | psqlcmd
 209
 210    echo "INSERT INTO wikidata_pages
 211          SELECT item,
 212                 instance_of,
 213                 lat,
 214                 lon,
 215                 ips_site_page,
 216                 language
 217          FROM wikidata_${i}_pages
 218          ;" | psqlcmd
 219 done
 220
 221 echo "ALTER TABLE wikidata_pages
 222       ADD COLUMN wp_page_title text
 223       ;" | psqlcmd
 224 echo "UPDATE wikidata_pages
 225       SET wp_page_title = REPLACE(ips_site_page, ' ', '_')
 226       ;" | psqlcmd
 227 echo "ALTER TABLE wikidata_pages
 228       DROP COLUMN ips_site_page
 229       ;" | psqlcmd
 230
 231
 232
 233
 234 echo "====================================================================="
 235 echo "Add wikidata to wikipedia_article table"
 236 echo "====================================================================="
 237
 238 echo "UPDATE wikipedia_article
 239       SET lat = wikidata_pages.lat,
 240           lon = wikidata_pages.lon,
 241           wd_page_title = wikidata_pages.item,
 242           instance_of = wikidata_pages.instance_of
 243       FROM wikidata_pages
 244       WHERE wikipedia_article.language = wikidata_pages.language
 245         AND wikipedia_article.title  = wikidata_pages.wp_page_title
 246       ;" | psqlcmd
 247
 248 echo "CREATE TABLE wikipedia_article_slim
 249       AS
 250       SELECT * FROM wikipedia_article
 251       WHERE wikidata_id IS NOT NULL
 252       ;" | psqlcmd
 253
 254 echo "ALTER TABLE wikipedia_article
 255       RENAME TO wikipedia_article_full
 256       ;" | psqlcmd
 257
 258 echo "ALTER TABLE wikipedia_article_slim
 259       RENAME TO wikipedia_article
 260       ;" | psqlcmd
 261
 262
 263
 264
 265 echo "====================================================================="
 266 echo "Dropping intermediate tables"
 267 echo "====================================================================="
 268
 269 echo "DROP TABLE wikidata_place_dump;" | psqlcmd
 270 echo "DROP TABLE geo_earth_primary;" | psqlcmd
 271 for i in "${LANGUAGES[@]}"
 272 do
 273     echo "DROP TABLE wikidata_${i}_pages;" | psqlcmd
 274 done