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.
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)
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.
Pour deux entrées O et D de la jointure de la table artworks avec artists,
Si
Alors (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.
O est un original si et seulement si O 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;
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.
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.
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.
Pour une paire de doublons (O, D),
Si
Alors (par hypothèse que O et D possèdent un élement de similarité car des doublons),
O est plus renseigné que D.
Pour une paire de doublons (O, D),
Si O est plus renseigné que D
Alors dans la paire de doublons (O,D), O est le possible original et D 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;
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
)
Le résultat nous donne 125 paires dont 111 uniques.
Reste à savoir quel est le meilleur tiebreaker pour ces 111 paires.
Un tiebreaker proposé est celui de choisir la source qui nous intéresse le plus.
Pour une paire de doublons (O, D),
Si
Alors dans la paire de doublons (O,D), O est le possible original et D 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;
Cette dernière requête nous permet de supprimer 108 doublons et il ne nous reste plus aucune paire avec un doublon à vérifier.
Pour une distance prise à 50m maximum, nous avons supprimé 85 + 108 = 193 doublons.
Etudions maintenant le cas des oeuvres proches qui n'ont pas de signes évidents de similarité entre elles.
Pour deux entrées O et D de la jointure de la table artworks avec artists,
Si
Alors (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 O et D de la jointure de la table artworks avec artists,
D et O ont un élément de similarité si et seulement si leur titre est similaire ou leur artiste est similaire.
O est un original vraisemblable si et seulement si O 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.
Il nous reste à trouver un moyen de pouvoir discerner les doublons parmi ces 114 paires d'oeuvres proches.
Alors dans la paire de doublons vraisemblables (O,D), O est le possible original vraisemblable et D 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;
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
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.
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),
Si
Alors dans la paire de doublons vraisemblables (O,D), O est le possible original vraisemblable et D 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
);
Ces 14 doublons sont les derniers trouvés grâce à notre méthode.
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.
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;
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;
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
);
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
Si
Alors D est un duplicata et D 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;
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
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;
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.
Pour deux entrées O et D de la jointure de la table artworks avec artists,
Si
Alors (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.
O est un original si et seulement si O n'est pas le duplicata d'un possible original.
Pour une paire de doublons (O, D),
Si
Alors (par hypothèse que O et D possèdent un élement de similarité car des doublons),
O est plus renseigné que D.
Pour une paire de doublons (O, D),
Si O est plus renseigné que D
Alors dans la paire de doublons (O,D), O est le possible original et D est son duplicata.
Pour une paire de doublons (O, D),
Si
Alors dans la paire de doublons (O,D), O est le possible original et D est son duplicata.
Une source est jugée privilégiée par un responsable du projet.
Pour deux entrées O et D de la jointure de la table artworks avec artists,
Si
Alors (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 O et D de la jointure de la table artworks avec artists,
D et O ont un élément de similarité si et seulement si leur titre est similaire ou leur artiste est similaire.
O est un original vraisemblable si et seulement si O n'est pas le duplicata d'un possible original vraisemblable.
Pour une paire de doublons vraisemblables (O,D),
Si
Alors dans la paire de doublons vraisemblables (O,D), O est le possible original vraisemblable et D est son duplicata vraisemblable.
Pour une paire de doublons vraisemblables (O,D),
Si
Alors dans la paire de doublons vraisemblables (O,D), O est le possible original vraisemblable et D est son duplicata vraisemblable.
Une source est jugée non fiable par un responsable du projet.
Si
Alors D est un duplicata et D est supprimé de la base de données.