Important

La traduction est le fruit d’un effort communautaire auquel vous pouvez vous joindre. Cette page est actuellement traduite à 98.21%.

16.5. Leçon : Construction de géométrie

Dans cette section nous allons creuser un peu plus profondément comment les géométries simples sont construites en SQL. En réalité, vous utiliserez probablement un SIG comme QGIS pour créer des géométries complexes en utilisant leurs outils de numérisation ; cependant, comprendre comment ils sont formulés peut être pratique pour écrire des requêtes et comprendre comment la base de données est assemblée.

Objectif de cette leçon : Mieux comprendre comment créer des entités spatiales directement dans PostgreSQL/PostGIS.

16.5.1. Création de polylignes

Retournons à notre base de données address et faisons en sorte que la table des rues corresponde à celles précédemment étudiées, c’est-à-dire qu’il existe une contrainte géométrique, un index et en entrée dans la table de métadonnées geometry_columns.

16.5.2. ★★☆ Essayez vous-même

  • Modifiez la table streets de sorte à ce qu’il y ait une colonne géométrie de type ST_PolyLigne.

  • N’oubliez pas de faire la mise à jour d’accompagnement à la table des colonnes géométriques !

  • Ajoutez également une contrainte pour empêcher toutes géométries ajoutées qui ne sont pas des POLYLIGNES ou nulles.

  • Créez un index spatial sur la nouvelle colonne géométrique

Réponse

alter table streets add column geom geometry;
alter table streets add constraint streets_geom_point_chk check
     (st_geometrytype(geom) = 'ST_LineString'::text OR geom IS NULL);
insert into geometry_columns values ('','public','streets','geom',2,4326,
     'LINESTRING');
create index streets_geo_idx
  on streets
  using gist
  (geom);

Insérons maintenant une polyligne dans notre table rues. Dans ce cas nous mettrons à jour un enregistrement de rue existant:

update streets
set geom = 'SRID=4326;LINESTRING(20 -33, 21 -34, 24 -33)'
where streets.id=2;

Jetez un coup d’œil aux résultats dans QGIS. (Vous pouvez devoir faire un clic droit sur la couches streets dans la “Légende” des couches, et choisissez “Zoomer sur l’emprise de la couche”.)

Créez maintenant plus d’entrées streets - certaines dans QGIS et d’autres depuis la ligne de commande.

16.5.3. Création de Polygones

Créer des polygones est tout aussi facile. Une chose à se souvenir est que par définition les polygones ont au moins quatre sommets, avec le dernier et le premier co-localisés:

insert into cities (name, geom)
values ('Tokyo', 'SRID=4326;POLYGON((10 -10, 5 -32, 30 -27, 10 -10))');

Note

Un polygone requière des doubles parenthèses autour de sa liste de coordonnées ; c’est pour vous permettre d’ajouter des polygones complexes avec de multiples aires non connectées. Par exemple

insert into cities (name, geom)
values ('Tokyo Outer Wards',
        'SRID=4326;POLYGON((20 10, 20 20, 35 20, 20 10),
                           (-10 -30, -5 0, -15 -15, -10 -30))'
        );

Si vous suivez cette étape, vous pouvez vérifier ce qui a été fait en chargeant le jeu de données villes dans QGIS, en ouvrant sa table attributaire, et en sélectionnant la nouvelle entrée. Notez comment les deux nouveaux polygones se comportent comme un polygone.

16.5.4. Exercice : Lier les Villes aux Personnes

Pour cet exercice, vous devriez faire comme suit :

  • Supprimez toutes les données depuis votre table personne.

  • Ajoutez une colonne clé étrangère aux personnes qui font référence à la clé primaire de la table des villes.

  • Utilisez QGIS pour capturer quelques villes.

  • Utilisez SQL pour insérer quelques nouveaux enregistrements de personnes, en vous assurant que chacun a une rue et une ville associées.

Votre schéma de personnes mis à jour devrait ressembler à quelque chose comme cela:

\d people

Table "public.people"
   Column   |         Type          |                      Modifiers
 -----------+-----------------------+--------------------------------------------
  id        | integer               | not null
            |                       | default nextval('people_id_seq'::regclass)
  name      | character varying(50) |
  house_no  | integer               | not null
  street_id | integer               | not null
  phone_no  | character varying     |
  geom      | geometry              |
  city_id   | integer               | not null
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(geom) =
                       'ST_Point'::text OR geom IS NULL)
Foreign-key constraints:
  "people_city_id_fkey" FOREIGN KEY (city_id) REFERENCES cities(id)
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

Réponse

delete from people;
alter table people add column city_id int not null references cities(id);

Ajouter des villes dans QGIS

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('Faulty Towers',
           34,
           3,
           '072 812 31 28',
           1,
           'SRID=4326;POINT(13 -15)');

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('IP Knightly',
           32,
           1,
           '071 812 31 28',
           1,
           'SRID=4326;POINT(18 -24)');

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('Rusty Bedsprings',
           39,
           1,
           '071 822 31 28',
           1,
           'SRID=4326;POINT(22 -25)');

Si vous obtenez le message suivant :

ERROR:  insert or update on table "people" violates foreign key constraint
        "people_city_id_fkey"
DETAIL: Key (city_id)=(1) is not present in table "cities".

Alors cela signifie que vous avez probablement supprimé des entités lorsque vous avez expérimenté la création de polygones dans la table « cities ». Vérifiez donc les entrées dans la table « cities » et utilisez n’importe quelle valeur du champ id qui existerait encore.

16.5.5. Regarder notre Schéma

Notre schéma devrait désormais ressembler à ça :

../../../_images/final_schema.png

16.5.6. ★★★ Essayez vous-même

Créez des contours de ville en calculant l’enveloppe convexe minimum de toutes les adresses pour cette ville et en calculant un tampon autour de cette zone.

16.5.7. Accès aux sous-objets

Avec les fonctions du Modèle-SFS, vous avez une large variété d’options pour accéder aux sous-objets des Géométries SFS. Lorsque vous voulez sélectionner le premier point du sommet de chaque géométrie de polygone dans la table myPolygonTable, vous devez faire cela de cette manière :

  • Transformez les contours du polygone en une polyligne:

    select st_boundary(geometry) from myPolygonTable;

  • Sélectionnez le premier point du sommet de la polyligne résultante :

    select st_startpoint(myGeometry)
from (
    select st_boundary(geometry) as myGeometry
    from myPolygonTable) as foo;

16.5.8. Traitement de données

PostGIS supporte toutes les fonctions conformes à la norme OGC SFS/MM. Toutes ces fonctions commencent avec ST_.

16.5.9. Découper

Pour découper une sous-partie de vos données, vous pouvez utiliser la fonction ST_INTERSECT() Pour éviter les géométries vides, utilisez:

where not st_isempty(st_intersection(a.geom, b.geom))
../../../_images/qgis_001.png 
select st_intersection(a.geom, b.geom), b.*
from clip as a, road_lines as b
where not st_isempty(st_intersection(st_setsrid(a.geom,32734),
  b.geom));
../../../_images/qgis_002.png

16.5.10. Construction de géométries à partir d’autres géométries

À partir d’une table d’un point donné, vous voulez générer une polyligne. L’ordre des points est défini par leur id. Une autre méthode d’ordre pourrait être un horodatage, comme celui que vous obtenez lorsque vous capturez des points de navigation avec un récepteur GPS.

../../../_images/qgis_006.png

Pour créer une polyligne à partir d’une nouvelle couche de points appelée “points”, vous pouvez exécuter la commande suivante:

select ST_LineFromMultiPoint(st_collect(geom)), 1 as id
from (
  select geom
  from points
  order by id
) as foo;

Pour voir comme cela fonctionne sans créer une nouvelle couche, vous pourriez aussi lancer cette commande sur la couche “people”, même si faire cela dans le monde réel aurait peu de sens.

../../../_images/qgis_007.png

16.5.11. Nettoyage de géométrie

Vous trouverez sur ce blog plus d’informations à ce sujet.

16.5.12. Différences entre les tables

Pour déceler la différence entre deux tables ayant la même structure, vous pouvez utiliser le mot-clé EXCEPT de PostgreSQL:

select * from table_a
except
select * from table_b;

Comme résultat, vous obtiendrez tous les enregistrements de la table_a qui ne sont pas stockés dans la table_b.

16.5.13. Espaces de table

Vous pouvez définir où Postgre doit stocker ses données sur le disque en créant des espaces de table:

CREATE TABLESPACE homespace LOCATION '/home/pg';

When you create a database, you can then specify which tablespace to use e.g.:

createdb --tablespace=homespace t4a

16.5.14. Conclusion

Vous avez appris comment créer des géométries complexes en utilisant les déclarations PostGIS. Gardez à l’esprit que vous avez amélioré votre connaissance globale sur les bases de données spatiales, à travers un outil SIG. Vous n’avez généralement pas besoin d’utiliser ces déclarations manuellement mais disposer de compétences sur leur utilisation et leur structure vous aidera à utiliser un SIG, particulièrement si vous rencontrez des erreurs qui pourraient apparaître comme incompréhensibles autrement.