[Postgis] Construction de requête

[Routchino]

Bonjour,

Actuellement en stage au Conseil Régional Aquitaine et plus particulièrement au sein de la cellule THD (Très Haut Débit), je travaille sur des données télécom. C’est dans ce cadre que j’essaye de créer une base de données Postgis, en lien avec Qgis, avec pour objectif d’extraire des données directement depuis la base via des requêtes.
Etant néophyte dans ce domaine, je fais appel à vous car j’ai plusieurs problèmes que je n’arrive pas à résoudre malgré mes lectures sur le langage SQL…

J’ai donc créé une base PostGis et y ai ajouté une centaine de tables de formats différents (shp, .txt, .csv).


Mes questions :
- j’ai dans la plupart de mes tables .txt deux colonnes avec des coordonnées X, Y. Dois-je faire une opération particulière pour ajouter une colonne the_geom comme c’est le cas pour les shapes (cela s’est fait automatiquement pour ces tables) ?

- Dois-je attribuer la même projection à toutes mes tables ?


L’objectif de cette base est d’extraire des données à une échelle infra-départementale (communauté de commune, communauté d’agglo, ou communauté urbaine), sachant que dans les tables les informations ne sont localisées qu’à l’échelle de la commune (code INSEE). En effet, une communauté de commune va faire appel à nous afin que nous leur fournissions les informations à l’échelle de leur territoire.

L’idéal serait donc de réaliser un ensemble de requêtes basées sur un territoire infra-départemental (communauté de commune…) afin de récupérer des données qui se trouvent à l’échelle de la commune dans la base.

J’ai donc ajouté une table contenant les correspondances entre code INSEE et communauté de commune d’appartenance.
Je pensais donc fonctionner de deux manières :
- pour les tables qui ne contiennent pas de coordonnées X,Y il faut donc que je fasse une requête attributaire, je voulais donc rechercher les différents codes INSEE qui m’intéresse au sein de toute la base pour rapatrier toutes les infos
Ex :

Code :

1
2
3
SELECT * 
FROM wifi_dep_24, wifi_dep_33……………
WHERE code_insee = '24001'

Problème, la requête ne peut pas se faire car le champ code_insee apparait dans plusieurs tables. Comment puis-je donc réaliser cette requête ?
Serait-il possible de réaliser la requête dans l’autre sens, à savoir rechercher une communauté de communes dans la table de correspondance INSEE-communauté de commune, trouver les codes INSEE associés et ensuite réaliser la requête de ces codes INSEE sur toute la table.

- pour les tables ayant une colonne the_geom, pensez-vous qu’il est possible de faire une requête spatiale ou géométrique, toujours par rapport à la table de correspondance code INSEE-communauté de commune ? Si oui, je n’ai aucune idée sur la façon de procéder donc je suis preneur de quelques pistes.


Merci d’avance pour votre aide qui me sera précieuse.

[MaitrePylos]

Une chose à la fois.....

tu peux récupérer les codes INSEE en fonction de la table

Code :

1
2
3
4
5
6
 
SELECT * 
FROM wifi_dep_24, wifi_dep_33……………
WHERE wifi_dep_24.code_insee = '24001'
AND wifi_dep_23.code_insee = '24001'
......

[Routchino]

Oui mais sachant que j'ai une centaine de tables je voulais savoir s'il était possible de simplifier la requête.

[MaitrePylos]

Oui, modéliser correctement la base de données .

[Routchino]

Désolé pour cette réponse tardive mais j'étais occupé sur une autre mission et celle-ci été mise en suspens.

J'ai donc ajouté une colonne the_geom à toutes mes tables et je les ai updatées avec cette formule :

Code :

UPDATE ma_table SET the_geom = ST_GeomFromText('POINT('||_long||' '|| _lat||')',4326);

Cela a fonctionné pour la majorité des tables mais j'ai deux types d'erreurs pour d'autres :


Soit :

- ERREUR: parse error - invalid geometry

HINT: "POINT(" <-- parse error at position 6 within geometry

********** Erreur **********

ERREUR: parse error - invalid geometry

État SQL :XX000

Soit :

- ERREUR: Invalid OGC WKT (too short)

********** Erreur **********
ERREUR: Invalid OGC WKT (too short)

État SQL :XX000

Je ne comprend pas à quoi sont dues ces erreurs car les tables .txt sont au même format et de structures similaires.


Sinon, concernant la requête elle-même, je suis parvenu à la réaliser via une jointure entre ma table insee_commune et les autres tables avec ce code :

Code :

1
2
3
4
5
6
SELECT* 
FROM aquit_communes_2011
 
INNER JOIN optique_pro_dep24 ON(optique_pro_dep24.insee=aquit_communes_2011.num_com)
 
WHERE aquit_communes_2011.pays_nom='Le Périgord Vert';

Étant donné que j'ai à chaque fois 5 tables de structures identiques (une par département de la région aquitaine), comment pourrais-je faire pour fusionner ces 5 tables et ainsi réaliser une seule requête sur cette "super table" plutôt que 5 requêtes sur 5 tables comme je le fait actuellement ? Quelle fonction dois-je utiliser.


Merci

[Routchino]

J'ai bien avancé grâce à tous vos conseils et je vous en remercie.

Je voulais savoir si c'était possible d'updater une colonne à partir d'une colonne d'un fichier .csv créé sous excel ?

J'ai essayé ça mais ça ne fonctionne pas :

Code:

Code :

UPDATE aquitaine_pylones_ftdit SET insee = 'C:/Program Files/PostgreSQL/9.0/Classeur2.csv' ;

Problème : il m'inscrit tout simplement ce qu'il y a écrit entre les guillemets au lieu de copier le contenu de la colonne située sous le chemin d'accès.

[ced]

Bonjour,

Pour charger le contenu d'un fichier CSV dans une table, il faut passer par la commande COPY.
L'idée, c'est de charger ce fichier dans une table temporaire, puis de procéder à l'UPDATE par jointure entre la table à mettre à jour et la table temporaire issue du chargement du ficher CSV.

[Routchino]

J'ai essayé ça :

Code :

1
2
 
COPY aquitaine_pylone_ftdit (insee) FROM 'C:/Program Files/PostgreSQL/9.0/Classeur2.csv';

Ça fonctionne il me rajoute bien les données de mon fichier csv mais il les rajoute après les données déjà existantes dans ma table.
En fait j'ai mes 196 lignes qui ne changent pas et il me rajoute 196 lignes contenant la colonne que je veux rajouter au lieu de modifier les 196 lignes déjà existantes. Je ne sais pas si c'est très clair

[ced]

C'est bien pour ça que j'ai parlé d'une mise à jour en 2 temps :

1. charger le fichier CSV dans une table temporaire ;
2. mettre à jour la table finale à partir de la table temporaire, via une jointure entre les 2 tables (UPDATE... FROM... WHERE...).

[Routchino]

D'accord je testerais ça

[Routchino]

J'ai essayé :

Code :

1
2
3
UPDATE aquitaine_pylones_ftdit 
FROM classeur2
WHERE aquitaine_pylones_ftdit.codepyl=classeur2.codepyl

qui me renvoi cette erreur :

ERREUR: erreur de syntaxe sur ou près de « FROM »
LINE 2: FROM classeur2

et

Code :

1
2
3
UPDATE aquitaine_pylones_ftdit 
FROM classeur2
INNER JOIN aquitaine_pylones_ftdit ON (aquitaine_pylones_ftdit.codepyl=classeur2.codepyl)

Même erreur :

ERREUR: erreur de syntaxe sur ou près de « FROM »
LINE 2: FROM classeur2

Savez-vous ce qui cloche ?

[estofilo]

Un UPDATE doit contenir une clause SET avec les champs à mettre à jour.

[Routchino]

Avec ce code ça a fonctionné :

Code :

1
2
3
4
 
UPDATE aquitaine_pylones_ftdit SET insee = classeur2.insee
FROM classeur2
WHERE aquitaine_pylones_ftdit.codepyl = classeur2.codepyl

Merci beaucoup

[Routchino]

Je fais de nouveau appel à vous

Voila la fonction que je cherche à effectuer :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE FUNCTION "extractionsss" (varchar) RETURNS varchar AS 
'
    DECLARE
        cc varchar;
	tmp varchar;
 
 
      BEGIN
cc := $1;
tmp := ''aquitaine_liens_nra_'' || cc;
 
CREATE TABLE tmp AS
SELECT * FROM aquit_communes_2011 
INNER JOIN aquitaine_liens_nra ON (aquitaine_liens_nra.insee_nra_a=aquit_communes_2011.num_com)
WHERE aquit_communes_2011.ca_cu_nom=cc;
 
 
return tmp ;
 
      END;
    ' 
LANGUAGE 'plpgsql';
SELECT extractionsss('CA Le Marsan');

Le problème c'est que la nouvelle table créée porte le nom "tmp" et non ce qu'il y a à l'intérieur de cette variable à savoir

Code :

aquitaine_liens_nra_'' || cc

[estofilo]

Pour exécuter du SQL généré dynamiquement dans une fonction, il faut utiliser EXECUTE.
Voir la doc du langage plpgsql.

[Routchino]

J'avais déjà essayé le EXECUTE mais ça ne fonctionnait pas :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE FUNCTION "extractionssssss" (varchar) RETURNS varchar AS 
'
    DECLARE
        cc varchar;
	tmp varchar;
 
 
      BEGIN
cc := $1;
tmp := ''aquitaine_liens_nra_'' || ''cc'';
EXECUTE (''CREATE TABLE ''|| tmp||'' AS
SELECT * FROM aquit_communes_2011 
INNER JOIN aquitaine_liens_nra ON (aquitaine_liens_nra.insee_nra_a=aquit_communes_2011.num_com)
WHERE aquit_communes_2011.ca_cu_nom=\' ''||cc||''\' ;'');
 
 
RETURN tmp;
      END;
    ' 
LANGUAGE 'plpgsql';
SELECT extractionssssss('CA Le Marsan');

Erreur suivante :
ATTENTION: utilisation non standard de \' dans une chaîne littérale
LINE 2: '
^
HINT: Utilisez '' pour écrire des guillemets dans une chaîne ou utilisez la syntaxe de
chaîne d'échappement (E'...').


ERREUR: erreur de syntaxe sur ou près de « '||cc||'' ;' »
LINE 14: WHERE aquit_communes_2011.ca_cu_nom=\' ''||cc||''\' ;'');


J'ai aussi essayé avec E' à la place de \' mais rien n'y fait
^

[estofilo]

Ce n'est pas le EXECUTE qui pose problème, c'est la combinaison infernale d'apostrophes.
Pour éviter ça, il serait préférable d'adopter le style appelé 'dollar-quoting', c.a.d:

Code :

1
2
3
4
5
6
7
CREATE FUNCTION "extractionssssss" (varchar) RETURNS varchar AS 
$BODY$
    DECLARE
 ... etc...
    END;
$BODY$
LANGUAGE 'plpgsql';

Dans tout le code entre les balises $BODY$, les apostrophes n'ont pas besoin d'être en double, ce qui simplifie déjà pas mal.

Par ailleurs le EXECUTE a une clause USING pour lui passer des arguments variables de l'extérieur de la requête elle-même, ce qui permet encore de simplifier l'écriture.

[Routchino]

Problème solutionné.

Nouveau problème :

J'ai dans mon fichier sql un

Code :

RETURN tab

Je voudrais récupérer la valeur du

Code :

RETURN tab

, à savoir Comcom, afin d'utiliser cela dans mon .bat.

Comment est-ce possible ?

De plus je souhaiterais pouvoir taper la variable "CA Le Marsan" dans le cmd afin de réaliser les requêtes sur ce que tape l'utilisateur. Dans mon fichier .bat j'ai écrit ça :

Code :

1
2
3
4
ECHO sql
echo %~1
copy totoTEST.sql tmp2.sql
echo SELECT extraction('%1'); >> tmp2.sql

Le problème c'est que j'ai seulement le premier mot de ce qui a été tapé (CA) et non les 3 mots. Comment pourrais-je remédier à cela ?

MErci