Importer creates duplicate citizenship properties for same country with different time periods

[monneyboi]

Problem

The importer creates multiple citizenship properties for the same politician-country combination when Wikidata has multiple statements with different start/end time qualifiers. This results in 30 duplicate entries in our database.

Examples

• Edward Leung (Q22094633) - 2x China (Q148)
• Anne Hidalgo (Q2851133) - 2x Spain (Q29)
• George Weah (Q173139) - 2x France (Q142)
• Ricky Martin (Q128799) - 2x Puerto Rico (Q1183)

San Marino (Q238) is particularly affected with 7 politicians having duplicate citizenships.

Expected Behavior

We should only store one citizenship property per politician-country combination. The time period qualifiers (start/end dates) are not relevant for our use case.

Suggested Fix

During import, deduplicate citizenship statements by (politician_id, entity_id) - only import the first one encountered or the one without time qualifiers.

[monneyboi]

In the case of Ricky, it's citizenships that have been deleted in Wikidata. It could be our property clean up (2 dumps not seen) strategy has to kick in still, we should check this.

[monneyboi]

SELECT pol.wikidata_id, pol.name, p.entity_id, we.name as country, COUNT(*) as cnt
FROM properties p
JOIN politicians pol ON pol.id = p.politician_id
JOIN wikidata_entities we ON we.wikidata_id = p.entity_id
WHERE p.type = 'CITIZENSHIP' AND p.deleted_at IS NULL
GROUP BY pol.wikidata_id, pol.name, p.entity_id, we.name
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

Query to find these people for reference.