Discussion :

[dhaulagiri]

Bonjour,

Je cherche des conseils pour gérer au mieux la problématique suivante avec une table PostGIS.

Une requête SELECT crée des secteurs géographiques (polygones) à partir de plusieurs autres tables de référence telles que des zones administratives (multipolygones) et des limites (lignes) servant à découper les zones en fonction de problématiques de gestion. Un code unique de type entier est attribué à chaque secteur en fonction d'un modèle à 10 chiffres, imposé par l'application dans laquelle les secteurs seront chargés ensuite: les deux premiers décrivent la nature du secteur, les 5 suivants correspondent à sa zone administrative et les 3 derniers à un numéro d'ordre dans la zone administrative permettant de garantir l'unicité du code du secteur. Ce numéro d'ordre est attribué en fonction de la position géographique des secteurs dans leur zone administrative: du nord-ouest au sud-est, en suivant le sens "naturel" de lecture de la plupart des languages.

Les tables de référence évoluent régulièrement, leur géométrie comme leurs autres attributs: modification d'un périmètre administratif, changement de nom, création d'une nouvelle limite, etc. La table de secteurs géographiques doit donc être actualisée en conséquence. En exécutant à nouveau la requête, on obtient un nouveau jeu de données à jour mais dans lequel un secteur aura pu changer de numéro: par exemple, si un secteur initial est découpé par une nouvelle limite, alors de nouveaux secteurs seront créés et tous les secteurs qui suivent dans l'ordre de numérotation auront un nouveau numéro (décalage).

L'enjeu consiste donc à trouver un identifiant pérenne permettant de mettre à jour les secteurs modifiés et de garantir l'intégrité référentielle avec les autres tables. Dans ce cas, je ne sais pas comment faire. J'ai pensé à une empreinte numérique mais je ne sais pas si c'est une bonne idée et avec quelles colonnes la calculer.

Pourriez-vous m'aider? Merci d'avance

[SQLpro]

Utilisez deux identifiants. Un identifant informatique type INT IDENTITY en PRIMARY KEY et l'autre sur le code à 10 caractères...

INT = 4 octets pour 2 milliards de valeur
Code 10 chiffres = 10 octets+ plus un extraoverhead du fait de la gestion de la collation + mise à jour possible de l'info... = Contraintes UNIQUE...

A +

[escartefigue]

Une clef primaire doit être :

• unique : chaque valeur n'apparait qu'une seule fois ;
• non "nullable" : chaque valeur est obligatoire ;
• irreductible : une sous partie de la clef ne peut suffire à déterminer de façon certaine la ligne dans la table ;
• stable : son contenu ne doit jamais changer ;
• concis : l'encombrement doit être le plus faible possible.

Les deux derniers points ne sont pas obligatoires, mais très fortement recommandés :

Stabilité : à cause de l'intégrité référentielle qui propage les valeurs de PK dans les tables liées sous forme de FK, en cas de modification, le phénomène de cascade pourrait impacter des millions de lignes !

Concision : se justifie par l'utilisation très fréquente des PK dans les requêtes, en particulier pour les jointures. Une colonne de type integer (small, int ou bigint) sera toujours beaucoup plus concise à nombre de valeurs égal qu'une colonne de tout autre type. De plus, les types numériques sont insensibles à la collation. Autant d'arguments en faveur d'une charge réseau et d'une charge CPU réduite, au bénéfice des performances.
Ce point milite en défaveur des GUID utilisés comme PK.

C'est pourquoi on fuit les identifiants "naturels", le plus souvent encombrants et instables, comme choix de clef primaire.
Un identifiant technique artificiel attribué par le SGBD est ce qui est le mieux comme PK, l'identifiant naturel pourra si besoin être doté d'une contrainte unique, mais ne sera pas PK

[dhaulagiri]

Merci. Voici plus précisément la structure de ma table:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE IF NOT EXISTS schema.ma_table
(
    id integer NOT NULL DEFAULT nextval('schema.ma_table_id_seq'::regclass),
    esz text COLLATE pg_catalog."default",
    code_du_carreau_1000x1000 text COLLATE pg_catalog."default",
    code_de_la_commune text COLLATE pg_catalog."default",
    code_du_departement text COLLATE pg_catalog."default",
    etat_de_l_objet text COLLATE pg_catalog."default",
    nom text COLLATE pg_catalog."default",
    has_convention boolean,
    geom_2154 geometry(Polygon,2154),
    CONSTRAINT ma_table_pkey PRIMARY KEY (id),
    CONSTRAINT ma_table_esz_key UNIQUE (esz)
)

J'avais déjà défini un id de type serial comme clé primaire. La colonne esz correspond au code unique que j'évoque (contrainte unique).

Ce que je n'ai pas compris dans la réponse est s'il faut ajouter une contrainte sur les deux colonnes. De plus, quand j'insère les objets potentiellement mis à jour à partir de la requête, est-ce que je dois préciser que la clause ON CONFLICT est basé sur les deux colonnes id et esz?

Merci!

[escartefigue]

Dans la mesure où la PK est de type serial, il n'y aura jamais de ON CONFLICT applicable sur cette colonne, c'est forcément sur la colonne unique non PK que pourra porter le viol de contrainte.
Oui, il faut bien mettre les deux contraintes : PK d'une part et UNIQUE d'autre part.

[SQLpro]

De plus l'autoincrémentation avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

id integer NOT NULL DEFAULT nextval('schema.ma_table_id_seq'::regclass),

c'est lourd et inutile
préférez le IDENTITY plus léger !

A +

[dhaulagiri]

Dans la mesure où la PK est de type serial, il n'y aura jamais de ON CONFLICT applicable sur cette colonne, c'est forcément sur la colonne unique non PK que pourra porter le viol de contrainte.
Oui, il faut bien mettre les deux contraintes : PK d'une part et UNIQUE d'autre part.

En effet. Du coup, je ne vois pas comment je peux m'en sortir à partir d'un jeu de données entièrement actualisé :
- il n'y a pas d'id puisque je ne fais pas de SELECT dans la table existante, je la reconstruis à partir d'autres tables
- je dispose uniquement de la colonne unique non PK mais un numéro affecté à un objet dans la table originale peut être affecté à un autre dans la table révisée (tout en demeurant unique au global)

En passant, il n'y a pas d'IDENTITY dans PostgreSQL à ma connaissance.