Perform Geocode Operation

This section has the scripts required to perform geocode operation for different types of formats.

Below are the examples showing use of Verify operation with different Formats.

Note: We recommend to use Format 5 to perform Geocoding in Snowflake.

Performing Geocoding in Snowflake environment by using Format 1:

create or replace table all_data_result as 
select ID,addressline1,city, state, zip, geocode(      
    iff (ADDRESSLINE1 is not null, ADDRESSLINE1,'') 
    || iff (city is not null, ','||city,'')
    || iff (state  is not  null, ','||state,'')
    || iff (zip  is not  null, ','||zip,'')
    ,country) as address, country from all_data;

Performing Geocoding in Snowflake environment by using Format 2:

create or replace table all_data_result as 
  select ID,addressline1,city,state,zip, geocode(array_construct(    
    iff(ADDRESSLINE1 is not null, ADDRESSLINE1,'') 
    ||iff(city is not null, ','||city,'')
    ||iff(state  is not  null, ','||state,'')
    ||iff(zip  is not  null, ','||zip,''),country)) 
    as address,country from all_data;

Performing Geocoding in Snowflake environment by using Format 3:

create or replace table all_data_result as 
    select ID,addressline1,city,state,zip, geocode(
      array_construct( 
        'addressLines, iff(ADDRESSLINE1 is not null, ADDRESSLINE1,''),
        'country',country     
      ))
    as address,country from all_data;

Performing Geocoding in Snowflake environment by using Format 4:

create or replace table all_data_result as 
  select ID,addressline1,city,state,zip, geocode(
    array_construct( 
      'addressLines', iff(ADDRESSLINE1 is not null, ADDRESSLINE1,'')
      ,'country',country
      ,'city',iff(city is not null, city,'')
      ,'admin1',iff(state  is not  null, state,'')
     ,'postalCode',iff(zip  is not  null, zip,'')
  ))
    as address,country from all_data ;

Performing Geocoding in Snowflake environment by using Format 5 (Recommended):

create or replace table all_data_result as 
  select ID,addressline1,city,state,zip,  
    object_construct( 
      'addressId', iff(ID is not null, ID,'')
      ,'addressLines', array_construct(iff(ADDRESSLINE1 is not null, ADDRESSLINE1,''))
      ,'country',country
      ,'city',iff(city is not null, city,'')
      ,'admin1',iff(state  is not  null, state,'')
     ,'postalCode',iff(zip  is not  null, zip,'')
  ) as sentObject
  , geocode(sentObject) as address,country from all_data;