Le portail francophone de la géomatique

legannec · Wed 29 July 2015 14:56

Bonjour,

J'ai deux couches de vecteurs, l'une comprenant des polygones et l'autre des ponctuels, dans deux bases de données différentes sur PostgresQL.

1ère couche de Polygones : 'Parcelles' (dbname='CADASTRE')

2ème couche de Points : 'f_rit_regard' (dbname='RIT')

Il y a parfois un ponctuel compris dans le polygone et parfois plusieurs. Je cherche à créer une vue en sélectionnant de façon automatique par requête SQL les polygones qui contiennent plusieurs ponctuels.

Du coup, suite à une première discussion avec Mathieu du Topic Qgis, j'ai légèrement modifié la requête suivant

CREATE VIEW parcelles_a_plusieurs_regard AS
SELECT * FROM dblink('hostaddr=localhost port=5432 dbname=CADASTRE user=postgres password=monMP','SELECT Parcelles.id, count(f_rit_regard.id), parcelles.geom
FROM parcelles JOIN f_rit_regard ON st_intersects(parcelles.geom, f_rit_regard.geom)
HAVING count(f_rit_regard.id)>1)')

La réponse :

ERREUR: une liste de définition de colonnes est requise pour les fonctions renvoyant
un « record »
État SQL :42601
Caractère : 66

Il me semble qu'il faut spécifier à la fin de la requête une structuration qui permette de récupérer les entités sélectionnés mais comme c'est ma première requête SQL, je ne suis sûr de rien et aurai vivement besoin d'un bon conseil.

MathieuB · Wed 29 July 2015 16:38

Bonjour,

Avec dblink, vous devez décrire l'enregistrement qui sera retourné :

Code:

WITH parcelles AS (SELECT * FROM dblink('hostaddr=localhost port=5432 dbname=CADASTRE user=postgres password=monMP','SELECT id, parcelles.geom) parcelles (id integer, geom geometry(POLYGON,2154),
f_rit_regardAS  (SELECT * FROM dblink('hostaddr=localhost port=5432 dbname=CADASTRE user=postgres password=monMP','SELECT id, geom) f_rit_regard(id integer, geom geometry(POINT,2154)
SELECT parcelles.id, count(f_rit_regard.id), parcelles.geom
FROM parcelles JOIN f_rit_regardON st_intersects(parcelles.geom, f_rit_regard.geom)
HAVING  count(f_rit_regard.id)>1)')

legannec · Fri 31 July 2015 08:50

Bonjour Mathieu,

J'ai essayé de bidouiller plusieurs éléments mais en vain,
j'obtiens ce résultat :

ERREUR: erreur de syntaxe sur ou près de « hostaddr »
LINE 2: f_rit_regard AS (SELECT * FROM dblink('hostaddr=localhost p...
^

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

ERREUR: erreur de syntaxe sur ou près de « hostaddr »
État SQL :42601
Caractère : 236

Du coup, plusieurs questions :

Tout d'abord, au niveau de la forme, comment fait t-on pour incruster du code dans une page du forum ?
Ensuite, mes données sont stockées dans 2 bases différentes, dois-je modifier l'une des deux "dbname" ?
A quoi correspond "id", serait-ce la clé primaire des tables ou un nouveau champs créé suite à la requête ?
Et enfin à quoi correspond le "2154" après le type géométrique des entités ?

Cordialement,

jmarsac · Fri 31 July 2015 09:21

Bonjour,

Sélectionnez la portion de code et cliquez sur le bouton Code

Pouvez-vous mettre la requête entière ?

Dernière modification par jmarsac (Fri 31 July 2015 09:27)

legannec · Fri 31 July 2015 10:40

Bonjour jmarsac,

Voici le code :

Code:

WITH parcelles AS (
    SELECT * 
    FROM dblink('host=localhost port=5432 dbname=CADASTRE user=postgres password=mon_mot_de_passe','SELECT id, parcelles.geom) parcelles (id integer, geom geometry(POLYGON,2154),
 f_rit_regard AS (
    SELECT * 
    FROM dblink('host=localhost port=5432 dbname=CADASTRE user=postgres password=mon_mot_de_passe','SELECT id, geom) f_rit_regard (id integer, geom geometry(POINT,2154)

    SELECT parcelles.id, count(f_rit_regard.id), parcelles.geom 
    FROM parcelles JOIN f_rit_regard ON st_intersects(parcelles.geom, f_rit_regard.geom) HAVING  count(f_rit_regard.id)>1)')

Nicolas Ribot · Fri 31 July 2015 11:43

Bonjour,

Puis-je vous demander pourquoi les données sont dans deux bases séparées ?

Je vois souvent, à l'occasion de formations, que la notion théorique de base de données (BD cadastre, BD référentiels ...) est traduite par la création de bases de données PostgreSQL différentes sur le meme serveur.
Il est souvent beaucoup plus intéressant de regrouper toutes les données travail (spatiales et non spatiales) au sein de la même base de donnée physique, en organisant les données en schémas.

C'est en général plus facile a administrer (sauvegardes, réplications, configuration d'une seule instance), plus performant et cela permet d'exploiter facilement TOUTES les données, en évitant justement de se connecter à plusieurs bases avec DBLink ou FDW. (Par exemple, si vous créez des fonctions SQL spécifiques pour vos besoins, il est plus intéressant de définir ces fonctions sur une seule base, en pouvant les utiliser avec toutes les données).

Les outils externes se connectant à PostgreSQL (qgis, mapserver, geoserver) sont également plus facile à configurer et ont a gérer moins de connexions à PostgreSQL.

Nicolas

legannec · Mon 03 August 2015 11:53

Bonjour,

Effectivement, cela simplifierait les prochaines requêtes de constituer une seule BDD avec plusieurs schémas.

Par contre au niveau de l'administration, peut-on configurer un accès à la BDD selon les différents schémas ?

Pour l'instant, sur le réseau local j'ai l'utilisateur A qui ne peut se connecter qu'à la BDD 1 et l'utilisateur B, à la BDD 2.
Il faudrait donc que les utilisateurs puissent se connecter à une unique BDD avec cependant des contrôles d’accès vers le schéma 1 ou le schéma 2.

Cordialement

Nicolas Ribot · Mon 03 August 2015 15:38

Bonjour,

Oui, la gestion des roles et des droits (commandes GRANT et REVOKE) vous permettre de donner les droits (lecture, écriture, connexion, etc.) que vous voulez sur les objets (schemas, tables, fonctions...) que vous voulez.

(http://www.postgresql.org/docs/current/ … manag.html)

Nicolas

jmarsac · Mon 10 August 2015 15:35

Bonjour,

legannec a écrit:

Bonjour jmarsac,

Voici le code :

Code:

WITH parcelles AS (
    SELECT * 
    FROM dblink('host=localhost port=5432 dbname=CADASTRE user=postgres password=mon_mot_de_passe','SELECT id, geom) parcelles (id integer, geom geometry(POLYGON,2154),
 f_rit_regard AS (
    SELECT * 
    FROM dblink('host=localhost port=5432 dbname=CADASTRE user=postgres password=mon_mot_de_passe','SELECT id, geom) f_rit_regard (id integer, geom geometry(POINT,2154)

    SELECT parcelles.id, count(f_rit_regard.id), parcelles.geom 
    FROM parcelles JOIN f_rit_regard ON st_intersects(parcelles.geom, f_rit_regard.geom) HAVING  count(f_rit_regard.id)>1)')

Pas le temps de tester, mais le code suivant devrait fonctionner en remplaçant t1 et t2 par le nom réel des tables concernées:

Code:

WITH
 parcelles AS (
    SELECT * 
    FROM dblink('host=localhost port=5432 dbname=CADASTRE user=postgres password=mon_mot_de_passe','SELECT id, parcelles.geom from t1') ),
 f_rit_regard AS (
    SELECT * 
    FROM dblink('host=localhost port=5432 dbname=CADASTRE user=postgres password=mon_mot_de_passe','SELECT id, geom from t2') )

    SELECT parcelles.id, count(f_rit_regard.id), parcelles.geom 
    FROM parcelles JOIN f_rit_regard ON st_intersects(parcelles.geom, f_rit_regard.geom) HAVING  count(f_rit_regard.id) > 1;

Dernière modification par jmarsac (Mon 10 August 2015 16:24)

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

Annonce

#1 Wed 29 July 2015 14:56

Créer une vue entre deux base de données

#2 Wed 29 July 2015 16:38

Re: Créer une vue entre deux base de données

Code:

#3 Fri 31 July 2015 08:50

Re: Créer une vue entre deux base de données

#4 Fri 31 July 2015 09:21

Re: Créer une vue entre deux base de données

#5 Fri 31 July 2015 10:40

Re: Créer une vue entre deux base de données

Code:

#6 Fri 31 July 2015 11:43

Re: Créer une vue entre deux base de données

#7 Mon 03 August 2015 11:53

Re: Créer une vue entre deux base de données

#8 Mon 03 August 2015 15:38

Re: Créer une vue entre deux base de données

#9 Mon 10 August 2015 15:35

Re: Créer une vue entre deux base de données

Code:

Code:

Pied de page des forums