Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Thu 04 October 2018 16:38

bruhnild
Participant actif
Lieu: Lyon
Date d'inscription: 7 Jun 2014
Messages: 130

Boucle sur toutes les tables d'un schéma

Bonjour,

J'ai une couche de points de plusieurs millions d'entités sur la  France entière.
J'ai décidé d'utiliser la fonction \gexec de psql pour diviser mon jeu de données en plusieurs tables correspondant à chaque département.

Code:

SELECT 'DROP TABLE IF EXISTS pt_cuivre.dep' || code_dep || '; CREATE TABLE pt_cuivre.dep'|| code_dep || ' AS SELECT  * FROM cuivre.v_france_extract_dep WHERE code_dep =' || quote_literal(code_dep) || ';' AS requete FROM (SELECT DISTINCT code_dep FROM cuivre.v_france_extract_dep) AS a \gexec

J'aimerais générer des diagrammes de voronoi en reprenant le même principe (c'est à dire une table de voronoi par département) mais en repartant des tables du schéma pt_cuivre.
Ma question est : comment exécuter mon script en lui faisant boucler sur toutes les tables du schéma pt_cuivre?

Code:

...
(SELECT ST_ConcaveHull( ST_Collect(geom),0.70,true ) FROM pt_cuivre.dep% points_dep )
...

Merci d'avance pour vos retour!

Marine.

Hors ligne

 

#2 Thu 04 October 2018 17:59

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Boucle sur toutes les tables d'un schéma

Bonsoir,

Le catalogue information_schema vous permet d'obtenir toutes les infos sur les tables, colonnes, schémas, types, etc. de la base de données.

en construisant une requête à partir de cette table, vous pouvez générer le code SQL pour chaque table de votre schéma:

Code:

select schema_name, table_name
from information_schema.tables
where schema_name = 'monschema';

Nicolas

En ligne

 

#3 Thu 04 October 2018 18:04

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: Boucle sur toutes les tables d'un schéma

Salut,

vous pouvez trouver la liste des tables de votre base dans la relation pg_catalog.pg_tables.

Code:

SELECT format($$
           SELECT ST_ConcaveHull( ST_Collect(geom),0.70,true ) 
           INTO %1$s.%2$s_concavhull 
           FROM %1$s.%2$s 
           $$, schemaname, tablename)
FROM pg_catalog.pg_tables
WHERE schemaname = 'mon_schema';

Dernière modification par tumasgiu (Thu 04 October 2018 18:20)

Hors ligne

 

#4 Fri 05 October 2018 10:26

bruhnild
Participant actif
Lieu: Lyon
Date d'inscription: 7 Jun 2014
Messages: 130

Re: Boucle sur toutes les tables d'un schéma

Bonjour,

Merci à tous les deux pour vos réponses.
J'ai réussi à construire la requête dynamique qui me donne pour chaque table la bonne requête à exécuter. (l'essentiel vient du travail de John Powell  https://gis.stackexchange.com/questions … 256#172256

Code:

CREATE OR REPLACE FUNCTION voronoi_pt_cuivre_dep() RETURNS VARCHAR as $BODY$
BEGIN
SELECT format($$
            -- Calcule l'enveloppe concave pour les points d'entrée
            -- les points sur l'enveloppe concave sont ceux qui ont des polygones non fermés dans le diagramme de Voronoï en sortie.
               points on the concave hull are those that have unbounded polygons in the output Voronoi diagram.  
           WITH conv_hull(geom) AS
            (SELECT ST_ConcaveHull( ST_Collect(geom),0.70,true ) 
           --INTO %1$s.%2$s
               FROM %1$s.%2$s meshpoints), 
           -- Trouve les points d'origine de l'enveloppe concave
           edge_points(points) AS 
               (SELECT mp.geom FROM %1$s.%2$s mp, conv_hull ch 
               WHERE ST_Touches(ch.geom, mp.geom)),
           -- Buffer autour de l'enveloppe (distance arbitraire)    
             buffered_points(geom) AS             
            (SELECT ST_Buffer(geom, 100) as geom FROM conv_hull),
           -- Trouve les points les plus proches du buffer de l'enveloppe la plus proche des points de l'étape 2                     
          closest_points(points) AS     
            (SELECT ST_Closestpoint(ST_Exteriorring(bp.geom), ep.points) as points,ep.points as epoints FROM buffered_points bp, edge_points ep),
          -- Ajouter ces points au jeu de données d'origine        
          combined_points(points) AS     
        (SELECT points FROM closest_points UNION SELECT geom FROM %1$s.%2$s meshpoints),
         -- Calcule les diagrammes de voronoi à partir de ce jeu de données combiné. Tous les points ont maintenant des polygones fermés                     
          voronoi (vor) AS 
       (SELECT ST_Dump(ST_VoronoiPolygons(ST_Collect(points))) as geom FROM combined_points)    
     SELECT 
     (vor).path[1] as id, 
     (vor).geom 
    INTO voronoi.%2$s                                  
     FROM voronoi                              
    $$, schemaname, tablename)
FROM pg_catalog.pg_tables
WHERE schemaname = 'pt_cuivre';               
END
$BODY$ LANGUAGE plpgsql;

Quand j'exécute la fonction

Code:

SELECT voronoi_pt_cuivre_dep()

j'obtiens cette erreur :
                      
ERROR: ERREUR: la requête n'a pas de destination pour les données résultantes HINT: Si vous voulez annuler les résultats d'un SELECT, utilisez PERFORM à la place. CONTEXT: fonction PL/pgsql voronoi_pt_cuivre_dep(), ligne 3 à instruction SQL SQL state: 42601

Hors ligne

 

#5 Fri 05 October 2018 10:47

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Boucle sur toutes les tables d'un schéma

Bonjour,

Comme le dit le message, un SELECT en plpgsql doit etre stocké qq part (SELECT ... INTO), ou renvoyé par la fonction.
Pour exécuter des requetes SQL, utilisez:

EXECUTE format(....);

Nicolas

En ligne

 

#6 Fri 05 October 2018 10:50

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Boucle sur toutes les tables d'un schéma

Hmm pas lu jusqu'au bout votre requetes sad

Il faut sortir la partie INTO de la chaine de caractère, ou créer la table voronoi.%2$s dans la requete SQL:

Code:

create table voronoi.%2$s  as (.... votre grosse requete ...)

Nicolas

En ligne

 

#7 Fri 05 October 2018 11:34

bruhnild
Participant actif
Lieu: Lyon
Date d'inscription: 7 Jun 2014
Messages: 130

Re: Boucle sur toutes les tables d'un schéma

Merci pour votre aide.
J'ai légèrement modifié le code pour boucler sur le code de département de ma vue de départ.

Code:

CREATE OR REPLACE FUNCTION voronoi_pt_cuivre_dep() 
RETURNS VARCHAR AS $BODY$
DECLARE
        lv VARCHAR;
BEGIN
        FOR lv IN SELECT DISTINCT code_dep
               FROM cuivre.v_france_extract_dep
               LOOP
EXECUTE format($$
            -- Calcule l'enveloppe concave pour les points d'entrée
            -- les points sur l'enveloppe concave sont ceux qui ont des polygones non fermés dans le diagramme de Voronoï en sortie.
           DROP TABLE IF EXISTS voronoi.%2$s;
           CREATE TABLE voronoi.%2$s AS
        (SELECT a.id, a.geom  
         FROM
           (WITH conv_hull(geom) AS
            (SELECT ST_ConcaveHull( ST_Collect(geom),0.70,true ) 
           --INTO %1$s.%2$s
               FROM %1$s.%2$s meshpoints), 
           -- Trouve les points d'origine de l'enveloppe concave
           edge_points(points) AS 
               (SELECT mp.geom FROM %1$s.%2$s mp, conv_hull ch 
               WHERE ST_Touches(ch.geom, mp.geom)),
           -- Buffer autour de l'enveloppe (distance arbitraire)    
             buffered_points(geom) AS             
            (SELECT ST_Buffer(geom, 100) as geom FROM conv_hull),
           -- Trouve les points les plus proches du buffer de l'enveloppe la plus proche des points de l'étape 2                     
          closest_points(points) AS     
            (SELECT ST_Closestpoint(ST_Exteriorring(bp.geom), ep.points) as points,ep.points as epoints FROM buffered_points bp, edge_points ep),
          -- Ajouter ces points au jeu de données d'origine        
          combined_points(points) AS     
        (SELECT points FROM closest_points UNION SELECT geom FROM %1$s.%2$s meshpoints),
         -- Calcule les diagrammes de voronoi à partir de ce jeu de données combiné. Tous les points ont maintenant des polygones fermés                     
          voronoi (vor) AS 
       (SELECT ST_Dump(ST_VoronoiPolygons(ST_Collect(points))) as geom FROM combined_points)    
     SELECT 
     (vor).path[1] as id, 
     (vor).geom 
 --   INTO voronoi.%2$s                                  
     FROM voronoi)a)                              
    $$, schemaname, tablename)
FROM pg_catalog.pg_tables
WHERE schemaname = 'pt_cuivre'; 
END LOOP;                                
END
$BODY$ LANGUAGE plpgsql;

Là postgres me dit que la requête a renvoyé plus d'une ligne..

Hors ligne

 

#8 Mon 22 October 2018 09:02

bruhnild
Participant actif
Lieu: Lyon
Date d'inscription: 7 Jun 2014
Messages: 130

Re: Boucle sur toutes les tables d'un schéma

Bonjour,
je me permet de relancer ce post car je n'ai toujours pas débloqué mon problème..Peut etre avez vous d'autres idées?

Marine.

Hors ligne

 

#9 Wed 24 October 2018 14:58

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: Boucle sur toutes les tables d'un schéma

J'ai corrigé ce qui ma semblé être des erreurs,
et indenter correctement.

Notez que vous ne vous servez à aucun moment de la variable lv.

CREATE OR REPLACE PROCEDURE voronoi_pt_cuivre_dep()
RETURNS VARCHAR AS $BODY$
DECLARE
        lv VARCHAR;
        r RECORD;
BEGIN
        FOR lv IN SELECT DISTINCT code_dep
               FROM cuivre.v_france_extract_dep
        LOOP
            FOR r IN SELECT schemaname, tablename
                    FROM pg_catalog.pg_tables
                    WHERE schemaname = 'pt_cuivre'
            LOOP
                --TO DO utiliser lv
                EXECUTE $$
                            -- Calcule l'enveloppe concave pour les points d'entrée
                            -- les points sur l'enveloppe concave sont ceux qui ont des polygones non fermés dans le diagramme de Voronoï en sortie.
                            DROP TABLE IF EXISTS voronoi.$2;
                            CREATE TABLE voronoi.$2 AS
                            SELECT a.id, a.geom 
                            FROM
                               (WITH conv_hull(geom) AS
                                    (SELECT ST_ConcaveHull( ST_Collect(geom),0.70,true )
                                    --INTO $1.$2
                                    FROM $1.$2 meshpoints),
                               -- Trouve les points d'origine de l'enveloppe concave
                                edge_points(points) AS
                                    (SELECT mp.geom FROM $1.$2 mp, conv_hull ch
                                    WHERE ST_Touches(ch.geom, mp.geom)),
                               -- Buffer autour de l'enveloppe (distance arbitraire)   
                                buffered_points(geom) AS             
                                    (SELECT ST_Buffer(geom, 100) as geom FROM conv_hull),
                               -- Trouve les points les plus proches du buffer de l'enveloppe la plus proche des points de l'étape 2                     
                                closest_points(points) AS     
                                    (SELECT ST_Closestpoint(ST_Exteriorring(bp.geom), ep.points) as points,ep.points as epoints FROM buffered_points bp, edge_points ep),
                              -- Ajouter ces points au jeu de données d'origine       
                                combined_points(points) AS     
                                    (SELECT points FROM closest_points UNION SELECT geom FROM $1.$2 meshpoints),
                             -- Calcule les diagrammes de voronoi à partir de ce jeu de données combiné. Tous les points ont maintenant des polygones fermés                     
                                voronoi (vor) AS
                                    (SELECT ST_Dump(ST_VoronoiPolygons(ST_Collect(points))) as geom FROM combined_points)
                                 SELECT
                                 (vor).path[1] as id,
                                 (vor).geom
                                 FROM voronoi) a                 
                        $$ USING r.schemaname, r.tablename, lv;
            END LOOP;
        END LOOP;                               
END
$BODY$ LANGUAGE plpgsql;

Dernière modification par tumasgiu (Wed 24 October 2018 15:36)

Hors ligne

 

Pied de page des forums

Powered by FluxBB