Skip to content

Latest commit

 

History

History
 
 

athena-udfs

README.md

Amazon Location Athena UDFs

This is a set of Federated Scalar Batch Functions for Amazon Location Service Amazon Athena using the Athena Query Federation SDK. These user-defined functions (UDFs) allow you to geocode and reverse geocode data accessible to Athena from the comfort of SQL.

Deploying

These UDFs can be deployed into your account using the AWS Serverless Application Repository.

Querying

search_place_index_for_text

The following SQL statement demonstrates how to convert textual addresses into coordinates using SearchPlaceIndexForText. In addition to returning a position, it will decompose the resulting address into components.

USING
-- use alternate function signatures shown below to provide additional parameters
EXTERNAL FUNCTION search_place_index_for_text(input VARCHAR)
  RETURNS ROW(
    label VARCHAR,
    address_number VARCHAR,
    street VARCHAR,
    municipality VARCHAR,
    postal_code VARCHAR,
    sub_region VARCHAR,
    region VARCHAR,
    country VARCHAR,
    geom VARCHAR)
  LAMBDA 'amazon_location' -- change this if necessary to reflect LambdaFunctionName
WITH addresses AS (
  -- substitute data from an Athena table here
  SELECT * FROM (VALUES '410 Terry Ave N, Seattle, WA') AS addresses(address)
), rsp AS (
  SELECT search_place_index_for_text(address) result
  FROM addresses
)
SELECT
  result.label,
  result.address_number,
  result.street,
  result.municipality,
  result.postal_code,
  result.sub_region,
  result.region,
  result.country,
  ST_GeometryFromText(result.geom) geom
FROM rsp

Alternate function definitions can be used to provide additional SearchPlaceIndexForText parameters:

EXTERNAL FUNCTION search_place_index_for_text(input VARCHAR, bias_position_x DOUBLE, bias_position_y DOUBLE)
EXTERNAL FUNCTION search_place_index_for_text(input VARCHAR, bias_position_x DOUBLE, bias_position_y DOUBLE, countries ARRAY(VARCHAR))
EXTERNAL FUNCTION search_place_index_for_text(input VARCHAR, filter_min_x DOUBLE, filter_min_y DOUBLE, filter_max_x DOUBLE, filter_max_y DOUBLE)
EXTERNAL FUNCTION search_place_index_for_text(input VARCHAR, filter_min_x DOUBLE, filter_min_y DOUBLE, filter_max_x DOUBLE, filter_max_y DOUBLE, countries ARRAY(VARCHAR))

search_place_index_for_position

The following SQL statement demonstrates how to convert coordinates into addresses using SearchPlaceIndexForPosition.

USING
EXTERNAL FUNCTION search_place_index_for_position(x DOUBLE, y DOUBLE)
  RETURNS ROW(
    label VARCHAR,
    address_number VARCHAR,
    street VARCHAR,
    municipality VARCHAR,
    postal_code VARCHAR,
    sub_region VARCHAR,
    region VARCHAR,
    country VARCHAR,
    geom VARCHAR)
  LAMBDA 'amazon_location' -- change this if necessary to reflect LambdaFunctionName
WITH positions AS (
  -- substitute data from an Athena table here
  SELECT * FROM (VALUES ROW(-122.46729, 37.80575)) AS positions(x, y)
), rsp AS (
  SELECT search_place_index_for_position(x, y) result
  FROM positions
)
SELECT
  result.label,
  result.address_number,
  result.street,
  result.municipality,
  result.postal_code,
  result.sub_region,
  result.region,
  result.country,
  ST_GeometryFromText(result.geom) geom
FROM rsp

Development / Publishing

To publish your own adaptions to the Serverless Application Repository as a private application, run the following:

# create a bucket if necessary and ensure that it has an appropriate bucket policy, similar to the one below
aws s3 mb s3://<bucket> --region <region>
AWS_REGION=<region> S3_BUCKET=<bucket> make publish

<bucket> must be readable by the Serverless Application Repository. This can be achieved by applying the following policy to the bucket:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "serverlessrepo.amazonaws.com"
      },
      "Action": "s3:GetObject",
      "Resource": "arn:aws:s3:::<bucket>/*"
    }
  ]
}

Once this runs successfully, you'll be able to view the (private) Serverless Application Repository entry for this application and deploy it.

Security

See CONTRIBUTING for more information.

License

This library is licensed under the MIT-0 License.