Seasonal Photos: Fun With Bash

I’m a lucky guy in that I get to walk my dog every day on the recreation paths and trails here outside of Burlington, Vermont. I like taking photos of the scenes, a LOT of photos. Here’s a sample photo to give you the idea.

I’ve given this collection of photos the title “Walking the Dog.” They are stored in directories by season, Winter, Spring, Summer and Fall, and as I said, there are a lot of them, over 1000 so far.

Our Objective: Displaying Photos For the Current Season

The photos are going to be displayed as part of a list of items we’ll see later in an Android application I’m building. They will be used as thumbnails on the item list and as a featured image on the item’s detail page. Think Flipboard, Feedly and most News apps in how the images will be used.

Here’s the kicker with the photos, we’re going to only use photos from the current season in our application. If it’s March through May, we’ll display Spring photos, June through August, Summer, and so on.

The PostgreSQL Data

As we established, the photos are stored by season.

The Walking the Dog photos are stored in three sizes, the original JPG, a medium-size JPG and a Thumbnail JPG. I won’t go into why we have 3 sizes, but it’s cool because having 3 sizes will help improve application performance and reduce data transfer. Here are the 3 files for image P10052004.jpg.

We’re going to store the image base filename and a season ID in our Images table. We also have a lookup table we’ll use to associate the month with the appropriate season which we may look at later if it becomes relevant.

Here’s how the P10052004.jpg image will appear in our Image Table.

Using Bash to Create the PostgreSQL Insert Script

Now we’ll create a bash script to generate a .SQL insert script. We can’t do a simple

$ ls P*.jpg

to output something to copy and paste into gedit or some other editor and fill in the rest. Images have all kinds of filenames due to different cameras and phones used over the years. We do have one constant to count on however, all medium and thumbnail image filenames start with a “z”, “zOpt_” and “zThumb_” respectively.

In our Bash script we’ll start at the season images root, then change directory into each season and pass its season_id to a generate_sql function. In the function we traverse each file in the directory with “for file in * ;” and if it doesn’t start with “z” we generate a SQL insert statement using the season_id and filename root.


# ---------------------------------------------------------------------- */
# Generate pgSQL Insert Statements for Seasonal Images - 10/15/2014
# ---------------------------------------------------------------------- */

# Variables ------------------------------------------------------------- */

# $imagespath - folder where database.sql.gz is located
# $seasonid - passed to generate_sql function 1 - summer, 2 - fall, 3 - winter, 4 - spring

cd $imagespath

function generate_sql {
  local seasonid="${1}"
  for file in * ;
     if [[ ! "$file" =~ ^z ]]; then
	fullfilename=$(basename "$file")
	echo INSERT INTO "nixmashup_images (\"season_id\", \"file_name\") VALUES ($seasonid, '${filename}');" >> /home/daveburke/tmp/season-images.sql


cd $imagespath/summer
generate_sql "1"

cd $imagespath/fall
generate_sql "2"

cd $imagespath/winter
generate_sql "3"

cd $imagespath/spring
generate_sql "4"

And an excerpt of the final result.

Next time we’ll look at the REST Service side and the Java logic used to send the Image Url to the Android application.