Sunday, October 18, 2015

Feeling Smug b/c My Database Query Works

I'm feeling pretty smug over here!

After three days of reading forum posts about the OpenOffice Base program I finally figured out the correct syntax for a query to select and manipulate information based on a specific date range.

Base is the free, open source version of MS Office's Access.

It was the date range syntax that was throwing me off, because it didn't follow exactly the SQL syntax as espoused by the w3schools tutorials. It didn't follow Access's syntax. The forum post that finally answered my question: https://forum.openoffice.org/en/forum/viewtopic.php?f=61&t=72504

Thank you, bhilton for asking your question and all the volunteers for helping her to reach the answer.

The code that I was trying to write:
SELECT "Listing", COUNT("Nights") AS TotalReservation, SUM( "Nights" ) AS TotalNightsSold, AVG( "Nights" ) AS AvgNightsPerRes FROM "Reservations" WHERE "Date" BETWEEN '2014-01-01' AND '2014-12-31' GROUP BY "Listing"
To make a table which grouped info by vacation house rental and showed me the total number of reservations in 2014, the total number of nights booked in 2014, the average number of nights per reservation.

With more data (and really, who doesn't like data?), I could find out the average number of people per reservation and which geographical locations provide the most/least bookings.

This sort of info would be handy in creating projections for future reservations, focusing marketing efforts on types of groups (family vs. 20 somethings traveling together), and focusing marketing efforts on locations (such as either marketing heavily on the east coast to tap into existing infrastructure, or moving into new market areas to expand.

And finally, thank you Drexel professor, whose name I don't remember at this moment, for being such a punk about us perfecting our SQL in the database class. It came in handy.

Wednesday, October 7, 2015

Distinguishing Fauna by their Sound Waveform

Back in July, my son and I saw this really great episode of SciGirls called Frog Whisperers (#301) (it's embedded at the bottom) where the girls learned various techniques to recall a frog's song and match it to the species. One technique was to look at a visual representation of the call, or its waveform. I'm a visual learner, so it was cool see that.

Fast forward to September and the Vieques National Wildlife Refuge gave a talk titled Anfibios y Reptiles, where among other species, they talked about the three species of coquis that we have in Vieques (out of a total of 17 found in Puerto Rico). The biologist presenting the charla was able to whistle the sounds of the frogs and toads, but I wanted something more permanent for recall purposes.

Below, you'll find my notes for the segment on the coquies in a garbled mix of Spanish and English, along with audio recordings which I found at CoquiPR.com and the waveform for each, which I produced from screenshots of the imported audio files using the open source audio editor Audacity.

Three Species of Coqui in Vieques

1) Coqui Comun- the largest of the three at approximately 1.4", it's named for its call, co-KI

This is what it's call looks like:

Here's what the coqui comun sounds like:

(audio courtesy LJ Villanueva-Rivera under a Creative Commons Reconocimiento-NoComercial-Compartir Igual License. See his article on the Common Coqui for more information.)

2) Coqui Churri- almost half the size of the coqui comun at .76" and makes a ki-ki-ki sound as well as a chu-RI, the wave forms of which look like:





Here's what the Churri sounds like (a common coqui can be heard in the back ground):

(audio courtesy LJ Villanueva-Rivera under a Creative Commons Reconocimiento-NoComercial-Compartir Igual License. See his article on the Churi Coqui for more information.) 

3) Coqui Pitito- the smallest of the three found in Vieques. It is .6" and is recognized by two curves on its back that look like a pair of parenthesis.

It's call is the least punctuated, but the most constant, kind of a p-riiiiiiiiii. No clue if this waveform represents one coqui, or several overlapping calls made by more than one frog:




And it sounds like:

(audio courtesy LJ Villanueva-Rivera under a Creative Commons Reconocimiento-NoComercial-Compartir Igual License. See his article on the Pitito Coqui for more information.) 

So there you have it, three ways- verbal description, visual waveform and audio file- to learn how to distinguish a few fauna of Vieques. A good information professional can help you to find and create all three information sources!

Enjoy the SciGirls video.




(minute 5:32 if you want to jump straight to the part of the sound waves).