Requête SQL pour export

[Adisser]

@zoometh Voici une base de requête SQL pour servir de base à une fonction d'export sous format tabulaire :

SELECT
    s.nom_site AS "context_name",
    s.nom_place AS "place_name",
    s.date_debut AS "start_date",
    s.date_fin AS "end_date",
    e.nom_ech AS "sample_name",

    (COALESCE(typo.categorie_en, '') || ' ' ||
	 COALESCE(typo.sous_categorie_en, '') || ' ' ||
	 COALESCE(typo.materiau_en, '')) AS typology,

    c.id_chips AS "chips_id",
    c.carbon AS c,
    c.n, c.o, c.na, c.mg, c.al, c.si, c.p, c.s, c.cl, c.k, c.ca, c.mn, c.fe,
    c.perte_feu AS "loss on ignition",
    c.ag, c.ar, c.arsenic AS "as", c.au, c.b, c.ba, c.be, c.bi, c.br,
    c.cd, c.ce, c.co, c.cr, c.cs, c.cu, c.dy, c.er, c.eu, c.f,
    c.delta56fe AS deltafe56,
    c.delta57fe AS deltafe57,
    c.ga, c.gd, c.ge, c.h, c.he, c.hf, c.hg, c.ho, c.i,
    c.indium AS "in", c.ir, c.kr, c.la, c.li, c.lu, c.mo, c.nb, c.nd, c.ni,
    c.os_ppt AS os,
    c.os187_os188,
    c.os187_os186,
    c.pb, c.pd, c.pr, c.pt, c.rb, c.re, c.rh, c.ru, c.sb, c.sc, c.se,
    c.sm, c.sn, c.sr, c.sr87_sr86, c.ta, c.tb, c.tc, c.te, c.th, c.ti,
    c.tl, c.tm, c.u, c.v, c.w, c.xe, c.y, c.yb, c.zn, c.zr,

    m.methode_analyse AS major_method,
    (m.laboratoire || ' - ' || m.marque || ' ' || m.modele) AS major_analytical_setup,
    m.methode_analyse AS trace_method,
    (m.laboratoire || ' - ' || m.marque || ' ' || m.modele) AS trace_analytical_setup,

    (
        COALESCE(l.authors, '') || ' (' || COALESCE(l.pub_year::text, '') || '), '
        || COALESCE(l.title, '') || ', '
        || COALESCE(l.journal_book, '') || ', '
        || COALESCE(l.volume::text, '')
    ) AS reference,

    l.url,
    s.longitude,
    s.latitude,

    typo.filiere_en AS process,
    typo.contexte_en AS "operation",
    typo.categorie_en AS category,
    typo.sous_categorie_en AS subcategory

FROM chips c
JOIN echantillons e ON c.id_ech = e.id_ech
JOIN sites s ON e.id_site = s.id_site
JOIN typo ON e.id_typo = typo.id_typo
JOIN personnes p ON e.referent = p.id_personne
JOIN gadm g ON s.id_localite = g.uid
JOIN machines m ON c.id_machinem = m.id_dispositif
JOIN literature l ON c.bibreference = l.id_lit

WHERE c.open = TRUE

ORDER BY s.nom_site, e.nom_ech) 
TO 'C:\Users\Public\example.csv' DELIMITER ';' ENCODING 'UTF-8' CSV HEADER;```

[zoometh]

Dans chips (pas chips_d) j'ai cette erreur:

ERROR:  doit être super-utilisateur ou membre de pg_read_all_settings pour utiliser COPY vers un fichier
HINT:  Tout le monde peut utiliser COPY vers stdout ou à partir de stdin.
La commande \copy de psql fonctionne aussi pour tout le monde. 

ERREUR:  doit être super-utilisateur ou membre de pg_read_all_settings pour utiliser COPY vers un fichier
SQL state: 42501

la requête SQL:

COPY (
SELECT
    s.nom_site AS "context_name",
    s.nom_place AS "place_name",
    s.date_debut AS "start_date",
    s.date_fin AS "end_date",
    e.nom_ech AS "sample_name",

    (COALESCE(typo.categorie_en, '') || ' ' ||
	 COALESCE(typo.sous_categorie_en, '') || ' ' ||
	 COALESCE(typo.materiau_en, '')) AS typology,

    c.id_chips AS "chips_id",
    c.carbon AS c,
    c.n, c.o, c.na, c.mg, c.al, c.si, c.p, c.s, c.cl, c.k, c.ca, c.mn, c.fe,
    c.perte_feu AS "loss on ignition",
    c.ag, c.ar, c.arsenic AS "as", c.au, c.b, c.ba, c.be, c.bi, c.br,
    c.cd, c.ce, c.co, c.cr, c.cs, c.cu, c.dy, c.er, c.eu, c.f,
    c.delta56fe AS deltafe56,
    c.delta57fe AS deltafe57,
    c.ga, c.gd, c.ge, c.h, c.he, c.hf, c.hg, c.ho, c.i,
    c.indium AS "in", c.ir, c.kr, c.la, c.li, c.lu, c.mo, c.nb, c.nd, c.ni,
    c.os_ppt AS os,
    c.os187_os188,
    c.os187_os186,
    c.pb, c.pd, c.pr, c.pt, c.rb, c.re, c.rh, c.ru, c.sb, c.sc, c.se,
    c.sm, c.sn, c.sr, c.sr87_sr86, c.ta, c.tb, c.tc, c.te, c.th, c.ti,
    c.tl, c.tm, c.u, c.v, c.w, c.xe, c.y, c.yb, c.zn, c.zr,

    m.methode_analyse AS major_method,
    (m.laboratoire || ' - ' || m.marque || ' ' || m.modele) AS major_analytical_setup,
    m.methode_analyse AS trace_method,
    (m.laboratoire || ' - ' || m.marque || ' ' || m.modele) AS trace_analytical_setup,

    (
        COALESCE(l.authors, '') || ' (' || COALESCE(l.pub_year::text, '') || '), '
        || COALESCE(l.title, '') || ', '
        || COALESCE(l.journal_book, '') || ', '
        || COALESCE(l.volume::text, '')
    ) AS reference,

    l.url,
    s.longitude,
    s.latitude,

    typo.filiere_en AS process,
    typo.contexte_en AS "operation",
    typo.categorie_en AS category,
    typo.sous_categorie_en AS subcategory

FROM chips c
JOIN echantillons e ON c.id_ech = e.id_ech
JOIN sites s ON e.id_site = s.id_site
JOIN typo ON e.id_typo = typo.id_typo
JOIN personnes p ON e.referent = p.id_personne
JOIN gadm g ON s.id_localite = g.uid
JOIN machines m ON c.id_machinem = m.id_dispositif
JOIN literature l ON c.bibreference = l.id_lit

WHERE c.open = TRUE

ORDER BY s.nom_site, e.nom_ech

LIMIT 10)

TO 'path\to\my\file.csv' DELIMITER ';' ENCODING 'UTF-8' CSV HEADER;

Je vais mettre cette requête dans une View export_data et lancer en psql:

\copy export_data TO 'path\to\my\file.csv'' WITH CSV

[zoometh]

Je vais faire un bouton CSV d'export (download) pour chaque jeu de données, ex: dataset_adisser17

[Adisser]

Utiliser l'interface GEO pour générer des exports à partir d'une requête cartographique

[zoometh]

Utiliser l'interface GEO pour générer des exports à partir d'une requête cartographique

Et ajouter une selection box dans https://iramat-apps.cnrs.fr/dash/ pour un export via une sélection spatiale

[Adisser]

@zoometh Cette nouvelle version corrige deux bugs constatés :

• Des données ignorées dans les résultats en cas de non correspondance de valeurs entre deux tables (valeurs manquantes typiquement) --> traité par le changement des clauses JOIN en LEFT JOIN

• Le conflit suscité par le fait que les champs 'id_machinem' et 'id_machinet' référençaient un champ id_dispositif identique dans la table 'machine' --> traité par la création de deux alias 'm' et 'm1' pour la table 'machines'

SELECT
    s.nom_site AS "context_name",
    s.nom_place AS "place_name",
    s.date_debut AS "start_date",
    s.date_fin AS "end_date",
    s.edtf AS "edtf_format",
    s.key_periodo AS "PeriodO_ID",
    e.nom_ech AS "sample_name",

    (COALESCE(typo.categorie_en, '') || ' ' ||
	 COALESCE(typo.sous_categorie_en, '') || ' ' ||
	 COALESCE(typo.materiau_en, '')) AS typology,
    typo.ark_frantic AS "Pactols_ID",

    c.id_chips AS "chips_id",
    c.carbon AS c,
    c.n, c.o, c.na, c.mg, c.al, c.si, c.p, c.s, c.cl, c.k, c.ca, c.mn, c.fe,
    c.perte_feu AS "loss on ignition",
    c.ag, c.ar, c.arsenic AS "as", c.au, c.b, c.ba, c.be, c.bi, c.br,
    c.cd, c.ce, c.co, c.cr, c.cs, c.cu, c.dy, c.er, c.eu, c.f,
    c.delta56fe AS deltafe56,
    c.delta57fe AS deltafe57,
    c.ga, c.gd, c.ge, c.h, c.he, c.hf, c.hg, c.ho, c.i,
    c.indium AS "in", c.ir, c.kr, c.la, c.li, c.lu, c.mo, c.nb, c.nd, c.ni,
    c.os_ppt AS os,
    c.os187_os188,
    c.os187_os186,
    c.pb, c.pd, c.pr, c.pt, c.rb, c.re, c.rh, c.ru, c.sb, c.sc, c.se,
    c.sm, c.sn, c.sr, c.sr87_sr86, c.ta, c.tb, c.tc, c.te, c.th, c.ti,
    c.tl, c.tm, c.u, c.v, c.w, c.xe, c.y, c.yb, c.zn, c.zr,

    m.methode_analyse AS major_method,
    (m.laboratoire || ' - ' || m.marque || ' ' || m.modele) AS major_analytical_setup,
    m1.methode_analyse AS trace_method,
    (m1.laboratoire || ' - ' || m1.marque || ' ' || m1.modele) AS trace_analytical_setup,

    (
        COALESCE(l.authors, '') || ' (' || COALESCE(l.pub_year::text, '') || '), '
        || COALESCE(l.title, '') || ', '
        || COALESCE(l.journal_book, '') || ', '
        || COALESCE(l.volume::text, '')
    ) AS reference,

    l.url,
    s.longitude,
    s.latitude,

    typo.filiere_en AS process,
    typo.contexte_en AS "operation",
    typo.categorie_en AS category,
    typo.sous_categorie_en AS subcategory

FROM chips c
LEFT JOIN echantillons e ON c.id_ech = e.id_ech
LEFT JOIN sites s ON e.id_site = s.id_site
LEFT JOIN typo ON e.id_typo = typo.id_typo
LEFT JOIN personnes p ON e.referent = p.id_personne
LEFT JOIN gadm g ON s.id_localite = g.uid
LEFT JOIN machines m ON c.id_machinem = m.id_dispositif
LEFT JOIN machines m1 ON c.id_machinet = m1.id_dispositif
LEFT JOIN literature l ON c.bibreference = l.id_lit

WHERE c.open = TRUE

ORDER BY s.nom_site, e.nom_ech) 
TO 'C:\Users\Public\example.csv' DELIMITER ';' ENCODING 'UTF-8' CSV HEADER;