VOGO Voice – Knowledge Base

CARTO: Near Me

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 interrelated 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 retrieve the nearest cities either by providing hardcoded values or by using the stipulated syntax to capture the location of the device. The tutorial also focuses on how to retrieve a record of the nearest cities where the population size is greater than the provided value.

Prerequisites: 

  •  VOGO Voice account: https://www.vogovoice.com/
  • Access rights to the Intents section under Settings.
  • Access rights to Interaction builder platform.
  • CARTO Account username.
  • Alexa device (In the case of sending device location information as geometry input choose an appropriate device such as Alexa Auto, Alexa Echo Buds or Alexa app on the phone)
  • 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 retrieve data on the nearest cities and provide a record of the cities where the population size is greater than the provided value.

Create an intent titled ‘Nearest City’ with a set of suitable sample utterances mapped to it. Let’s say the sample utterances are: “What cities are near me”, “What cities near me that have a population greater than {PopSize}”, “What cities near me that have a population greater than {PopSize} people” where ‘PopSize’ is the slot name. Assign the slot type to ENVIRONMENT_Number, 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 ‘Nearest City’ intent to be directed into the Interaction builder platform.

Now let’s create an interaction flow to return a resultset of data by employing a two way querying. The foremost query would include a basic one in order to retrieve a record of the nearest cities and limit it to a record of three. Whereas, the second part of the query would be formulated by adding a filter for population size so that the retrieved records are based on the input population size alongside its distance. In order for the resultset to generate data based on the distance an example is provided to capture the location of the device in order to access the nearest populated cities. This action is implemented through {{#if ….}} {{else}} template condition.

  1. In 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 Nearest City intent.
    Say: In this example we will look at how to interact with a CARTO database to attain the nearest cities by the input geolocation. It will query a dataset of the populated cities across the world based on the user input population size and provide a record of 3 nearest 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 to query data records from selected dataset of CARTO database. Here, the query is framed by means of an {{#if ….}} {{else}} template condition to filter data by population size i.e. if the user provides the population size, the resultset limited to return a record of three would reflect the nearest cities with a population density greater than the input population size besides the provided input geolocation. But, if there is no input value suggesting a population size then the resultset would list the nearest cities by capturing the location of the device alone.

SELECT
name, adm1name AS state, pop_max
FROM ne_10m_populated_places_simple
WHERE 1=1
{{#if inputs.PopSize}}
AND pop_max > {{inputs.PopSize.value}}
{{/if}}
ORDER BY 
the_geom <-> ST_SetSRID(ST_MakePoint({{geolocation.longitude}},
{{geolocation.latitude}}), 4326)
LIMIT 3

👍 Note: In order to return a resultset by setting a point using latitude and longitude the query has to be formulated as follows:
ORDER BY  the_geom <-> ST_SetSRID(ST_MakePoint(-90, 30), 4326)
This is what orders the values from the database with the provided point. 
ST_MakePoint uses Longitude / Latitude

To know more on how to order geometry coordinates by distance in an SQL query visit https://postgis.net/docs/ST_MakePoint.html https://postgis.net/docs/ST_SetSRID.html

8. Drag and drop another Response component onto the flow canvas. You can alternatively right click on the anchor icon and
choose the response component.
9. Connect the CARTO component to the Response card.
10. 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 a record of the 3 closest cities by looping through the results filtered by the
population size along with its respective state. Also,the showList function is made use of in the below query in order to access
the 3 values and list them in the form of A, B and C. 

Say: {{#each connectors.c1}}  
{{name}}, {{state}} ({{pop_max}})  
{{/each}}   
{{#showList connectors.c1}}{{name}}{{/showList}}

To learn more about the showList function or about different template engines 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: Nearest Cities
    Display Message: {{#each connectors.c1}}  
     {{name}}, {{state}} ({{pop_max}})   
     {{/each}}

    {{#showList connectors.c1}}{{name}}{{/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. 
4. Leave the ‘With slots’ field under Conversation blank, thereby providing no population size. Further down, check the
checkmark box of Geolocation under User Permissions to capture the location of the device. Provide the hardcoded
latitudinal and longitudinal values accordingly to run the test.

5. Finally, click the Save button to save the input and perform the test.
6. To validate the Test Response, click on the lightning symbol on the left-hand side of the test case. 

👍 Note: The testing can also be done by specifying the population size. To do so, the only variation in the aforementioned steps is to input within the With Slots field under Conversation with the population size as illustrated in the screenshot below.

7. On the resultant Test Response window, the blue chat bubbles will cumulatively present the previously input message within
the Response component. The single lightning bolt symbol in yellow represents the CARTO connector instance. 

8. 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 instance, click on the lightning bolt icon highlighted in yellow. 

🖍 Note: The below shown screenshot validates the test response when a population size such as 5000,000 is given within the With Slots field under Conversation.

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 nearest cities limiting to a record of three with and without the input population size by setting a point using latitudinal and longitudinal values.

👍 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: Posting messages using PubNub
Next: Retrieving Data from an Array