How to do it...

First, be sure of the format of the .gpx files that you need to import to PostGIS. Open one of them and check the file structure—each file must be in the XML format composed of just one <trk> element, which contains just one <trkseg> element, which contains many <trkpt> elements (the points stored from the runner's GPS device). Import these points to a PostGIS Point table:

1. Create a new schema named chp03 to store the data for all the recipes in this chapter, using the following command:

      postgis_cookbook=# create schema chp03;

2. Create the chp03.rk_track_points table in PostgreSQL by executing the following command lines:

      postgis_cookbook=# CREATE TABLE chp03.rk_track_points 
      ( 
        fid serial NOT NULL, 
        the_geom geometry(Point,4326), 
        ele double precision, 
        "time" timestamp with time zone, 
        CONSTRAINT activities_pk PRIMARY KEY (fid) 
      ); 

3. Create the following script to import all of the .gpx files in the chp03.rk_track_points table using the GDAL ogr2ogr command.

The following is the Linux version (name it working/chp03/import_gpx.sh):

        #!/bin/bash 
        for f in `find runkeeper_gpx -name \*.gpx -printf "%f\n"` 
        do 
          echo "Importing gpx file $f to chp03.rk_track_points 
                PostGIS table..." #, ${f%.*}" 
          ogr2ogr -append -update  -f PostgreSQL
          PG:"dbname='postgis_cookbook' user='me'
          password='mypassword'" runkeeper_gpx/$f 
          -nln chp03.rk_track_points 
          -sql "SELECT ele, time FROM track_points" 
        done 

The following is the command for macOS (name it working/chp03/import_gpx.sh):

        #!/bin/bash 
        for f in `find runkeeper_gpx -name \*.gpx ` 
        do 
          echo "Importing gpx file $f to chp03.rk_track_points 
                PostGIS table..." #, ${f%.*}" 
          ogr2ogr -append -update  -f PostgreSQL
          PG:"dbname='postgis_cookbook' user='me'
          password='mypassword'" $f 
          -nln chp03.rk_track_points 
          -sql "SELECT ele, time FROM track_points" 
        done 

The following is the Windows version (name it working/chp03/import_gpx.bat):

        @echo off 
        for %%I in (runkeeper_gpx\*.gpx*) do ( 
          echo Importing gpx file %%~nxI to chp03.rk_track_points 
               PostGIS table... 
          ogr2ogr -append -update -f PostgreSQL
          PG:"dbname='postgis_cookbook' user='me'
          password='mypassword'" runkeeper_gpx/%%~nxI 
          -nln chp03.rk_track_points 
          -sql "SELECT ele, time FROM track_points" 
        ) 

4. In Linux and macOS, don't forget to assign execution permission to the script before running it. Then, run the following script:

      $ chmod 775 import_gpx.sh
      $ ./import_gpx.sh
      Importing gpx file 2012-02-26-0930.gpx to chp03.rk_track_points 
        PostGIS table...
      Importing gpx file 2012-02-29-1235.gpx to chp03.rk_track_points 
        PostGIS table...
      ...
      Importing gpx file 2011-04-15-1906.gpx to chp03.rk_track_points 
        PostGIS table...
  

In Windows, just double-click on the .bat file or run it from the command prompt using the following command:

      > import_gpx.bat

5. Now, create a polyline table containing a single runner's track details using the ST_MakeLine function. Assume that on each distinct day, the runner had just one training session. In this table, you should include the start and end times of the track details as follows:

      postgis_cookbook=# SELECT 
      ST_MakeLine(the_geom) AS the_geom, 
      run_date::date, 
      MIN(run_time) as start_time, 
      MAX(run_time) as end_time 
      INTO chp03.tracks 
      FROM ( 
        SELECT the_geom, 
        "time"::date as run_date, 
        "time" as run_time 
        FROM chp03.rk_track_points 
        ORDER BY run_time 
      ) AS foo GROUP BY run_date; 

6. Before querying the created tables, don't forget to add spatial indexes to both of the tables to improve their performance, as follows:

      postgis_cookbook=# CREATE INDEX rk_track_points_geom_idx 
      ON chp03.rk_track_points USING gist(the_geom); 
      postgis_cookbook=# CREATE INDEX tracks_geom_idx 
      ON chp03.tracks USING gist(the_geom);

7. If you try to open both the spatial tables on a desktop GIS on any given day, you should see that the points from the rk_track_points table compose a single polyline geometry record in the tracks table, as shown in the following screenshot:
   
8. If you open all the tracks from a desktop GIS (such as QGIS), you will see the following:

9. Now, query the tracks table to get a report of the total distance run (in km) by the runner for each month. For this purpose, use the ST_Length function, as shown in the following query:

      postgis_cookbook=# SELECT 
        EXTRACT(year FROM run_date) AS run_year, 
        EXTRACT(MONTH FROM run_date) as run_month, 
        SUM(ST_Length(geography(the_geom)))/1000 AS distance 
        FROM chp03.tracks 
        GROUP BY run_year, run_month ORDER BY run_year, run_month; 

      (28 rows) 

10. Using a spatial join between the tracks and countries tables, and again using the ST_Length function as follows, you will get a report of the distance run (in km) by the runner, per country:

      postgis_cookbook=# SELECT 
        c.country_name, 
        SUM(ST_Length(geography(t.the_geom)))/1000 AS run_distance 
      FROM chp03.tracks AS t 
      JOIN chp01.countries AS c 
      ON ST_Intersects(t.the_geom, c.the_geom)

      GROUP BY c.country_name 
      ORDER BY run_distance DESC; 

      (4 rows)