Getting .fit file data into PostGIS

GPSBabel released an update (1.4.4) on Labor Day.  This update fixed GPSBabel’s FIT file compatibility.  Now FIT files created on Garmin Edge and other devices can be converted into formats that can be imported into PostGIS.  Follow along after the jump to see how.

GPSBabel is a cross platform tool that can convert GPS data from almost any GPS device to the format used by another GPS device.  For our needs we’ll be converting FIT files from a Garmin Edge to a simple CSV text file.

To do this you can either use the GPSBabel GUI or GPSBabel’s command line utility.  From the command line you would do the following:

gpsbabel -t -i garmin_fit -f input_file.fit -o unicsv,datum=WGS 84,format=0,filename=0 -F output_file.csv

Replace input_file.fit and output_file.csv with your input and output file names.  Now you’ll need to create a table in your PostGIS database.

CREATE TABLE ride ( id serial primary key,
no integer,
latitude float,
longitude float,
altitude float,
temperature float,
speed float,
cadence integer,
ride_date date,
ride_time without time zone);

You’ll need the id field, or another primary key field if you want to display the data in some (most?) GIS applications.  If you don’t have a cadence sensor you won’t have a cadence field, and if you have a power meter you may have more fields available to you than I have.  When the data comes in altitude will be in meters, temperature in Celsius, and speed will be in meters per second, even if your recording device is set to display your data in Imperial units.

Next up, loading the data from the CSV file.  I like to use Postgresql’s COPY command to do this.  From psql run the following:

COPY ride (
no, latitude, longitude, altitude
, temperature, speed, cadence
, ride_date, ride_time)
FROM '/path/to/output_file.csv'
WITH DELIMITER AS ','
CSV HEADER;

If you’re using Windows for simplicity’s sake you’ll still want to use forward slashes in the path to your csv file, like so: FROM ‘C:/output_file.csv’.

Once your data is loaded we need a geometry column so we can convert our lat/long data to geometry.

SELECT AddGeometryColumn('','ride','the_geom',4326,'POINT',2);

And then to convert the lat/long data:

UPDATE ride SET the_geom = ST_SetSRID(ST_Point( longitude, latitude), 4326);

And finally, add a spatial index to speed things up:

CREATE INDEX idx_ride_the_geom ON ride USING GIST (the_geom);
VACUUM ANALYZE;

And you’re done!  Later I’ll put together a batch script to handle the conversions, build polylines from the points collected, calculate some medians and run other statistical analysis on my data. Fun!

Advertisements
    • darrencope
    • May 15th, 2013

    Hey Phil,

    Cool to see you’re also a cyclist/beer fan/GIS nerd! This is a great post; I’d love to see the script you came up with to create polylines from the points! I’m thinking of doing something similar to track my rides now that I have a Garmin!

  1. Thanks! If I put out the raw data the cyclist part would be debateable. I mentioned the query I came up with to “connect the dots” in a post I made about turning my ride data into Chernoff faces. I haven’t posted it because it only works for rides where you don’t take a break. Currently if you stop your Garmin and move the query ignores the stoppage and counts the move as part of your ride. To get around it I think I’m going to build a PL/Ruby / Python / Perl function to loop through the rides and split lines based on their time stamp and throw the results into a multiline. Anyway, here’s what I’ve got so far:

    SELECT row_number() OVER () As id, ride_date, ST_Length(ST_Transform(ST_Makeline(the_geom ORDER BY ride_time),26916)) As length FROM rides.ride GROUP BY ride_date ORDER BY length DESC;

    The cool part is the ORDER BY inside the ST_Makeline aggregate; Postgres is full of awesome little tricks like that. In this case it fixed a problem I was having where the query would “connect the dots” in a random order. It would create art, but not a useful line.

    The row_number() is a window function that creates an integer field that I can use in QGIS’s DB Manager plugin to display the results of the query on a map.

  1. No trackbacks yet.

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

%d bloggers like this: