Gestion et manipulation de grandes tables

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Gestion et manipulation de grandes tables

Johanna B.
Bonjour à tous,

Voici mon problème : je travaille sur des matrices origine-destination de très grande taille que j'ai pu calculé avec la fonction ST_Shortestpathlenght, mais j'ai de gros problèmes pour manipuler la table obtenue en sortie à cause de sa taille je pense.

J'ai pu créer une table d'identifiants source-destination contenant un peu plus de 3 700 000 lignes, et j'ai pu calculer les plus courts chemins avec ST_Shortestpathlenght qui me renvoie une table d'à peu près la même taille.
Mais j'ai besoins de rajouter des attributs à cette table de plus courts chemins pour pouvoir la filtrer. Or une requête de la forme :

CREATE TABLE od_matrix_attributes AS
SELECT od_matrix.*, TabCorres.cid AS dest_cid, TabCorres.index_CC AS dest_CC
FROM od_matrix
INNER JOIN TabCorres ON od_matrix.destination = TabCorres.id;

me renvoie une table où près 200 000 lignes ont été perdu dans la jointure, alors que TabCorres m'a permis de créer la table source-destination, donc tous les id de od_matrix sont présents dans TabCorres.

De plus, une requête pour supprimer une colonne dans une table de cette taille ne tient pas en mémoire.

J'aurais donc aimé avoir vos avis sur le problème, et j'aurais aimé savoir si il est possible d'ajouter/de travailler avec des index sur les champs d'une table pour accélérer certaines requêtes comme dans une base PostgreSQL.


Reply | Threaded
Open this post in threaded view
|

Re: Gestion et manipulation de grandes tables

Gwendall Petit
Administrator
Bonjour Johanna et bienvenue sur cette liste de diffusion.

Il est vrai que le volume de donnée que tu traites commence à être intéressant

Une remarque concernant ta requête. Ici tu fais une jointure dite alphanumérique, c'est à dire entre deux champs identiques. Pour faire cela, tu pourrais écrire :
CREATE TABLE od_matrix_attributes AS
SELECT a.*, b.cid AS dest_cid, b.index_CC AS dest_CC
FROM od_matrix AS a, TabCorres AS b where a.destination = b.id;
Outre le fait que j'utilise des alias (a et b) pour renommer les tables (mais ça c'est du bonus), je fait la jointure à l'aide du terme WHERE a.mon_champ=b.mon_champ

Essayes et dit nous si cela résout ton problème.

Pour la suppression de la colonne, c'est étonnant. Ceci dit j'ai rarement testé cette fonctionnalité sur une telle table. Je vais essayer de mon côté. En attendant, deux options :
- manuellement avec les outils d'éditions de la table (clic-droit : start edition ou quelque chose comme ça),
- tu fais une nouvelle couche en ne sélectionnant que les champs qui t'intéressent (oui je sais ce n'est pas propre ).

Pour ce qui est des index, c'est une très bonne remarque. La version V4.0 snapshot sur laquelle tu travailles (tu ne l'as pas précisé, mais je pense que c'est ça) dispose des index alphanumérique, mais pas des spatiaux (ils ne sont pas bien pris en compte lors des requêtes). C'est un élément important à nos yeux et sache qu'un gros travail est actuellement en cours pour fournir un terme un langage qui sera capable de bien exploiter tout cela. Les choses avancent vite, j'ai bon espoir pour le printemps prochain.

En espérant que ça te débloque sur certains points.

Cordialement,

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: Gestion et manipulation de grandes tables

ebocher
Administrator
Bonjour Johanna,

Adam et Nicolas travaillent activement sur le nouveau système de gestion des données dans OrbisGIS. Nous utilisons la base de données H2 (http://www.h2database.com/html/main.html). Nicolas a recensement ajouté un cartouche spatial equivalent à POSTGIS (https://github.com/irstv/H2GIS). Adam va transposer les fonctions de graphes pour H2. H2 étant une véritable base de données et optimisé tu n'auras plus (espérons) de pb de performance.

Erwan

@Adam
Is it ok for you



Le 14 novembre 2013 14:05, Gwendall Petit [via OrbisGIS] <[hidden email]> a écrit :
Bonjour Johanna et bienvenue sur cette liste de diffusion.

Il est vrai que le volume de donnée que tu traites commence à être intéressant

Une remarque concernant ta requête. Ici tu fais une jointure dite alphanumérique, c'est à dire entre deux champs identiques. Pour faire cela, tu pourrais écrire :
CREATE TABLE od_matrix_attributes AS
SELECT a.*, b.cid AS dest_cid, b.index_CC AS dest_CC
FROM od_matrix AS a, TabCorres AS b where a.destination = b.id;
Outre le fait que j'utilise des alias (a et b) pour renommer les tables (mais ça c'est du bonus), je fait la jointure à l'aide du terme WHERE a.mon_champ=b.mon_champ

Essayes et dit nous si cela résout ton problème.

Pour la suppression de la colonne, c'est étonnant. Ceci dit j'ai rarement testé cette fonctionnalité sur une telle table. Je vais essayer de mon côté. En attendant, deux options :
- manuellement avec les outils d'éditions de la table (clic-droit : start edition ou quelque chose comme ça),
- tu fais une nouvelle couche en ne sélectionnant que les champs qui t'intéressent (oui je sais ce n'est pas propre ).

Pour ce qui est des index, c'est une très bonne remarque. La version V4.0 snapshot sur laquelle tu travailles (tu ne l'as pas précisé, mais je pense que c'est ça) dispose des index alphanumérique, mais pas des spatiaux (ils ne sont pas bien pris en compte lors des requêtes). C'est un élément important à nos yeux et sache qu'un gros travail est actuellement en cours pour fournir un terme un langage qui sera capable de bien exploiter tout cela. Les choses avancent vite, j'ai bon espoir pour le printemps prochain.

En espérant que ça te débloque sur certains points.

Cordialement,

Gwendall


If you reply to this email, your message will be added to the discussion below:
http://orbisgis.3871844.n2.nabble.com/Gestion-et-manipulation-de-grandes-tables-tp7575036p7575037.html
To start a new topic under OrbisGIS Users, email [hidden email]
To unsubscribe from OrbisGIS Users, click here.
NAML



--
Institut de recherche en sciences et techniques de la ville
École Centrale de Nantes
BP 92101
1 rue de la Noë, 44321 NANTES Cedex 3
France
Tél : 02 40 37 68 67
http://www.irstv.fr/
Reply | Threaded
Open this post in threaded view
|

Re: Gestion et manipulation de grandes tables

Johanna B.
Re-Bonjour à tous, et merci pour vos réponses.

Etant habituée à travailler avec PostgreSQL/PostGIS, il serait effectivement très intéressant pour moi de pouvoir retrouver dans OrbisGIS le maximum de fonctions venant de ces gestionnaires de base de données.

Je travaille bien sur la version V4.0 snapshot, et j'ai effectivement besoins de travailler sur de très grosses tables puisque je calcule des plus courts chemins (tant que ça tient en mémoire!) ayant pour origines et destinations des mailles de grilles régulières. L'optimisation de ces requêtes est super sous OrbiGIS et jusque là je n'avais pas trouvé d'outil qui me permettait de faire autant de calculs d'un coup et directement sur mon réseau routier déjà bien volumineux.

J'avais déjà testé une requête de la forme :

CREATE TABLE od_matrix_attributes AS
SELECT a.*, b.cid AS dest_cid, b.index_CC AS dest_CC
FROM od_matrix AS a, TabCorres AS b where a.destination = b.id;

et j'obtiens strictement le même résultat. Je précise en plus que j'ai fini par exporter mes tables od_matrix et TabCorres dans ma base PostGIS et que la même requête me retourne bien une table od_matrix_attributes avec le même nombre de ligne que od_matrix.

Concernant la suppression de colonne, c'est vrai que je n'avais même pas pensé à utiliser les outils d'éditions mais malheureusement le résultat est pire : ça fait entièrement planter OrbiGIS.  Donc effectivement mon recours est de créer une nouvelle table avec uniquement les champs qui m'intéresse.

Johanna
Reply | Threaded
Open this post in threaded view
|

Re: Gestion et manipulation de grandes tables

agouge
Bonjour Johanna,

Quant aux lignes perdues dans la jointure, je ne peux rien apporter à la discussion de plus que les autres. Le problème sera sûrement réglé avec H2. Nous travaillons pour finir le développent le plus vite possible. Si tu as trouvé une solution en attendant, c'est déjà bien.

Comme l'a dit Erwan, tu pourras suivre en direct l'implémentation des fonctions SQL dans H2 ici : https://github.com/irstv/h2gis

Par ailleurs, si tu souhaites partager tes scripts pour montrer des cas d'usage, on a créé un endroit pour le faire : https://github.com/irstv/orbisgis-scripts. N'hésite pas à demander des renseignements si tu n'as pas l'habitude de travailler avec git.

Cordialement,
Adam


2013/11/14 Johanna B. [via OrbisGIS] <[hidden email]>
Re-Bonjour à tous, et merci pour vos réponses.

Etant habituée à travailler avec PostgreSQL/PostGIS, il serait effectivement très intéressant pour moi de pouvoir retrouver dans OrbisGIS le maximum de fonctions venant de ces gestionnaires de base de données.

Je travaille bien sur la version V4.0 snapshot, et j'ai effectivement besoins de travailler sur de très grosses tables puisque je calcule des plus courts chemins (tant que ça tient en mémoire!) ayant pour origines et destinations des mailles de grilles régulières. L'optimisation de ces requêtes est super sous OrbiGIS et jusque là je n'avais pas trouvé d'outil qui me permettait de faire autant de calculs d'un coup et directement sur mon réseau routier déjà bien volumineux.

J'avais déjà testé une requête de la forme :

CREATE TABLE od_matrix_attributes AS
SELECT a.*, b.cid AS dest_cid, b.index_CC AS dest_CC
FROM od_matrix AS a, TabCorres AS b where a.destination = b.id;

et j'obtiens strictement le même résultat. Je précise en plus que j'ai fini par exporter mes tables od_matrix et TabCorres dans ma base PostGIS et que la même requête me retourne bien une table od_matrix_attributes avec le même nombre de ligne que od_matrix.

Concernant la suppression de colonne, c'est vrai que je n'avais même pas pensé à utiliser les outils d'éditions mais malheureusement le résultat est pire : ça fait entièrement planter OrbiGIS.  Donc effectivement mon recours est de créer une nouvelle table avec uniquement les champs qui m'intéresse.

Johanna


If you reply to this email, your message will be added to the discussion below:
http://orbisgis.3871844.n2.nabble.com/Gestion-et-manipulation-de-grandes-tables-tp7575036p7575039.html
To start a new topic under OrbisGIS Users, email [hidden email]
To unsubscribe from OrbisGIS, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

Re: Gestion et manipulation de grandes tables

Gwendall Petit
Administrator
Bonjour Johanna,

Pour tenter d'isoler ton problème, as-tu testé de faire ta jointure via un LEFT JOIN ?
CREATE TABLE od_matrix_attributes AS
SELECT od_matrix.*, TabCorres.cid AS dest_cid, TabCorres.index_CC AS dest_CC
FROM od_matrix LEFT OUTER JOIN TabCorres ON od_matrix.destination = TabCorres.id;
Cordialement,

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: Gestion et manipulation de grandes tables

Johanna B.
Bonjour,

Je n'avais pas tenté un LEFT JOIN et effectivement ça marche. 
Par contre la requête est très lente et a mis plus de 30 minutes à me retourner la nouvelle table.

Pour ce qui est de mes scripts, je n'ai jamais utilisé github mais je les partagerai très prochainement.

Cordialement,
Johanna


De: "Gwendall Petit [via OrbisGIS]" <[hidden email]>
À: "Johanna B." <[hidden email]>
Envoyé: Lundi 18 Novembre 2013 10:18:28
Objet: Re: Gestion et manipulation de grandes tables

Bonjour Johanna,

Pour tenter d'isoler ton problème, as-tu testé de faire ta jointure via un LEFT JOIN ?
CREATE TABLE od_matrix_attributes AS
SELECT od_matrix.*, TabCorres.cid AS dest_cid, TabCorres.index_CC AS dest_CC
FROM od_matrix LEFT OUTER JOIN TabCorres ON od_matrix.destination = TabCorres.id;
Cordialement,

Gwendall



If you reply to this email, your message will be added to the discussion below:
http://orbisgis.3871844.n2.nabble.com/Gestion-et-manipulation-de-grandes-tables-tp7575036p7575041.html
To unsubscribe from Gestion et manipulation de grandes tables, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

Re: Gestion et manipulation de grandes tables

Gwendall Petit
Administrator
Ok merci pour le retour.
C'est un bon point à noter. Pour la lenteur je présume (plus que fortement) que l’absence d'index y est pour quelque chose.
J'attends comme toi avec impatience l'arrivée d'H2

Cordialement,

Gwendall