Skip to content

bigfunctions > validate_address

validate_address

Call or Deploy validate_address ?

✅ You can call this validate_address bigfunction directly from your Google Cloud Project (no install required).

  • This validate_address function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
  • Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • You may prefer to deploy the BigFunction in your own project if you want to build and manage your own catalog of functions. This is particularly useful if you want to create private functions (for example calling your internal APIs). Discover the framework

Public BigFunctions Datasets:

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...

Description

Signature

validate_address(address)

Description

Validate address using Google Maps

Examples

1. Correct address

select bigfunctions.eu.validate_address('1 Avenue des Champs-Élysées, 75008 Paris, France')
select bigfunctions.us.validate_address('1 Avenue des Champs-Élysées, 75008 Paris, France')
select bigfunctions.europe_west1.validate_address('1 Avenue des Champs-Élysées, 75008 Paris, France')
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| validation_result                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {'result': {'verdict': {'inputGranularity': 'PREMISE',
  'validationGranularity': 'PREMISE',
  'geocodeGranularity': 'PREMISE',
  'addressComplete': True},
  'address': {'formattedAddress': '1 Avenue des Champs-Élysées, 75008 Paris, France',
  ...},
  'geocode': {'location': {'latitude': 48.8698877, 'longitude': 2.3079341},
  ...,
  'bounds': {'low': {'latitude': 48.8698877, 'longitude': 2.3079341},
    'high': {'latitude': 48.8698877, 'longitude': 2.3079341}},
  ...}},
'responseId': ...}
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2. Address with inference

select bigfunctions.eu.validate_address('1 Avenue des Champs-Élysées, 75008 Paris, France')
select bigfunctions.us.validate_address('1 Avenue des Champs-Élysées, 75008 Paris, France')
select bigfunctions.europe_west1.validate_address('1 Avenue des Champs-Élysées, 75008 Paris, France')
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| validation_result                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {'result': {'verdict': {'inputGranularity': 'PREMISE',
  'validationGranularity': 'PREMISE',
  'geocodeGranularity': 'PREMISE',
  'addressComplete': True,
  'hasUnconfirmedComponents': True,
  'hasInferredComponents': True},
  'address': {
    'formattedAddress': '1 Avenue des Champs-Élysées, 75008 Paris, France',
    'addressComponents': [
      {'componentName': {'text': '1', 'languageCode': 'fr'},
        'componentType': 'street_number',
        'confirmationLevel': 'CONFIRMED'},
      {'componentName': {'text': 'rue des champs elysees', 'languageCode': 'fr'},
        'componentType': 'route',
        'confirmationLevel': 'UNCONFIRMED_BUT_PLAUSIBLE'},
      ...,]
    'unconfirmedComponentTypes': ['route']}
  'geocode': {'location': {'latitude': 48.8698877, 'longitude': 2.3079341},
  ...,
  'bounds': {'low': {'latitude': 48.8698877, 'longitude': 2.3079341},
    'high': {'latitude': 48.8698877, 'longitude': 2.3079341}},
  ...}},
'responseId': ...}
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3. Route granularity

select bigfunctions.eu.validate_address('Avenue des Champs-Élysées, 75008 Paris, France')
select bigfunctions.us.validate_address('Avenue des Champs-Élysées, 75008 Paris, France')
select bigfunctions.europe_west1.validate_address('Avenue des Champs-Élysées, 75008 Paris, France')
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| validation_result                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {'result': {'verdict': {inputGranularity': 'ROUTE',
  'validationGranularity': 'ROUTE',
  'geocodeGranularity': 'ROUTE'},
  'address': {
    'formattedAddress': 'Avenue des Champs-Élysées, 75008 Paris, France',
    'addressComponents': [
      {'componentName': {'text': 'Avenue des Champs-Élysées',
        'languageCode': 'fr'},
        'componentType': 'route',
        'confirmationLevel': 'CONFIRMED'},
      ...,]
    'missingComponentTypes': ['street_number']}
  'geocode': {'location': {'latitude': 48.8729602, 'longitude': 2.2978526},
  ...,
  'bounds': {'low': {'latitude': 48.8655318, 'longitude': 2.2952047},
    'high': {'latitude': 48.8748338, 'longitude': 2.3200376}},
  ...}},
'responseId': ...}
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Need help using validate_address?

The community can help! Engage the conversation on Slack

For professional suppport, don't hesitate to chat with us.

Found a bug using validate_address?

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

For professional suppport, don't hesitate to chat with us.

Use cases

A practical use case for the validate_address function is cleaning and standardizing address data in a customer database.

Imagine you have a large table of customer data in BigQuery, including a column with their addresses. These addresses might have been entered manually or collected from various sources, leading to inconsistencies like:

  • Different formats: "123 Main St", "123 Main Street", "123 Main St.", etc.
  • Typos: "123 Main Sreet", "124 Main St", etc.
  • Missing information: Some addresses might be missing city, state, or zip code.

You can use the validate_address function within a BigQuery query to process these addresses and improve their quality:

SELECT
    original_address,
    bigfunctions.us.validate_address(original_address).result.address.formattedAddress AS standardized_address,
    bigfunctions.us.validate_address(original_address).result.verdict.validationGranularity AS validation_granularity,
    bigfunctions.us.validate_address(original_address).result.verdict.geocodeGranularity AS geocode_granularity
FROM
    `your_project.your_dataset.your_customer_table`;

This query will:

  1. Standardize the format: The formattedAddress field in the function's output will provide a consistent format for all valid addresses.
  2. Correct minor errors: The function can often correct typos and infer missing information.
  3. Identify invalid addresses: By checking the validationGranularity and geocodeGranularity, you can identify addresses that are completely invalid or only partially valid (e.g., only the street is valid). You can then flag these addresses for manual review or further investigation.

This standardized and validated address data can then be used for various purposes, such as:

  • Geocoding: Accurately map customer locations for visualizations or analyses.
  • Logistics: Optimize delivery routes and calculate shipping costs.
  • Marketing: Target specific geographic areas with advertising campaigns.
  • Data integration: Improve the accuracy and consistency of data when integrating with other systems.

By using the validate_address function, you can significantly enhance the quality and usability of your customer address data. This leads to more accurate analyses, improved operational efficiency, and better business decisions.

Spread the word

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on