Before you begin

• A Snowflake account on a supported cloud and region.
• The ACCOUNTADMIN role, or a role that can install a Native App and grant an external access integration.
• A running virtual compute cluster you can use for the session.
• A table of US addresses, or permission to create one.

Get the app from the Snowflake Marketplace

Already installed Address Geocode US and started your trial? Skip to Step 2.

If not: open the Address Geocode US listing on the Snowflake Marketplace and select Try Now to start the free trial.

The trial is for 20 days and includes 1,000 geocode lookups with full output including latitude and longitude, match level, and the census and jurisdiction fields. No credit card required.

Snowflake will ask you to name the installed application. The examples on this page use the default name SERVICE_OBJECTS_AGUS_APP and the application schema APP_CODE. If you chose a different application name, substitute it in Step 2.

Grant the external access integration

Address Geocode US runs inside your account. Before it can do anything, grant the installed application permission to reach the database and schema you are working in, and to create the table it writes results to.

GRANT USAGE ON DATABASE MY_DB
  TO APPLICATION SERVICE_OBJECTS_AGUS_APP;

GRANT USAGE ON SCHEMA MY_DB.PUBLIC
  TO APPLICATION SERVICE_OBJECTS_AGUS_APP;

GRANT CREATE TABLE ON SCHEMA MY_DB.PUBLIC
  TO APPLICATION SERVICE_OBJECTS_AGUS_APP;

Substitute your own database and schema for MY_DB and MY_DB.PUBLIC. If you are unsure what the application ended up being called, run SHOW APPLICATIONS; and use the name listed there.

The app reaches the Service Objects geocoding service through a single external access integration. You can inspect it in the app manifest at any time, and you can revoke it at any time.

Prepare your address data

The app reads five columns, and the order is fixed. Every column has to be present in the SELECT statement you pass in, even for records that have no value for it. Pass an empty value rather than dropping the column.

To follow along without touching production data, create a small test table that covers the range of input quality you actually hold:

CREATE OR REPLACE TABLE MY_SCHEMA.TEST_ADDRESSES (
  CONSUMER_ROW_ID NUMBER,
  ADDRESS         VARCHAR,
  CITY            VARCHAR,
  STATE           VARCHAR,
  POSTALCODE      VARCHAR
);

INSERT INTO MY_SCHEMA.TEST_ADDRESSES VALUES
  (1, '27 E Cota St',              'Santa Barbara', 'CA', '93101'),
  (2, '1600 Pennsylvania Ave NW',  'Washington',    'DC', '20500'),
  (3, '123 Main Street',           'Springfield',   'IL', NULL),
  (4, '',                          '',              NULL, '10001'),
  (5, '4 Privet Drive',            'Nowhere',       'XX', '00000');

Those five cover a clean full address, a well known landmark, a street with no postal code, a postal code on its own, and a record that should not resolve at all. Note that the missing values are empty strings, not omitted columns.

Run the geocode

Geocoding runs as a stored procedure. You call it, it writes a results table, and you query that table. Supply a SELECT statement and the path where you want results written.

CALL APP_CODE.AGUS_GET_BEST_MATCHES_V4(
  'SELECT CONSUMER_ROW_ID, ADDRESS,
          CITY, STATE, POSTALCODE
   FROM MY_SCHEMA.TEST_ADDRESSES',
  'MY_SCHEMA.AGUS_RESULTS'
);

Read the results

Query the results table the call produced:

SELECT * FROM MY_SCHEMA.AGUS_RESULTS LIMIT 10;

These are the columns you act on. They are the location itself, and how precisely the app was able to resolve it.

Each record also comes back with the jurisdictions and identifiers its location falls inside: state and county, place name, census tract, block and GeoID, congressional and state legislative districts, FIPS codes, the UTC offset, and ready made Google, Bing and MapQuest links. Those are what make the output joinable to public datasets, and the full field list is in the Service Objects documentation for this operation.

Before you use the coordinates for anything, check how precisely your records actually resolved. This counts your results by level, so you can see at a glance how many matched at the property and how many fell back to something wider:

SELECT
  MATCH_LEVEL,
  COUNT(*) AS RECORDS
FROM MY_SCHEMA.AGUS_RESULTS
GROUP BY 1
ORDER BY 2 DESC;

Move to production

When the trial ends, you can continue to the Marketplace listing’s paid plan. If your organization has a Snowflake capacity commitment, Marketplace Capacity Drawdown lets you apply a percentage of that committed capacity toward the purchase, so the spend runs through an agreement you already have rather than a new one.