Las traducciones son generadas a través de traducción automática. En caso de conflicto entre la traducción y la version original de inglés, prevalecerá la version en inglés.

Funciones definidas por el usuario de Amazon Aurora PostgreSQL para Amazon Location Service

Puede utilizar Amazon Location Service para trabajar con las coordenadas y direcciones almacenadas en tablas de bases de datos para limpiar y enriquecer sus datos geoespaciales.

Por ejemplo:

En este tutorial se muestra cómo formatear y enriquecer las direcciones almacenadas en una tabla de base de datos de Amazon Aurora PostgreSQL mediante Amazon Location Service.

El código de muestra está disponible en el repositorio de muestras de Amazon Location Service GitHub, que incluye una AWS CloudFormation plantilla.

Información general

La arquitectura incluye las siguientes integraciones:

Requisitos previos

Antes de empezar, necesita los siguientes requisitos previos:

Inicio rápido

Como alternativa a seguir los pasos de este tutorial, puede lanzar una pila rápida para implementar una función de AWS Lambda que admita la operación SearchPlaceIndexForText de Amazon Location. Esto configura automáticamente su cuenta de AWS para permitir que Amazon Aurora llame a AWS Lambda.

Una vez que haya configurado su cuenta de AWS, tendrá que:

Crear un recurso de índice de ubicación

Comience creando un recurso de índice de ubicación para admitir las consultas de geocodificación.

Crear una función de AWS Lambda para geocodificación

Para crear una conexión entre Amazon Aurora PostgreSQL y Amazon Location Service, necesita una función de AWS Lambda que gestione las solicitudes del motor de base de datos. Esta función traduce el evento de la función definida por el usuario de Lambda y llama a la operación SearchPlaceIndexForText de Amazon Location.

Puede crear la función mediante la consola de AWS Lambda, AWS Command Line Interface o las API de AWS Lambda.

Para crear una función definida por el usuario de Lambda con la consola

Conceder a Amazon Aurora PostgreSQL acceso a AWS Lambda

Antes de que Amazon Aurora PostgreSQL pueda invocar una función de AWS Lambda, debe conceder permiso de acceso.

Si su clúster de Amazon Aurora PostgreSQL no es de acceso público, primero tendrá que crear un punto de conexión de VPC para AWS Lambda a fin de que Amazon Aurora pueda llamar a su función de Lambda.

Crear un punto de conexión de VPC para AWS Lambda

Crear una política de IAM para permitir que se invoque su función de AWS Lambda

Crear un rol de IAM para conceder permiso a Amazon Relational Database Service (Amazon RDS)

Al crear un rol de IAM, Amazon Aurora PostgreSQL puede asumir el rol en su nombre para acceder a su función de Lambda. Para obtener más información, vea Crear un rol para delegar permisos a un usuario de IAM en Guía del usuario de IAM.

El siguiente ejemplo es un AWS CLI comando que crea un rol llamado 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"
        }
    ] 
}'

Adjuntar su política de IAM al rol de IAM

Cuando tenga un rol de IAM, asocie la política de IAM que creó.

El siguiente ejemplo es un AWS CLI comando que asocia la política AuroraGeocoderInvokeal rol AuroraGeocoderInvokeRole.

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

Agregar el rol de IAM a un clúster de base de datos de Amazon Aurora

El siguiente ejemplo es un AWS CLI comando para añadir una función de IAM a un Amazon Aurora PostgreSQL clúster de base de datos denominado. MyAuroraCluster

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

Invocar la función de AWS Lambda

Tras conceder permiso a Amazon Aurora PostgreSQL para invocar la función de Lambda de geocodificación, puede crear una función de Amazon Aurora PostgreSQL definida por el usuario para invocar la función de AWS Lambda de geocodificación. Para obtener más información, consulte Invocación de una función de AWS Lambda desde un clúster de bases de datos de Amazon Aurora PostgreSQL en la Guía del usuario de Amazon Aurora.

Instalar las extensiones necesarias de PostgreSQL

Para instalar las extensiones aws_lambda y aws _commons de PostgreSQL necesarias, consulte Información general sobre el uso de una función de Lambda en la Guía del usuario de Amazon Aurora.

CREATE EXTENSION IF NOT EXISTS aws_lambda CASCADE;

Instalar las extensiones necesarias de PostGIS

PostGIS es una extensión de PostgreSQL para almacenar y administrar información espacial. Para obtener más información, consulte Trabajo con la extensión de PostGIS en la Guía del usuario de Amazon Relational Database Service.

CREATE EXTENSION IF NOT EXISTS postgis;

Crear una función de SQL definida por el usuario que invoque la función de Lambda

En un editor de SQL, cree una nueva función definida por el usuario f_SearchPlaceIndexForText para invocarla: 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$;

Llamar a la función de SQL para geocodificar desde Aurora

Al ejecutar la instrucción SQL, se invoca la AuroraGeocoderfunción Lambda, que toma los registros de direcciones de la tabla de la base de datos de la base de datos y Amazon Aurora PostgreSQL los geocodifica mediante un recurso de índice de lugares.

La siguiente función f_SearchPlaceIndexForText SQL realiza solicitudes a la SearchPlaceIndexForText API de Amazon Location a través de la función AuroraGeocoderLambda. La función devuelve una columna de geom que es una geometría de PostGIS, que ST_AsText(geom) convierte en texto.

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

De forma predeterminada, el retorno contendrá una fila. Para solicitar filas adicionales, hasta el límite de MaxResults, ejecute la siguiente instrucción de SQL proporcionando un BiasPosition y limitándolo a los resultados en Canadá.

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

Para filtrar los resultados mediante un cuadro delimitador, pase un Box2D como filter_bbox:

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

Para obtener más información sobre los tipos y funciones de PostGIS, consulte la Referencia de PostGIS.

Enriquecer una base de datos que contiene datos de direcciones

Puede crear una dirección formateada y, al mismo tiempo, normalizarla y geocodificarla mediante la operación SearchPlaceIndexForTextde Amazon Location dada una tabla de base de datos con los siguientes datos divididos en las siguientes columnas:

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;

En el siguiente ejemplo, se ilustra una fila de tabla de datos resultante:

 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)

Actualizar la tabla de la base de datos y rellenar las columnas

En el siguiente ejemplo, se actualiza la tabla y se rellenan las columnas con los resultados de las consultas de SearchPlaceIndexForText:

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;

Siguientes pasos

El código de muestra está disponible en el repositorio de muestras de Amazon Location Service GitHub, que incluye una AWS CloudFormation plantilla.