Le portail francophone de la géomatique

Routchino · Wed 29 June 2011 10:10

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 travail 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 à solutionner 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ête basé sur un territoire infra-départementale (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 rapatrié toutes les infos
Ex :
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 commune dans la table de correspondance INSEE-communauté de commune, trouver les code INSEE associé et ensuite réaliser la requête de ces codes INSEE sur toute la table.

- pour les table 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.

Dernière modification par Routchino (Wed 29 June 2011 10:12)

Floflo49fb · Wed 29 June 2011 11:19

Bonjour Routchino,

- 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) ?

Cela m'a l'air bien expliqué pour la conversion: http://mgeospatial.wordpress.com/2011/0 … ostgresql/

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

Je pense que ça te facilitera la tâche pour tes futures requêtes!

Bonne journée.

Dernière modification par Floflo49fb (Wed 29 June 2011 11:23)

Yves · Wed 29 June 2011 11:28

Bonjour,

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 ?

Comme ceci :

Code:

SELECT *
FROM wifi_dep_24 AS wd24, wifi_dep_33 AS wd33……………
WHERE wd24.code_insee = '24001'

Le mot clé AS est optionnel et permet de créer des alias de champ pour simplifier l'écriture. Ensuite tu reprends le nom de la table ou son alias pour préciser le champ, la structure est comme ceci : 'schema'.'table'.'colonne'.

A priori je verrai bien une jointure sur tes tables voire même une union pour fusionner dans la requête toute tes tables, surtout si elles ont le même schéma !

Y.

Routchino · Wed 13 July 2011 09:25

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

J'ai donc ajouté une colonne the_geom à toute mes tables et je les ai update 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'erreur 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:

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';

Etant donné que j'ai a 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

Yves · Wed 13 July 2011 10:04

Bonjour,

Regarde si tu as des valeurs null dans les colonnes oùlat et long pour les tables qui posent problèmes. Sinon tu peux lancer la requete :

Code:

SELECT isValid(ST_GeomFromText('POINT('||_long||' '|| _lat||')',4326)) FROM ma_table;

Pour la deuxième question, regarde le mot clé UNION dans la doc postgreSQL.

Y.

Routchino · Tue 19 July 2011 11:04

Yves a écrit:

Bonjour,
Code:
SELECT isValid(ST_GeomFromText('POINT('||_long||' '|| _lat||')',4326)) FROM ma_table;
Pour la deuxième question, regarde le mot clé UNION dans la doc postgreSQL.

Lorsque je lance cette fonction j'ai toujours la même erreur qu'avant :

- ERREUR: parse error - invalid geometry
HINT: "POINT(" <-- parse error at position 6 within geometry
********** Erreur **********
ERREUR: parse error - invalid geometry
État SQL :XX000

Sinon pour le Union j'ai utilisé cela :

Code:

SELECT nra_a
FROM liens_nra_dep24
UNION
SELECT nra_a
FROM liens_nra_dep33
UNION
SELECT nra_a
FROM liens_nra_dep40
UNION
SELECT nra_a
FROM liens_nra_dep47
UNION
SELECT nra_a
FROM liens_nra_dep64;

Est-il possible de créer une nouvelle table à partir de ces jointures ?

MathieuB · Tue 19 July 2011 11:47

Bonjour,

Lorsque je lance cette fonction j'ai toujours la même erreur qu'avant :

Je viens de l'essayer, tout est ok.
N'aurais tu pas des valeurs non numériques dans tes colonnes ?

Pour créer une table à partir d'une requête :

Code:

CREATE TABLE ma_table AS SELECT ...

Voir la documentation : http://www.postgresql.org/docs/8.4/stat … bleas.html

Routchino · Mon 01 August 2011 16:05

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

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

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

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.

Dernière modification par Routchino (Mon 01 August 2011 16:06)

Routchino · Thu 18 August 2011 15:39

Je fais de nouveau appel à vous

Voila la fonction que je cherche à effectuer :

Code:

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

MathieuB · Thu 18 August 2011 18:25

Bonsoir,

voici la fonction, qui devrait fonctionner :

Code:

CREATE FUNCTION "extractionsss" (varchar) RETURNS varchar AS 
$BODY$
    DECLARE
    cc ALIAS for $1;
    tmp varchar;
 
 
BEGIN
tmp := 'aquitaine_liens_nra_'||cc;

EXECUTE 
$req$
    CREATE TABLE $req$||tmp||$req$ 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='$req$||cc||$req$';
$req$;
 
return tmp ;
 
      END;
$BODY$
LANGUAGE 'plpgsql';

http://www.postgresonline.com/journal/archives/87-PostgreSQL-8.3-PLPGSQL-Cheatsheet-Overview.html

Bonne soirée,

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

#1 Wed 29 June 2011 10:10

[Postgis] Construction de requête SQL

#2 Wed 29 June 2011 11:19

Re: [Postgis] Construction de requête SQL

#3 Wed 29 June 2011 11:28

Re: [Postgis] Construction de requête SQL

Code:

#4 Wed 13 July 2011 09:25

Re: [Postgis] Construction de requête SQL

Code:

Code:

#5 Wed 13 July 2011 10:04

Re: [Postgis] Construction de requête SQL

Code:

#6 Tue 19 July 2011 11:04

Re: [Postgis] Construction de requête SQL

Code:

Code:

#7 Tue 19 July 2011 11:47

Re: [Postgis] Construction de requête SQL

Code:

#8 Mon 01 August 2011 16:05

Re: [Postgis] Construction de requête SQL

Code:

#9 Thu 18 August 2011 15:39

Re: [Postgis] Construction de requête SQL

Code:

Code:

#10 Thu 18 August 2011 18:25

Re: [Postgis] Construction de requête SQL

Code:

Pied de page des forums