Split strings in SQLite

I am just back from vegetation survey in Kenya and Uganda. Before starting with the analysis, I will have to go through the more boring data entry and organization. I will use a Spatialite database to store all the data as it plays nicely together with QGIS as well as R. For example, importing the waypoint data (gpx format) into a spatialite database is a breeze with the QSpatialite plugin for QGIS.

One problem is that data and time in the gpx file are given in one field, in the form ‘2011/11/01 5:49:41+00‘. There are various ways I could split this before importing in the SQLite database, e.g., using R or a spreadsheet. But how to do this after importing the data in the Spatialite database? The date + time are imported in the field ‘time‘.

I found the solution on this site. It requires the use of the SUBSTR Function to extract the date and the time. The syntax is as follows:

SUBSTR(field_name,start_location)
SUBSTR(field_name,start_location,substring_length )

If the start location is a positive integer X then the substring will begin X number of characters from the left of the string. If the start location is a negative integer then the substring will begin X number of characters from the right. Thus, to extract a column with only the ‘date‘ from the field ‘time’ in the table KenyaWP, I use the following:

SELECT SUBSTR(time,1,10) FROM KenyaWP

The time is slightly more complicated as the length of the time string varies (see e.g., 9:10:26+00 vs 12:10:26+00). First step is the same as above

SELECT SUBSTR(time,12,8) FROM KenyaWP

This extracts the time, excluding the +00. Now I need to remove the excess whitespace, for which I can use the sqlite trim(X,Y) function. The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes spaces from both ends of X. I can combine this function with the one above to extract and at the same time remove white space:

SELECT TRIM(SUBSTR(time,12,8)) FROM KenyaWP

These queries just produces a temporary view of course. To add it to the table, we need to add one new column for the ‘date’. We will use the already existing ‘time‘ column for the ‘time’.

ALTER TABLE KenyaWP ADD date TEXT;
UPDATE KenyaWP SET date = (SELECT substr(time,1,10) FROM KenyaWP);
UPDATE KenyaWP SET time = (SELECT TRIM(SUBSTR(time,12,8)) FROM KenyaWP);

What if you want to keep a column with the combined date and time, or want to change the order of the columns? SQLite does not allow to drop or rename columns, so you need need a workaround as explained e.g., here. It basically means you’ll need to create a new table and fill those with the original and new values:

BEGIN TRANSACTION;
CREATE TABLE tmp (
PKUID INTEGER,
Geometry MULTIPOINT,
WP TEXT,
date_time TEXT,
date TEXT,
time TEXT,
Elevation REAL
);

INSERT INTO tmp (PKUID, Geometry, WP,date_time, Elevation, date, time) SELECT PKUID, Geometry, WP, time, elevation, substr(time,1,10), TRIM(SUBSTR(time,12,8)) FROM KenyaWP;

DROP TABLE KenyaWP;
ALTER TABLE tmp RENAME TO KenyaWP;
COMMIT;

The first part creates a temporary table ‘tmp’, the second parts fills it with the data from the original table KenyaWP, including the ‘date’ and ‘time’, and in the third part the KenyaWP table is replaced by the temporary table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s