Get your GPS locations into Spatialite

Spatialite, built on the shoulders of the popular sqlite single-file database, offers a broad feature set of GIS analysis tools. Getting data into a spatialite database is a snap when you’re starting from a shapefile. But what about GPS data. Here’s a few tips on how to upload data from the standard “GPX” format into a spatialite DB.

I’ll present three methods and point out the advantages and drawbacks of each. The first and second for those with “CLI phobia”, and the third – easier in my opinion – a command line option.

The QGIS method

We begin with a straightforward “copy – paste” process right in Quantum GIS. You DO have QGIS installed already, I hope… First activate the GPS plugin in QGIS (this plugin should get installed automatically with QGIS), and click on the GPS icon to open the GPS Tools window. Go over to the “Load GPX File” tab, and browse to find your GPX file. Click “Open” and you’ll see the waypoints or tracks from your GPS on the QGIS canvas.

Now in the QGIS menu, choose “Layer”->”New”->”New Spatialite Layer”.  You’ll need to:

  • Select the spatialite *.db file
  • Enter a name for the new layer
  • Be sure the SRID is set to 4326 (The Lon/Lat WGS84 based spatial reference system used by all GPS instruments)
  • Click to add an autoincrementing primary key
  • Add any other attribute columns you’ll need

Then click OK to add the new layer into into the Spatialite DB, and into QGIS. Now comes the copy/paste dance: choose the GPX layer in the Table of Contents, and using the Select tool, select all of the features (so that they all appear yellow) and click the “Copy Features” button on the edit toolbar. Now Choose from the TOC the new empty spatialite layer, and click the “Toggle Editing” button to begin editing this new layer. And finally click the “Paste Features” button, again from the editing toolbar, to paste all the selected GPX features into the new spatialite table. Simple and elegant, but…

…if you open up the new layer’s attribute table you’ll see it’s emtpy.  “So what”, you might ask. Well every GPS captures, along with each point location, at least a waypoint name, and elevation. In the original GPX file these appear as XML elements. If this additional information is not important then the above method should be fine. But if you want to keep the waypoint numbers and elevation, you’ll need a better method.

The intermediate CSV method

For our second try, we need two GUI tools; the spatialite-gui binary and GPSbabel GUI. GPSBabel is traditionally a command line program, but there’s a “Front End” for Windows users. This program reads and writes practically every GPS file format in use on the planet. The steps to importing a GPX file with attributes are as follows. Using GPSBabel we convert the original GPX to the “Universal CSV” format. Then in Spatialite we create a VirtualTable based on this file. And then, using an SQL insert within spatialite we transfer all the GPS locations to a permanent table, and finally create the requisite Geometry column for this new table. Here are the details:

The GPSBabel command to convert to Universal CSV format reads (assuming the original GPX file is waypoints.gpx):

gpsbabel -w -i gpx -f waypoints.gpx  -o unicsv -F waypoints.csv

Alternatively, Using the GPSBabel GUI should be intuitive, choose the input format as GPX (or whatever format you downloaded from your  GPS), and output format Universal CSV. Select the files and check waypoints or tracks, whichever is appropriate. Then run the conversion.

Now fire up spatialite-gui. On the button bar, locate and click the button “Virtual CSV/TXT”. Browse to your waypoints.csv from the previous step and click Open to select the file. In the next “Creating VIrtual CSV/TXT” window change the table name to something like  “waypoints_tmp”, be sure to select “Comma” as the Column Separator. and click OK. You now have access to the CSV file from within spatialite. You can examine the table structure by right-clicking on it’s name and choosing “Show Columns”. The data is viewable by right-click and “Query table”.

It’s now time to create a new, permanent table for the waypoints. In the query window type the following:

CREATE TABLE waypoints (

long DOUBLE,
lat DOUBLE,
elev DOUBLE,
wpt TEXT );

 

At this point we want to insert all the rows from the GPX table – waypoints_tmp – into the new waypoints table. Here’s the required SQL.

INSERT INTO waypoints (long, lat,elev, wpt)
SELECT Longitude, Latitude, Altitude, Name FROM waypoints_tmp;

That should leave us with a fully populated, permanent waypoints table. At this point a word of explanation is in order. Why was it necessary to leap-frog over that Virtual CSV/TXT? Why not just import the CSV table directly into a permanent Spatialite table? The reason lies with how numeric columns are recognized by the CSV/TXT importer. If numbers appear as negative values, i.e. preceded by a minus sign (latitudes south of the equator, longitudes west of Greenwich, or below sea level elevations like at the Dead Sea) then these numbers get imported as text, and they are then useless for creating the Geometry column. When we hand crafted our permanent waypoints table as above, we set the long,lat,elev types to DOUBLE. Then, when inserting the values from the virtual table, the “text” with its minus sign got coerced into true numeric values.

We have yet one more step to complete. So far this new table is NOT a spatial table. The Longitude and Latitude values appear as attribute columns but we haven’t yet added the necessary Geometry column. Two more SQL statements complete the job:

SELECT AddGeometryColumn('waypoints','Geometry',4326,'POINT',2);
UPDATE waypoints SET Geometry=MakePoints(long.lat,4326);

The first Spatialite function adds the additional Geometry column, the metadata and constraints, and the second command populates that column based on the longitude/latitude values. Now we have a spatial table with attribute values, imported from a GPS file.

Yet one additional method is available. For those tired of reading such a long post, don’t worry, this final one is short.

The OGR command method

The ubiquitous GDAL library includes a sometimes overlooked, but powerful tool for handling vector format conversions. It’s called ogr2ogr. Since OGR can read and write both GPX formatted files and Spatialite database layers, we can concoct a one liner ogr2ogr command to push GPS locations from a GPX file directly into a Spatialite database layer, and including their attributes. SO, without further ado here it is:

ogr2ogr -append -f "SQLite" \

-dsco SPATIALITE=yes -dsco INIT_WITH_EPSG=yes -t_srs epsg:4326 \
spatial_data.db waypoints.gpx \
-nln waypoints

Just a few words of explanation: -append allows you to add a table to an existing Spatialite database. Based on this maillist thread, it seems that adding the -dsco (dataset creation options) and the target spatial reference system is a good idea. The -nln parameter is the new layer name – that is the new Spatialite table. Pay attention to the unusual order of the files on the command line: First is the destination database, and after the source (in this case) GPX file.

That’s it, the whole procedure distilled into one command. Now who still thinks the CLI is old fashioned or clunky?

5 thoughts on “Get your GPS locations into Spatialite

  1. Pingback: GPS update

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>