Nextbillion.ai Snowflake Native App

This application provides wrappers for NextBillion.ai geospatial APIs in the form of Stored Procedures, designed to be used within the Snowflake environment.

Table of Contents

  1. Introduction
  2. Usage Instructions and Installation
  3. Supported APIs

Introduction

The NextBillion.ai Snowflake application ships wrappers for geospatial APIs, offering seamless integration of geocoding, routing, and isochrone functionalities via Stored Procedures. The stored procedures require inputs such as table_name, query_column, and result_column to operate within the Snowflake environment.

Usage Instructions and Installation

API Key Requirement

To run the stored procedures, you will need an API key. Please contact [email protected] or [email protected] to obtain your key.

Configuration

After installing the app, follow these steps to configure your connection settings and API key :

  1. Review Connection Settings
    • Navigate to Security > Connections.
    • Go to Connections and click Review.
    • View the allowed endpoints set to api.nextbillion.io
    • Click Connect to finalize the connection.
  2. Enter the API Key
    • Navigate to Security > Connections.
    • Go to Credentials and click Configure.
    • Enter your API key in the provided field.
    • Click Configure to save your changes.

Call Init APP

After connection is reviewed and API key is configured properly, call init_app() stored procedure.

Example:

1
CALL app_nb.init_app();

Grant Access To Consumer Tables

To allow the NextBillion.ai application access to certain databases, schemas, and tables within your Snowflake account, grant the required permissions. Ensure the prefix app_nb represents your installed application (replace it with the actual app name in your environment if it differs).

Example:

1
GRANT USAGE ON DATABASE <your_database> TO APPLICATION app_nb;
2
GRANT USAGE ON SCHEMA <your_schema> TO APPLICATION app_nb;
3
GRANT SELECT ON TABLE <your_table> TO APPLICATION app_nb;
4
GRANT UPDATE ON TABLE <your_table> TO APPLICATION app_nb;

Supported APIs

Directions (Fast) API

The Directions (Fast) API calculates the distance and duration of travel between two geographic points. It returns a JSON object with the calculated distance and estimated travel time.

Stored Procedure:

1
CALL directions_fast (
2
 DB_NAME VARCHAR,             -- Database name
3
 SCHEMA_NAME VARCHAR,         -- Schema name
4
 TABLE_NAME VARCHAR,          -- Table with start and end points
5
 RESULT_COLUMN VARCHAR,       -- Column to store the result (distance and duration)
6
 OVERWRITE BOOLEAN,           -- Overwrite existing data (false = do not overwrite)
7
 ORIGIN_LONGITUDE_COLUMN VARCHAR, -- Column for origin longitude
8
 ORIGIN_LATITUDE_COLUMN VARCHAR,  -- Column for origin latitude
9
 DESTINATION_LONGITUDE_COLUMN VARCHAR, -- Column for destination longitude
10
 DESTINATION_LATITUDE_COLUMN VARCHAR,  -- Column for destination latitude
11
 MODE_COLUMN VARCHAR          -- Column for mode
12
);

Return Value:
On success: {"distance": distance, "duration": duration}

For more details about the API, refer to the NextBillion.ai API documentation.

Example:

1
create or replace table db1.schema1.directions_fast_example(origin_lat varchar, origin_lon varchar, dest_lat varchar, dest_lon varchar,mode varchar, result varchar);
2
insert into db1.schema1.directions_fast_example values('1.30625100','103.81436443','1.30725100','103.85436443','4w','');
3
grant usage on database db1 to application app_nb;
4
grant usage on schema db1.schema1 to application app_nb;
5
grant select on table db1.schema1.directions_fast_example to application app_nb;
6
grant update on table db1.schema1.directions_fast_example to application app_nb;
7
call app_nb.app_public.directions_fast('db1','schema1','directions_fast_example','result',true,'origin_lon','origin_lat','dest_lon','dest_lat','mode');
8
-- db1.schema1.directions_fast_example table result column should have '{"distance":8.612600000000000e+03,"duration":1.031700000000000e+03}' after successful execution

Forward Geocode API

The Forward Geocode API converts an address or location into geographic coordinates (latitude and longitude).

Stored Procedure:

1
CALL geocode_forward (
2
 DB_NAME VARCHAR,              -- Database name
3
 SCHEMA_NAME VARCHAR,          -- Schema name
4
 TABLE_NAME VARCHAR,           -- Table with addresses
5
 RESULT_COLUMN VARCHAR,        -- Column to store latitude and longitude
6
 OVERWRITE BOOLEAN,            -- Overwrite existing data (false = do not overwrite)
7
 QUERY_COLUMN VARCHAR,         -- Column with addresses
8
 IN_COLUMN VARCHAR             -- Optional: restrict search within a geographic area
9
);

IN_COLUMN Details:
This parameter allows restricting the search within specific geographic areas. Examples include:

  • in=countryCode:CAN,MEX,USA to search within specific countries.
  • in=circle:52.53,13.38;r=10000 to search within a 10,000-meter radius of a central point.
  • in=bbox:13.08836,52.33812,13.761,52.6755 to search within a bounding box.

Return Value:
On success: {"lat": latitude, "lng": longitude}

For more details about the API, refer to the NextBillion.ai API documentation.

Example:

1
create or replace table db1.schema1.geocode_forward_example(query varchar, in_param varchar, result varchar);
2
insert into db1.schema1.geocode_forward_example values('125, Berliner, berlin','countryCode:DE','');
3
grant usage on database db1 to application app_nb;
4
grant usage on schema db1.schema1 to application app_nb;
5
grant select on table db1.schema1.geocode_forward_example to application app_nb;
6
grant update on table db1.schema1.geocode_forward_example to application app_nb;
7
call app_nb.app_public.geocode_forward('db1','schema1','geocode_forward_example','result',true,'query','in_param');
8
-- db1.schema1.geocode_forward_example result column should have '{"access":[{"lat":5.248655000000000e+01,"lng":1.331927000000000e+01}],"address":{"city":"Berlin","countryCode":"DEU","countryName":"Deutschland","county":"Berlin","district":"Wilmersdorf","houseNumber":"125","label":"Berliner Straße 125, 10713 Berlin, Deutschland","postalCode":"10713","state":"Berlin","stateCode":"Berlin","street":"Berliner Straße"},"id":"ARSSUTDTmdy_FxE-av5kBg","mapView":{"east":1.332074000000000e+01,"north":5.248727000000000e+01,"south":5.248547000000000e+01,"west":1.331778000000000e+01},"position":{"lat":5.248637000000000e+01,"lng":1.331926000000000e+01},"scoring":{"fieldScore":{"city":1,"postalCode":5.000000000000000e-01,"state":1,"streets":[8.400000000000000e-01]},"queryScore":1},"title":"Berliner Straße 125, 10713 Berlin, Deutschland"}'

Reverse Geocode API

The Reverse Geocode API converts geographic coordinates into a human-readable address.

Stored Procedure:

1
CALL geocode_reverse (
2
 DB_NAME VARCHAR,              -- Database name
3
 SCHEMA_NAME VARCHAR,          -- Schema name
4
 TABLE_NAME VARCHAR,           -- Table with lat/long data
5
 RESULT_COLUMN VARCHAR,        -- Column to store address
6
 OVERWRITE BOOLEAN,            -- Overwrite existing data (false = do not overwrite)
7
 LONGITUDE_COLUMN VARCHAR,     -- Column with longitude data
8
 LATITUDE_COLUMN VARCHAR       -- Column with latitude data
9
);

Return Value:
On success: Address string

For more details about the API, refer to the NextBillion.ai API documentation.

Example:

1
create or replace table db1.schema1.geocode_reverse_example(lat varchar, lon varchar, result varchar);
2
insert into db1.schema1.geocode_reverse_example values('1.30625100','103.81436443','');
3
grant usage on database db1 to application app_nb;
4
grant usage on schema db1.schema1 to application app_nb;
5
grant select on table db1.schema1.geocode_reverse_example to application app_nb;
6
grant update on table db1.schema1.geocode_reverse_example to application app_nb;
7
call app_nb.app_public.geocode_reverse('db1','schema1','geocode_reverse_example','result',true,'lon','lat');
8
-- db1.schema1.geocode_reverse_example column result should have '{"access":[{"lat":1.306453000000000e+00,"lng":1.038143390000000e+02}],"address":{"city":"Singapore","countryCode":"SGP","countryName":"Singapore","district":"Tanglin","label":"Holland Road, Singapore, 249537, Singapore","postalCode":"24","street":"Holland Road"},"distance":23,"id":"lvvb45HPUOn7zbCrgyCowg","mapView":{"east":1.038143490000000e+02,"north":1.306454000000000e+00,"south":1.306426000000000e+00,"west":1.038141020000000e+02},"position":{"lat":1.306453000000000e+00,"lng":1.038143390000000e+02},"speedLimit":"70.00KPH","title":"Holland Road, Singapore, 249537, Singapore"}'

Isochrone API

The Isochrone API generates a polygon representing areas reachable within a specified distance or time from a central point.

Stored Procedure:

1
CALL isochrone(
2
 DB_NAME VARCHAR,              -- Database name
3
 SCHEMA_NAME VARCHAR,          -- Schema name
4
 TABLE_NAME VARCHAR,           -- Table with location data
5
 RESULT_COLUMN VARCHAR,        -- Column to store GeoJSON polygon/linestring
6
 OVERWRITE BOOLEAN,            -- Overwrite existing data (false = do not overwrite)
7
 LONGITUDE_COLUMN VARCHAR,     -- Column for central longitude
8
 LATITUDE_COLUMN VARCHAR,      -- Column for central latitude
9
 MODE_COLUMN VARCHAR,          -- Column for mode
10
 CONTOURS_VALUE_COLUMN VARCHAR,-- Column with contour value (e.g., 10 for 10 minutes)
11
 CONTOURS_UNIT_COLUMN VARCHAR, -- Column with contour unit (minutes or meters)
12
 POLYGONS_COLUMN VARCHAR,      -- If true, returns a GeoJSON Polygon; if false, returns a Linestring
13
 DEPARTURE_TIME_COLUMN VARCHAR -- Departure time (optional, null if not required)
14
);

Return Value:
On success: GeoJSON Polygon or Linestring

For more details about the API, refer to the NextBillion.ai API documentation.

Example:

1
create or replace table db1.schema1.isochrone_example (lat varchar, lon varchar,mode varchar,value varchar,unit varchar,polygons boolean,departure varchar, result varchar);
2
insert into db1.schema1.isochrone_example values('1.30625100','103.81436443','','1','minutes',true,'','');
3
grant usage on database db1 to application app_nb;
4
grant usage on schema db1.schema1 to application app_nb;
5
grant select on table db1.schema1.isochrone_example to application app_nb;
6
grant update on table db1.schema1.isochrone_example to application app_nb;
7
call app_nb.app_public.isochrone('db1','schema1','isochrone_example','result',true,'lon','lat','mode','value','unit','polygons','departure');
8
-- db1.schema1.isochrone_example column result should have '{"features":[{"properties":{"fill":"#bf4040","fillOpacity":0.33,"fillColor":"#bf4040","color":"#bf4040","contour":1,"opacity":0.33,"metric":"time"},"geometry":{"coordinates":[[[103.810364,1.30853],[103.809224,1.308391],[103.810364,1.306147],[103.811364,1.30659],[103.812364,1.305805],[103.814572,1.306044],[103.814529,1.306415],[103.812999,1.306886],[103.812364,1.30768],[103.81067,1.307556],[103.810364,1.30853]]],"type":"Polygon"},"type":"Feature"}],"type":"FeatureCollection","status":"Ok"}'
PRODUCTS
SOLUTIONS
COMPARE
DEVELOPERS
GUIDES
RESOURCES
COMPANY
logo
UNITED STATES

NEXTBILLION.AI, INC.
21 E State Street,
Suite 200,
Columbus, OH 43215

SINGAPORE

#32-01, 80 Raffles Place, UOB Plaza,
Singapore, 048624

© 2024 NextBillion.ai all rights reserved.

Privacy PolicyTerms of ServiceSLASupport Services Agreement