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.
Search WWH ::




Custom Search