Blog SIG & Territoires

Le list partitioning (partitionnement par liste) dans PostgreSQL/PostGIS est une méthode de partitionnement déclaratif qui permet de diviser une grande table en plusieurs sous-tables, en fonction des valeurs discrètes d’une colonne (par exemple : par région, type, année, etc.).

C’est particulièrement utile en SIG quand on souhaite gérer des couches volumineuses (ex : une table avec des millions d’objets géographiques répartis par pays, ou par type de zone).

Qu’est-ce que le list partitioning ?

C’est une technique qui :

• Crée une table principale (la table parente),
• Et plusieurs partitions (tables filles) qui contiennent chacune les lignes correspondant à une valeur spécifique d’une colonne.

Exemples d’usage en SIG :

• Partitionner une table batiments selon le type (résidentiel, industriel, public…)
• Partitionner une table zones selon la commune ou l’île (Maurice, Rodrigues, etc.)
• Partitionner une table routes selon le type de route (autoroute, chemin, voie verte, etc.)

Avantages

• Requêtes plus rapides si la clause WHERE filtre sur type
• Moins de verrous en écriture
• Maintenance plus facile (on peut archiver ou détacher une partition)
• Peut être combiné avec la gestion du vacuum, indexes locaux, triggers spécifiques par partition…

Limitations

• La colonne de partition doit être bien choisie (valeurs discrètes, pas trop nombreuses)
• Une ligne ne peut aller que dans une seule partition
• Les contraintes et index doivent être définis sur chaque partition (à partir de PostgreSQL 13, certains peuvent être hérités)

Et pour les différents types de géométrie (point,ligne,polygone)?

Dans PostGIS, vous pouvez utiliser les tables partitionnées pour stocker différents types de géométries dans des partitions distinctes. PostgreSQL prend en charge la partitionnement par liste (LIST PARTITIONING), qui est bien adapté pour séparer des entités géographiques en fonction de leur type (point, ligne, polygone).

1. Création de la table principale

La table principale est une table partitionnée basée sur le type de géométrie.

création de la table parente

CREATE TABLE formation.objets_geographiques (

    id SERIAL,

    nom TEXT NOT NULL,

    geom GEOMETRY NOT NULL,

    type_geom TEXT NOT NULL,

    PRIMARY KEY (type_geom, id)  -- Ajout de type_geom dans la clé primaire

) PARTITION BY LIST (type_geom);

Ici, la colonne type_geom sert de clé de partition.

2. Création des partitions pour chaque type de géométrie

On crée des partitions spécifiques pour chaque type de géométrie (POINT, LINESTRING, POLYGON).

création des partitions

CREATE TABLE formation.objets_points

PARTITION OF formation.objets_geographiques

FOR VALUES IN (‘POINT’);

CREATE TABLE formation.objets_lignes

PARTITION OF formation.objets_geographiques

FOR VALUES IN (‘LINESTRING’);

CREATE TABLE formation.objets_polygones

PARTITION OF formation.objets_geographiques

FOR VALUES IN (‘POLYGON’);

3. Ajout d’une contrainte sur le type de géométrie

Pour garantir que chaque partition ne contienne que le bon type de géométrie, on ajoute une contrainte CHECK.

ajout des contraintes

ALTER TABLE formation.objets_points ADD CONSTRAINT chk_geom_point

CHECK (GeometryType(geom) = ‘POINT’);

ALTER TABLE formation.objets_lignes ADD CONSTRAINT chk_geom_linestring

CHECK (GeometryType(geom) = ‘LINESTRING’);

ALTER TABLE formation.objets_polygones ADD CONSTRAINT chk_geom_polygon

CHECK (GeometryType(geom) = ‘POLYGON’);

4. Insertion des données

Les données seront automatiquement redirigées vers la bonne partition selon la valeur de type_geom.

insertion des données

INSERT INTO formation.objets_geographiques (nom, geom, type_geom)

VALUES

    (‘École’, ST_GeomFromText(‘POINT(57.5 -20.2)’, 4326), ‘POINT’),

    (‘Route côtière’, ST_GeomFromText(‘LINESTRING(57.5 -20.2, 57.6 -20.3)’, 4326), ‘LINESTRING’),

    (‘Parc national’, ST_GeomFromText(‘POLYGON((57.5 -20.2, 57.6 -20.3, 57.7 -20.1, 57.5 -20.2))’, 4326), ‘POLYGON’);

5. Création d’index sur les partitions

On peut créer des index GIST ou SP-GiST sur chaque partition pour améliorer les performances des requêtes spatiales.

création des index

CREATE INDEX idx_geom_points ON formation.objets_points USING GIST (geom);

CREATE INDEX idx_geom_lignes ON formation.objets_lignes USING GIST (geom);

CREATE INDEX idx_geom_polygones ON formation.objets_polygones USING GIST (geom);

Avantages du partitionnement dans PostGIS

• Améliore les performances des requêtes spatiales en réduisant le volume de données analysé.
• Facilite la gestion et l’organisation des objets géographiques.
• Permet une maintenance simplifiée (ex: suppression d’une partition spécifique sans impacter les autres).

6. Requête pour récupérer un type spécifique

Si on veut récupérer uniquement les polygones, PostgreSQL interroge directement la bonne partition.

requête de données

SELECT * FROM formation.objets_geographiques WHERE type_geom = ‘POLYGON’;

Cela améliore l’efficacité des requêtes en évitant de scanner des données inutiles.

Conclusion

Le partitionnement par type de géométrie dans PostGIS permet une gestion efficace des données spatiales, tout en optimisant les performances d’interrogation et de stockage.

Si cet article vous a intéressé et que vous pensez qu'il pourrait bénéficier à d'autres personnes, n'hésitez pas à le partager sur vos réseaux sociaux en utilisant les boutons ci-dessous. Votre partage est apprécié !

Atilio Francois