Skip to main content

Extracting Latitude and Longitude from a Point column

If your data is stored in a database with a column of type POINT, which represents geographic points, you can extract the latitude and longitude values from this column and create separate dimensions for Latitude and Longitude in Akuko. 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 POINT column resides.

Step 2: Cast Point column to string​

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

  2. Locate the dimension that is stored as a POINT in the database.

  3. In the calculation or formula section, cast the column to a string using:

ST_AsText(your_point_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, use patial functions to extract the Latitude from your POINT column. You can use the ST_X function to get the longitude. For example:

    ST_Y(point_column_name)

  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, use spatial functions to extract the Longitude from your POINT column. You can use the ST_X function for this purpose. For example:

    ST_X(point_column_name)

  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 a POINT column in a MySQL database 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.