Utiliser l'extension PostGIS dans PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez à utiliser l'extension PostGIS pour ajouter des capacités spatiales à une base de données PostgreSQL. PostGIS transforme votre base de données en une base de données spatiale, lui permettant de stocker et d'interroger des données définies dans un espace géométrique.

Vous commencerez par créer une nouvelle base de données et activer l'extension PostGIS. Ensuite, vous créerez une table avec une colonne GEOMETRY pour stocker les données de localisation. Enfin, vous insérerez des données pour plusieurs villes et effectuerez des requêtes spatiales pour calculer des distances et trouver des données dans une zone spécifique.

Créer une base de données spatiale et activer PostGIS

Dans cette étape, vous allez créer une nouvelle base de données et y activer l'extension PostGIS. Il est recommandé d'utiliser une base de données dédiée aux données spatiales pour les maintenir organisées.

Tout d'abord, ouvrez un terminal et connectez-vous au serveur PostgreSQL en utilisant le terminal interactif psql. Vous vous connecterez en tant que superutilisateur par défaut postgres.

sudo -u postgres psql

Vous verrez l'invite psql, qui ressemble à postgres=#.

Ensuite, créez une nouvelle base de données nommée spatial_db. Toutes les commandes SQL dans psql doivent se terminer par un point-virgule (;).

CREATE DATABASE spatial_db;

Vous devriez voir le message de confirmation suivant :

CREATE DATABASE

Maintenant, connectez-vous à votre base de données nouvellement créée. La commande \c est utilisée pour se connecter à une base de données différente.

\c spatial_db

Votre invite changera pour indiquer que vous êtes maintenant connecté à spatial_db :

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

Une fois la base de données prête, activez l'extension PostGIS. Cette commande charge tous les types, fonctions et tables spatiales de PostGIS dans votre base de données.

CREATE EXTENSION postgis;

La sortie confirmera que l'extension a été créée :

CREATE EXTENSION

Pour vérifier que PostGIS est correctement installé, vous pouvez vérifier sa version.

SELECT PostGIS_full_version();

Ceci affichera des informations détaillées sur la version de PostGIS et de ses bibliothèques associées, confirmant une configuration réussie. Vous pouvez maintenant passer à l'étape suivante. Ne quittez pas le shell psql.

Créer une table spatiale et insérer des données

Maintenant que vous avez une base de données spatiale, vous pouvez créer des tables pour stocker des données géographiques. Dans cette étape, vous allez créer une table cities et insérer les coordonnées de quelques grandes villes.

Vous devriez toujours être dans le shell psql, connecté à la base de données spatial_db.

Tout d'abord, créez une table nommée cities. Cette table aura des colonnes standard id et name, ainsi qu'une colonne location de type GEOMETRY.

Comprendre GEOMETRY(Point, 4326)

• GEOMETRY : C'est le type de données fondamental de PostGIS pour stocker des entités spatiales.
• Point : Ceci spécifie que la colonne de géométrie ne stockera que des points. D'autres types incluent LINESTRING et POLYGON.
• 4326 : C'est l'identifiant du système de référence spatiale (SRID). Le SRID 4326 correspond au WGS 84, le système de coordonnées standard utilisé par le GPS et Google Maps.

Exécutez la commande suivante pour créer la table :

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

Vous verrez la confirmation :

CREATE TABLE

Ensuite, insérez des données dans la table cities. Vous utiliserez la fonction ST_GeomFromText pour convertir des représentations en texte bien connu (Well-Known Text - WKT) de points en objets géométriques. Le format est POINT(longitude latitude).

INSERT INTO cities (name, location) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
    ('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
    ('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));

La sortie indique que trois lignes ont été insérées :

INSERT 0 3

Pour vérifier que les données ont été correctement insérées, vous pouvez interroger la table. Utilisez la fonction ST_AsText pour convertir l'objet géométrique en un format texte lisible par l'homme.

SELECT id, name, ST_AsText(location) FROM cities;

La sortie devrait afficher les données que vous venez d'insérer :

 id |   name   |          st_astext
----+----------+-----------------------------
  1 | New York | POINT(-74.006 40.7128)
  2 | London   | POINT(-0.1278 51.5074)
  3 | Tokyo    | POINT(139.6917 35.6895)
(3 rows)

Vous avez créé avec succès une table spatiale et l'avez remplie de données.

Effectuer des requêtes et analyses spatiales

La véritable puissance de PostGIS réside dans sa capacité à effectuer des requêtes spatiales. Dans cette étape, vous utiliserez les fonctions PostGIS pour calculer la distance entre deux points et pour trouver quels points se situent dans une certaine zone.

Vous devriez toujours être dans le shell psql, connecté à la base de données spatial_db.

Tout d'abord, calculons la distance entre New York et Londres. Pour des calculs de distance précis sur la surface de la Terre, il est préférable de convertir le type geometry en type geography. Le type geography prend en compte la courbure de la Terre, et ST_Distance retournera le résultat en mètres.

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

La requête retournera la distance orthodromique en mètres. Le résultat sera un grand nombre, environ 5,57 millions de mètres.

   st_distance
-----------------
 5570299.7888681
(1 row)

Ensuite, effectuons une analyse pour trouver quelles villes se trouvent à moins de 200 kilomètres de Londres. Pour ce faire, vous allez d'abord créer un tampon circulaire autour de la localisation de Londres, puis vérifier quelles localisations de villes intersectent ce tampon.

• ST_Buffer : Crée une géométrie qui représente tous les points dont la distance par rapport à la géométrie source est inférieure ou égale à une distance spécifiée. La distance est en mètres lorsqu'elle est utilisée avec le type geography.
• ST_Intersects : Retourne true si deux géométries "s'intersectent spatialement" – si elles ont un espace en commun.

WITH london_buffer AS (
  SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);

Cette requête utilise une expression de table commune (CTE) pour créer un tampon de 200 000 mètres (200 km) autour de Londres. Elle trouve ensuite toutes les villes qui intersectent ce tampon. Comme prévu, seule Londres elle-même se trouve dans cette zone.

  name
--------
 London
(1 row)

Vous avez maintenant effectué une analyse spatiale de base. Vous pouvez quitter le shell psql en tapant \q.

\q

Nettoyer la base de données

Il est de bonne pratique de nettoyer votre environnement après avoir terminé votre travail. Dans cette étape, vous allez supprimer la base de données spatial_db. La suppression d'une base de données retire la base de données elle-même, ainsi que tous les objets qu'elle contient, tels que les tables et les extensions.

Vous devriez être de retour à l'invite de terminal normale (labex@vm:~$).

Pour supprimer une base de données, vous devez être connecté à une base de données différente. Connectez-vous à la base de données par défaut postgres.

sudo -u postgres psql

Maintenant, exécutez la commande DROP DATABASE.

DROP DATABASE spatial_db;

Vous verrez un message de confirmation :

DROP DATABASE

Vous pouvez vérifier que la base de données a été supprimée en listant toutes les bases de données disponibles avec la commande \l.

\l

Vous verrez que spatial_db n'est plus dans la liste.

Enfin, quittez le shell psql.

\q

Résumé

Dans ce laboratoire, vous avez appris à utiliser l'extension PostGIS pour ajouter la prise en charge des données spatiales à PostgreSQL. Vous avez créé avec succès une base de données dédiée, activé l'extension PostGIS, défini une table avec une colonne de géométrie, inséré des données de localisation et effectué des requêtes spatiales pour calculer des distances et trouver des intersections. Ces compétences constituent une base solide pour la création d'applications conscientes de la localisation avec PostgreSQL.