Le portail francophone de la géomatique

Elsane · Thu 22 May 2014 22:52

Bonjour,

j'aimerais savoir si quelqu'un a déjà effectué une ST_intersects sur plusieurs couches à la fois ou une piste à creuser ?

Merci

Stych · Fri 23 May 2014 09:43

Bonjour Elsane,

Vous voulez dire plusieurs tables intersectant une seule et même table?
Si c'est le cas, je fais aussi face à la même question. Pour l'instant j'ai essayer de créer une fonction pl/pgsql suivante:

Code:

CREATE OR REPLACE FUNCTION test2(out nom_table text, OUT id_littoral text, out geom_littoral geometry ) RETURNS SETOF record
LANGUAGE plpgsql
AS
$BODY$
DECLARE
  tables record;
BEGIN
  FOR tables IN SELECT DISTINCT tablename FROM pg_tables where tablename 
         IN 
        (
        (select tablename from pg_tables where schemaname ='france' and tablename in (SELECT f_table_name FROM geometry_columns where f_table_name not like '%_t' and f_table_name not like 'france_littoral' and f_table_name not like 'box%' and f_table_name not like 'rast%'))
        ) 
    AND pg_tables.schemaname='france'  
  LOOP
    nom_table := tables.tablename;
   Execute 'select '||quote_ident (tables.tablename)||'.id from france_littoral,' ||quote_ident(tables.tablename)||' WHERE st_intersects('|| quote_ident (tables.tablename)||'.the_geom_box, france_littoral.the_geom_wgs84) ;'
      Into id_littoral;
   Execute 'select france_littoral.the_geom_wgs84 from france_littoral, '||quote_ident (tables.tablename)|| ' where st_intersects('||quote_ident(tables.tablename)||'.the_geom_box, france_littoral.the_geom_wgs84);'
      Into geom_littoral;
    RETURN NEXT;
  END LOOP;
END
$BODY$;

La fonction me donne un résultat mais pas celui escompté, en revanche même si le résultat de la requête est à discuté, il m'effectue bien un ST_intersect sur plusieurs couches. plusieurs couches.
J'espère avoir pu vous mettre sur une piste.

Nicolas Ribot · Fri 23 May 2014 10:03

Bonjour,

Quel type d'intersection souhaitez-vous réaliser ? Une couche avec toutes les autres qui seraient fusionnées ? Une combinatoire de tous les cas possible ? Les couches ont des types de geom différents ?

Suivant le résultat escompté et le type géométriques des couches en jeu, il y a plusieurs approches:
Union de tous les objets des n couches (en conservant les sources et id originaux) puis intersection avec la couche de référence, par ex.

Nicolas

Stych · Fri 23 May 2014 10:26

Bonjour,

Pour ma part le résultat espéré serait d'avoir dans la couches d'intersection de référence, les id et le noms de toutes les tables qui l'intersectent. Est-ce possible?
Désolé de profiter du poste d'Elsane pour poser ma question mais j'avoue faire face au même problème...

Stych · Fri 23 May 2014 10:37

De plus j'aurais une autre question.

Suivant le résultat escompté et le type géométriques des couches en jeu, il y a plusieurs approches:
Union de tous les objets des n couches (en conservant les sources et id originaux) puis intersection avec la couche de référence, par ex.

Il est donc possible de regrouper toutes les tables géométriques (point, polyligne, multipoint, polygone...) en une seul table?
Merci.

Stych

Nicolas Ribot · Fri 23 May 2014 11:27

Oui tout a fait:

Suivant ce qui est le plus convenable dans votre contexte, une fonction (comme celle que vous avez faite) ou une requete SQL avec préparation d'une table de synthèse sont possibles:

Avec une table regroupant les geoms des autres tables:

Code:

create table all_geoms as (
    select id as id_source, 'table1' as source, geom
    from table1
 UNION
    select id as id_source, 'table2' as source, geom
    from table2
  UNION
...
);

Création d'un index spatial sur cette table.

Puis interrogation de tous les objets qui intersectent la table de référence:

Code:

select fl.id, a.id_source, a.source
france_littoral fl, all_geoms a
where st_intersects(fl.geom, a.geom);

Nicolas

Stych · Fri 23 May 2014 16:58

Re,

Merci pour votre réponse Nicolas.
J'ai pu ainsi réaliser une fusion de toutes mes tables de manière plus ou moins dynamique en passant par une fonction.
Puis j'ai réaliser mon St_intersects avec ma couche de référence en faisant un "CREATE OR REPLACE VIEW" en faisant bien attention de récupérer la colonne géométrique de ma table de référence.
Mais la vue ne peux pas s'afficher dans mon client carto(Qgis) et Qgis me dit que ma couche est non valide...

Est ce quelqu'un à déjà été confronté à ce genre de problème?

Bon week end à vous!!
Stych.

Elsane · Fri 23 May 2014 21:33

Pas de soucis Stych,

je suis tout à fait dans le même cas de figure que toi à savoir récupérer les id et noms des tables intersectées, et avec des géométries différentes.

Peux-tu me mettre ta fonction qui fusionne dynamiquement des tables ?

Elsane

SANTANNA · Sat 24 May 2014 12:58

Bonjour,

Il est donc possible de regrouper toutes les tables géométriques (point, polyligne, multipoint, polygone...) en une seul table?
Merci.

En effet, c'est possible mais il faut garder en mémoire que toutes les fonctions de Postgis ne sont pas adaptées à toutes les géométries et donc bien savoir quel usage et quel traitement on veut faire de cette table pour en comprendre les résultats ou messages d'erreur.

Mais la vue ne peux pas s'afficher dans mon client carto(Qgis) et Qgis me dit que ma couche est non valide...

Pour pouvoir afficher une table dans QGIS, deux conditions requises:
- un identifiant unique de type integer dans la table, ce qui ne devrait pas être le cas ici avec la fusion de toutes ces couches. Ca peut se faire en ajoutant un champ dans la requete d'intersection avec row_number() OVER () as num
- un type identique de géométrie (pas de mélange autorisé entre ligne, polygone, point). cf point précédent.

Stych · Mon 26 May 2014 13:19

Salut,

Code:

--drop function table_union();
CREATE OR REPLACE FUNCTION france.table_union () RETURNS table (gid bigint,id varchar, nom_donnee varchar, the_geom geometry)  
language plpgsql
as
$BODY$
DECLARE
 tables record;
BEGIN
  FOR tables IN SELECT tablename FROM pg_tables where tablename 
         IN 
        (select tablename from pg_tables where schemaname ='france' and tablename in (SELECT f_table_name FROM geometry_columns where f_table_name not like '%_t' and f_table_name not like 'france_littoral' and f_table_name not like 'box%' and f_table_name not like 'rast%'))
  LOOP
  return query Execute 'select row_number() over(order by id) as gid ,id,  nom_donnee, the_geom_wgs84  from '||quote_ident (tables.tablename)||';' 
    RETURN ;
  END LOOP;
END
$BODY$;

Elsane, voici la fonction qui me permet de fusionner mes tables et de récupérer les champs désirés et leurs géométries.
Ensuite je crée une vue en intersectant cette tables et ma couche de référence, en ne récupérant que la géométrie de ma couche de référence pour pouvoir ensuite visualiser mon résultat dans un client carto. Mais pour l'instant cette dernière étape me pose toujours problème.

Santana, merci pour ton intervention.
La fonction me récupère mes id de toutes mes tables, qui est une concaténation du nom des tables + leurs gid. Logiquement l'identifiant est unique est cela marche parfaitement sur les autre vues. Dans le doute j'ai tout de même utilisé row_number() over() comme tu me l'a indiqué, mais rien n'a changé. Qgis bloque sur la première couche de ma vue et me dit qu'elle n'existe pas... Pourtant les géométries récupérée sont les géométries de ma couches de référence. Donc un seul et même type de géométrie.
Je continue de chercher et posterais une solution quand j'aurais réussis à résoudre le problème.

Stych

Dernière modification par Stych (Mon 26 May 2014 13:21)

SANTANNA · Mon 26 May 2014 14:42

Bonjour,
Stych, tu passes par quelle interface pour ouvrir ta vue dans QGIS? DBManager, Parcourir ou ouvrir une table PG? Quelle version de QGIS?
Quand dans PG, tu fais un "select st_isvalidreason(geom) from mavue", il te renvoie que de la géométrie valide?

En regardant ta fonction, je me demande si le row_number()over() ne va pas itérer couche par couche, ce qui fait qu'en sortie on risque d'avoir des numéros non uniques (venant de différentes couches). A vérifier sur ta donnée. Je me trompe peut-être.
Quand j'en parlais, c'était plutôt à insérer dans la requête vérifiant l'intersection.
Mais bizarre qu'il accepte tes vues si tu me dis que la clé primaire n'est pas integer. La doc de QGIs en parle juste dans la section au dessus de ce lien

Elsane · Mon 26 May 2014 15:27

Bonjour Stych,

peux-tu m'expliquer à quoi te sert "row_number() over(order by id) as gid"

Merci !

Stych · Tue 27 May 2014 10:50

Bonjour,

Elsane:
La fonction row_number() over() devrait me permettre de récupérer un identifiant unique de type integer. En gros il me numérote mes lignes de 1 à n lignes. Elle me servait surtout à récupérer un identifiant que Qgis pourrait interpréter comme clés primaire. Selon tes besoins elle ne t'es pas forcément indispensable.

Santanna:
Merci pour ta réponse. Pour ouvrir mes table j'utilise l'outil 'ajouter une couche Postgis'. J'ai réalisé un st_validreason comme tu me la conseillé, et le résultat en sorti me confirme bien que mes géométries sont toutes valides.
J'ai aussi enlevé row_number()over() de ma fonction et je l'ai plutôt placé dans la création de ma vue. En analysant cette même vue via un select, chaque lignes de ma tables sont numéroté de 1 à n lignes. Donc un identifiant unique et de type integer.

Lors de l'importation de mes vues vers qgis j'ai pu récupérer les messages d'erreurs suivant:

Code:

Requête erronée : SELECT DISTINCT CASE WHEN upper(geometrytype("the_geom_wgs84")) IN ('POINT','MULTIPOINT','POINTM','MULTIPOINTM') THEN 'POINT' WHEN upper(geometrytype("the_geom_wgs84")) IN ('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM') THEN 'LINESTRING' WHEN upper(geometrytype("the_geom_wgs84")) IN ('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM') THEN 'POLYGON' END, st_srid("the_geom_wgs84") FROM "france"."table_liste" a retourné 7 [ERREUR:  la relation Â« mnt_alentour_emprise Â» n'existe pas
LINE 1: select id,  nom_donnee, the_geom_wgs84  from mnt_alentour_em...
                                                     ^
QUERY:  select id,  nom_donnee, the_geom_wgs84  from mnt_alentour_emprise;
CONTEXT:  fonction PL/pgsql france.table_union(), ligne 9 Ã  RETURN QUERY
]
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Requête erronée : SELECT * FROM "france"."table_intersect" LIMIT 1 a retourné 7 [ERREUR:  la relation Â« mnt_alentour_emprise Â» n'existe pas
LINE 1: select id,  nom_donnee, the_geom_wgs84  from mnt_alentour_em...
                                                     ^
QUERY:  select id,  nom_donnee, the_geom_wgs84  from mnt_alentour_emprise;
CONTEXT:  fonction PL/pgsql france.table_union(), ligne 9 Ã  RETURN QUERY
]
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Impossible d'accéder à la relation "france"."table_intersect".
Le message d'erreur de la base de données est :
ERREUR:  la relation « mnt_alentour_emprise » n'existe pas
LINE 1: select id,  nom_donnee, the_geom_wgs84  from mnt_alentour_em...
                                                     ^
QUERY:  select id,  nom_donnee, the_geom_wgs84  from mnt_alentour_emprise;
CONTEXT:  fonction PL/pgsql france.table_union(), ligne 9 à RETURN QUERY

SQL : SELECT * FROM "france"."table_intersect" LIMIT 1

Apparemment, pour Qgis mes table n'existe pas. Je trouve cela vraiment bizarre puisque dans postgis, la fonction marche et mes vues issues de cette fonction me retournent les bons résultats.
Quelqu'un aurait une idée de la provenance du problème?

Dernière modification par Stych (Tue 27 May 2014 10:52)

Stych · Tue 27 May 2014 15:22

Re bonjour,

Je reviens vers vous pour apporter une nouvelle vision du problème, et aussi un semblant de solution.
Il semblerais que créer une vue en allant piocher des coordonnées dans une autre vue ne soit pas faisable, ou plutôt si, Postgis le fait sans problème, mais Qgis ne sait pas le prendre en compte.
J'ai donc du créer une table dans postgis issue de cette intersection et là tout fonctionne !
Mais cela ne fait que déplacer mon problème puisque mon opération d'intersection n'est plus dynamique...
@Elsane, si tu fais face au même cas que moi, as tu trouvé quelques pistes?

Dernière modification par Stych (Tue 27 May 2014 15:27)

Nicolas Ribot · Tue 27 May 2014 16:42

Bonjour,

Si, QGis sait afficher des vues PostGIS contenant des geometries. Il faut pour cela que la vue contienne un type geometrique unique, et que ce type ne soit pas "GEOMETRY" mais un type plus précis (point, ligne...)

Hmm attention a la confusion: vous avez créé une fonction qui renvoie des lignes, mais ce n'est pas une vue ni une table matérialisée.

Si vous voulez travailler avec une table, je vous invite à la créer dans la fonction, et pas a retourner les lignes.
Si vous préférez une vue, utilisez la fonction pl/pgsql pour générer le SQL de la vue, style:

create view toto as (
select id, geom from table1
UNION
select id, geom from table2
UNION
...
)

Puis executez le code SQL généré par la fonction.

Si la vue ou table nouvellement créée contient des géométries hétérogènes, je ne suis pas sur que QGIs sache la lire. OpenJump, autre SIG OpenSource, sait le faire. Mais il est moins riche que QGis.

Si la vue/table ne contient qu'un seul type d'objet (POLYGONE par ex), vous pouvez caster le champs geometry en un type polygone: geom::geometry(Polygon, 2154) (si le SRID de la couche est 2154)

Dans ce cas, la table ou vue sera automatiquement enregistrées dans les métadonnées de Postgis (vue geoemtry_columns) et qgis saura l'afficher.

Nicolas

Stych · Tue 27 May 2014 17:19

Bonjour Nicolas,

Postgis n'est effectivement pas capable de lire une table avec des géométries hétérogène. Cependant ce n'est pas mon cas puisque je ne garde que les géométries de ma couches de référence que j'utilise lors de mon découpage.

Pour la fonction(), j'ai oublié de préciser que je créait une vue en récupérant le résultat de ma fonction, désolé

Code:

create or replace view table_liste as 
select * from fonction();

Puis je créait une vue à partir de cette vue et de ma couche d'intersection... de quoi s'embrouiller.

En revanche je n'ai pas essayer de créer une table via ma fonction directement. J'essaierais et tiendrais à jour le poste.
Mais la table issue de cette fonction sera t'elle mise à jour dynamiquement?
En tout cas merci pour vos piste de réflexions.

Stych.

Dernière modification par Stych (Tue 27 May 2014 17:20)

SANTANNA · Tue 27 May 2014 17:55

Bonjour,

Il semblerais que créer une vue en allant piocher des coordonnées dans une autre vue ne soit pas faisable, ou plutôt si, Postgis le fait sans problème, mais Qgis ne sait pas le prendre en compte.

Pas d'accord. J'utilise au quotidien QGIS avec des vues PG qui sont des combinaisons (le fameux UNION qu'évoque Nicolas) de tables et de vues. Ça ne pose pas de problème, pour autant que les conditions que j'avais déjà listées soient respectées (identifiant unique numérique et un "type" de géométrie).

Pour ce qui est des fonctions, je ne suis pas très à l'aise donc je laisse Nicolas en parler. Mais une fois la fonction table_union effectuée, arrives-tu à ouvrir cette table dans QGIS (avant toute opération d'intersection)?

J'ai réalisé un st_validreason comme tu me la conseillé, et le résultat en sorti me confirme bien que mes géométries sont toutes valides.

Désolé mais ce que j'aurais plutôt dû te faire checker c'est la géométrie des entités. Que donne un "select distinct st_geometrytype(geom) from matable"?

-- EDIT--
Désolé Stych si des éléments de réponse sont déjà dans ton précédent message. J'ai tardé à envoyer mon message et n'ai pas pensé à actualiser la fenêtre avant l'envoi.

Dernière modification par SANTANNA (Tue 27 May 2014 18:23)

Stych · Wed 28 May 2014 13:14

Salut,

Pas de soucis Santanna

Non, pour moi la fonction table_union() n'a pas vocation à être ouverte sur Qgis. Elle ne me sert qu'à 'fusionner' toutes mes tables (géométriques) en une seul, puis je crée une vue qui récupère les résultats de ma fonction. De plus cette table cette table possède plusieurs type de géométrie, et ne peut donc pas s'ouvrir dans Qgis comme vous me l'avez indiquez.

J'ai réalisé un st_geometrytype sur mes vues issues par la suite de mon intersections et il me retourne bien qu'un seul type de géométrie.

Ce que je trouve étrange c'est qu'avec la même requête d'intersection, que j'utilise pour réaliser mes vues et table d'intersection, Qgis ne réagis pas de la même façon. Il ouvre les tables mais pas les vues. Elles ont pourtant toutes les deux tous les paramètres nécessaire à l'ouverture d'une couche postgis dans Qgis.
Au regard de vos réponse, je pense que le problème vient peut être de ma fonction...
Mais je continue de chercher.
Merci pour toutes vos précisions

Stych

Dernière modification par Stych (Wed 28 May 2014 13:19)

SANTANNA · Wed 28 May 2014 13:34

Salut,
Tu passes par quelle interface pour ouvrir tes couches dans QGIS? Si c'est par le menu vecteur > Ajouter une couche PG, parfois, dans la liste des tables, QGIs n'arrive pas à identifier quel champ fait office de ID ou de geom et il faut explicitement les lui indiquer. Regarde peut-être par là.

Stych · Wed 28 May 2014 17:14

J'ai finalement compris pourquoi Qgis ne voulait pas ouvrir mes vues.
J'avais oublié de spécifier mon schéma dans le lequel mes tables ce trouvent.

Code:

  FOR tables IN SELECT tablename FROM pg_tables where tablename 
         IN 
        (select tablename from pg_tables where schemaname ='france' and

Avec le FOR je lui dis bien de récupérer mes table dans le bon schéma, mais par la suite,

Code:

return query Execute 'select row_number() over(order by id) as gid ,id,  nom_donnee, the_geom_wgs84  from '||quote_ident (tables.tablename)||';'

le return query m'effectue bêtement la requête avec les tables spécifier dans la boucle FOR mais sans savoir dans quel schémas elles se trouvent.
J'ai donc rajouter 'france.' dans le return query afin que lors de l'import de Qgis, le chemin des tables soient bien renseignés.
Je ne sait pas si c'est réellement la bonne explication, mais c'est en tout cas la seul que j'ai trouvé.

Code:

--drop function table_union();
CREATE OR REPLACE FUNCTION france.table_union () RETURNS table (gid bigint,id varchar, nom_donnee varchar, the_geom geometry)  
language plpgsql
as
$BODY$
DECLARE
 tables record;
BEGIN
  FOR tables IN SELECT tablename FROM pg_tables where tablename 
         IN 
        (select tablename from pg_tables where schemaname ='france' and tablename in (SELECT f_table_name FROM geometry_columns where f_table_name not like '%_t' and f_table_name not like 'france_littoral' and f_table_name not like 'box%' and f_table_name not like 'rast%'))
  LOOP
  return query Execute 'select row_number() over(order by id) as gid ,id,  nom_donnee, the_geom_wgs84  from france.'||quote_ident (tables.tablename)||';' 
    RETURN ;
  END LOOP;
END
$BODY$;

Merci pour votre aide en tout cas

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 Thu 22 May 2014 22:52

intersect n couches

#2 Fri 23 May 2014 09:43

Re: intersect n couches

Code:

#3 Fri 23 May 2014 10:03

Re: intersect n couches

#4 Fri 23 May 2014 10:26

Re: intersect n couches

#5 Fri 23 May 2014 10:37

Re: intersect n couches

#6 Fri 23 May 2014 11:27

Re: intersect n couches

Code:

Code:

#7 Fri 23 May 2014 16:58

Re: intersect n couches

#8 Fri 23 May 2014 21:33

Re: intersect n couches

#9 Sat 24 May 2014 12:58

Re: intersect n couches

#10 Mon 26 May 2014 13:19

Re: intersect n couches

Code:

#11 Mon 26 May 2014 14:42

Re: intersect n couches

#12 Mon 26 May 2014 15:27

Re: intersect n couches

#13 Tue 27 May 2014 10:50

Re: intersect n couches

Code:

#14 Tue 27 May 2014 15:22

Re: intersect n couches

#15 Tue 27 May 2014 16:42

Re: intersect n couches

#16 Tue 27 May 2014 17:19

Re: intersect n couches

Code:

#17 Tue 27 May 2014 17:55

Re: intersect n couches

#18 Wed 28 May 2014 13:14

Re: intersect n couches

#19 Wed 28 May 2014 13:34

Re: intersect n couches

#20 Wed 28 May 2014 17:14

Re: intersect n couches

Code:

Code:

Code:

Pied de page des forums