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.

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.

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​

  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 the calculation or formula section, cast the column to a string using:

ST_AsText(your_array_column)

Step 3: Create Separate Latitude and Longitude Dimensions​

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

  2. Click on the "Add Dimension" or "Create Dimension" button to initiate the creation process.

  3. Create a new dimension for Latitude. Give it a meaningful name like "Latitude."

  4. For the data type, select the appropriate type for your latitude values, often "Number" or "Decimal."

  5. In the calculation or formula section, write an expression to extract the Latitude from the array column. If your data is stored as an array like [lng, lat], you can use SQL functions to extract the Latitude. For example:

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

  6. Click "Save" or "Create" to save your new Latitude dimension.

  7. Repeat the process to create a new dimension for Longitude. Name it something like "Longitude."

  8. For the data type, select the appropriate type for your longitude values, often "Number" or "Decimal."

  9. In the calculation or formula section, write an expression to extract the Longitude from the array column. For example:

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

  10. Click "Save" or "Create" to save your new Longitude dimension.

Step 4: Utilize Your Latitude and Longitude Dimensions​

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

  2. You can use these dimensions in various components, including Map components, to visualize geographical data effectively.

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.