Database Reference
In-Depth Information
...
2012 | 7 |
38.8795349962323
2012 | 8 |
72.0557697278750
(28 rows)
9. Usingaspatialjoinbetweenthe
tracks
and
countries
tablesandagain
usingthe
ST_Length
functionasfollows,youwillgetareportofthedistance
run (in km) by the runner, per country:
postgis_cookbook=# SELECT
c.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.name
ORDER BY run_distance DESC;
country_name | run_distance
--------------------+------------------
Italy | 2628.78393844143
...
Greece | 18.1060004468414
(4 rows)
How it works...
The
.gpx
filesstoreallofthepoints'detailsintheWGS84spatialreferencesystem;
therefore, we created the
rk_track_points
table with SRID (4326).
Aftercreatingthe
rk_track_points
table,weimportedallofthe
.gpx
filesinthe
runkeeper_gpx
directoryusingabashscript.Thebashscriptiteratesallofthefiles
withtheextension
*.gpx
inthe
runkeeper_gpx
directory.Foreachofthesefiles,
