![]() 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 It basically means you’ll need to create a new table and fill those with the original and new values: BEGIN TRANSACTION 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. UPDATE KenyaWP SET time = (SELECT TRIM(SUBSTR(time,12,8)) FROM KenyaWP) UPDATE KenyaWP SET date = (SELECT substr(time,1,10) FROM KenyaWP) We will use the already existing ‘ time‘ column for the ‘time’. To add it to the table, we need to add one new column for the ‘date’. These queries just produces a temporary view of course. 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 If the Y argument is omitted, trim(X) removes spaces from both ends of X. The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. Now I need to remove the excess whitespace, for which I can use the sqlite trim(X,Y) function. This extracts the time, excluding the +00. First step is the same as above SELECT SUBSTR(time,12,8) 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). 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 If the start location is a negative integer then the substring will begin X number of characters from the right. If the start location is a positive integer X then the substring will begin X number of characters from the left of the string. ![]() The syntax is as follows: SUBSTR(field_name,start_location) It requires the use of the SUBSTR Function to extract the date and the time. But how to do this after importing the data in the Spatialite database? The date + time are imported in the field ‘ time‘. There are various ways I could split this before importing in the SQLite database, e.g., using R or a spreadsheet. One problem is that data and time in the gpx file are given in one field, in the form ‘ 1 5:49:41+00‘. ![]() ![]() For example, importing the waypoint data (gpx format) into a spatialite database is a breeze with the QSpatialite plugin for QGIS. I will use a Spatialite database to store all the data as it plays nicely together with QGIS as well as R. Before starting with the analysis, I will have to go through the more boring data entry and organization. I am just back from vegetation survey in Kenya and Uganda. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |