Amazon Aurora PostgreSQL funções definidas pelo usuário para o Amazon Location Service

Você pode usar o Amazon Location Service para trabalhar com coordenadas e endereços armazenados em tabelas de banco de dados para limpar e enriquecer seus dados geoespaciais.

Por exemplo: .

• Você pode usar a geocodificação para converter endereços em coordenadas para normalizar e preencher lacunas nos dados dos endereços armazenados em uma tabela de banco de dados.

• Você pode geocodificar endereços para obter suas posições e usar as coordenadas com funções espaciais do banco de dados, como uma função que mostra linhas em uma área especificada.

• Você pode usar dados enriquecidos para gerar relatórios automatizados, como gerar um relatório automatizado que ilustra todos os dispositivos em uma determinada área ou um relatório automatizado para machine learning que ilustra áreas com maiores taxas de falha ao enviar atualizações de localização.

Este tutorial mostra como formatar e enriquecer endereços armazenados em uma Amazon Aurora PostgreSQL tabela de banco de dados usando o Amazon Location Service.

• Amazon Aurora PostgreSQL – Um mecanismo de banco de dados relacional totalmente gerenciado, compatível com o MySQL e o PostgreSQL, que gera até cinco vezes o throughput do MySQL e até três vezes o throughput do PostgreSQL, sem alterar a maior parte do aplicativo existente. Para obter mais informações, consulte O que é o Amazon Aurora? no Guia do usuário do Amazon Aurora.

Importante

O aplicativo resultante deste tutorial usa um índice de local que armazena os resultados da geocodificação. Para obter informações sobre as cobranças aplicáveis para armazenar resultados de geocodificação, consulte os preços do Amazon Location Service.

O código de amostra está disponível no repositório de amostras do Amazon Location Service em GitHub, que inclui um AWS CloudFormation modelo.

Visão geral

A arquitetura envolve as seguintes integrações:

• Essa solução usa um recurso de índice de local do Amazon Location para fornecer suporte a consultas de geocodificação usando a operação SearchPlaceIndexForText.

• AWS Lambda usa um Python Lambda que geocodifica endereços quando uma política do IAM dá permissão para deixar que AWS Lambda chame a operação de geocodificação do Amazon Location, SearchPlaceIndexForText.

• Conceda permissão para Amazon Aurora PostgreSQL invocar a função do Lambda de geocodificação usando uma função SQL definida pelo usuário.

Pré-requisitos

Antes de começar, você precisa dos seguintes pré-requisitos:

• Um cluster Amazon Aurora PostgreSQL. Para obter mais informações sobre Criar um cluster do banco de dados do Amazon Aurora, consulte o Guia do usuário do Amazon Aurora.

  nota

  Se o cluster do Amazon Aurora não estiver disponível publicamente, você também deverá configurar o Amazon Aurora para se conectar AWS Lambda a uma nuvem privada virtual (VPC) na sua conta AWS. Para ter mais informações, consulte Conceda Amazon Aurora PostgreSQL acesso a AWS Lambda.

• Uma ferramenta de desenvolvedor de SQL para se conectar ao cluster Amazon Aurora PostgreSQL.

Início rápido

Como alternativa às etapas deste tutorial, você pode iniciar uma pilha rápida para implantar uma AWS Lambda função que ofereça suporte à operação SearchPlaceIndexForText do Amazon Location. Isso configura automaticamente sua conta AWS para permitir que o Amazon Aurora faça chamadas AWS Lambda.

Depois de configurar sua conta AWS, você precisará:

• Adicionar o atributo do Lambda ao Amazon Aurora. Consulte Adicionar o perfil do IAM a um cluster do banco de dados do Amazon Aurora em Conceda Amazon Aurora PostgreSQL acesso a AWS Lambda.

• Carregar a função definida pelo usuário em seu banco de dados. Consulte Invocar a função do AWS Lambda.

Criar um recurso de índice de localização

Comece criando um recurso de índice de local para dar suporte às consultas de geocodificação.

1. Abra o console do Amazon Location Service em https://console.aws.amazon.com/location/.

2. No painel de navegação, escolha Índices de localização.

3. Preencha as seguintes caixas:

   • Nome – insira um nome para o recurso de índice de localização. Por exemplo, AuroraPlaceIndex. Máximo de 100 caracteres. Entradas válidas incluem caracteres alfanuméricos, hifens, pontos e sublinhados.

   • Descrição – insira uma descrição opcional. Por exemplo, Índice de local do Amazon Aurora.

4. Em Provedores de dados, escolha um provedor de dados disponível para usar com seu recurso de índice de localização. Se você não tiver nenhuma preferência, recomendamos começar com o Esri.

5. Em Opções de armazenamento de dados, especifique Sim, os resultados serão armazenados. Isso indica que você pretende salvar os resultados da geocodificação em um banco de dados.

6. (Opcional) Em Tags, insira uma tag Chave e Valor. Isso adiciona uma tag ao seu novo recurso de índice de localização. Para obter mais informações, consulte Marcar seus recursos.

7. Escolha Criar índice de localização.

Crie uma AWS Lambda função para geocodificação

Para criar uma conexão entre Amazon Aurora PostgreSQL e o Amazon Location Service, você precisa de uma função AWS Lambda para lidar com solicitações do mecanismo de banco de dados. Essa função traduz o evento de função definida pelo usuário do Lambda e chama a operação SearchPlaceIndexForText do Amazon Location.

Você pode criar a função usando o console AWS Lambda, o AWS Command Line Interface ou as APIs AWS Lambda.

Para criar uma função do Lambda definida pelo usuário usando o console

1. Abra o console AWS Lambda em https://console.aws.amazon.com/lambda/.

2. No painel de navegação à esquerda, selecione Funções.

3. Selecione Criar função e certifique-se de que Autor do zero esteja selecionado.

4. Preencha as seguintes caixas:

   • Nome da função – insira um nome para a sua função. Entradas válidas incluem caracteres alfanuméricos, hifens, pontos e sublinhados, sem espaços. Por exemplo, AuroraGeocoder.

   • Runtime – selecione Python 3.8.

5. Selecione Criar função.

6. Selecione a guia Código para abrir o editor.

7. Substitua o código do espaço reservado em lambda_function.py pelo seguinte:

   from os import environ
   
   import boto3
   from botocore.config import Config
   
   # load the place index name from the environment, falling back to a default
   PLACE_INDEX_NAME = environ.get("PLACE_INDEX_NAME", "AuroraPlaceIndex")
   
   location = boto3.client("location", config=Config(user_agent="Amazon Aurora PostgreSQL"))
   
   """
   This Lambda function receives a payload from Amazon Aurora and translates it to
   an Amazon Location `SearchPlaceIndex` call and returns the results as-is, to be
   post-processed by a PL/pgSQL function.
   """
   def lambda_handler(event, context):
       kwargs = {}
       
       if event.get("biasPosition") is not None:
           kwargs["BiasPosition"] = event["biasPosition"]
   
       if event.get("filterBBox") is not None:
           kwargs["FilterBBox"] = event["filterBBox"]
   
       if event.get("filterCountries") is not None:
           kwargs["FilterCountries"] = event["filterCountries"]
   
       if event.get("maxResults") is not None:
           kwargs["MaxResults"] = event["maxResults"]
       
       return location.search_place_index_for_text(
           IndexName=PLACE_INDEX_NAME,
           Text=event["text"],
           **kwargs)["Results"]

8. Se você nomeou seu índice de local de forma diferente AuroraPlaceIndex, crie uma variável de ambiente chamada PLACE_INDEX_NAME para atribuir o nome do recurso a:

   • Selecione a guia Configuração, selecione Variáveis de ambiente.

   • Selecione Editare, em seguida, selecione Adicionar variável de ambiente.

   • Em Chave: insira PLACE_INDEX_NAME.

   • Em Valor: insira o nome do seu recurso de índice de local.

9. Selecione Implantar para salvar a função atualizada.

10. Selecione Testar no menu suspenso, selecione Configurar eventos de teste.

11. Selecione Criar evento de teste.

12. Insira o seguinte evento de teste:

    {
      "text": "Baker Beach",
      "biasPosition": [-122.483, 37.790],
      "filterCountries": ["USA"]
    }

13. Selecione Testar para testar a função do Lambda.

14. Escolha a guia Configuração.

15. Em Configuração geral: selecione Permissões.

16. Em Função de execução: selecione o Nome da função com hiperlink para conceder permissões do Amazon Location Service à sua função do Lambda.

17. Na guia Permissões: selecione Adicionar permissões no menu suspenso e, em seguida, selecione Criar política em linha.

18. Selecione a guia JSON.

19. Adicione a política do IAM a seguir:

    • A política a seguir dá permissão para enviar SearchPlaceIndexForText para o recurso de índice de locais AuroraPlaceIndex.

      {
        "Version": "2012-10-17",
        "Statement": [
          {
            "Effect": "Allow",
            "Action": "geo:SearchPlaceIndexForText",
            "Resource": "arn:aws:geo:<Region>:<AccountId>:place-index/AuroraPlaceIndex"
          }
        ]
      }

20. Escolha Revisar política.

21. Insira um nome de política. Por exemplo, AuroraPlaceIndexReadOnly.

22. Escolha Criar política.

Conceda Amazon Aurora PostgreSQL acesso a AWS Lambda

Antes de Amazon Aurora PostgreSQL poder invocar uma função AWS Lambda, você deve conceder permissão de acesso.

Se seu cluster Amazon Aurora PostgreSQL não estiver acessível publicamente, você precisará, primeiro, criar um endpoint da VPC para AWS Lambda para que o Amazon Aurora chame sua função do Lambda.

Crie um endpoint da VPC para AWS Lambda

nota

Essa etapa só é necessária se seu cluster Amazon Aurora PostgreSQL não estiver acessível publicamente.

1. Abra a Amazon Virtual Private Cloud Console.

2. No painel de navegação à esquerda, selecione Endpoints.

3. Selecione Criar endpoint.

4. No filtro Nome de serviço, insira “lambda” e, em seguida, selecione com.amazonaws.<region>.lambda.

5. Escolha a VPC que contém seu cluster do Aurora.

6. Selecione uma sub-rede para cada zona de disponibilidade.

7. No filtro do Grupo de segurança, insira “default” ou o nome do grupo de segurança do qual seu cluster do Aurora é membro e selecione o grupo de segurança.

8. Selecione Criar endpoint.

Crie uma política do IAM para conceder permissão para invocar sua função AWS Lambda

1. Abra o console do IAM.

2. No painel de navegação à esquerda, expanda Gerenciamento de acesso e selecione Políticas.

3. Escolha Criar política.

4. Na guia JSON, insira a seguinte política:

   • Veja a seguir um exemplo de uma política do IAM que concede Amazon Aurora PostgreSQL permissão para invocar a função AuroraGeocoder AWS Lambda.

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Action": "lambda:InvokeFunction",
               "Resource": [
                   "arn:aws:lambda:<Region>:<AccountId>:function:AuroraGeocoder"
               ]
           }
       ]
   }

5. Selecione Próximo: Tags para adicionar tags opcionais.

6. Escolha Próximo: Revisar.

7. Revise sua política e insira os seguintes detalhes para a política:

   • Nome – Use caracteres alfanuméricos e ‘+=,.@-_’. Máximo de 128 caracteres. Por exemplo, AuroraGeocoderInvoke.

   • Descrição – insira uma descrição opcional. Use caracteres alfanuméricos e ‘+=,.@-_’. Máximo de 1000 caracteres.

8. Escolha Criar política. Observe o ARN dessa política, que você usa para vincular a política a um perfil do IAM.

Crie um perfil do IAM para dar permissão ao Amazon Relational Database Service (Amazon RDS)

Ao criar um perfil do IAM, Amazon Aurora PostgreSQL você pode assumir a função em seu nome para acessar sua função do Lambda. Para ter mais informações, consulte Criar um perfil para delegar permissões a um usuário do IAM no Guia do usuário do IAM.

O exemplo a seguir é um AWS CLI comando que cria uma função chamada 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"
        }
    ] 
}'

Anexe a política do IAM ao perfil do IAM

Quando você tiver um perfil do IAM, anexe a política do IAM que você criou.

O exemplo a seguir é um AWS CLI comando que anexa a política AuroraGeocoderInvokeà função AuroraGeocoderInvokeRole.

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

Adicione o perfil do IAM a um cluster do banco de dados do Amazon Aurora

O exemplo a seguir é um AWS CLI comando para adicionar uma função do IAM a um cluster de Amazon Aurora PostgreSQL banco de dados chamado MyAuroraCluster.

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

Invocar a função do AWS Lambda

Depois de conceder permissão para Amazon Aurora PostgreSQL invocar sua função do Lambda de geocodificação, você pode criar uma Amazon Aurora PostgreSQL função definida pelo usuário para invocar a função AWS Lambda de geocodificação. Para obter mais informações, consulte Invocando uma AWS Lambda função a partir de um Amazon Aurora PostgreSQL cluster de banco de dados no Guia do usuário do Amazon Aurora.

Instale as extensões necessárias do PostgreSQL

Para instalar as extensões aws_lambda e aws _commons necessárias do PostgreSQL, consulte Visão geral do uso de uma função do Lambda no Guia do usuário do Amazon Aurora.

CREATE EXTENSION IF NOT EXISTS aws_lambda CASCADE;

Instale as extensões necessárias do PostGIS

PostGIS é uma extensão do PostgreSQL para armazenar e gerenciar informações espaciais. Para obter mais informações, consulte Trabalhando com e extensão PostGIS no Guia do usuário do Amazon Relational Database Service.

CREATE EXTENSION IF NOT EXISTS postgis;

Crie uma função SQL definida pelo usuário que invoque a função do Lambda

Em um editor SQL, crie uma nova função definida pelo usuário f_SearchPlaceIndexForText para invocar a função: 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$;

Chame a função SQL para geocodificar a partir do Aurora

A execução da instrução SQL invoca a função Lambda AuroraGeocoder, que pega registros de endereço da tabela do banco de dados no banco de dados e os codifica geograficamente usando um recurso de índice de local. Amazon Aurora PostgreSQL

nota

Amazon Aurora PostgreSQL invoca a função do Lambda para cada chamada para a função SQL definida pelo usuário.

Se você estiver geocodificando 50 linhas, Amazon Aurora PostgreSQL invoca a função do Lambda 50 vezes. Uma invocação para cada linha.

A função f_SearchPlaceIndexForText SQL a seguir faz solicitações à SearchPlaceIndexForText API do Amazon Location por meio da função AuroraGeocoderLambda. A função retorna uma geom coluna que é uma geometria PostGIS, ST_AsText(geom) que é convertida em texto.

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

Por padrão, o retorno conterá uma linha. Para solicitar linhas adicionais, até o MaxResults limite, execute a instrução do SQL a seguir, fornecendo um BiasPosition e limitando os resultados ao Canadá.

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

Para filtrar os resultados usando uma caixa delimitadora, passe um Box2D como filter_bbox:

• FilterBBox – Filtra os resultados retornando lugares dentro de uma caixa delimitadora. Esse parâmetro é opcional.

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

Para obter mais informações sobre os tipos e funções do PostGIS, consulte a Referência do PostGIS.

Enriquecendo um banco de dados contendo dados de endereço

Você pode estruturar um endereço formatado e, simultaneamente, normalizar e geocodificar usando a operação SearchPlaceIndexForText do Amazon Location, dada uma tabela de banco de dados com os seguintes dados divididos nas seguintes colunas:

• id

• address

• city

• state

• zip

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;

O exemplo a seguir ilustra uma linha de dados resultante na tabela:

 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)

Atualize a tabela do banco de dados e preencha as colunas

O exemplo a seguir atualiza a tabela e preenche as colunas com os resultados das SearchPlaceIndexForText consultas:

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;

Próximas etapas

O código de amostra está disponível no repositório de amostras do Amazon Location Service em GitHub, que inclui um AWS CloudFormation modelo.