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.

Amazon Aurora PostgreSQLfonctions définies par l'utilisateur pour Amazon Location Service

Vous pouvez utiliser Amazon Location Service pour utiliser les coordonnées et les adresses stockées dans les tables de base de données afin de nettoyer et d'enrichir vos données géospatiales.

Par exemple :

Ce didacticiel explique comment formater et enrichir les adresses stockées dans une table de Amazon Aurora PostgreSQL base de données à l'aide d'Amazon Location Service.

Un exemple de code est disponible dans le référentiel d'exemples d'Amazon Location Service sur GitHub, qui inclut un AWS CloudFormation modèle.

Présentation

L'architecture implique les intégrations suivantes :

Prérequis

Avant de commencer, vous devez remplir les conditions préalables suivantes :

Démarrage rapide

Au lieu de suivre les étapes décrites dans ce didacticiel, vous pouvez lancer une pile rapide pour déployer une AWS Lambda fonction prenant en charge le fonctionnement d'Amazon LocationSearchPlaceIndexForText. Cela configure automatiquement votre AWS compte pour autoriser Amazon Aurora à appelerAWS Lambda.

Une fois que vous aurez configuré votre AWS compte, vous devrez :

Création d'une ressource d'index des lieux

Commencez par créer une ressource d'index de lieux pour prendre en charge les requêtes de géocodage.

Création d'une AWS Lambda fonction pour le géocodage

Pour créer une connexion entre Amazon Location Service Amazon Aurora PostgreSQL et Amazon, vous avez besoin d'une AWS Lambda fonction permettant de gérer les demandes provenant du moteur de base de données. Cette fonction traduit l'événement de fonction Lambda défini par l'utilisateur et appelle l'opération Amazon Location. SearchPlaceIndexForText

Vous pouvez créer la fonction à l'aide de la AWS Lambda consoleAWS Command Line Interface, des API ou des AWS Lambda API.

Pour créer une fonction Lambda définie par l'utilisateur à l'aide de la console

Accorder Amazon Aurora PostgreSQL l'accès à AWS Lambda

Avant de Amazon Aurora PostgreSQL pouvoir invoquer une AWS Lambda fonction, vous devez accorder une autorisation d'accès.

Si votre Amazon Aurora PostgreSQL cluster n'est pas accessible au public, vous devez d'abord créer un point de terminaison VPC pour AWS Lambda qu'Amazon Aurora puisse appeler votre fonction Lambda.

Créez un point de terminaison VPC pour AWS Lambda

Créez une politique IAM pour autoriser l'appel de votre fonction AWS Lambda

Créez un rôle IAM pour autoriser Amazon Relational Database Service (Amazon RDS)

En créant un rôle IAM, Amazon Aurora PostgreSQL vous pouvez assumer le rôle en votre nom pour accéder à votre fonction Lambda. Pour plus d'informations, veuillez consulter Création d'un rôle pour déléguer des autorisations à un utilisateur IAM dans le Guide de l'utilisateur IAM.

L'exemple suivant est une AWS CLI commande qui crée un rôle nommé AuroraGeocoderInvokeRole:

aws iam create-role  --role-name rds-lambda-role --assume-role-policy-document '{
    "Version": "2012-10-17",
    "Statement": [
        {
        "Effect": "Allow",
        "Principal": {
            "Service": "rds.amazonaws.com"
        },
        "Action": "sts:AssumeRole"
        }
    ] 
}'

Associez votre politique IAM au rôle IAM

Lorsque vous avez un rôle IAM, attachez la politique IAM que vous avez créée.

L'exemple suivant est une AWS CLI commande qui associe la politique AuroraGeocoderInvokeau rôle AuroraGeocoderInvokeRole.

aws iam attach-role-policy  --policy-arn AuroraGeocoderInvoke  --role-name AuroraGeocoderInvokeRole

Ajouter le rôle IAM à un cluster de base de données Amazon Aurora

L'exemple suivant est une AWS CLI commande permettant d'ajouter un rôle IAM à un Amazon Aurora PostgreSQL cluster de base de données nommé MyAuroraCluster.

aws rds add-role-to-db-cluster \
--db-cluster-identifier MyAuroraCluster \
--feature-name Lambda \
--role-arn AuroraGeocoderInvokeRole   \
--region your-region

Appeler la fonction AWS Lambda

Après avoir autorisé l'appel de Amazon Aurora PostgreSQL votre fonction Lambda de géocodage, vous pouvez créer Amazon Aurora PostgreSQL une fonction définie par l'utilisateur pour appeler la fonction de géocodage. AWS Lambda Pour plus d'informations, consultez la section Invocation d'une AWS Lambda fonction depuis un Amazon Aurora PostgreSQL cluster de bases de données dans le guide de l'utilisateur Amazon Aurora.

Installez les extensions PostgreSQL requises

Pour installer les aws_lambda extensions aws _commons et extensions PostgreSQL requises, consultez la section Présentation de l'utilisation d'une fonction Lambda dans le guide de l'utilisateur Amazon Aurora.

CREATE EXTENSION IF NOT EXISTS aws_lambda CASCADE;

Installez les extensions PostGIS requises

PostGIS est une extension de PostgreSQL pour le stockage et la gestion des informations spatiales. Pour plus d'informations, consultez la section Utilisation de l'extension PostGIS dans le guide de l'utilisateur d'Amazon Relational Database Service.

CREATE EXTENSION IF NOT EXISTS postgis;

Créez une fonction SQL définie par l'utilisateur qui invoque la fonction Lambda

Dans un éditeur SQL, créez une nouvelle fonction définie par l'utilisateur f_SearchPlaceIndexForText pour appeler la fonction AuroraGeocoder:

CREATE OR REPLACE FUNCTION f_SearchPlaceIndexForText(
  text text,
  bias_position geometry(Point, 4326) DEFAULT NULL,
  filter_bbox box2d DEFAULT NULL,
  filter_countries text[] DEFAULT NULL,
  max_results int DEFAULT 1
)
 RETURNS TABLE (
   label text,
   address_number text,
   street text,
   municipality text,
   postal_code text,
   sub_region text,
   region text,
   country text,
   geom geometry(Point, 4326)
 )
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
begin
    RETURN QUERY
    WITH results AS (
      SELECT json_array_elements(payload) rsp
      FROM aws_lambda.invoke(
        aws_commons.create_lambda_function_arn('AuroraGeocoder'),
        json_build_object(
          'text', text,
          'biasPosition',
          CASE WHEN bias_position IS NOT NULL THEN
            array_to_json(ARRAY[ST_X(bias_position), ST_Y(bias_position)])
          END,
          'filterBBox',
          CASE WHEN filter_bbox IS NOT NULL THEN
            array_to_json(ARRAY[ST_XMin(filter_bbox), ST_YMin(filter_bbox), ST_XMax(filter_bbox), ST_YMax(filter_bbox)])
          END,
          'filterCountries', filter_countries,
          'maxResults', max_results
        )
      )
    )
    SELECT
      rsp->'Place'->>'Label' AS label,
      rsp->'Place'->>'AddressNumber' AS address_number,
      rsp->'Place'->>'Street' AS street,
      rsp->'Place'->>'Municipality' AS municipality,
      rsp->'Place'->>'PostalCode' AS postal_code,
      rsp->'Place'->>'SubRegion' AS sub_region,
      rsp->'Place'->>'Region' AS region,
      rsp->'Place'->>'Country' AS country,
      ST_GeomFromGeoJSON(
        json_build_object(
          'type', 'Point',
            'coordinates', rsp->'Place'->'Geometry'->'Point'
        )
      ) geom
    FROM results;
end;
$function$;

Appelez la fonction SQL pour géocoder depuis Aurora

L'exécution de l'instruction SQL appelle la AuroraGeocoderfonction Lambda, qui prend les enregistrements d'adresses de la table de base de données de la base de données et les géocode à Amazon Aurora PostgreSQL l'aide d'une ressource d'index de localisation.

La fonction f_SearchPlaceIndexForText SQL suivante envoie des requêtes à l'SearchPlaceIndexForTextAPI d'Amazon Location via la fonction AuroraGeocoderLambda. La fonction renvoie une geom colonne qui est une géométrie PostGIS, qui est ST_AsText(geom) convertie en texte.

SELECT *, ST_AsText(geom)
FROM f_SearchPlaceIndexForText('Vancouver, BC');

Par défaut, le retour contiendra une ligne. Pour demander des lignes supplémentaires, dans la MaxResults limite autorisée, exécutez l'instruction SQL suivante tout en fournissant un BiasPosition et en limitant les résultats au Canada.

SELECT *
FROM f_SearchPlaceIndexForText('Mount Pleasant', ST_MakePoint(-123.113, 49.260), null, '{"CAN"}', 5);

Pour filtrer les résultats à l'aide d'un cadre de sélection, transmettez un Box2D comme suit : filter_bbox

SELECT *
FROM f_SearchPlaceIndexForText('Mount Pleasant', null, 'BOX(-139.06 48.30, -114.03 60.00)'::box2d, '{"CAN"}', 5);

Pour plus d'informations sur les types et les fonctions de PostGIS, consultez le manuel de référence PostGIS.

Enrichir une base de données contenant des données d'adresses

Vous pouvez créer une adresse formatée, la normaliser et le géocoder simultanément à l'aide de l'opération Amazon Location à partir d'une table de base de SearchPlaceIndexForText données contenant les données suivantes réparties dans les colonnes suivantes :

WITH source_data AS (
  SELECT
    id,
    address || ', ' || city || ', ' || state || ', ' || zip AS formatted_address
  FROM addresses
),
geocoded_data AS (
  SELECT
    *,
    (f_SearchPlaceIndexForText(formatted_address)).*
  FROM source_data
)
SELECT
  id,
  formatted_address,
  label normalized_address,
  ST_Y(geom) latitude,
  ST_X(geom) longitude
FROM geocoded_data
-- limit the number of rows that will be geocoded; remove this to geocode the entire table
LIMIT 1;

L'exemple suivant illustre une ligne de table de données résultante :

 id |      formatted_address         |            normalized_address              |     latitude     |     longitude
----+--------------------------------+--------------------------------------------+------------------+-------------------
 42 | 123 Anytown Ave N, Seattle, WA | 123 Anytown Ave N, Seattle, WA, 12345, USA | 47.6223000127926 | -122.336745971039
(1 row)

Mettre à jour la table de base de données et remplir les colonnes

L'exemple suivant met à jour le tableau et remplit les colonnes avec les résultats des SearchPlaceIndexForText requêtes :

WITH source_data AS (
  -- select rows that have not been geocoded and created a formatted address for each
  SELECT
    id,
    address || ', ' || city || ', ' || state || ', ' || zip AS formatted_address
  FROM addresses
  WHERE label IS NULL
  -- limit the number of rows that will be geocoded; remove this to geocode the entire table
  LIMIT 1
),
geocoded_data AS (
  -- geocode each row and keep it linked to the source's ID
  SELECT
    id,
    (f_SearchPlaceIndexForText(formatted_address)).*
  FROM source_data
)
UPDATE addresses
-- populate columns
SET
  normalized_address = geocoded_data.label,
  latitude = ST_Y(geocoded_data.geom),
  longitude = ST_X(geocoded_data.geom)
FROM geocoded_data
-- ensure that rows match
WHERE addresses.id = geocoded_data.id;

Étapes suivantes

Un exemple de code est disponible dans le référentiel d'exemples d'Amazon Location Service sur GitHub, qui inclut un AWS CloudFormation modèle.