Gestion des données spatiales avec l'extension PostGIS - Amazon Aurora
Étape 1 : créer un utilisateur (rôle) pour gérer l'extension PostGISÉtape 2 : Chargez les extensions PostGISÉtape 3 : transfert de la propriété des extensionsÉtape 4 : transfert de la propriété des objets PostGISÉtape 5 : Testez les extensionsÉtape 6 : Mettre à niveau l'extension PostGISVersions de l'extension PostGISMise à niveau de PostGIS 2 vers PostGIS 3

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Gestion des données spatiales avec l'extension PostGIS

PostGIS est une extension de PostgreSQL pour le stockage et la gestion des informations spatiales. Pour en savoir plus sur PostGIS, veuillez consulter PostGIS.net.

À partir de la version 10.5, PostgreSQL prend en charge la bibliothèque libprotobuf 1.3.0 utilisée par PostGIS pour travailler avec les données des tuiles vectorielles des boîtes de cartes.

La configuration de l'extension PostGIS nécessite des privilèges rds_superuser. Nous vous recommandons de créer un utilisateur (rôle) pour gérer l'extension PostGIS et vos données spatiales. L'extension PostGIS et ses composants associés ajoutent des milliers de fonctions à PostgreSQL. Pensez à créer l'extension PostGIS dans son propre schéma si cela est logique pour votre cas d'utilisation. L'exemple suivant montre comment installer l'extension dans sa propre base de données, mais cela n'est pas nécessaire.

Étape 1 : créer un utilisateur (rôle) pour gérer l'extension PostGIS

Tout d'abord, connectez-vous à votre instance de base de données RDS for PostgreSQL en tant qu'utilisateur disposant de privilèges rds_superuser. Si vous avez conservé le nom par défaut lors de la configuration de votre instance, vous vous connectez en tant que postgres. 

psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

Créez un rôle (utilisateur) distinct pour administrer l'extension PostGIS.

postgres=>  CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE

Accordez des privilèges rds_superuser à ce rôle, pour lui permettre d'installer l'extension.

postgres=> GRANT rds_superuser TO gis_admin;
GRANT

Créez une base de données à utiliser pour vos artefacts PostGIS. Cette étape est facultative. Vous pouvez également créer un schéma dans votre base de données utilisateur pour les extensions PostGIS, mais cela n'est pas non plus nécessaire.

postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE

Accordez à gis_admin tous les privilèges sur la base de données lab_gis.

postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT

Quittez la session et reconnectez-vous à votre instance de base de données RDS for PostgreSQL en tant que gis_admin.

postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>

Continuez à configurer l'extension comme indiqué dans les étapes suivantes.

Étape 2 : Chargez les extensions PostGIS

L'extension PostGIS comprend plusieurs extensions connexes qui fonctionnent ensemble pour fournir des fonctionnalités géospatiales. En fonction de votre cas d'utilisation, vous n'aurez peut-être pas besoin de toutes les extensions créées dans cette étape.

Utilisez les instructions CREATE EXTENSION pour charger les extensions PostGIS. 

CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION

Vous pouvez vérifier les résultats en exécutant la requête SQL présentée dans cet exemple, qui répertorie les extensions et leurs propriétaires. 

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;
List of schemas
     Name     |   Owner
--------------+-----------
 public       | postgres
 tiger        | rdsadmin
 tiger_data   | rdsadmin
 topology     | rdsadmin
(4 rows)

Étape 3 : transfert de la propriété des extensions

Utilisez les instructions ALTER SCHEMA pour transférer la propriété des schémas au rôle gis_admin.

ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin; 
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA

Vous pouvez confirmer le changement de propriétaire en exécutant la requête SQL suivante. Vous pouvez également utiliser la méta-commande \dn à partir de la ligne de commande psql. 

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;

       List of schemas
     Name     |     Owner
--------------+---------------
 public       | postgres
 tiger        | gis_admin
 tiger_data   | gis_admin
 topology     | gis_admin
(4 rows)

Étape 4 : transfert de la propriété des objets PostGIS

Utilisez la fonction suivante pour transférer la propriété des objets PostGIS au rôle gis_admin. Exécutez l'instruction suivante à partir de l'invite psql pour créer la fonction.

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION

Ensuite, exécutez cette requête pour exécuter la fonction exec qui à son tour exécute les instructions et modifie les autorisations.

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;

Étape 5 : Testez les extensions

Pour éviter d'avoir à spécifier le nom du schéma, ajoutez le schéma tiger à votre chemin de recherche en utilisant la commande suivante.

SET search_path=public,tiger;
SET

Testez le schéma tiger à l'aide de l'instruction SELECT suivante.

SELECT address, streetname, streettypeabbrev, zip
 FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev |  zip
---------+------------+------------------+-------
       1 | Devonshire | Pl               | 02109
(1 row)

Pour en savoir plus sur cette extension, consultez Tiger Geocoder dans la documentation de PostGIS.

Testez l'accès au schéma topology en utilisant l'instruction SELECT suivante. Cela appelle la fonction createtopology qui enregistre un nouvel objet topologique (my_new_topo) avec l'identifiant de référence spatiale spécifié (26986) et la tolérance par défaut (0.5). Pour en savoir plus, consultez CreateTopologyla documentation de PostGIS. 

SELECT topology.createtopology('my_new_topo',26986,0.5);
 createtopology
----------------
              1
(1 row)

Étape 6 : Mettre à niveau l'extension PostGIS

Chaque nouvelle version de PostgreSQL prend en charge une ou plusieurs versions de l'extension PostGIS compatibles avec cette version. La mise à niveau du moteur PostgreSQL vers une nouvelle version ne met pas automatiquement à niveau l'extension PostGIS. Avant de mettre à niveau le moteur PostgreSQL, vous mettez généralement à niveau PostGIS vers la version la plus récente disponible pour la version actuelle de PostgreSQL. Pour plus de détails, consultez Versions de l'extension PostGIS.

Après la mise à niveau du moteur PostgreSQL, vous mettez à nouveau à niveau l'extension PostGIS, vers la version prise en charge par la nouvelle version du moteur PostgreSQL. Pour obtenir plus d'informations sur la mise à niveau du moteur PostgreSQL, consultez Test d'une mise à niveau de votre cluster de base de données de production vers une nouvelle version majeure.

Vous pouvez vérifier à tout moment si des mises à jour de l'extension PostGIS sont disponibles sur votre cluster de base de données Aurora PostgreSQL . Pour ce faire, exécutez la commande suivante. Cette fonction est disponible avec PostGIS 2.5.0 et les versions ultérieures.

SELECT postGIS_extensions_upgrade();

Si votre application ne prend pas en charge la dernière version de PostGIS, vous pouvez installer une version plus ancienne de PostGIS qui est disponible dans votre version majeure comme suit.

CREATE EXTENSION postgis VERSION "2.5.5";

Si vous souhaitez effectuer une mise à niveau vers une version PostGIS spécifique à partir d'une version antérieure, vous pouvez également utiliser la commande suivante.

ALTER EXTENSION postgis UPDATE TO "2.5.5";

Selon la version à partir de laquelle vous effectuez la mise à niveau, vous devrez peut-être utiliser à nouveau cette fonction. Le résultat de la première exécution de la fonction détermine si une mise à niveau supplémentaire est nécessaire. C'est le cas, par exemple, pour la mise à niveau de PostGIS 2 vers PostGIS 3. Pour plus d’informations, consultez Mise à niveau de PostGIS 2 vers PostGIS 3.

Si vous avez mis à niveau cette extension pour vous préparer à une mise à niveau de version majeure du moteur PostgreSQL, vous pouvez continuer avec d'autres tâches préliminaires. Pour de plus amples informations, veuillez consulter Test d'une mise à niveau de votre cluster de base de données de production vers une nouvelle version majeure.

Versions de l'extension PostGIS

Nous vous recommandons d'installer les versions de toutes les extensions, telles que PostGIS, telles qu'elles sont répertoriées dans Extension versions for Aurora PostgreSQL-Compatible Edition (Versions des extensions pour l'Édition compatible avec PostgreSQL) dans les Release Notes for Aurora PostgreSQL (Notes de mise à jour d'Aurora PostgreSQL). Pour obtenir une liste des versions qui sont disponibles dans votre version, utilisez la commande suivante.

SELECT * FROM pg_available_extension_versions WHERE name='postgis';

Vous pouvez trouver des informations sur les versions dans les sections suivantes des Release Notes for Aurora PostgreSQL (Notes de mise à jour d'Aurora PostgreSQL) :

Mise à niveau de PostGIS 2 vers PostGIS 3

Depuis la version 3.0, la fonctionnalité matricielle de PostGIS est désormais une extension distincte, postgis_raster. Cette extension dispose de son propre chemin d'installation et de mise à niveau. Cela supprime des dizaines de fonctions, de types de données et d'autres artefacts nécessaires au traitement des images matricielles de l'extension postgis de base. Cela signifie que si votre cas d'utilisation ne nécessite pas de traitement matriciel, vous n'avez pas besoin d'installer l'extension postgis_raster.

Dans l'exemple de mise à niveau suivant, la première commande de mise à niveau extrait la fonctionnalité raster dans l'extension postgis_raster. Une deuxième commande de mise à niveau est alors nécessaire pour mettre à niveau postgres_raster vers la nouvelle version.

Pour effectuer une mise à niveau de PostGIS 2 vers PostGIS 3

  1. Identifiez la version par défaut de PostGIS qui est disponible pour la version de PostgreSQL sur votre cluster de base de données Aurora PostgreSQL. Pour ce faire, exécutez la requête suivante.

    SELECT * FROM pg_available_extensions
    WHERE default_version > installed_version;
  name   | default_version | installed_version |                          comment
---------+-----------------+-------------------+------------------------------------------------------------
 postgis | 3.1.4           | 2.3.7             | PostGIS geometry and geography spatial types and functions
(1 row)

  2. Identifiez les versions de PostGIS installées dans chaque base de données sur l'instance d'écriture de votre cluster de base de données Aurora PostgreSQL. En d'autres termes, interrogez chaque base de données utilisateur comme suit.

    SELECT
    e.extname AS "Name",
    e.extversion AS "Version",
    n.nspname AS "Schema",
    c.description AS "Description"
FROM
    pg_catalog.pg_extension e
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
    AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
    e.extname LIKE '%postgis%'
ORDER BY
    1;
  Name   | Version | Schema |                             Description
---------+---------+--------+---------------------------------------------------------------------
 postgis | 2.3.7   | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)

    Ce décalage entre la version par défaut (PostGIS 3.1.4) et la version installée (PostGIS 2.3.7) signifie que vous devez mettre à niveau l'extension PostGIS.

    ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged

  3. Exécutez la requête suivante pour vérifier que la fonctionnalité raster est maintenant dans son propre package.

    SELECT
    probin,
    count(*)
FROM
    pg_proc
WHERE
    probin LIKE '%postgis%'
GROUP BY
    probin;
          probin          | count
--------------------------+-------
 $libdir/rtpostgis-2.3    | 107
 $libdir/postgis-3        | 487
(2 rows)

    Le résultat montre qu'il y a toujours une différence entre les versions. Les fonctions PostGIS sont en version 3 (postgis-3), tandis que les fonctions raster (rtpostgis) sont en version 2 (rtpostgis-2.3). Pour terminer la mise à niveau, vous exécutez à nouveau la commande de mise à niveau, comme suit.

    postgres=> SELECT postgis_extensions_upgrade();

    Vous pouvez ignorer les messages d'avertissement, il n'y a aucun risque. Exécutez à nouveau la requête suivante pour vérifier que la mise à niveau est terminée. La mise à niveau est terminée lorsque PostGIS et toutes les extensions associées ne sont plus marquées comme nécessitant une mise à niveau. 

    SELECT postgis_full_version();

  4. Utilisez la requête suivante pour voir le processus de mise à niveau terminé et les extensions packagées séparément, et vérifiez que leurs versions correspondent. 

    SELECT
    e.extname AS "Name",
    e.extversion AS "Version",
    n.nspname AS "Schema",
    c.description AS "Description"
FROM
    pg_catalog.pg_extension e
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
        AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
    e.extname LIKE '%postgis%'
ORDER BY
    1;
      Name      | Version | Schema |                             Description
----------------+---------+--------+---------------------------------------------------------------------
 postgis        | 3.1.5   | public | PostGIS geometry, geography, and raster spatial types and functions
 postgis_raster | 3.1.5   | public | PostGIS raster types and functions
(2 rows)

    Le résultat montre que l'extension PostGIS 2 a été mise à niveau vers PostGIS 3, et que postgis et l'extension postgis_raster maintenant séparée sont en version 3.1.5.

Une fois cette mise à niveau terminée, si vous ne prévoyez pas d'utiliser la fonctionnalité raster, vous pouvez abandonner l'extension comme suit.

DROP EXTENSION postgis_raster;