Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

A little Gorilla Time

Posted by smathermather on June 12, 2017

I miss my mountain gorilla friends in Rwanda. Let’s write a little more code to support them. I’ll be visiting Karisoke again next week, so it seems timely to post a little more code (HT Jean Pierre Samedi Mucyo for working with me on this one).

The problem today is simple — given a time series of gorilla locations and dates, can we calculate rate of travel using PostgreSQL? Well, of course we can. We can do anything in Postgre.

We have two tricks here:

  1. The first is to order our data so we can just compare one row to the next.
  2. Once we do that, we need simply to use PostGIS to calculate distance, and ordinary time functions from Postgres to calculate time difference.

This is my first use of WITH RECURSIVE, and it’s probably unnecessary (could be replaced with windowing functions), but I was very proud to finally get over my fear of WITH RECURSIVE . (We actually use one windowing function in our prep of the data. But there we are… ).

snip

For the record, WITH RECURSIVE isn’t recursive, but it is useful here in allowing us to compare the current row with the previous.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: