Create External User Defined Function for Geocode

This section describes the requirements for creating external defined function named ‘geocode’ by using these examples.

Below important headers must be passed in external defined function:
  • headers-api-key: It expects DIS API's production environment key.
  • headers-api-secret: It expects DIS API's production environment secret.
  • application: Snowflake users need to define dis-geoaddressing.
There are three types of geocode methods:
  1. With (address, country) as a parameter.
  2. With (array) as a parameter.
  3. With (object) as a parameter.
Currently, geocoding is supported for input addresses in five address formats: Format 1, Format 2, Format 3, Format 4, and Format 5. We recommend using Format 5.
Note: The user should replace ‘<<DIS API KEY>>’ and ‘<<DIS API SECRET>>’ with own API Key and API Secret.
Note: Country is a mandatory input for Geocoding using any Format, failing to provide a Country will lead to unsuccessful Geocode.
Note: All the Formats support secure User Defined Functions. For more information see Protecting Sensitive Information with Secure UDF's

Type 1

This script is used for input addresses of Format 1 type and expects address and country as input parameters.
create or replace external function geocode(address string ,country  string )
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
HEADERS = ('headers-api-key'= '<<DIS API KEY>>', 'headers-api-secret'='<<DIS API SECRET>>',
    'application'='dis-geoaddressing')
MAX_BATCH_ROWS = 25
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode'
Figure 1. Creating External User-Defined Function of Type 1

Type 2

This script is used for input addresses of Format 2, Format 3, and Format 4 types and expects array as an input parameter.
create or replace external function geocode(address array)
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
HEADERS = ('headers-api-key'= '<<DIS API KEY>>', 'headers-api-secret'='<<DIS API SECRET>>',
    'application'='dis-geoaddressing')
MAX_BATCH_ROWS = 25
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';
Figure 2. Creating External User-Defined Function of Type2

Type 3

This script is used for input addresses of Format 5 type and expects JSON object as an input parameter. 
create or replace external function geocode(address object)
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
HEADERS = ('headers-api-key'= '<<DIS API KEY>>', 'headers-api-secret'='<<DIS API SECRET>>'
    ,'application'='dis-geoaddressing')
MAX_BATCH_ROWS = 25
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';