DBpedia importation des données - Raphix93/2HS2179_militants-pour-la-paix GitHub Wiki

Extraction de la liste "person"

[!NOTE] Effectif de la liste (08.12.2024) : 369 personnes

Lien vers les fichiers d'import csv

Lien vers la base de données

Cette requête SPARQL extrait les personnes (?o1) associées à la ressource List_of_peace_activists sur DBpedia en conservant leurs sources sous la forme d'un uri (?subject_uri). Elle récupère leur année de naissance (?birthYear), calculée à partir de leur date de naissance, ainsi que leur label en anglais (?label). Les résultats incluent uniquement les individus nés après 1800 et sont triés par année de naissance croissante.

PREFIX dbr: <http://dbpedia.org/resource/>
PREFIX dbp: <http://dbpedia.org/property/>
PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT DISTINCT (?o1 AS ?subject_uri) ?birthYear ?label
WHERE {
  dbr:List_of_peace_activists ?p ?o1.
  ?o1 a dbo:Person;
      dbo:birthDate ?birthDate;
      rdfs:label ?langLabel.
  BIND(xsd:integer(SUBSTR(STR(?birthDate), 1, 4)) AS ?birthYear)
  BIND(STR(?langLabel) AS ?label)
  FILTER (?birthYear >= 1800 && LANG(?langLabel) = 'en')
}
ORDER BY ?birthYear

Effacer les tables

Description

[!NOTE] Le script fourni dans les consignes ne fonctionnait pas pour moi, j'ai procédé table par table pour remettre mes tables à zero.

Ce script réinitialise la base de données en supprimant toutes les données des tables, en réinitialisant les compteurs d'auto-incrémentation, et en optimisant la base. Il désactive temporairement les clés étrangères pour éviter les conflits lors des suppressions, puis les réactive après les modifications. Enfin, la commande VACUUM exécutée ensuite libère l'espace inutilisé.


PRAGMA foreign_keys = OFF;

-- Suppression des données dans chaque table
DELETE FROM occupation;
DELETE FROM geographical_place;
DELETE FROM pursuit;
DELETE FROM birth;
DELETE FROM organization;
DELETE FROM membership;
DELETE FROM publication;
DELETE FROM document;
DELETE FROM obtainment;
DELETE FROM award;
DELETE FROM person;

-- Réinitialisation des auto-incrémentations
DELETE FROM sqlite_sequence WHERE name = 'occupation';
DELETE FROM sqlite_sequence WHERE name = 'geographical_place';
DELETE FROM sqlite_sequence WHERE name = 'pursuit';
DELETE FROM sqlite_sequence WHERE name = 'birth';
DELETE FROM sqlite_sequence WHERE name = 'organization';
DELETE FROM sqlite_sequence WHERE name = 'membership';
DELETE FROM sqlite_sequence WHERE name = 'publication';
DELETE FROM sqlite_sequence WHERE name = 'document';
DELETE FROM sqlite_sequence WHERE name = 'obtainment';
DELETE FROM sqlite_sequence WHERE name = 'award';
DELETE FROM sqlite_sequence WHERE name = 'person';

PRAGMA foreign_keys = ON;

VACUUM;

Importer les personnes

Lien vers les fichiers d'import csv

Lien vers la base de données

Chercher les doublons

Ce script vérifie l'absence de doublons dans person_uri et compte le nombre total d'enregistrements dans la table dbp_liste_personnes.

-- si le résultat est vide (pas de lignes) il n'y a pas de doublons
SELECT person_uri
FROM dbp_liste_personnes lp 
GROUP BY person_uri
HAVING COUNT(*) > 1 ;

-- compter les personnes à importer
SELECT COUNT(*)
FROM dbp_liste_personnes lp;

Insérer les personnes

Ce script insère dans la table _person _les données issues de dbp_liste_personnes, avec une métadonnée indiquant leur provenance et la date d'importation.

INSERT INTO person (birth_year, dbpedia_uri, import_metadata)
SELECT birthYear, subject_uri, "Importé le 01 décembre 2024 depuis le résultat d'une requête SPARQL sur DBPedia (voir: https://github.com/Raphix93/2HS2179_militants-pour-la-paix/wiki/DBpedia-importation-des-données) "
FROM dbp_liste_personnes lp ;

Ajouter les noms

Ce script met à jour la colonne name de la table person en extrayant et en nettoyant le nom depuis label.

UPDATE person 
SET label = TRIM(REPLACE(SUBSTR(dbpedia_uri,LENGTH('http://dbpedia.org/resource/')+ 1), '_', ' '));

Création des lignes dans la table référence contenant l'URI

INSERT INTO reference (fk_person , exact_reference , fk_document , fk_reference_type)
SELECT pk_person, dbpedia_uri, 1, 1
FROM person p ;

Importer les occupations

Lien vers les fichiers d'import csv

Lien vers la base de données

Liste des occupations

PREFIX dbr: <http://dbpedia.org/resource/>
PREFIX dbp: <http://dbpedia.org/property/>
PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#>
SELECT DISTINCT (?o1 AS ?subject_uri) (dbo:occupation as ?property_uri) (?target AS ?object_uri)  (?name as ?label)
#(COUNT(*) AS ?effectif) 
WHERE {
SELECT DISTINCT ?o1 ?target (str(?label) as ?name)
WHERE { 
    dbr:List_of_peace_activists ?p ?o1.
    ?o1 a dbo:Person;
    dbp:birthDate | dbo:birthDate ?birthDate;
    dbp:occupation | dbo:occupation ?target.
?target rdfs:label ?label.
BIND(xsd:integer(SUBSTR(STR(?birthDate), 1, 4)) AS ?birthYear)
    FILTER ( (?birthYear >= 1800   )  && LANG(?label) = 'en') 
        }
ORDER BY ?birthYear
}

Création des occupations / activités

Cette requête SQL regroupe les enregistrements de la table dbp_occupation par label (label) et URI d'objet (object_uri). Elle compte le nombre d'occurrences pour chaque combinaison (COUNT(*) AS effectif) et trie les résultats par ordre décroissant de cet effectif (ORDER BY effectif DESC). Elle permet d'identifier les occupations les plus fréquentes pour nos personnes (person).

SELECT label , object_uri , COUNT(*) AS effectif
FROM dbp_occupation do 
GROUP BY label, object_uri 
ORDER BY effectif DESC ;

Cette suite de requêtes ci-dessous insère dans la table occupation des données uniques sur les occupations (original_uri, label) issues de la table dbp_occupation, en regroupant les résultats par URI et nom. Ensuite, elle inspecte les données insérées dans occupation, puis analyse les relations entre les personnes (person) et leurs occupations (occupation) via la table de jointure dbp_occupation, en affichant les labels correspondants.

INSERT INTO occupation (original_uri, label)
SELECT target, name
FROM dbp_occupation do 
GROUP BY target, name 
ORDER BY name;

--puis on inspecte le résultat
SELECT label, original_uri 
FROM occupation o ;

/*
on inspecte les données à travers la table jointure dbp_occupation
*/
SELECT p.label, o.label 
FROM dbp_occupation do 
JOIN person p ON p.dbpedia_uri = do.subject_uri 
JOIN occupation o ON o.original_uri = do.target ;

Table pursuit

Cette requête insère dans la table pursuit les relations entre les personnes et leurs occupations en utilisant leurs clés primaires. Elle associe les identifiants des personnes (fk_person) et des occupations (fk_occupation) en effectuant des jointures entre la table de jointure dbp_occupation, la table des personnes (person), et celle des occupations (occupation).

INSERT INTO pursuit (fk_person, fk_occupation )
SELECT p.pk_person, o.pk_occupation 
FROM dbp_occupation do 
JOIN person p ON p.dbpedia_uri = do.subject_uri 
JOIN occupation o ON o.original_uri = do.target ;