VOGO Voice – Knowledge Base

CARTO – Select

Estimated reading time: 6 min

CARTO, an open source tool built on PostGIS and PostgreSQL. It helps in storing and analyzing geospatial data by managing uploaded data and visualizing it with a web map based on the data inputs.

This instructional tutorial aims at illustrating a few use cases on how to interact with the CARTO database. Adopting a comprehensive methodology, the tutorial is dedicated towards presenting a guided step-by-step walk-through to understand how to configure a CARTO instance to retrieve the total count of data. More specifically, we will count the populated cities across the world, then filter the data by a specific input country name to count cities and narrow down the resultset to the top 5 cities based on its population count. 

Prerequisites: 

  •  VOGO Voice account: https://www.vogovoice.com/
  • Access rights to the Intents section under Settings.
  • Access rights to the Interaction builder platform.
  • CARTO Account username.
  • Understanding of how to write SQL queries.

Setting up a CARTO connector instance

In order to create a CARTO connector instance

  1. Log into your VOGO Voice account to be directed to VOGO Voice Management Console (https://account.vogovoice.com)
  2. Navigate to Settings on the top right-hand corner of the Dashboard and select Integration from the list of tabs displayed.
    Settings > Integration
  3. Click on the Add Button on the top left-hand corner of the screen.

4. In the Select Account Type window, choose the CARTO icon from the list of connectors and click Create
Connector button which opens up Add New CARTO Account window.
5. Populate the required fields in the Add CARTO Connector window.

Field values

Provide the username of the CARTO account. This is the same username used when signing into https://carto.com/login. Here, the username is cartojs-test.

The additional parameters in the Add CARTO Connector window are optional fields that need to be populated depending on the requirements of the skill. 
To learn more about the additional parameters refer to the Add CARTO connector guide: https://university.vogovoice.com/kb/carto/

6. After filling in the required fields click the Save button to set up and integrate the connector instance with the platform.

Create a custom intent

Create an appropriate intent to be invoked for executing an interaction flow mapped to it to capture the name of the country and provide a count of cities based on the population size.

Create an intent titled ‘Number of cities’ with a set of suitable sample utterances mapped to it. Let’s say the sample utterances are: “How many cities are there in the world”, “How many cities are there in {country}”, “How many cities in {country}” where ‘country’ is the slot name. Assign the slot type to ENVIRONMENT_Country, which is a global slot with predefined values. 

To learn how to create custom intents refer the guide: https://university.vogovoice.com/kb/creating-intents/

Steps to Configure the CARTO connector

Once the CARTO connector has been created navigate to Interactions and click on the Interaction button at the bottom right-hand corner of the screen. Select the ‘Number of cities’ intent to be directed into the Interaction builder platform.

Now let’s create an interaction flow to return a resultset of data representing the count of populated cities by capturing the input of country name. In the absence of a country name, the resultset will be retrieved based on the count of populated cities across the world. This action is implemented in the Response component through {{#if ….}} {{else}} template condition.

  1. n the flow diagram area, you will be able to see the Start and Response component.
  2. Roll the mouse over the Response component to see the voice, computer and camera icon on the top right-hand side of the component.
  3. Clicking on the Voice icon opens a Speak pop-up window with a field titled Say wherein you need to type the voice action intended to be spoken to the user in regard to Number of Cities intent.
    Say: In this example we will look at how to interact with a CARTO database to attain the count of populated cities. It will query a dataset of the world’s populated cities based on the user input country name and provide a list of the top 5 cities.
  4. Check the checkmark box in the Expect Response and type in the suitable Prompt message to proceed with the flow.
    Prompt: Say Next to continue.
    The same message will be replicated in the Reprompt field.
  5. Populate the field under Expectations by selecting the Global Intent Next from the drop-down list. 

6. Drag and drop the CARTO component onto the flow diagram area and connect the anchor symbol of Next on the
Response card to the CARTO component.
🖍 Note: To interconnect the different components click on their respective Anchor symbols so as to chain and hold them in the interaction flow.
7. Double click the CARTO component, or click the pencil to access the Configuration window and fill in the
required fields to query and retrieve the desired data from the specific dataset.

Setting up the configuration fields for c1

Name: The name is provided by default and can be changed manually. It is used to identify the connector instance and how it is referenced throughout the flow. 
Query: PostgreSQL SELECT statement syntax is used toquery data records from selected dataset of CARTO database. Here, the query is framed by means of an {{#if ….}} {{else}} template condition to retrieve data i.e. If the user provides the name of a country, the count of populated cities specific to the country will be retrieved whereas if there is no input value an overall count of the populated cities across the world is generated.  

{{#if inputs.country}}  
SELECT count(*)
FROM ne_10m_populated_places_simple 
WHERE adm0name = '{{inputs.country.value}}'
{{else}}  
SELECT count(*) 
FROM ne_10m_populated_places_simple
{{/if}}

8. Drag and drop another CARTO component onto the flow diagram area and connect its anchor symbol to the
previous CARTO component.
9. Double click the CARTO component, or click the pencil to access the Configuration window and fill in the required
fields.

Setting up the configuration fields for c2

Name: The name is provided by default and can be changed manually. It is used to identify the connector instance and how it is referenced throughout the flow. 

👍 Note: Ensure not to give the same name for both the connector instances. 

Query: Provide the query to retrieve the list of first 5 cities with its population size such that it produces the desired result whether or not the name of a country is specified by the user. 

{{#if inputs.country}}
SELECT name, pop_max FROM ne_10m_populated_places_simple WHERE adm0name = '{{inputs.country.value}}' LIMIT 5
{{else}}
SELECT name, pop_max FROM ne_10m_populated_places_simple LIMIT 5
{{/if}}

If you want to access the list of top 5 cities sorted in descending order of population size, the query will be as follows:

{{#if inputs.country}}
SELECT name, pop_max FROM ne_10m_populated_places_simple WHERE adm0name = '{{inputs.country.value}}' ORDER BY pop_max DESC LIMIT 5
SELECT name, pop_max FROM ne_10m_populated_places_simple ORDER BY pop_max DESC LIMIT 5
{{/if}}

10. Drag and drop another Response component onto the flow canvas. You can alternatively right-click on the anchor
icon and choose the response component.
11. Connect the second CARTO component to the Response card.
12. Click on the Voice icon of the Response card to open up the Speak pop-up window as mentioned before and type the voice action intended to be spoken to the user to provide data on the count of populated cities along with the listing the names of the top 5 cities. 
Say: {{#if inputs.country.value}}
   In {{inputs.country.value}} there are {{connectors.c1.first.count}} cities.
    {{else}}
   In the entire world, there are {{connectors.c1.first.count}} cities.
{{/if}}  
The top 5 cities are {{# each connectors.c2}}{{name}}
  {{/each}}.

To learn about different template engines to be followed to run functions visit https://university.vogovoice.com/kb/template-engine/

To add a display message in a video-enabled voice-assisted device

  1. Click on the Computer icon, and type in a condensed version of the voice message in the field titled Display Message of the Show pop-up window.
    2. Populate the required fields and click the Save button. 
    Title: Populated cities
    Display Message:
    {{#if inputs.country}}  
    Country: {{inputs.country.value}}  
    Cities: {{connectors.c1.first.count}}
    {{else}}
    Cities: {{connectors.c1.first.count}}
    {{/if}}
    Top Cites: {{# showList connectors.c2}}{{name}} ({{pop_max}}){{/showList}}

For further reference on how to populate the additional fields visit https://university.vogovoice.com/kb/guide-response-component/

The screenshot below captures the aforementioned interaction flow

Testing and Validation

  1. Click on the Test button next to the Flow Editor button on the interaction builder platform. 

2. In the Tests pop-up window that appears click the + button on its top right-hand corner. 

3. Fill the Name field of the Test Response window that shows up, then populate the field titled User invokes by selecting the
Next intent called to action within the interaction flow.    
1. Fill the ‘With slots’ field under Conversation with the name of a country (France) and click the Save button.

4. To validate the Test Response, click on the lightning symbol on the left-hand side of the test case. 

5. On the resultant Test Response window, the blue chat bubbles will cumulatively present the previously input message within
the Response components. The 2 lightning bolt symbols in yellow represents the CARTO connector instances. 

6. Click on the chat bubble to see the details of the individual responses. To take a detailed look at the data fetching part of the
connector instances, click on the respective lightning bolt icons highlighted in yellow. 

👍 Note: The testing can also be done without specifying the name of a country. To do so, the only variation in the aforementioned steps is to leave the With Slots field under Conversation empty.

To know more about testing an interaction refer https://university.vogovoice.com/kb/testings/

Congratulations! You have successfully created an interaction flow to retrieve data from  a CARTO database to attain the count of populated cities and provide a list of the top 5 cities.

👍 Note: To test the functioning of the interactions on your device you have to deploy the custom skill to facilitate the skill with the new edits. To accomplish this, click on the Deploy button seen at the bottom of the Skill Settings pop-up window.

To know how to deploy a custom skill visit https://university.vogovoice.com/kb/deploying-a-skill/

Tags:
Was this article helpful?
Dislike 0
Previous: Fibonacci
Next: CARTO inserting input data