aurora-udfs

Amazon Aurora UDFs for Amazon Location Service

This is a set of AWS Lambda and user-defined functions for Amazon Aurora PostgreSQL that enable querying Amazon Location Service using SQL. These facilitate cleaning, validating, and enriching data in place.

Deploying

This will deploy an AWS CloudFormation stack containing an Amazon Location place index resource, AWS Lambda functions callable from Aurora PostgreSQL that query the place index, all required IAM policies and roles, and an optional AWS Lambda VPC Endpoint (required if your Aurora PostgreSQL cluster is not publicly-accessible). The Lambdas' Reserved Concurrency will be set to 10 by default, which will limit the amount of rate-limiting applied by Amazon Location, given default transaction limits.

Once deployed, you will need to enable calling Lambda functions from Aurora (step 4 of Giving Aurora access to Lambda). <Aurora Lambda Role ARN> will be provided in the CloudFormation stack's Outputs.

aws rds \
  add-role-to-db-cluster \
  --db-cluster <DB cluster name> \
  --role-arn <Aurora Lambda Role ARN> \
  --feature-name Lambda

You will also need to register the UDFs (and the extensions they depend on) within your database. To do so, replace AuroraUDFs (if you didn't use the default stack name) with the name of your CloudFormation Stack in each function definition within sql/, and load each into your database using your PostgreSQL client of choice.

f_SearchPlaceIndexForText

f_SearchPlaceIndexForText wraps SearchPlaceIndexForText. Each element in the response will be mapped to a column, with Geometry surfaced as a PostGIS geometry, which can be converted to WKT using ST_AsText. For example:

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

label	address_number	street	municipality	postal_code	sub_region	region	country	geom	wkt
Vancouver, British Columbia, CAN			Vancouver		Metro Vancouver	British Columbia	CAN	0101000000282B4D4A41C75EC03044C02154A14840	POINT(-123.11336 49.2603800000001)

By default, the return will contain one row. To request additional rows, up to the MaxResults limit, run the following SQL statement while providing a BiasPosition and limiting to results in Canada.

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

label	address_number	street	municipality	postal_code	sub_region	region	country	geom
Mt Pleasant, Vancouver, British Columbia, CAN			Vancouver		Metro Vancouver	British Columbia	CAN	01010000002C73BA2C26C65EC0A05FCD0182A14840
Mount Pleasant, Vancouver, British Columbia, CAN			Vancouver			British Columbia	CAN	0101000000B8D05CA791C65EC0407BA01518A24840
Mt Pleasant, Vancouver, British Columbia, CAN			Vancouver		Metro Vancouver	British Columbia	CAN	0101000000A0CC069964C65EC070BD18CA89A24840
Mount Pleasant, Saskatchewan, CAN						Saskatchewan	CAN	010100000080471B47AC595AC040B4C876BE3B4940
Mount Pleasant, Ontario, CAN						Ontario	CAN	0101000000E4C1DD59BB9F53C0C0B9313D61214640

To filter results using a bounding box, then pass a Box2D or polygon as filter_bbox:

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

label	address_number	street	municipality	postal_code	sub_region	region	country	geom
Mt Pleasant, Calgary, Alberta, CAN			Calgary		Alberta	Alberta	CAN	010100000064834C3272845CC058158C4AEA884940
Mt Pleasant, Vancouver, British Columbia, CAN			Vancouver		Metro Vancouver	British Columbia	CAN	01010000002C73BA2C26C65EC0A05FCD0182A14840
Mt Pleasant, Vancouver, British Columbia, CAN			Vancouver		Metro Vancouver	British Columbia	CAN	0101000000A0CC069964C65EC070BD18CA89A24840
Mount Pleasant, Calgary, Alberta, CAN			Calgary			Alberta	CAN	0101000000088A1F63EE845CC0E8B4E0455F894940
Mount Pleasant, Vancouver, British Columbia, CAN			Vancouver			British Columbia	CAN	0101000000B8D05CA791C65EC0407BA01518A24840

For more information on PostGIS types and functions, see the PostGIS Reference.

f_SearchPlaceIndexForPosition

f_SearchPlaceIndexForPosition wraps SearchPlaceIndexForText. Each element in the response will be mapped to a column, with [PostGIS geometries]('POINT(-122.46729 37.80575)') used as input and output types. This will automatically cast a WKT input to a geometry and convert the output geometry:

SELECT
  *,
  ST_AsText(geom) wkt
FROM f_SearchPlaceIndexForPosition('POINT(-122.46729 37.80575)');

label	address_number	street	municipality	postal_code	sub_region	region	country	geom	wkt
Crissy Field, 603 Mason St, San Francisco, CA, 94129, USA	603		San Francisco	94129	City and County of San Francisco	California	USA	0101000000DCEF5014E89D5EC04860E5D022E74240	POINT(-122.46729 37.80575)

For more information on PostGIS types and functions, see the PostGIS Reference.

Security

See CONTRIBUTING for more information.

License

This library is licensed under the MIT-0 License. See the LICENSE file.