This Quickstart will help you get started with the Mapbox Snowflake Native App, which adds new stored procedures for geocoding, navigation, and Mapbox boundaries APIs and data that you can use in your SQL queries. Geocoding converts your address data (e.g. "1600 Pennsylvania Ave, Washington, DC, 20500") into geographic coordinates which can be used for mapping and spatial analysis. Navigation stored procedures help calculate distance and duration between start and destination points, and calculate isochrones. Mapbox boundaries stored procedures allow you to do point-in-polygon lookups against the Mapbox boundaries dataset.

Mapbox geocoding, navigation, and boundaries APIs and data are used by companies for a variety of purposes, including asset tracking, supply chain and retail strategy, and analysis of demographic trends, elections, and real estate.

The Mapbox Snowflake Native App is powered by Mapbox APIs and the boundaries lookup is powered by Mapbox Boundaries.

logos

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

© 2023 Mapbox, Inc

Step 1

After you install the Mapbox application, go to "Data products", then click on "Apps", then click on the Mapbox app. Then from the Mapbox app, click on the "SETUP" link in the upper-left side of the screen and follow the steps. These steps will help create an API Integration and external functions required for the application to work.

Once you complete the steps using the "SETUP" link, return here to finish the rest of the installation steps.

Step 2

You will need to do this step any time you want the Mapbox application to be able to access certain databases, schemas, and tables within your Snowflake account, specifying the names of those specific resources in your Snowflake account. The below is an example.

Grant access to allow the application to access databases, schemas, and tables within your Snowflake account. The below assumes you have a database called "mydatabase" with a schema called "testing", and a table called "sample_addresses". Also, if you did not name the application exactly "mapbox", change "mapbox" to the exact name of the Mapbox application.

GRANT USAGE ON DATABASE mydatabase to application mapbox;
GRANT USAGE ON SCHEMA mydatabase.testing to application mapbox;
GRANT SELECT ON TABLE mydatabase.testing.sample_addresses to application mapbox;
GRANT UPDATE ON TABLE mydatabase.testing.sample_addresses to application mapbox;

Note that some of the examples in the "Usage" section below use sample data from sample tables provided by the application. The application does not need additional privileges to run against those sample tables and data provided by the application.

A general usage note: stored procedures must pass all parameters in the stored procedure signature. Pass "null" if you do not wish to use a given parameter. All of the examples act on sample data that comes with the application. Wherever you see "mapbox" as a parameter in the stored procedure examples, if you did not name the native app "mapbox" you must change "mapbox" to match whatever you named the app.

The geocoding stored procedures comply with the Mapbox Geocoding V6 API.

geocode_forward

Perform a forward geocode. Refer to API documentation here.

Procedure signature

CALL geocode_forward_enrich (
  DB_NAME VARCHAR,
  SCHEMA_NAME VARCHAR,
  TABLE_NAME VARCHAR,
  DESTINATION_COLUMN_NAME VARCHAR,
  INCREMENTAL BOOLEAN,
  QUERY VARCHAR,
  PARAM_BBOX VARCHAR,
  PARAM_COUNTRY VARCHAR,
  PARAM_FORMAT VARCHAR,
  PARAM_LANGUAGE VARCHAR,
  PARAM_PROXIMITY VARCHAR,
  PARAM_TYPES VARCHAR,
  PARAM_WORLDVIEW VARCHAR
)

All parameters must be passed to the stored procedure, but if you do not wish to use any particular parameters, pass null for that parameter. For all parameters that start with "PARAM" you may pass in a string literal value instead of referencing a table column name by surrounding it in three single-quotes, like ‘‘‘myvalue'''.

Differences from normal Mapbox API

The following parameters mentioned in the Mapbox Geocoding V6 API documentation cannot be changed:

Examples

CALL mapbox.core.geocode_forward_enrich (
  'mapbox',
  'sample_data',
  'geocode_forward',
  'response',
  false,
  'query',
  null,
  null,
  null,
  null,
  null,
  null,
  null
);

This will update the mapbox.sample_data.geocode_forward table's "response" column and populate it with the forward geocoding result for each row

geocode_reverse

Perform a reverse geocode using the Mapbox Geocoding V6 endpoint. Refer to API documentation here.

Procedure signature

CALL mapbox.core.geocode_reverse_enrich (
  DB_NAME VARCHAR,
  SCHEMA_NAME VARCHAR,
  TABLE_NAME VARCHAR,
  DESTINATION_COLUMN_NAME VARCHAR,
  INCREMENTAL BOOLEAN,
  LONGITUDE VARCHAR,
  LATITUDE VARCHAR,
  PARAM_COUNTRY VARCHAR,
  PARAM_LANGUAGE VARCHAR,
  PARAM_TYPES VARCHAR,
  PARAM_WORLDVIEW VARCHAR
);

All parameters must be passed to the stored procedure, but if you do not wish to use any particular parameters, pass null for that parameter. For all parameters that start with "PARAM" you may pass in a string literal value instead of referencing a table column name by surrounding it in three single-quotes, like ‘‘‘myvalue'''.

Differences from normal Mapbox API

The following parameters mentioned in the Mapbox Geocoding V6 API documentation cannot be changed:

Examples

CALL mapbox.core.geocode_reverse_enrich (
  'mapbox',
  'sample_data',
  'geocode_reverse',
  'response',
  false,
  'longitude',
  'latitude',
  null,
  null,
  null,
  null
);

This will update the mapbox.sample_data.geocode_reverse table's "response" column and populate it with the reverse geocoding result for each row

geocode_structured

Perform a structured forward geocode using the Mapbox Geocoding V6 endpoint. Refer to API documentation here.

Procedure signature

CALL mapbox.core.geocode_structured_enrich (
  DB_NAME VARCHAR,
  SCHEMA_NAME VARCHAR,
  TABLE_NAME VARCHAR,
  DESTINATION_COLUMN_NAME VARCHAR,
  INCREMENTAL BOOLEAN,
  ADDRESS_LINE1 VARCHAR,
  ADDRESS_NUMBER VARCHAR,
  STREET VARCHAR,
  BLOCK VARCHAR,
  PLACE VARCHAR,
  REGION VARCHAR,
  POSTCODE VARCHAR,
  LOCALITY VARCHAR,
  NEIGHBORHOOD VARCHAR,
  COUNTRY VARCHAR
);

All parameters must be passed to the stored procedure, but if you do not wish to use any particular parameters, pass null for that parameter. For all parameters that start with "PARAM" you may pass in a string literal value instead of referencing a table column name by surrounding it in three single-quotes, like ‘‘‘myvalue'''.

Differences from normal Mapbox API

None

Examples

CALL mapbox.core.geocode_structured_enrich (
  'mapbox',
  'sample_data',
  'geocode_structured',
  'response',
  false,
  null,
  'address_number',
  'address_street',
  null,
  'address_place',
  'address_region',
  null,
  null,
  null,
  null
);

This will update the mapbox.sample_data.geocode_structured table's "response" column and populate it with the structured geocoding result for each row

Distance and duration

The distance_and_duration_enrich procedure provides simplified access to the Mapbox Matrix API with the primary difference being it can only calculate the distance and duration between two points. The result is an OBJECT value with structured contents that match the JSON responses provided by the Mapbox Matrix API.

Procedure signature

CALL mapbox.core.distance_and_duration_enrich(
  DB_NAME VARCHAR,
  SCHEMA_NAME VARCHAR,
  TABLE_NAME VARCHAR,
  DESTINATION_COLUMN_NAME VARCHAR,
  INCREMENTAL BOOLEAN,
  START_LONGITUDE VARCHAR,
  START_LATITUDE VARCHAR,
  END_LONGITUDE VARCHAR,
  END_LATITUDE VARCHAR,
  PROFILE VARCHAR
);

All parameters must be passed to the stored procedure, but if you do not wish to use any particular parameters, pass null for that parameter.

Differences from the normal Mapbox API

Examples

Get distance and duration for two points:

CALL mapbox.core.distance_and_duration_enrich (
  'mapbox',
  'sample_data',
  'distance_and_duration',
  'response',
  false,
  'start_longitude',
  'start_latitude',
  'end_longitude',
  'end_latitude',
  'mapbox/walking'
);

Isochrone

The isochrone_enrich procedure provides a simplified interface to the Mapbox Isochrone API. The result is a polygon GEOGRAPHY value.

Procedure signature

CALL mapbox.core.isochrone_enrich(
  DB_NAME VARCHAR,
  SCHEMA_NAME VARCHAR,
  TABLE_NAME VARCHAR,
  DESTINATION_COLUMN_NAME VARCHAR,
  INCREMENTAL BOOLEAN,
  LONGITUDE VARCHAR,
  LATITUDE VARCHAR,
  PROFILE VARCHAR,
  DISTANCE VARCHAR,
  METRIC VARCHAR
);

The value of metric must be either ‘minutes' or ‘meters'.

All parameters must be passed to the stored procedure, but if you do not wish to use any particular parameters, pass null for that parameter.

Differences from the normal Mapbox API

Unlike the Mapbox API, the isochrone procedure will provide only one contour at a time.

The following parameters mentioned in the Mapbox Isochrone API documentation cannot be changed:

Examples

CALL mapbox.core.isochrone_enrich (
  'mapbox',
  'sample_data',
  'isochrone',
  'response',
  false,
  'longitude',
  'latitude',
  'mapbox/walking',
  15,
  'minutes'
)

mapbox_boundaries_reverse_enrich

Perform a point-in-polygon lookup on Mapbox boundaries data. This stored procedure is designed to be given a database, schema, and table on which to operate, and will fill in a specified column in that table with the specified boundaries layer.

Stored procedure signature

CALL mapbox.core.mapbox_boundaries_reverse_enrich(
  DB_NAME STRING,
  SCHEMA_NAME STRING,
  TABLE_NAME STRING,
  DESTINATION_COLUMN_NAME STRING,
  INCREMENTAL BOOLEAN,
  SOURCE_LONGITUDE_COLUMN_NAME VARCHAR,
  SOURCE_LATITUDE_COLUMN_NAME VARCHAR,
  PARAM_BOUNDARIES_SET VARCHAR,
  PARAM_WORLDVIEW VARCHAR
)

Examples

The below example uses a sample schema and table that comes with the Mapbox app. It uses:

This stored procedure call will populate the provided table in-place with boundaries data in the specified destination column

CALL mapbox.core.mapbox_boundaries_reverse_enrich(
  'mapbox',
  'sample_data',
  'sample_points',
  'BOUNDARIES_BASIC',
  false,
  'longitude',
  'latitude',
  'basic',
  null
)

Keep in mind that you must grant access to allow the application to access databases, schemas, and tables within your Snowflake account. The below example assumes you have a database called "mydatabase" with a schema called "testing", and a table called "sample_points".

GRANT USAGE ON DATABASE mydatabase to application mapbox
GRANT USAGE ON SCHEMA mydatabase.testing to application mapbox
GRANT SELECT ON TABLE mydatabase.testing.sample_points to application mapbox
GRANT UPDATE ON TABLE mydatabase.testing.sample_points to application mapbox

Using response values

Responses to geocoding stored procedures as well as responses returned from the boundaries stored procedure are in JSON format. You can read more here about how to access properties within JSON reponses. Below are some specific examples.

Access a specific property

Take the following example JSON response, which is the same structure that would be in the sample_data.geocode_reverse table if you ran the geocode_reverse_enrich example above:

{
  "accuracy": "rooftop",
  "confidence": "exact",
  "context": {
    "country": {
      "country_code": "US",
      "country_code_alpha_3": "USA",
      "mapbox_id": "dXJuOm1ieHBsYzpJdXc",
      "name": "United States",
      "wikidata_id": "Q30"
    },
    "district": {
      "mapbox_id": "dXJuOm1ieHBsYzpudWJz",
      "name": "Hillsborough County",
      "wikidata_id": "Q488874"
    },
    "neighborhood": {
      "mapbox_id": "dXJuOm1ieHBsYzpEWW1NN0E",
      "name": "Fountain Square"
    },
    "place": {
      "alternate": {
        "mapbox_id": "dXJuOm1ieHBsYzpFTkRvN0E",
        "name": "Rocky Point"
      },
      "mapbox_id": "dXJuOm1ieHBsYzpFejVJN0E",
      "name": "Tampa",
      "wikidata_id": "Q49255"
    },
    "postcode": {
      "mapbox_id": "dXJuOm1ieHBsYzpCc0J1N0E",
      "name": "33607"
    },
    "region": {
      "mapbox_id": "dXJuOm1ieHBsYzpCZVRz",
      "name": "Florida",
      "region_code": "FL",
      "region_code_full": "US-FL",
      "wikidata_id": "Q812"
    }
  },
  "feature_type": "address",
  "latitude": 27.97981,
  "longitude": -82.534586,
  "mapbox_id": "dXJuOm1ieGFkcjo5ZTQzZGI5Ni1mNzhkLTQ1MGEtOTEwYy0wYTM3ZDMyNmMxMzg",
  "name": "4100 George J. Bean Parkway",
  "place_formatted": "Tampa, Florida 33607, United States"
}

You can access a specific property in the JSON response like the following:

SELECT response:name from mapbox.sample_data.geocode_forward;

This will return only the value of the "name" property in the JSON response.

If the property is multiple levels deep in the JSON hierarchy, such as the "country_code" beneath the country property, which is beneath the context object, you can do:

SELECT response:context:country:country_code from mapbox.sample_data.geocode_forward;

Earlier versions of the application included user-defined functions (UDFs) for accessing geocoding functionality. These UDFs have been replaced with stored procedures. This table shows the name of the stored procedures that supercede each UDF

old UDF name

new stored procedure name

geocode_forward

geocode_forward_enrich

geocode_reverse

geocode_reverse_enrich

geocode_structured

geocode_structured_enrich

Congratulations! You've successfully completed the Getting Started with Mapbox Geocoding & Analysis Tools quickstart guide.

What You Learned

Related Resources