Bulk update of asset languages

[stijnvermeeren-swisstopo]

At swisstopo, we have applied an automatic language identification algorithm on all the asset PDF documents. Currently, only about 1300 (10%) assets have a language defined in the production database. All other assets have their language set to a default value ("other"). Thanks to the automatic language identification, we can now set a language for almost all assets.

Database
Setting the new language means updating the column «language_item_code» in the database table «asset». I.e. we could generate a lot of statements of the form

UPDATE "asset" SET "language_item_code" = 'xx' WHERE "asset_id" = yyyy;

Shall we execute these statements ourselves, directly on the database? Or does someone have a different suggestion for how to add this new data to the database in a safe and efficient way?

Elasticsearch
The language of an asset is also a field in the Elasticsearch index that is used for the asset search in the UI. In the code, I could only find two locations where data gets sent to this index:

• When editing an asset in the UI
  • https://github.com/geoadmin/web-asset-swissgeol/blob/develop/apps/server-asset-sg/src/app/asset-edit/asset-edit.service.ts
• When executing the Skript export-to-elasticsearch.ts
  • It appears to me, that this script is currently not triggered anywhere in the application, but rather needs to be started manually.
  • The script seems to read all assets from the database (serving as the principal data state) and then (re)indexes all of them into Elasticsearch.
  • https://github.com/geoadmin/web-asset-swissgeol/blob/develop/apps/server-asset-sg/src/app/scripts/export-to-elasticsearch.ts

What is the best way to ensure that the new languages are correctly added to Elasticsearch? Should we run the export-to-elasticsearch.ts script (and if so, how exactly can it be triggered?) after doing the database updated? Or is there a better way?

Note
After #29 is implemented, we will have another update for those assets where the automatic language identification selected multiple languages. However, even with a single language per asset, the new data will provide a lot of additional value, so we would like to already add this data, without waiting for the other issue to be solved.

[vej-ananas]

The estimate of 0.5 days is given that the elasticsearch index update is already solved. #71

[daniel-va]

Da der Elasticsearch-Index seit #71 mit der Datenbank abgeglichen werden kann, ist das Einfügen der Sprachen per SQL-Statements der wahrscheinlich einfachste und beste Weg. Mit #29 muss das Statement aber geändert werden, da Sprachen und Assets neu in einer Many-to-Many-Beziehung stehen:

DELETE FROM asset_language WHERE asset_id = yyyy AND language_item_code = 'other';
INSERT INTO asset_language (asset_id, language_item_code) VALUES (yyyy, 'xx');

Ihr könnt diese Statements gerne selbst ausführen, sobald #71 und #29 jeweils auf dev, int und prod deployed sind. Alternativ könnt ihr uns auch die Statements zukommen lassen, damit wir sie ausführen können.

[stijnvermeeren-swisstopo]

@daniel-va Vielen Dank für das Update. #71 konnte ich mittlerweile testen auf INT, und das scheint gut zu funktionieren.

Ich denke, sobald #71 und #29 auf Prod deployed sind, können wir die Updates selber seitens swisstopo durchführen. Aber wenn wir doch noch Unterstützung brauchen, dann gebe ich sicher Bescheid.