Quick tip: SingleStoreDB's Persistent Computed Columns
Tags: singlestoredb, geospatial
Published: 2022-10-04 12:26:12

## Abstract
SingleStoreDB supports a feature called [Persistent Computed Columns](https://docs.singlestore.com/managed-service/en/create-a-database/physical-database-schema-design/procedures-for-physical-database-schema-design/using-persistent-computed-columns.html) that enables the creation of a table column defined by an expression that uses other table columns. In this short article, we'll look at an example of how to use this feature with Geospatial data.

## Introduction
When working with some types of data, it can be useful to automatically compute values, saving time and effort for the developer. In SingleStoreDB, this capability is available through Persistent Computed Columns. Let's see how to use this with a simple Geospatial example.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **PCC Demo Group** as our Workspace Group Name and **pcc-demo** as our Workspace Name. We'll make a note of our **password** and **host** name.

## Create the database and table
First, we'll download a [CSV](https://github.com/VeryFatBoy/singlestore-pipelines-example/blob/main/data/sensor.csv) file containing some synthetic sensor data.

Next, we'll use a MySQL client to connect to SingleStoreDB Cloud, as follows:

```shell
mysql --local-infile \
      -u admin \
      -h <host> \
      -P 3306 \
      --default-auth=mysql_native_password \
      -p
```

The `<host>` being replaced with the value obtained from SingleStoreDB Cloud.

We'll now create a database and database table, as follows:

```sql
CREATE DATABASE pcc_demo;

USE pcc_demo;

CREATE ROWSTORE TABLE sensors (
     id INT PRIMARY KEY,
     name VARCHAR (50),
     latitude DOUBLE,
     longitude DOUBLE,
     location AS GEOGRAPHY_POINT(longitude, latitude) PERSISTED GEOGRAPHYPOINT,
     INDEX(location)
);
```

## Load the data

We'll now load the sensor data into the sensors table, as follows:

```sql
LOAD DATA LOCAL INFILE '/path/to/sensor.csv'
INTO TABLE sensors
IGNORE 1 LINES
COLUMNS TERMINATED BY ',';
```

We would replace `/path/to/` with the actual path to where the CSV file was located.

Our CSV file contains four columns (`id`, `name`, `latitude`, `longitude`). As the data are loaded into the table, the `location` column is automatically populated for us with a Geospatial Point by using the `longitude` and `latitude` values.

## Query the data

Once the data are loaded we can check the table, as follows:

```sql
SELECT id, name, location FROM sensors LIMIT 5;
```

The result should be similar to the following:

```
+----+-----------+-----------------------------------+
| id | name      | location                          |
+----+-----------+-----------------------------------+
|  1 | sensor_1  | POINT(88.44146008 -50.58444199)   |
|  3 | sensor_3  | POINT(93.61987432 -54.16528058)   |
|  6 | sensor_6  | POINT(-117.33446641 -55.57414366) |
|  9 | sensor_9  | POINT(-58.40311992 -5.46648307)   |
| 11 | sensor_11 | POINT(-141.93731583 10.42001621)  |
+----+-----------+-----------------------------------+
```

We can now use the `location` column as part of Geospatial queries. This column is also indexed, similar to any standard column.

## Summary
Through a quick example, we have seen how to use Persistent Computed Columns. As we loaded the CSV data, SingleStoreDB automatically created the data values in the extra column, saving us time and effort.