Database Reference
In-Depth Information
At first, you got a month-by-month report of the total distance run by the runner.
For this purpose, you selected all of the track records grouped by date (year and
month), with the total distance obtained by summing up the lengths of the single
tracks(obtainedwiththe
ST_Length
function).Togettheyearandthemonthfrom
the
run_date
function,youusedthePostgreSQL
EXTRACT
function;beawarethat
ifyoumeasurethedistanceusinggeometriesintheWGS84system,youwillobtain
it in degree units. For this reason, you have to project the geometries to a planar
metricsystemdesignedforthespecificregionfromwherethedatawillbeprojected.
Forlarge-scaleareas,suchasinourcasewherewehavepointsthatspanallaround
Europe,asshowninthelastqueryresults,agoodoptionistousethe
geography
datatypeintroducedwithPostGIS1.5.Thecalculationsmaybeslower,buttheyare
much more accurate than in other systems. This is the reason why you casted the
geometries to the
geography
data type before making measures.
The last spatial query used a spatial join with the
ST_Intersects
function to get
thenameofthecountrywhereeachtrackwasrunbytherunner(withtheassump-
tionthattherunnerdidn'truncross-bordertracks).Togetthetotaldistancerunper
countryisjustamatterofaggregatingtheselectiononthe
country_name
fieldand
aggregating the track distances with the PostgreSQL
SUM
operator.