Skip to main content

Extracting Latitude and Longitude from an Array column

When your data stores latitude and longitude coordinates in an array format within a column, you can use Akuko to extract and create separate dimensions for Latitude and Longitude. This guide will walk you through the process step by step.

caution

This guide applies to Sources connected to your own PostgreSQL or MySQL database.

A dimension's SQL runs on the database behind the Source, so it has to be written in that database's dialect, and the string functions below are PostgreSQL and MySQL ones.

Every other source type — CSV, GeoJSON, Parquet, GeoParquet, Google Sheet, Ona, and a connected ClickHouse database — is queried through ClickHouse, whose string functions are named differently. On those Sources this SQL fails. If your coordinates arrive combined in one column there, split them into two columns before the data reaches Akuko. Ona Sources are already handled for you: a geopoint question arrives as separate numeric _lat and _lng columns.

Prerequisites​

Before you start, ensure you have:

  • Access to Akuko: You should have an active Akuko account with the necessary permissions to create or modify Dimensions within a Source.

  • A PostgreSQL or MySQL Source: connected to a database you control, with a column holding coordinate pairs.

Step 1: Access Your Source​

  1. Log in to your Akuko account.

  2. From the Akuko dashboard, navigate to the "Sources" section.

  3. Select the Source where your data with the array column resides.

Step 2: Cast Array column to string​

The extraction below works on the text form of the column, so start by looking at what that text actually is.

  1. In the Source settings, navigate to the "Dimensions" section.

  2. Locate the dimension that is stored as an array in the database.

  3. In its SQL field, cast the column to text — your_array_column::text on PostgreSQL, CAST(your_array_column AS CHAR) on MySQL — then use the Query panel to see the result.

You are looking for two things: the order of the pair, and the punctuation around it. [36.8219, -1.2921] and 36.8219,-1.2921 need slightly different expressions below.

tip

If the column is a real array or JSON type, index it instead of parsing text. On PostgreSQL an array column gives you your_array_column[1] and your_array_column[2] directly — arrays are numbered from 1 — and a JSON array gives you (your_array_column->>0)::numeric. On MySQL, a JSON column gives you JSON_EXTRACT(your_array_column, '$[0]'). This is shorter and far less brittle than splitting a string, so prefer it where the column type allows.

Step 3: Create Separate Latitude and Longitude Dimensions​

These examples assume the text looks like [lng, lat] — longitude first, which is the order GeoJSON uses. Check yours against Step 2 before copying them.

  1. In the Source settings, navigate to the "Dimensions" section.

  2. Click the Add dimension button at the bottom of the panel.

  3. Create a new dimension for Latitude. Give it a name like latitude — it must begin with a letter and cannot contain spaces.

  4. Set Type to number.

  5. In the SQL field, take the second value — everything after the comma, with the closing bracket removed:

    SUBSTRING_INDEX(SUBSTRING_INDEX(array_column_name, ',', -1), ']', 1)

  6. Click Save to save your new Latitude dimension.

  7. Repeat the process to create a new dimension for Longitude, named something like longitude.

  8. Set Type to number for this one too.

  9. In the SQL field, take the first value — everything before the comma, with the opening bracket removed:

    SUBSTRING_INDEX(SUBSTRING_INDEX(array_column_name, ',', 1), '[', -1)

  10. Click Save to save your new Longitude dimension.

note

SUBSTRING_INDEX is a MySQL function. The PostgreSQL equivalent is split_part, which takes the piece you want by position — split_part(array_column_name, ',', 2) for the second value — leaving you to trim any brackets with trim.

Step 4: Utilize Your Latitude and Longitude Dimensions​

  1. Your separate Latitude and Longitude dimensions are now available for use within your Source.

  2. Use them to build a Latitude / Longitude Geometry, which is what puts the points on a map. See Generating Point Geometries.

Conclusion​

Extracting Latitude and Longitude from an array column within your Source allows you to work seamlessly with geographical data in Akuko. These separate dimensions empower you to build interactive maps, perform spatial analysis, and create engaging data stories that incorporate location-based insights.

Explore the possibilities of your newly created Latitude and Longitude dimensions to unlock the full potential of your geographical data within Akuko.