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.

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. ST_AsText, ST_X and ST_Y are PostgreSQL and MySQL functions.

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

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 POINT column.

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​

Before extracting anything, it is worth looking at what the column actually holds — the coordinate order in a POINT is a common source of swapped maps.

  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 its SQL field, cast the column to readable text:

ST_AsText(your_point_column)

Run the Query panel against that dimension and you will see values like POINT(36.8219 -1.2921), which tells you which number is which.

note

This step is for checking your data. The Latitude and Longitude dimensions below read the POINT column directly — they do not need the text version.

Step 3: Create Separate Latitude and Longitude Dimensions​

  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, use ST_Y to extract the latitude:

    ST_Y(point_column_name)

  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, use ST_X to extract the longitude:

    ST_X(point_column_name)

  10. Click Save to save your new Longitude dimension.

tip

ST_Y is latitude and ST_X is longitude. X is the horizontal axis, which is longitude. Getting these the wrong way round is the usual reason a map of your data comes out looking plausible but wrong, so check a known location before building on it.

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 a POINT column lets you work 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.