Visualize geopoints from KoboToolbox to CARTO

[rosario_romero]

From Surveys to Maps: Why Humanitarian Data Needs Cartography

This is a QGIS 3.4 plugin, only tested with UNHCR KoboToolbox server. It enables loading geopoint data from a KoboToolbox survey into a QGIS layer, also offers the possibility to edit survey data of selected geopoints in QGIS canvas. In humanitarian work, data is everywhere, surveys, assessments, reports. But raw numbers alone rarely tell the full story. That’s where visualization and mapping step in. Maps bring clarity: They show who needs assistance and where. They highlight patterns that …

Hi.

I want to know if There are something similar for visualize geopoints from the Kobo in CARTO?

[jorcabrera]

Hi @rosario_romero wanted to share a workflow I put together after exploring CARTO. They have a feature called CARTO Workflows that lets you build data pipelines from components visually. I used it to pull submission data from KoboToolbox via the API and end up with map-ready geometries, all inside CARTO.

Sharing the steps in case it’s useful to anyone else working with KoboToolbox data.

Step 1 — HTTP Request

Add the HTTP Request component and configure it to hit the Kobo API:

• URL: https://[your-server]/api/v2/assets/[your-asset-uid]/data.json

• Request options:

json

  {
    "method": "GET",
    "headers": {
      "Authorization": "Token [YOUR_TOKEN]"
    }
  }

Replace [your-server] with your Kobo instance (e.g. kf.kobotoolbox.org, or your org’s deployment), [your-asset-uid] with the form’s asset UID, and [YOUR_TOKEN] with your API token (available at https://[your-server]/token/?format=json once logged in).

This returns the full API response as a single JSON blob in a response_data column.

Step 2 — Parse JSON

Add the Parse JSON component and connect it to the HTTP Request output. The Kobo response wraps submissions inside a results array, so:

• From column: response_data

• New column name: submission (or whatever you prefer)

• JSON path: $.results

Step 3 — Custom SQL Select

The submission column now holds an array of all submissions. To unnest it into one row per submission and pull out the geopoint, add a Custom SQL Select with:

sql

SELECT
  JSON_VALUE(sub, '$._id') AS submission_id,
  JSON_VALUE(sub, '$._submission_time') AS submission_time,
  CAST(JSON_VALUE(sub, '$._geolocation[0]') AS FLOAT64) AS lat,
  CAST(JSON_VALUE(sub, '$._geolocation[1]') AS FLOAT64) AS lon,
  sub AS raw
FROM $a,
UNNEST(JSON_QUERY_ARRAY(submission)) AS sub

A few notes:

• $a refers to the upstream input (Parse JSON’s output).

• Kobo automatically populates _geolocation as a clean [lat, lon] array on any submission with a geopoint question — much easier than parsing the space-separated string from the named geopoint field.

• I kept the full submission JSON in a raw column so additional fields can be pulled out later without re-running the pipeline. Pull whichever specific fields you want to map/filter on into their own columns here.

Step 4 — ST GeogPoint

Add the ST GeogPoint component (under Parsers) to convert lat/lon into a geometry CARTO can map:

• Longitude: lon

• Latitude: lat

This adds a geography column to the output.

Step 5 — Save and schedule

Add a Save as Table component to persist the result to your CARTO data warehouse, then use the schedule option (clock icon in the top toolbar) to run the workflow on whatever cadence you need — daily works well for most field-data use cases. The saved table can then be used as a source in any CARTO Builder map and refreshes automatically with each run.

Hope this helps anyone else trying to connect Kobotoolbox to CARTO.

[rosario_romero]

Thank you so much for your response.

I will try and let you know if I can work with the pipeline.

Best regrets.

[jorcabrera]

Keep us updated if you manage to get it working