Le portail francophone de la géomatique

bruhnild · Thu 04 October 2018 16:38

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.

Nicolas Ribot · Thu 04 October 2018 17:59

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

tumasgiu · Thu 04 October 2018 18:04

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)

bruhnild · Fri 05 October 2018 10:26

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

Nicolas Ribot · Fri 05 October 2018 10:47

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

Nicolas Ribot · Fri 05 October 2018 10:50

Hmm pas lu jusqu'au bout votre requetes

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

bruhnild · Fri 05 October 2018 11:34

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..

bruhnild · Mon 22 October 2018 09:02

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.

tumasgiu · Wed 24 October 2018 14:58

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)

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 Thu 04 October 2018 16:38

Boucle sur toutes les tables d'un schéma

Code:

Code:

#2 Thu 04 October 2018 17:59

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

Code:

#3 Thu 04 October 2018 18:04

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

Code:

#4 Fri 05 October 2018 10:26

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

Code:

Code:

#5 Fri 05 October 2018 10:47

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

#6 Fri 05 October 2018 10:50

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

Code:

#7 Fri 05 October 2018 11:34

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

Code:

#8 Mon 22 October 2018 09:02

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

#9 Wed 24 October 2018 14:58

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

Pied de page des forums