Lesson 7. Consolidation and use of the spatial database: recap exercises

In the first 6 lessons you created a database with tracking data and related information (animals, sensors, deployments). You defined a set of procedures to automatically manage the update of the database whenever new GPS data are acquired. You further extended the database including ancillary environmental information. In this lesson, you play with your data to apply and consolidate the SQL skills learnt so far (particularly during the first part of the summer school) to tracking data. Here 9 exercises are proposed to simulate some possible data processes that can be done in the database framework.


  1. Find the GPS position stored in the database that is closest to the city of Trento.
  2. Calculate the distance and the time gap of each locations to the next one.
  3. What is the maximum average distance covered with a time gap of 4 hours?
  4. Is the average distance covered between 8 p.m. and 8 a.m smaller or bigger than the average distance covered between 8 a.m. and 8 p.m?
  5. Is the average distance covered in winter smaller or bigger than in summer?
  6. Which is the animal that stays at the highest average altitude in December?
  7. According to the GPS positions stored in the database, what is the R2 between NDVI and altitude (see the function regr_r2())?
  8. Which is the month with the highest (average) NDVI value for each of the animals?
  9. Compare the percentage of each land cover class used by animal 1 with the land cover class available in its convex hull (calculated using all the locations).