Scratching Surfaces

The recent stable version of Spatialite, 3.0, supports linking to and importing Excel spreadsheet tables. Read on to see how it’s done.

The developers of spatialite have added a driver for *.xls files (thru the FreeXL library ). You can either link to, or import a single sheet from an Excel file provided that:

• the file is Excel 2003 format (not the newer xml format)
• the table is “cleanly” formated – only data in rows, no empty rows, etc.
• the first row contains either column headers, or straight-away the first data entry

However the columns of data are imported into spatialite without any data type. If you want to specify the data type for each column, my procedure is:

1. link to the external table (create a virtualXL table)
2. create your own, well defined table
3. use the SQL construct “INSERT INTO … SELECT FROM…” in order to copy all data from the linked table into your structured table.
4. then run the spatial funtions AddGeometryColumn() and MakePoint() in order to convert the table to a spatial layer

These few steps might seems a bit complex just to get a table of data into spatialite, but this method insures that data will be structured exactly as you want.

If you’re working with spatialite from the command line, here’s the command syntax for loading an excel sheet:

.loadxl <args> Loads a XL spreadsheet (.xls) into a SpatiaLite table
arg_list: xl_path table_name
[worksheet_index [first_line_titles{0/1}]]


Note that the worksheet index begins with 0. So if you have more than one sheet in your Excel file, be sure to set the correct index for the worksheet containing the table. The last parameter indicates (by values 0 or 1) whether the first row holds column headers. So for example you could import the first sheet of an excel worksheet called ‘host_trees.xls’, which contains column headers in the first row, with the following command:

spatialite>.loadxl 'host_trees.xls' host_trees 0 1
XL loaded

3770 inserted rows
spatialite>


We’ll follow the full procedure to smoothly import a spreadsheet of longitude,latitude data and create a spatial layer with screen shots from the spatialite_gui. Suppose we begin with a table that looks like this:

Excel table of data with Longitude/Latitude loctaions

First import the table using the VirtualXL button. This creates a link in the spatialite db to the sheet from Excel.

Importing an Excel table as VirtualXL

In the next image note that the linked data table has no types for the columns. So we’ll create our premanent table (named here ‘MyHostTrees’)within spatialite, specifying the correct data types, then we will transfer the date over from the virtual table to the permanent one. Follow the steps…

Crete a new, permanent table with correct data types

Now we transfer the data from the virtual table to the permanent table using the SQL construct: “INSERT (…) INTO … SELECT … FROM …”.  Here’s how it looks:

Copy data from virtual table to permanent table

We continue by making this new table a spatial table with the AddGeometryColumn() function, then we populate this Geometry column using the MakePoint() function.

Using the AddGeometryColumn function

Populating the Geometry column

And here’s the result:

Spatial table containing data with Geometry column

This work, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.