Gestion des doublons

Préambule

Pour nettoyer la base de données, il m'est chargé de trouver une méthode efficace pour supprimer les doublons provenant des différentes sources de la même ville. Au début du projet dans l'interface d'administration seulement les oeuvres les plus proches étaient suggérés en tant que doublons. Et il fallait sélectionner manuellement chaque oeuvre pour voir sa liste potentielle de doublons qui pouvaient aussi afficher des entrées n'ayant aucun rapport avec l'oeuvre en question si ce n'est une localisation proche. Ayant à date plus de 1544 oeuvres, cette méthode se révèle pénible et une de mes tâches sera de trouver un moyen d'automatiser autant que possible la chose.

Identification des doublons

Pour trouver les doublons, j'ai utilisé la formule d'Haversine
d = 2r \arcsin\left(\sqrt{\sin^2\left(\dfrac{\varphi_2 - \varphi_1}{2}\right) + \cos(\varphi_1) \cos(\varphi_2)\sin^2\left(\dfrac{\lambda_2 - \lambda_1}{2}\right)}\right)d=2rarcsin(sin2(φ2φ12)+cos(φ1)cos(φ2)sin2(λ2λ12))d = 2r \arcsin\left(\sqrt{\sin^2\left(\dfrac{\varphi_2 - \varphi_1}{2}\right) + \cos(\varphi_1) \cos(\varphi_2)\sin^2\left(\dfrac{\lambda_2 - \lambda_1}{2}\right)}\right)
me permettant d'identifier les ressources les plus proches entre elles. Parmi ces ressources proches, j'ai considéré que les doublons ne pouvaient pas provenir de la même source car je suppose que les sources de données sont fiables et que deux oeuvres d'art proches l'une de l'autre provenant de la même source et ayant des titres ou/et artistes similaires sont plus susceptibles de provenir d'un même regroupement d'oeuvres d'art mais elles restent uniques entre elles. Enfin pour que des oeuvres d'art soient considérées comme doublon, non seulement il faut une localisation proche mais aussi au moins une similarité parmi les titres ou nom des artistes de ces oeuvres.
Et j'ai considéré le cas des titres nuls ou intitulés "sans titre" ou toute autre formulation similaire comme étant des titres non similaires avec un autre. Et nous n'avons pas de similarité pour les artistes si les entrées associées sont vides ou nulles.

Formulation

Pour deux entrées OOO et DDD de la jointure de la table artworks avec artists,

SiSiSi

  • OOO et DDD partagent une localisation proche,
  • (OOO et DDD proviennent de différentes sources) et
  • (le titre de OOO et le titre de DDD sont similaires) et (le titre de OOO et le titre de DDD ne sont pas inconnues)
    ou
    (l'artiste de OOO et l'artiste de DDD sont similaires) et (l'artiste de OOO et l'artiste de DDD ne sont pas inconnues)

AlorsAlorsAlors (O,D)(O,D)(O,D) forme une paire de doublons où l'un des deux élements est un possible original et l'autre le duplicata de cet possible original.


Un titre est dit inconnu si le titre est nul ou s'il est composé d'élément indiquant qu'il est sans titre comme "Sans titre", "Non titré" ou "Sans-titre".


Un artiste est dit inconnu si il est nul ou s'il est vide.


Deux titres sont dit similaires s'ils ne sont pas inconnus et l'un est composé de l'autre.


Deux artistes sont dit similaires s'ils ne sont pas inconnus et l'un est composé de l'autre.


OOO est un original si et seulement si OOO n'est pas le duplicata d'un possible original.

DROP TABLE IF EXISTS doublons;
CREATE TABLE doublons
AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        x(location) AS "x1",
        y(location) AS "y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        x(location) AS "x2",
        y(location) AS "y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (y2 - y1) / 2) ) ), 2 ) + cos( radians(y1)) * cos( radians(y2) ) * power( ( sin( radians( (x2 - x1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 <> source2
)

SELECT
    *
FROM
    artworks12
WHERE
    distance_metres < 50
    AND (
        (
            (
                title1 IS NOT NULL
                    AND title2 IS NOT NULL
            )
            AND (
                title1 NOT LIKE '%sans titre%'
                    AND title1 NOT LIKE '%non titré%'
                    AND title1 NOT LIKE '%sans-titre%'
            )
            AND (
                title2 NOT LIKE '%sans titre%'
                    AND title2 NOT LIKE '%non titré%'
                    AND title2 NOT LIKE '%sans-titre%'
            )
            AND (
                title1 LIKE concat('%' , title2 , '%')
                    OR title2 LIKE concat('%' , title1 , '%')
            )
        )
            OR
        (
                (
                    artist1 IS NOT NULL
                        AND artist2 IS NOT NULL
                )
                AND (
                    artist1 NOT IN ('')
                        AND artist2 NOT IN ('')
                )
                    AND (
                        artist1 LIKE concat('%' , artist2 , '%')
                            OR artist2 LIKE concat('%' , artist1 , '%')
                    )
            )
    )
ORDER BY
    id1;
DROP TABLE IF EXISTS doublons;
CREATE TABLE doublons
AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        x(location) AS "x1",
        y(location) AS "y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        x(location) AS "x2",
        y(location) AS "y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (y2 - y1) / 2) ) ), 2 ) + cos( radians(y1)) * cos( radians(y2) ) * power( ( sin( radians( (x2 - x1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 <> source2
)

SELECT
    *
FROM
    artworks12
WHERE
    distance_metres < 50
    AND (
        (
            (
                title1 IS NOT NULL
                    AND title2 IS NOT NULL
            )
            AND (
                title1 NOT LIKE '%sans titre%'
                    AND title1 NOT LIKE '%non titré%'
                    AND title1 NOT LIKE '%sans-titre%'
            )
            AND (
                title2 NOT LIKE '%sans titre%'
                    AND title2 NOT LIKE '%non titré%'
                    AND title2 NOT LIKE '%sans-titre%'
            )
            AND (
                title1 LIKE concat('%' , title2 , '%')
                    OR title2 LIKE concat('%' , title1 , '%')
            )
        )
            OR
        (
                (
                    artist1 IS NOT NULL
                        AND artist2 IS NOT NULL
                )
                AND (
                    artist1 NOT IN ('')
                        AND artist2 NOT IN ('')
                )
                    AND (
                        artist1 LIKE concat('%' , artist2 , '%')
                            OR artist2 LIKE concat('%' , artist1 , '%')
                    )
            )
    )
ORDER BY
    id1;

Precision vs rappel

Le paramètre de distance est modifiable. Plus ce chiffre sera petit plus sûr sera notre confiance que tous les doublons soient bel et bien des doublons. On gagne en précision. Mais nous risquons aussi de perdre de potentiels doublons parmi ceux ignorés. Nous perdons en rappel. Et plus ce chiffre sera grand, plus nous perdons en précision mais gagnons en rappel. Il faudra ainsi trouver ce juste milieu en jouant avec ce paramètre.

Ici nous l'avons mis à 50.
On se retrouve avec 254 paires dont 232 paires uniques et 357 oeuvres d'art uniques.

table doublons

Choix du doublon dans la paire

Chaque ligne est une paire où l'un est plus susceptible d'être une donnée "véritable" et l'autre une donnée doublon. Il faut trouver un moyen pour pouvoir départager entre ces données pour savoir lequel est le doublon pour par la suite supprimer l'entrée de notre base de données.


1er tiebreaker : si une oeuvre d'art possède plus de renseignements pertinents (titre ou/et nom d'artistes)

Lorsque nous sélectionnons une oeuvre d'art dans l'application mobile, les informations les plus mises en valeur sont le titre et le nom des artistes. Il serait alors une décision raisonnable de donner plus d'importance aux oeuvres d'art ayant toutes ces informations à ce sujet plutôt que celle n'ayant qu'une de ces deux informations.

Formulation

Pour une paire de doublons (OOO, DDD),

SiSiSi

AlorsAlorsAlors (par hypothèse que OOO et DDD possèdent un élement de similarité car des doublons),
OOO est plus renseigné que DDD.


Pour une paire de doublons (OOO, DDD),

SiSiSi OOO est plus renseigné que DDD

AlorsAlorsAlors dans la paire de doublons (O,D)(O,D)(O,D), OOO est le possible original et DDD est son duplicata.

DROP TABLE IF EXISTS duplicatas1;
CREATE TABLE duplicatas1
AS
WITH

doublons1 AS
(
    SELECT
        id1 AS id,
        id2 AS possible_original_id,
        title1 AS title,
        artist1 AS artist,
        source1 AS source
    FROM
        doublons
    WHERE
        (
            (
                (
                    title1 IS NULL
                        OR title1 LIKE '%sans titre%'
                        OR title1 LIKE '%non titré%'
                        OR title1 LIKE '%sans-titre%'
                )
                AND (
                        title2 IS NOT NULL
                        AND title2 NOT LIKE '%sans titre%'
                        AND title2 NOT LIKE '%non titré%'
                        AND title2 NOT LIKE '%sans-titre%'
                )
            )
            OR
        (
                    (
                        artist1 IS NULL
                        OR
                                artist1 IN ('')
                )
                    AND (
                            artist2 IS NOT NULL
                            AND
                                    artist2 NOT IN ('')
                    )
            )
        )
),

doublons2 AS
(
    SELECT
        id2 AS id,
        id1 AS possible_original_id,
        title2 AS title,
        artist2 AS artist,
        source2 AS source
    FROM
        doublons
    WHERE
        (
            (
                (
                    title2 IS NULL
                        OR title2 LIKE '%sans titre%'
                        OR title2 LIKE '%non titré%'
                        OR title2 LIKE '%sans-titre%'
                )
                AND (
                        title1 IS NOT NULL
                        AND title1 NOT LIKE '%sans titre%'
                        AND title1 NOT LIKE '%non titré%'
                        AND title1 NOT LIKE '%sans-titre%'
                )
            )
            OR
        (
                    (
                        artist2 IS NULL
                        OR
                                artist2 IN ('')
                )
                    AND (
                            artist1 IS NOT NULL
                            AND
                                    artist1 NOT IN ('')
                    )
            )
        )
),

doublons12 AS
(
    SELECT
        *
    FROM
        doublons1
UNION
    SELECT
        *
    FROM
        doublons2
)

SELECT
    *
FROM
    doublons12
GROUP BY
    id,
    possible_original_id
ORDER BY
    id;
DROP TABLE IF EXISTS duplicatas1;
CREATE TABLE duplicatas1
AS
WITH

doublons1 AS
(
    SELECT
        id1 AS id,
        id2 AS possible_original_id,
        title1 AS title,
        artist1 AS artist,
        source1 AS source
    FROM
        doublons
    WHERE
        (
            (
                (
                    title1 IS NULL
                        OR title1 LIKE '%sans titre%'
                        OR title1 LIKE '%non titré%'
                        OR title1 LIKE '%sans-titre%'
                )
                AND (
                        title2 IS NOT NULL
                        AND title2 NOT LIKE '%sans titre%'
                        AND title2 NOT LIKE '%non titré%'
                        AND title2 NOT LIKE '%sans-titre%'
                )
            )
            OR
        (
                    (
                        artist1 IS NULL
                        OR
                                artist1 IN ('')
                )
                    AND (
                            artist2 IS NOT NULL
                            AND
                                    artist2 NOT IN ('')
                    )
            )
        )
),

doublons2 AS
(
    SELECT
        id2 AS id,
        id1 AS possible_original_id,
        title2 AS title,
        artist2 AS artist,
        source2 AS source
    FROM
        doublons
    WHERE
        (
            (
                (
                    title2 IS NULL
                        OR title2 LIKE '%sans titre%'
                        OR title2 LIKE '%non titré%'
                        OR title2 LIKE '%sans-titre%'
                )
                AND (
                        title1 IS NOT NULL
                        AND title1 NOT LIKE '%sans titre%'
                        AND title1 NOT LIKE '%non titré%'
                        AND title1 NOT LIKE '%sans-titre%'
                )
            )
            OR
        (
                    (
                        artist2 IS NULL
                        OR
                                artist2 IN ('')
                )
                    AND (
                            artist1 IS NOT NULL
                            AND
                                    artist1 NOT IN ('')
                    )
            )
        )
),

doublons12 AS
(
    SELECT
        *
    FROM
        doublons1
UNION
    SELECT
        *
    FROM
        doublons2
)

SELECT
    *
FROM
    doublons12
GROUP BY
    id,
    possible_original_id
ORDER BY
    id;

table duplicatas1

Ainsi nous avons 85 doublons que l'on peut supprimer sans crainte de perdre la meilleure information.

Regardons maintenant les paires qu'ils nous restent à vérifier

DROP TABLE IF EXISTS doublons_reste;
CREATE TABLE doublons_reste AS

SELECT
    *
FROM
    doublons d
WHERE
    d.id1 NOT IN (
        SELECT
            ID
        FROM
            duplicatas1
    )
    AND d.id2 NOT IN (
        SELECT
            ID
        FROM
            duplicatas1
    )
DROP TABLE IF EXISTS doublons_reste;
CREATE TABLE doublons_reste AS

SELECT
    *
FROM
    doublons d
WHERE
    d.id1 NOT IN (
        SELECT
            ID
        FROM
            duplicatas1
    )
    AND d.id2 NOT IN (
        SELECT
            ID
        FROM
            duplicatas1
    )

table doublons_reste

Le résultat nous donne 125 paires dont 111 uniques.
Reste à savoir quel est le meilleur tiebreaker pour ces 111 paires.


2nd tiebreaker: si une oeuvre d'art provient d'une source jugée plus pertinente

Un tiebreaker proposé est celui de choisir la source qui nous intéresse le plus.

Formulation


Pour une paire de doublons (OOO, DDD),

SiSiSi

  • OOO n'est pas plus renseigné que DDD
    et
    DDD n'est pas plus renseigné que OOO et
  • OOO provient d'une source jugée privilégiée

AlorsAlorsAlors dans la paire de doublons (O,D)(O,D)(O,D), OOO est le possible original et DDD est son duplicata.


Une source est jugée privilégiée par un responsable du projet.


On remarque que chacune de ces paires (toutes les 110 paires sans exception) contient une oeuvre d'art qui provient de la source Art Public Montréal. Or Lena nous a fait part lors de la réunion du 29 septembre dernier qu'il serait utile de pouvoir scrapper les adresses webs menant vers les routes spécifiques des oeuvres d'arts d'Art Public Montréal afin que les utilisateurs de notre application puissent être redirigé vers cette information après avoir collectionné l'oeuvre, donnant plus de valeur aux oeuvres de cette source. Ainsi, pour choisir tous les doublons parmi ces 110 paires, une solution évidente est de choisir les entrées qui ne proviennent pas de la source Art Public Montréal.

DROP TABLE IF EXISTS duplicatas2;
CREATE TABLE duplicatas2 AS
WITH

doublons3 AS
(
    SELECT
        id1 AS id,
        id2 AS possible_original_id,
        title1 AS title,
        artist1 AS artist,
        source1 AS source
    FROM
        doublons_reste dr
    WHERE
        dr.source1 <> 'Art Public Montreal'
),

doublons4 AS
(
    SELECT
        id2 AS id,
        id1 AS possible_original_id,
        title2 AS title,
        artist2 AS artist,
        source2 AS source
    FROM
        doublons_reste dr
    WHERE
        dr.source2 <> 'Art Public Montreal'
),

doublons34 AS
(
    SELECT
        *
    FROM
        doublons3
UNION
    SELECT
        *
    FROM
        doublons4
)
SELECT
    *
FROM
    doublons34
GROUP BY
    id,
    possible_original_id
ORDER BY
    id;
DROP TABLE IF EXISTS duplicatas2;
CREATE TABLE duplicatas2 AS
WITH

doublons3 AS
(
    SELECT
        id1 AS id,
        id2 AS possible_original_id,
        title1 AS title,
        artist1 AS artist,
        source1 AS source
    FROM
        doublons_reste dr
    WHERE
        dr.source1 <> 'Art Public Montreal'
),

doublons4 AS
(
    SELECT
        id2 AS id,
        id1 AS possible_original_id,
        title2 AS title,
        artist2 AS artist,
        source2 AS source
    FROM
        doublons_reste dr
    WHERE
        dr.source2 <> 'Art Public Montreal'
),

doublons34 AS
(
    SELECT
        *
    FROM
        doublons3
UNION
    SELECT
        *
    FROM
        doublons4
)
SELECT
    *
FROM
    doublons34
GROUP BY
    id,
    possible_original_id
ORDER BY
    id;

table duplicatas2

Cette dernière requête nous permet de supprimer 108 doublons et il ne nous reste plus aucune paire avec un doublon à vérifier.


Total des doublons supprimés à date

Pour une distance prise à 50m maximum, nous avons supprimé 85 + 108 = 193 doublons.

Doublons dans les oeuvres "seulement" proches ?

Etudions maintenant le cas des oeuvres proches qui n'ont pas de signes évidents de similarité entre elles.


Pour deux entrées OOO et DDD de la jointure de la table artworks avec artists,

SiSiSi

  • DDD et OOO partagent une localisation proche,
  • DDD et OOO proviennent de différentes sources et
  • DDD et OOO n'ont pas d'élément de similarité

AlorsAlorsAlors (O,D)(O,D)(O,D) forme une paire de doublons vraisemblables où l'un des deux élements est un possible original vraisemblable et l'autre le duplicata vraisemblable de cet possible original.


Pour deux entrées OOO et DDD de la jointure de la table artworks avec artists,

DDD et OOO ont un élément de similarité si et seulement si leur titre est similaire ou leur artiste est similaire.


OOO est un original vraisemblable si et seulement si OOO n'est pas le duplicata d'un possible original vraisemblable.

DROP TABLE IF EXISTS doublons_vraisemblables;
CREATE TABLE doublons_vraisemblables AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        X(location) AS "X1",
        Y(location) AS "Y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        X(location) AS "X2",
        Y(location) AS "Y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (Y2 - Y1) / 2) ) ), 2 ) + cos( radians(Y1)) * cos( radians(Y2) ) * power( ( sin( radians( (X2 - X1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 <> source2
)


(
    SELECT
        *
    FROM
        artworks12
    WHERE
        distance_metres < 50
        AND (

(
                (
                    title1 IS NULL
                        OR title2 IS NULL
                        OR title1 LIKE '%sans titre%'
                        OR title1 LIKE '%non titré%'
                        OR title1 LIKE '%sans-titre%'
                        OR title2 LIKE '%sans titre%'
                        OR title2 LIKE '%non titré%'
                        OR title2 LIKE '%sans-titre%'
                )
                OR (
                    (
                        title1 IS NOT NULL
                            AND title2 IS NOT NULL
                            AND title1 NOT LIKE '%sans titre%'
                            AND title1 NOT LIKE '%non titré%'
                            AND title1 NOT LIKE '%sans-titre%'
                            AND title2 NOT LIKE '%sans titre%'
                            AND title2 NOT LIKE '%non titré%'
                            AND title2 NOT LIKE '%sans-titre%'
                    )
                    AND
(
                        title1 NOT LIKE CONCAT('%' , title2 , '%')
                            AND title2 NOT LIKE CONCAT('%' , title1 , '%')
                    )
                )
            )
                AND
                -- et
                -- si on ne peut pas comparer par artistes
(
                    (
                        artist1 IS NULL
                            OR artist2 IS NULL
                            OR artist1 IN ('')
                                OR artist2 IN ('')
                    )
                        OR (
                            (
                                artist1 IS NOT NULL
                                    AND artist2 IS NOT NULL
                                    AND artist1 NOT IN ('')
                                        AND artist2 NOT IN ('')
                            )
                                AND
(
                                    artist1 NOT LIKE CONCAT('%' , artist2 , '%')
                                        AND artist2 NOT LIKE CONCAT('%' , artist1 , '%')
                                )
                        )
                )
                -- et ne fait pas parti des doublons qu'on a supprimé precedemment
                    AND (
                        id1 NOT IN (
                            SELECT
                                id
                            FROM
                                duplicatas1
                        )
                    )
                    AND (
                        id2 NOT IN (
                            SELECT
                                id
                            FROM
                                duplicatas1
                        )
                    )
                    AND (
                        id1 NOT IN (
                            SELECT
                                id
                            FROM
                                duplicatas2
                        )
                    )
                    AND (
                        id2 NOT IN (
                            SELECT
                                id
                            FROM
                                duplicatas2
                        )
                    )
        )
    ORDER BY
        id1
);
DROP TABLE IF EXISTS doublons_vraisemblables;
CREATE TABLE doublons_vraisemblables AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        X(location) AS "X1",
        Y(location) AS "Y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        X(location) AS "X2",
        Y(location) AS "Y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (Y2 - Y1) / 2) ) ), 2 ) + cos( radians(Y1)) * cos( radians(Y2) ) * power( ( sin( radians( (X2 - X1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 <> source2
)


(
    SELECT
        *
    FROM
        artworks12
    WHERE
        distance_metres < 50
        AND (

(
                (
                    title1 IS NULL
                        OR title2 IS NULL
                        OR title1 LIKE '%sans titre%'
                        OR title1 LIKE '%non titré%'
                        OR title1 LIKE '%sans-titre%'
                        OR title2 LIKE '%sans titre%'
                        OR title2 LIKE '%non titré%'
                        OR title2 LIKE '%sans-titre%'
                )
                OR (
                    (
                        title1 IS NOT NULL
                            AND title2 IS NOT NULL
                            AND title1 NOT LIKE '%sans titre%'
                            AND title1 NOT LIKE '%non titré%'
                            AND title1 NOT LIKE '%sans-titre%'
                            AND title2 NOT LIKE '%sans titre%'
                            AND title2 NOT LIKE '%non titré%'
                            AND title2 NOT LIKE '%sans-titre%'
                    )
                    AND
(
                        title1 NOT LIKE CONCAT('%' , title2 , '%')
                            AND title2 NOT LIKE CONCAT('%' , title1 , '%')
                    )
                )
            )
                AND
                -- et  
                -- si on ne peut pas comparer par artistes
(
                    (
                        artist1 IS NULL
                            OR artist2 IS NULL
                            OR artist1 IN ('')
                                OR artist2 IN ('')
                    )
                        OR (
                            (
                                artist1 IS NOT NULL
                                    AND artist2 IS NOT NULL
                                    AND artist1 NOT IN ('')
                                        AND artist2 NOT IN ('')
                            )
                                AND
(
                                    artist1 NOT LIKE CONCAT('%' , artist2 , '%')
                                        AND artist2 NOT LIKE CONCAT('%' , artist1 , '%')
                                )
                        )
                )
                -- et ne fait pas parti des doublons qu'on a supprimé precedemment
                    AND (
                        id1 NOT IN (
                            SELECT
                                id
                            FROM
                                duplicatas1
                        )
                    )
                    AND (
                        id2 NOT IN (
                            SELECT
                                id
                            FROM
                                duplicatas1
                        )
                    )
                    AND (
                        id1 NOT IN (
                            SELECT
                                id
                            FROM
                                duplicatas2
                        )
                    )
                    AND (
                        id2 NOT IN (
                            SELECT
                                id
                            FROM
                                duplicatas2
                        )
                    )
        )
    ORDER BY
        id1
);

Nous avons 139 paires d'oeuvres proches dont 114 paires sont uniques en prenant comme limite de distance toujours 50m.

table doublons_vraisemblables

Il nous reste à trouver un moyen de pouvoir discerner les doublons parmi ces 114 paires d'oeuvres proches.


Etude des typos

  • DDD et OOO ne présentent pas de typos repérables à l'oeil nu et
  • DDD provient d'une source jugée non fiable

AlorsAlorsAlors dans la paire de doublons vraisemblables (O,D)(O,D)(O,D), OOO est le possible original vraisemblable et DDD est son duplicata vraisemblable.


Une source est jugée non fiable par un responsable du projet.

En jetant un coup d'oeil attentif au tableau, nous discernons quelques doublons qui proviennent de typos. Example : "Tower of Songs, Hommage à Leonard Cohen" vs "Tower of Songs: Hommage à Leonard Cohen" ou 1 709 151 103 vs 1709151103 ou Ashop vs A'Shop ou Rafael Sottolichio vs Rafael Sotolichio. Etant peu nombreuses, ces données sont facilement supprimables en les identifiant au préalable à l'oeil nu. Après avoir étudié le tableau ci-dessus et en départageant parmi les éléments de la paire en prenant l'oeuvre provenant de la source Art Public Montréal, nous identifions 11 doublons provenant de typos.

DROP TABLE IF EXISTS duplicatas3;
CREATE TABLE duplicatas3 AS
WITH

doublons5 AS
(
    SELECT
        id1 AS id,
        id2 AS possible_original_id,
        title1 AS title,
        artist1 AS artist,
        source1 AS source
    FROM
        doublons_vraisemblables
),

doublons6 AS
(
    SELECT
        id2 AS id,
        id1 AS possible_original_id,
        title2 AS title,
        artist2 AS artist,
        source2 AS source
    FROM
        doublons_vraisemblables
),

doublons56 AS
(
    SELECT
        *
    FROM
        doublons5
UNION
    SELECT
        *
    FROM
        doublons6
)

SELECT
    *
FROM
    doublons56
WHERE
    id IN (
        1437, 1429, 1185, 1201, 1199, 1154, 1161, 1324, 1313, 1155, 991
    )
GROUP BY
    id,
    possible_original_id
ORDER BY
    id;
DROP TABLE IF EXISTS duplicatas3;
CREATE TABLE duplicatas3 AS
WITH

doublons5 AS
(
    SELECT
        id1 AS id,
        id2 AS possible_original_id,
        title1 AS title,
        artist1 AS artist,
        source1 AS source
    FROM
        doublons_vraisemblables
),

doublons6 AS
(
    SELECT
        id2 AS id,
        id1 AS possible_original_id,
        title2 AS title,
        artist2 AS artist,
        source2 AS source
    FROM
        doublons_vraisemblables
),

doublons56 AS
(
    SELECT
        *
    FROM
        doublons5
UNION
    SELECT
        *
    FROM
        doublons6
)

SELECT
    *
FROM
    doublons56
WHERE
    id IN (
        1437, 1429, 1185, 1201, 1199, 1154, 1161, 1324, 1313, 1155, 991
    )
GROUP BY
    id,
    possible_original_id
ORDER BY
    id;

table duplicatas3

11 nouveaux doublons sont donc à ajouter à la liste de suppression.
Après avoir éliminé ces doublons, on se retrouve avec 100 paires uniques à vérifier


Etude des oeuvres proches inconnues

Ayant éliminé ces typos, nous pouvons maintenant sans risque d'oublier des doublons évidents choisir de vérifier seulement les paires d'oeuvres proches qu'on ne peut pas comparer par manque d'information. Ainsi on a :

DROP TABLE IF EXISTS doublons_vraisemblables_inconnus;
CREATE TABLE doublons_vraisemblables_inconnus AS
SELECT
    *
FROM
    doublons_vraisemblables
WHERE
    (
        id1 NOT IN (
            SELECT
                id
            FROM
                duplicatas3
        )
    )
    AND (
        id2 NOT IN (
            SELECT
                id
            FROM
                duplicatas3
        )
    )
    AND
-
    -- si on ne peut ni comparer par titre ni par artiste
(
        (
            (
                title1 IS NULL
                    OR (
                        title1 LIKE '%non titré%'
                            OR title1 LIKE '%sans titre%'
                            OR title1 LIKE '%sans-titre%'
                    )
            )
            AND
(
                (
                    artist1 IS NULL
                        OR artist1 IN ('')
                )
                    OR (
                        artist2 IS NULL
                            OR artist2 IN ('')
                    )
            )
        )
            OR

(
                (
                    title2 IS NULL
                        OR (
                            title2 LIKE '%non titré%'
                                OR title2 LIKE '%sans titre%'
                                OR title2 LIKE '%sans-titre%'
                        )
                )
                AND
(
                    (
                        artist1 IS NULL
                            OR artist1 IN ('')
                    )
                        OR (
                            artist2 IS NULL
                                OR artist2 IN ('')
                        )
                )
            )
                OR

(
                    (
                        artist1 IS NULL
                            OR artist1 IN ('')
                    )
                        AND
(
                            (
                                title1 IS NULL
                                    OR (
                                        title1 LIKE '%non titré%'
                                            OR title1 LIKE '%sans titre%'
                                            OR title1 LIKE '%sans-titre%'
                                    )
                            )
                            OR
(
                                title2 IS NULL
                                    OR (
                                        title2 LIKE '%non titré%'
                                            OR title2 LIKE '%sans titre%'
                                            OR title2 LIKE '%sans-titre%'
                                    )
                            )
                        )
                )
                OR

(
                    (
                        artist2 IS NULL
                            OR artist2 IN ('')
                    )
                        AND
(
                            (
                                title1 IS NULL
                                    OR (
                                        title1 LIKE '%non titré%'
                                            OR title1 LIKE '%sans titre%'
                                            OR title1 LIKE '%sans-titre%'
                                    )
                            )
                            OR
(
                                title2 IS NULL
                                    OR (
                                        title2 LIKE '%non titré%'
                                            OR title2 LIKE '%sans titre%'
                                            OR title2 LIKE '%sans-titre%'
                                    )
                            )
                        )
                )
    ); 
DROP TABLE IF EXISTS doublons_vraisemblables_inconnus;
CREATE TABLE doublons_vraisemblables_inconnus AS
SELECT
    *
FROM
    doublons_vraisemblables
WHERE
    (
        id1 NOT IN (
            SELECT
                id
            FROM
                duplicatas3
        )
    )
    AND (
        id2 NOT IN (
            SELECT
                id
            FROM
                duplicatas3
        )
    )
    AND
-
    -- si on ne peut ni comparer par titre ni par artiste
(
        (
            (
                title1 IS NULL
                    OR (
                        title1 LIKE '%non titré%'
                            OR title1 LIKE '%sans titre%'
                            OR title1 LIKE '%sans-titre%'
                    )
            )
            AND
(
                (
                    artist1 IS NULL
                        OR artist1 IN ('')
                )
                    OR (
                        artist2 IS NULL
                            OR artist2 IN ('')
                    )
            )
        )
            OR

(
                (
                    title2 IS NULL
                        OR (
                            title2 LIKE '%non titré%'
                                OR title2 LIKE '%sans titre%'
                                OR title2 LIKE '%sans-titre%'
                        )
                )
                AND
(
                    (
                        artist1 IS NULL
                            OR artist1 IN ('')
                    )
                        OR (
                            artist2 IS NULL
                                OR artist2 IN ('')
                        )
                )
            )
                OR

(
                    (
                        artist1 IS NULL
                            OR artist1 IN ('')
                    )
                        AND
(
                            (
                                title1 IS NULL
                                    OR (
                                        title1 LIKE '%non titré%'
                                            OR title1 LIKE '%sans titre%'
                                            OR title1 LIKE '%sans-titre%'
                                    )
                            )
                            OR
(
                                title2 IS NULL
                                    OR (
                                        title2 LIKE '%non titré%'
                                            OR title2 LIKE '%sans titre%'
                                            OR title2 LIKE '%sans-titre%'
                                    )
                            )
                        )
                )
                OR

(
                    (
                        artist2 IS NULL
                            OR artist2 IN ('')
                    )
                        AND
(
                            (
                                title1 IS NULL
                                    OR (
                                        title1 LIKE '%non titré%'
                                            OR title1 LIKE '%sans titre%'
                                            OR title1 LIKE '%sans-titre%'
                                    )
                            )
                            OR
(
                                title2 IS NULL
                                    OR (
                                        title2 LIKE '%non titré%'
                                            OR title2 LIKE '%sans titre%'
                                            OR title2 LIKE '%sans-titre%'
                                    )
                            )
                        )
                )
    ); 

table doublons_vraisemblables_inconnus

On se retrouve avec seulement 20 paires de données dont 19 paires sont uniques.


Choix parmi les oeuvres restantes

Parmi ces oeuvres, il est compliqué de savoir si l'oeuvre est réellement un doublon.
Mais en faisant une recherche google, on se rend compte que les oeuvres Gardien, C.R.U.X et Tropical proviennent bien des artistes Borrris|, Doras (Marc O'Brien) et Antoine Tavaglione. On peut ainsi supprimer les trois oeuvres ne provenant pas de Art Public Montreal. Enfin, on a plusieurs oeuvres sans titres et sans artiste de la source Murales subventionnées Ville de Montréal, je serais d'avis aussi à les supprimer parce que la source de données n'est pas la plus fiable et qu'il y a de grandes chances que ce soient des doublons. Enfin, il reste des oeuvres d'art d'autres sources comme MU ou Données ouverte, Ville de Montréal. Comme ces sources de données sont relativement fiables, je laisserais ces oeuvres d'art dans la base de données. Les 16 dernières entrées supprimées sont donc :


Pour une paire de doublons vraisemblables (O,D)(O,D)(O,D),

SiSiSi

  • DDD et OOO ne présentent pas de typos repérables à l'oeil nu et
  • DDD provient d'une source jugée non fiable

AlorsAlorsAlors dans la paire de doublons vraisemblables (O,D)(O,D)(O,D), OOO est le possible original vraisemblable et DDD est son duplicata vraisemblable.


Une source est jugée non fiable par un responsable du projet.

DROP TABLE IF EXISTS duplicatas4;
CREATE TABLE duplicatas4 AS
(
    SELECT
        id2 AS id,
        id1 AS possible_original_id,
        title2 AS title,
        artist2 AS artist,
        source2 AS source
    FROM
        doublons_vraisemblables_inconnus
    WHERE
        source2 = "Murales subventionnées Ville de Montréal"
    GROUP BY
        id,
        possible_original_id
    ORDER BY
        id
);
DROP TABLE IF EXISTS duplicatas4;
CREATE TABLE duplicatas4 AS
(
    SELECT
        id2 AS id,
        id1 AS possible_original_id,
        title2 AS title,
        artist2 AS artist,
        source2 AS source
    FROM
        doublons_vraisemblables_inconnus
    WHERE
        source2 = "Murales subventionnées Ville de Montréal"
    GROUP BY
        id,
        possible_original_id
    ORDER BY
        id
);

table duplicatas4

Ces 14 doublons sont les derniers trouvés grâce à notre méthode.

Verification oeuvres provenant de la même source ?

Une question se pose aussi : Faut-il aussi vérifier les oeuvres proches provenant de la même source? Jusque là on a fait confiance aux sources de données et on a supposé qu'elles mettaient à jour elles mêmes leurs données si jamais elles inséraient des doublons par erreur. La table résultante ne laisse pas entrevoir de doublons certains et il faudra se pencher plus en détail pour discerner si jamais il y a des doublons parmi ces entrées.

Oeuvres proches avec 2 similarités

DROP TABLE IF EXISTS doublons_same2;
CREATE TABLE doublons_same2
AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        x(location) AS "x1",
        y(location) AS "y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        x(location) AS "x2",
        y(location) AS "y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (y2 - y1) / 2) ) ), 2 ) + cos( radians(y1)) * cos( radians(y2) ) * power( ( sin( radians( (x2 - x1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 = source2
)

SELECT
    *
FROM
    artworks12
WHERE
    distance_metres < 50
    AND (
        (
            (
                title1 IS NOT NULL
                    AND title2 IS NOT NULL
            )
            AND (
                title1 NOT LIKE '%sans titre%'
                    AND title1 NOT LIKE '%non titré%'
                    AND title1 NOT LIKE '%sans-titre%'
            )
            AND (
                title2 NOT LIKE '%sans titre%'
                    AND title2 NOT LIKE '%non titré%'
                    AND title2 NOT LIKE '%sans-titre%'
            )
            AND (
                title1 LIKE concat('%' , title2 , '%')
                    OR title2 LIKE concat('%' , title1 , '%')
            )
        )
            AND
        (
                (
                    artist1 IS NOT NULL
                        AND artist2 IS NOT NULL
                )
                AND (
                    artist1 NOT IN (
                        '', 'artiste inconnu'
                    )
                        AND artist2 NOT IN (
                            '', 'artiste inconnu'
                        )
                )
                    AND (
                        artist1 LIKE concat('%' , artist2 , '%')
                            OR artist2 LIKE concat('%' , artist1 , '%')
                    )
            )
    )
ORDER BY
    id1;
DROP TABLE IF EXISTS doublons_same2;
CREATE TABLE doublons_same2
AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        x(location) AS "x1",
        y(location) AS "y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        x(location) AS "x2",
        y(location) AS "y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (y2 - y1) / 2) ) ), 2 ) + cos( radians(y1)) * cos( radians(y2) ) * power( ( sin( radians( (x2 - x1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 = source2
)

SELECT
    *
FROM
    artworks12
WHERE
    distance_metres < 50
    AND (
        (
            (
                title1 IS NOT NULL
                    AND title2 IS NOT NULL
            )
            AND (
                title1 NOT LIKE '%sans titre%'
                    AND title1 NOT LIKE '%non titré%'
                    AND title1 NOT LIKE '%sans-titre%'
            )
            AND (
                title2 NOT LIKE '%sans titre%'
                    AND title2 NOT LIKE '%non titré%'
                    AND title2 NOT LIKE '%sans-titre%'
            )
            AND (
                title1 LIKE concat('%' , title2 , '%')
                    OR title2 LIKE concat('%' , title1 , '%')
            )
        )
            AND
        (
                (
                    artist1 IS NOT NULL
                        AND artist2 IS NOT NULL
                )
                AND (
                    artist1 NOT IN (
                        '', 'artiste inconnu'
                    )
                        AND artist2 NOT IN (
                            '', 'artiste inconnu'
                        )
                )
                    AND (
                        artist1 LIKE concat('%' , artist2 , '%')
                            OR artist2 LIKE concat('%' , artist1 , '%')
                    )
            )
    )
ORDER BY
    id1;

table oeuvres_proches2

Oeuvres proches avec 1 similarité

DROP TABLE IF EXISTS doublons_same1;
CREATE TABLE doublons_same1
AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        x(location) AS "x1",
        y(location) AS "y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        x(location) AS "x2",
        y(location) AS "y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (y2 - y1) / 2) ) ), 2 ) + cos( radians(y1)) * cos( radians(y2) ) * power( ( sin( radians( (x2 - x1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 = source2
)

SELECT
    *
FROM
    artworks12
WHERE
    distance_metres < 50
    AND (
        (
            (
                title1 IS NOT NULL
                    AND title2 IS NOT NULL
            )
            AND (
                title1 NOT LIKE '%sans titre%'
                    AND title1 NOT LIKE '%non titré%'
                    AND title1 NOT LIKE '%sans-titre%'
            )
            AND (
                title2 NOT LIKE '%sans titre%'
                    AND title2 NOT LIKE '%non titré%'
                    AND title2 NOT LIKE '%sans-titre%'
            )
            AND (
                title1 LIKE concat('%' , title2 , '%')
                    OR title2 LIKE concat('%' , title1 , '%')
            )
        )
            OR
        (
                (
                    artist1 IS NOT NULL
                        AND artist2 IS NOT NULL
                )
                AND (
                    artist1 NOT IN (
                        '', 'artiste inconnu'
                    )
                        AND artist2 NOT IN (
                            '', 'artiste inconnu'
                        )
                )
                    AND (
                        artist1 LIKE concat('%' , artist2 , '%')
                            OR artist2 LIKE concat('%' , artist1 , '%')
                    )
            )
    )
EXCEPT
SELECT
    *
FROM
    doublons_same2
ORDER BY
    id1;
DROP TABLE IF EXISTS doublons_same1;
CREATE TABLE doublons_same1
AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        x(location) AS "x1",
        y(location) AS "y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        x(location) AS "x2",
        y(location) AS "y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (y2 - y1) / 2) ) ), 2 ) + cos( radians(y1)) * cos( radians(y2) ) * power( ( sin( radians( (x2 - x1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 = source2
)

SELECT
    *
FROM
    artworks12
WHERE
    distance_metres < 50
    AND (
        (
            (
                title1 IS NOT NULL
                    AND title2 IS NOT NULL
            )
            AND (
                title1 NOT LIKE '%sans titre%'
                    AND title1 NOT LIKE '%non titré%'
                    AND title1 NOT LIKE '%sans-titre%'
            )
            AND (
                title2 NOT LIKE '%sans titre%'
                    AND title2 NOT LIKE '%non titré%'
                    AND title2 NOT LIKE '%sans-titre%'
            )
            AND (
                title1 LIKE concat('%' , title2 , '%')
                    OR title2 LIKE concat('%' , title1 , '%')
            )
        )
            OR
        (
                (
                    artist1 IS NOT NULL
                        AND artist2 IS NOT NULL
                )
                AND (
                    artist1 NOT IN (
                        '', 'artiste inconnu'
                    )
                        AND artist2 NOT IN (
                            '', 'artiste inconnu'
                        )
                )
                    AND (
                        artist1 LIKE concat('%' , artist2 , '%')
                            OR artist2 LIKE concat('%' , artist1 , '%')
                    )
            )
    )
EXCEPT
SELECT
    *
FROM
    doublons_same2
ORDER BY
    id1;

table oeuvres_proches1

Oeuvres proches

DROP TABLE IF EXISTS oeuvres_proches;
CREATE TABLE oeuvres_proches AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        X(location) AS "X1",
        Y(location) AS "Y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        X(location) AS "X2",
        Y(location) AS "Y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (Y2 - Y1) / 2) ) ), 2 ) + cos( radians(Y1)) * cos( radians(Y2) ) * power( ( sin( radians( (X2 - X1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 = source2
)


(
    SELECT
        *
    FROM
        artworks12
    WHERE
        distance_metres < 50
        AND (

(
                (
                    title1 IS NULL
                        OR title2 IS NULL
                        OR title1 LIKE '%sans titre%'
                        OR title1 LIKE '%non titré%'
                        OR title1 LIKE '%sans-titre%'
                        OR title2 LIKE '%sans titre%'
                        OR title2 LIKE '%non titré%'
                        OR title2 LIKE '%sans-titre%'
                )
                OR (
                    (
                        title1 IS NOT NULL
                            AND title2 IS NOT NULL
                            AND title1 NOT LIKE '%sans titre%'
                            AND title1 NOT LIKE '%non titré%'
                            AND title1 NOT LIKE '%sans-titre%'
                            AND title2 NOT LIKE '%sans titre%'
                            AND title2 NOT LIKE '%non titré%'
                            AND title2 NOT LIKE '%sans-titre%'
                    )
                    AND
(
                        title1 NOT LIKE CONCAT('%' , title2 , '%')
                            AND title2 NOT LIKE CONCAT('%' , title1 , '%')
                    )
                )
            )
                AND
                -- et
                -- si on ne peut pas comparer par artistes
(
                    (
                        artist1 IS NULL
                            OR artist2 IS NULL
                            OR artist1 IN (
                                '', 'artiste inconnu'
                            )
                                OR artist2 IN (
                                    '', 'artiste inconnu'
                                )
                    )
                        OR (
                            (
                                artist1 IS NOT NULL
                                    AND artist2 IS NOT NULL
                                    AND artist1 NOT IN (
                                        '', 'artiste inconnu'
                                    )
                                        AND artist2 NOT IN (
                                            '', 'artiste inconnu'
                                        )
                            )
                                AND
(
                                    artist1 NOT LIKE CONCAT('%' , artist2 , '%')
                                        AND artist2 NOT LIKE CONCAT('%' , artist1 , '%')
                                )
                        )
                )
        )
    ORDER BY
        id1
);
DROP TABLE IF EXISTS oeuvres_proches;
CREATE TABLE oeuvres_proches AS
WITH

artworks1 AS (
    SELECT
        aw.id AS id1,
        aw.title AS title1,
        a.name AS artist1,
        aw.source AS source1,
        X(location) AS "X1",
        Y(location) AS "Y1"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks2 AS (
    SELECT
        aw.id AS id2,
        aw.title AS title2,
        a.name AS artist2,
        aw.source AS source2,
        X(location) AS "X2",
        Y(location) AS "Y2"
    FROM
        artworks aw
    LEFT JOIN artist_artwork aa ON
        aw.id = aa.artwork_id
    LEFT JOIN artists a ON
        a.id = aa.artist_id
),

artworks12 AS (
    SELECT
        id1,
        title1,
        artist1,
        source1,
        id2,
        title2,
        artist2,
        source2,
        (
            2 * 6371000 * asin( sqrt( power( ( sin( radians( (Y2 - Y1) / 2) ) ), 2 ) + cos( radians(Y1)) * cos( radians(Y2) ) * power( ( sin( radians( (X2 - X1) / 2 ) ) ) , 2 ) ) )
        ) AS distance_metres
    FROM
        artworks1
    JOIN artworks2 ON
        id1 < id2
        AND source1 = source2
)


(
    SELECT
        *
    FROM
        artworks12
    WHERE
        distance_metres < 50
        AND (

(
                (
                    title1 IS NULL
                        OR title2 IS NULL
                        OR title1 LIKE '%sans titre%'
                        OR title1 LIKE '%non titré%'
                        OR title1 LIKE '%sans-titre%'
                        OR title2 LIKE '%sans titre%'
                        OR title2 LIKE '%non titré%'
                        OR title2 LIKE '%sans-titre%'
                )
                OR (
                    (
                        title1 IS NOT NULL
                            AND title2 IS NOT NULL
                            AND title1 NOT LIKE '%sans titre%'
                            AND title1 NOT LIKE '%non titré%'
                            AND title1 NOT LIKE '%sans-titre%'
                            AND title2 NOT LIKE '%sans titre%'
                            AND title2 NOT LIKE '%non titré%'
                            AND title2 NOT LIKE '%sans-titre%'
                    )
                    AND
(
                        title1 NOT LIKE CONCAT('%' , title2 , '%')
                            AND title2 NOT LIKE CONCAT('%' , title1 , '%')
                    )
                )
            )
                AND
                -- et  
                -- si on ne peut pas comparer par artistes
(
                    (
                        artist1 IS NULL
                            OR artist2 IS NULL
                            OR artist1 IN (
                                '', 'artiste inconnu'
                            )
                                OR artist2 IN (
                                    '', 'artiste inconnu'
                                )
                    )
                        OR (
                            (
                                artist1 IS NOT NULL
                                    AND artist2 IS NOT NULL
                                    AND artist1 NOT IN (
                                        '', 'artiste inconnu'
                                    )
                                        AND artist2 NOT IN (
                                            '', 'artiste inconnu'
                                        )
                            )
                                AND
(
                                    artist1 NOT LIKE CONCAT('%' , artist2 , '%')
                                        AND artist2 NOT LIKE CONCAT('%' , artist1 , '%')
                                )
                        )
                )
        )
    ORDER BY
        id1
);

table oeuvres_proches0

Oeuvres proches inconnues

DROP TABLE IF EXISTS oeuvres_proches_inconnues;
CREATE TABLE oeuvres_proches_inconnues AS
SELECT
    *
FROM
    oeuvres_proches
WHERE
    -- si on ne peut ni comparer par titre ni par artiste
(
        (
            (
                title1 IS NULL
                    OR (
                        title1 LIKE '%non titré%'
                            OR title1 LIKE '%sans titre%'
                            OR title1 LIKE '%sans-titre%'
                    )
            )
            AND
(
                (
                    artist1 IS NULL
                        OR artist1 IN (
                            '', 'artiste inconnu'
                        )
                )
                    OR (
                        artist2 IS NULL
                            OR artist2 IN (
                                '', 'artiste inconnu'
                            )
                    )
            )
        )
            OR

(
                (
                    title2 IS NULL
                        OR (
                            title2 LIKE '%non titré%'
                                OR title2 LIKE '%sans titre%'
                                OR title2 LIKE '%sans-titre%'
                        )
                )
                AND
(
                    (
                        artist1 IS NULL
                            OR artist1 IN (
                                '', 'artiste inconnu'
                            )
                    )
                        OR (
                            artist2 IS NULL
                                OR artist2 IN (
                                    '', 'artiste inconnu'
                                )
                        )
                )
            )
                OR

(
                    (
                        artist1 IS NULL
                            OR artist1 IN (
                                '', 'artiste inconnu'
                            )
                    )
                        AND
(
                            (
                                title1 IS NULL
                                    OR (
                                        title1 LIKE '%non titré%'
                                            OR title1 LIKE '%sans titre%'
                                            OR title1 LIKE '%sans-titre%'
                                    )
                            )
                            OR
(
                                title2 IS NULL
                                    OR (
                                        title2 LIKE '%non titré%'
                                            OR title2 LIKE '%sans titre%'
                                            OR title2 LIKE '%sans-titre%'
                                    )
                            )
                        )
                )
                OR

(
                    (
                        artist2 IS NULL
                            OR artist2 IN (
                                '', 'artiste inconnu'
                            )
                    )
                        AND
(
                            (
                                title1 IS NULL
                                    OR (
                                        title1 LIKE '%non titré%'
                                            OR title1 LIKE '%sans titre%'
                                            OR title1 LIKE '%sans-titre%'
                                    )
                            )
                            OR
(
                                title2 IS NULL
                                    OR (
                                        title2 LIKE '%non titré%'
                                            OR title2 LIKE '%sans titre%'
                                            OR title2 LIKE '%sans-titre%'
                                    )
                            )
                        )
                )
    )
ORDER BY
    id1; 
DROP TABLE IF EXISTS oeuvres_proches_inconnues;
CREATE TABLE oeuvres_proches_inconnues AS
SELECT
    *
FROM
    oeuvres_proches
WHERE
    -- si on ne peut ni comparer par titre ni par artiste
(
        (
            (
                title1 IS NULL
                    OR (
                        title1 LIKE '%non titré%'
                            OR title1 LIKE '%sans titre%'
                            OR title1 LIKE '%sans-titre%'
                    )
            )
            AND
(
                (
                    artist1 IS NULL
                        OR artist1 IN (
                            '', 'artiste inconnu'
                        )
                )
                    OR (
                        artist2 IS NULL
                            OR artist2 IN (
                                '', 'artiste inconnu'
                            )
                    )
            )
        )
            OR

(
                (
                    title2 IS NULL
                        OR (
                            title2 LIKE '%non titré%'
                                OR title2 LIKE '%sans titre%'
                                OR title2 LIKE '%sans-titre%'
                        )
                )
                AND
(
                    (
                        artist1 IS NULL
                            OR artist1 IN (
                                '', 'artiste inconnu'
                            )
                    )
                        OR (
                            artist2 IS NULL
                                OR artist2 IN (
                                    '', 'artiste inconnu'
                                )
                        )
                )
            )
                OR

(
                    (
                        artist1 IS NULL
                            OR artist1 IN (
                                '', 'artiste inconnu'
                            )
                    )
                        AND
(
                            (
                                title1 IS NULL
                                    OR (
                                        title1 LIKE '%non titré%'
                                            OR title1 LIKE '%sans titre%'
                                            OR title1 LIKE '%sans-titre%'
                                    )
                            )
                            OR
(
                                title2 IS NULL
                                    OR (
                                        title2 LIKE '%non titré%'
                                            OR title2 LIKE '%sans titre%'
                                            OR title2 LIKE '%sans-titre%'
                                    )
                            )
                        )
                )
                OR

(
                    (
                        artist2 IS NULL
                            OR artist2 IN (
                                '', 'artiste inconnu'
                            )
                    )
                        AND
(
                            (
                                title1 IS NULL
                                    OR (
                                        title1 LIKE '%non titré%'
                                            OR title1 LIKE '%sans titre%'
                                            OR title1 LIKE '%sans-titre%'
                                    )
                            )
                            OR
(
                                title2 IS NULL
                                    OR (
                                        title2 LIKE '%non titré%'
                                            OR title2 LIKE '%sans titre%'
                                            OR title2 LIKE '%sans-titre%'
                                    )
                            )
                        )
                )
    )
ORDER BY
    id1; 

table oeuvres_proches_inconnues

Conclusion

SiSiSi

  • DDD est un duplicata d'un possible original
    ou
    DDD est un duplicata d'un possible original vraisemblable,

AlorsAlorsAlors DDD est un duplicata et DDD est supprimé de la base de données.


Au total, ce raisonnement méthodique nous a permis de pouvoir identifier 193 + 11 + 14 = 218 doublons!

DROP TABLE IF EXISTS duplicatas;
CREATE TABLE duplicatas AS
WITH

union1 AS
(
    SELECT
        *
    FROM
        duplicatas1
UNION
    SELECT
        *
    FROM
        duplicatas2
),

union2 AS
(
    SELECT
        *
    FROM
        union1
UNION
    SELECT
        *
    FROM
        duplicatas3
),

union3 AS
(
    SELECT
        *
    FROM
        union2
UNION
    SELECT
        *
    FROM
        duplicatas4
)

SELECT
    *
FROM
    union3
GROUP BY
    id,
    possible_original_id
ORDER BY
    id;
DROP TABLE IF EXISTS duplicatas;
CREATE TABLE duplicatas AS
WITH

union1 AS
(
    SELECT
        *
    FROM
        duplicatas1
UNION
    SELECT
        *
    FROM
        duplicatas2
),

union2 AS
(
    SELECT
        *
    FROM
        union1
UNION
    SELECT
        *
    FROM
        duplicatas3
),

union3 AS
(
    SELECT
        *
    FROM
        union2
UNION
    SELECT
        *
    FROM
        duplicatas4
)

SELECT
    *
FROM
    union3
GROUP BY
    id,
    possible_original_id
ORDER BY
    id;

table duplicatas

Unification des doublons ?

Mais avant de les supprimer serais ce possible de unifier les résultats pour ne pas perdre de données? Quels sont les données qu'on veut garder? Tous? A discuter

Liste des doublons supprimés

Enfin les doublons supprimés sont :

DROP TABLE IF EXISTS duplicatas_uniques;
CREATE TABLE duplicatas_uniques AS

SELECT
    id, title, artist, source
FROM
    duplicatas
GROUP BY
    id
ORDER BY
    id;
DROP TABLE IF EXISTS duplicatas_uniques;
CREATE TABLE duplicatas_uniques AS

SELECT
    id, title, artist, source
FROM
    duplicatas
GROUP BY
    id
ORDER BY
    id;

table duplicatas_uniques

Cette méthode a été appliquée en prenant comme limite maximale 50 mètres. Si à l'avenir, on se rend compte qu'on a éliminé trop de données qui n'étaient finalement pas des doublons, nous pourrions refaire ce raisonnement en abaissant la limite. Et dans le cas contraire, si on se rend compte qu'il y a encore un certain bon nombre de doublons, nous pourrions refaire cela mais en haussant la limite.


Algorithme au complet

Pour deux entrées OOO et DDD de la jointure de la table artworks avec artists,

SiSiSi

  • OOO et DDD partagent une localisation proche,
  • (OOO et DDD proviennent de différentes sources) et
  • (le titre de OOO et le titre de DDD sont similaires) et (le titre de OOO et le titre de DDD ne sont pas inconnues)
    ou
    (l'artiste de OOO et l'artiste de DDD sont similaires) et (l'artiste de OOO et l'artiste de DDD ne sont pas inconnues)

AlorsAlorsAlors (O,D)(O,D)(O,D) forme une paire de doublons où l'un des deux élements est un possible original et l'autre le duplicata de cet possible original.


Un titre est dit inconnu si le titre est nul ou s'il est composé d'éléments indiquant qu'il n'a pas de titre comme "Sans titre", "Non titrée" ou "Sans-titre".


Un artiste est dit inconnu si l'artiste est nul ou s'il est le string vide.


Deux titres sont dit similaires s'ils ne sont pas inconnus et l'un est composé de l'autre.


Deux artistes sont dit similaires s'ils ne sont pas inconnus et l'un est composé de l'autre.


OOO est un original si et seulement si OOO n'est pas le duplicata d'un possible original.


Pour une paire de doublons (OOO, DDD),

SiSiSi

AlorsAlorsAlors (par hypothèse que OOO et DDD possèdent un élement de similarité car des doublons),
OOO est plus renseigné que DDD.


Pour une paire de doublons (OOO, DDD),

SiSiSi OOO est plus renseigné que DDD

AlorsAlorsAlors dans la paire de doublons (O,D)(O,D)(O,D), OOO est le possible original et DDD est son duplicata.


Pour une paire de doublons (OOO, DDD),

SiSiSi

  • OOO n'est pas plus renseigné que DDD
    et
    DDD n'est pas plus renseigné que OOO et
  • OOO provient d'une source jugée privilégiée

AlorsAlorsAlors dans la paire de doublons (O,D)(O,D)(O,D), OOO est le possible original et DDD est son duplicata.


Une source est jugée privilégiée par un responsable du projet.


Pour deux entrées OOO et DDD de la jointure de la table artworks avec artists,

SiSiSi

  • DDD et OOO partagent une localisation proche,
  • DDD et OOO proviennent de différentes sources et
  • DDD et OOO n'ont pas d'élément de similarité

AlorsAlorsAlors (O,D)(O,D)(O,D) forme une paire de doublons vraisemblables où l'un des deux élements est un possible original vraisemblable et l'autre le duplicata vraisemblable de cet possible original.


Pour deux entrées OOO et DDD de la jointure de la table artworks avec artists,

DDD et OOO ont un élément de similarité si et seulement si leur titre est similaire ou leur artiste est similaire.


OOO est un original vraisemblable si et seulement si OOO n'est pas le duplicata d'un possible original vraisemblable.


Pour une paire de doublons vraisemblables (O,D)(O,D)(O,D),

SiSiSi

  • DDD et OOO présentent des typos repérables à l'oeil nu et
  • OOO provient d'une source jugée privilégiée

AlorsAlorsAlors dans la paire de doublons vraisemblables (O,D)(O,D)(O,D), OOO est le possible original vraisemblable et DDD est son duplicata vraisemblable.


Pour une paire de doublons vraisemblables (O,D)(O,D)(O,D),

SiSiSi

  • DDD et OOO ne présentent pas de typos repérables à l'oeil nu et
  • DDD provient d'une source jugée non fiable

AlorsAlorsAlors dans la paire de doublons vraisemblables (O,D)(O,D)(O,D), OOO est le possible original vraisemblable et DDD est son duplicata vraisemblable.


Une source est jugée non fiable par un responsable du projet.


SiSiSi

  • DDD est un duplicata d'un possible original
    ou
    DDD est un duplicata d'un possible original vraisemblable,

AlorsAlorsAlors DDD est un duplicata et DDD est supprimé de la base de données.