(PostGIS) Modelisation relationnelle d'un LINESTRING

[fanfouer]

Bonjour,

J'étudie actuellement les capacités d'un moteur comme postGIS pour tenter de mieux répondre à mes besoins GIS que ce que le fait actuellement MySQL et un peu de code maison en couche 2.

Mon système actuel modélise 2 types d'objets : les points et les chemins (qui peuvent être fermés pour former un polygone).
Mon but est de trouver les correspondances exactes dans postGIS.

Mes points présentent actuellement deux colonnes lat/lon qu'il est facile de rassembler en une seule colonne de type POINT, ca n'est pas un problème.
Par contre pour les chemins, j'utilise 3 tables : Une table qui représente le chemin (son numéro et quelques propriétés propres à tout le chemin), la table des points et une 3ième table pour établir une liaison entre le chemin et les points qui le composent.

Ca permet de pouvoir documenter précisément chaque point qui compose le chemin, à la manière de ce que fait Openstreetmap par exemple : il est possible de taguer les Nodes d'une Way.

La documentation du type LINESTRING de PostGIS montre visiblement que toutes les coordonnées sont stockées dans la colonne même.
Je chercherai donc à produire un LINESTRING avec une suite d'identifiants d'enregistrements (qui comportent une colonne POINT) plutôt que de stocker uniquement les coordonnées des points.

Est-ce possible?

Merci par avance pour vos réponses.

[SQLpro]

À partir d'une géométrie vous pouvez extraire ce que vous voulez. Par exemple pour un LINSTRING, vous pouvez extraire le point de position N.

Le but d'une base de données étant de ne pas avoir de redondance, soit vous partez de vos nuages de point et des liens sémantiques entre eux pour constituer une LINESTRING par requête, soit vous partez justement de la LINESTRING pour constituer vos nuages de points.

Tout dépend du traitement des données qui devra être fait dans vos applications

A +

[fanfouer]

Bonsoir,

Envoyé par SQLpro

À partir d'une géométrie vous pouvez extraire ce que vous voulez. Par exemple pour un LINSTRING, vous pouvez extraire le point de position N.

Oui en effet j'ai vu cet aspect des choses.
Le but est cependant de pouvoir lier des données de structure variables (-> jointures) à chaque point mais aussi au chemin complet.
L'opération ici ne donnera qu'un objet POINT manipulable par les fonctions spatiales mais il va tout de même, je crois, manquer une clé de jointure.

Le but d'une base de données étant de ne pas avoir de redondance, soit vous partez de vos nuages de point et des liens sémantiques entre eux pour constituer une LINESTRING par requête, soit vous partez justement de la LINESTRING pour constituer vos nuages de points.

Tout dépend du traitement des données qui devra être fait dans vos applications

A +

La deuxième approche est plus interessante.
Mon modèle actuel (nuage de point + jointure pour constituer un objet chemin qui m'est propre) se suffit à lui-même. Aussi je pensais passer à PostGIS pour bénéficier des traitements spatiaux pour alléger ma couche 2 sans pourtant remettre en cause mes objets.

Si la constitution d'une LINESTRING par requête au besoin n'est pas trop lourd pour pouvoir faire des opérations (calcul ou sélection) dessus ensuite, c'est ce qui me conviendrait le mieux.

Auriez-vous une idée de la requête à mettre en oeuvre?

Merci et bonne soirée.

[SQLpro]

Envoyé par fanfouer

Auriez-vous une idée de la requête à mettre en oeuvre?

Sans un exemple plus concret, difficilement !

PS : vous pouvez venir à mon cours sur le sujet :
http://www.orsys.fr/formation-donnees-spatiales.asp

Prochaine session le 24 septembre....

A +

[fanfouer]

Envoyé par SQLpro

Sans un exemple plus concret, difficilement !

Oui je comprends, c'est stupide de ma part.

Voici donc un peu de SQL (des tables MySQL pour l'instant, j'ai adapté le type de la colonne point pour donner une idée de ce à quoi ca pourrait ressembler sous postgre)
Je ne donne que les tables principales. S'y ajoutent pour les points et les chemins 2 tables pour y faire correspondre des paires clés/valeur (des "tags") qui ne concernent pas le périmètre de cette étude.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
# La table contenant les points
CREATE TABLE `points` (
  `record_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `obj_id` int(11) UNSIGNED NOT NULL,
  `version` smallint(5) UNSIGNED NOT NULL DEFAULT '1',
  `model_id` varchar(250) DEFAULT NULL COMMENT,
  `point` POINT
  PRIMARY KEY (`record_id`),
  KEY `obj_index` (`obj_id`),
  KEY `model` (`model_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
 
# La table représentant un enregistrement de chemin (rien de géométrique là-dedans).
CREATE TABLE `chemins` (
  `record_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `obj_id` int(11) UNSIGNED NOT NULL,
  `version` smallint(5) UNSIGNED NOT NULL DEFAULT '1',
  `model_id` varchar(250) DEFAULT NULL COMMENT,
  PRIMARY KEY (`record_id`),
  KEY `obj_index` (`obj_id`),
  KEY `model` (`model_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
 
# La table qui constitue un noeud membre d'un chemin.
CREATE TABLE `chemins_contenu` (
  `content_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `classe` text NOT NULL,
  `obj_id` int(10) UNSIGNED NOT NULL,
  `position` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`content_id`),
  UNIQUE KEY `unicity` (`obj_id`,`classe`(100),`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
 
# Enfin la table qui fait le lien entre les noeuds membres et les chemins
CREATE TABLE `chemins_contenuCollection` (
  `record_id` int(11) UNSIGNED NOT NULL,
  `content_id` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`record_id`,`content_id`),
  KEY `content_id` (`content_id`),
  CONSTRAINT `ibfk_1` FOREIGN KEY (`record_id`) REFERENCES `chemins` (`record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ibfk_2` FOREIGN KEY (`content_id`) REFERENCES `chemins_contenu` (`content_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Cela peut paraitre bien alambiqué mais voici quelques précisions sur mon fonctionnement.
Mon ORM gère lui-même les versions successives d'un même objet dans la base.
Ainsi, les chemins et les points disposent de 3 champs essentiels :
- record_id : Le numéro d'enregistrement unique dans la table
- obj_id : Le numéro d'objet logique (toutes les versions d'un même objet disposent du même numéro d'objet).
- version : Le numéro de version.

La table chemins_contenu présente des noeuds qui ciblent eux-même les points d'une manière particulière.
Le doublet (classe, obj_id) permet de cibler la bonne table de points (il y en a plusieurs). Obj_id est utilisé au lieu de la clé primaire puisqu'un changement de version d'un point obligerait à la restauration de toutes les liaisons aux 4 coins de la base en cas de modification du seul point. Pour info, deux versions ne peuvent pas co-exister à un même instant temporel et d'autres champs qui n'ont pas été indiqués ici permettent de faire le distinguo en fonction de la date. C'est de toute façon intégré au ON de la jointure entre points et chemins_contenu (ON obj_id=... AND time < %TIME%).

Un même noeud peut être présent dans plusieurs chemins à la même position (rare mais ça existe). On relie donc simplement chemins et nœuds avec la table chemins_contenuCollection.


Je n'ai pas de jeu de test à fournir car tout ceci est encore vide malheureusement.

Est-il donc possible en 2 jointures d'obtenir un LINESTRING par requêtes de telle sorte à pouvoir effectuer des opérations géographiques/géométriques (j'hésite encore mais peu importe) dessus?

PS : vous pouvez venir à mon cours sur le sujet :
http://www.orsys.fr/formation-donnees-spatiales.asp

Prochaine session le 24 septembre....

Cela aurait été très volontiers, mais je ne fais ça que sur mon temps libre et je ne suis pas dispo à cette date.

[SQLpro]

Oulala, vous me donnez une table en MySQL avec toutes les merdes possible de MySQL qui n'a rien d'un SGBDR et il faudrait que je me plonge dans le nettoyage pendant une heure !!!!

Commencez donc par travailler sous l'outil adéquate et fournir un jeu PG ou au moins en SQL pur !

A +

[fanfouer]

Bonjour,

Oui mes tables viennent de MySQL et je souhaite justement m'en séparer.

J'ai pas forcément eu le temps de tout mettre en place, mais voici ce que donne mon schéma sous postgre 9.1, postGIS 1.5.1 (mais migration vers 2.0 dès que possible sur ma prod) et pgadmin III :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
# Table chemins
CREATE TABLE chemins (
  record_id serial NOT NULL,
  obj_id integer,
  version smallint,
  model_id text,
  CONSTRAINT chemins_pkey PRIMARY KEY (record_id )
) WITH (
  OIDS=FALSE
);
CREATE INDEX chemins_model_id_idx ON chemins USING btree (model_id COLLATE pg_catalog."default" );
CREATE INDEX chemins_obj_id ON chemins USING btree (obj_id);
 
# Table chemins_contenu
CREATE TABLE chemins_contenu (
  content_id serial NOT NULL,
  classe text,
  obj_id integer,
  "position" integer,
  CONSTRAINT chemins_contenu_pkey PRIMARY KEY (content_id )
) WITH (
  OIDS=FALSE
);
CREATE UNIQUE INDEX chemins_contenu_classe_obj_id_position_idx ON chemins_contenu USING btree (classe COLLATE pg_catalog."default" , obj_id , "position" );
 
# Table chemins_contenuCollection
CREATE TABLE "chemins_contenuCollection" (
  record_id integer NOT NULL,
  content_id integer NOT NULL,
  CONSTRAINT "chemins_contenuCollection_pkey" PRIMARY KEY (record_id , content_id ),
  CONSTRAINT ibfk_1 FOREIGN KEY (record_id)
      REFERENCES chemins (record_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT ibfk_2 FOREIGN KEY (content_id)
      REFERENCES chemins_contenu (content_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE
) WITH (
  OIDS=FALSE
);
CREATE INDEX "chemins_contenuCollection_content_id_idx" ON "chemins_contenuCollection" USING btree (content_id );
CREATE INDEX fki_ibfk_1 ON "chemins_contenuCollection" USING btree (record_id );
CREATE INDEX fki_ibfk_2 ON "chemins_contenuCollection" USING btree (content_id );
 
# Table points
CREATE TABLE points (
  record_id serial NOT NULL,
  obj_id integer NOT NULL,
  version smallint NOT NULL,
  model_id text,
  point point,
  CONSTRAINT points_pkey PRIMARY KEY (record_id )
) WITH (
  OIDS=FALSE
);
CREATE INDEX id_model_id ON points USING btree (model_id COLLATE pg_catalog."default" );
CREATE INDEX id_obj_id ON points USING btree (obj_id );

Je pense que c'est tout bon, je peux ajouter des données dans ce schéma et ça m'a l'air cohérent.
Je reste par là pour tout autre problème.

A+

[fanfouer]

Bonjour,

Je me permets de relancer ce topic.

Moi même n'ayant pas plus progressé sur cette question, peut-être que d'autres auront une réponse à m'apporter suivant le schéma fourni dans le message précédent.

Merci par avance.