{"id":1114,"date":"2013-12-26T14:00:26","date_gmt":"2013-12-26T14:00:26","guid":{"rendered":"http:\/\/all-geo.org\/volcan01010\/?p=1114"},"modified":"2014-01-01T14:09:58","modified_gmt":"2014-01-01T14:09:58","slug":"generate-volcano-trivia-with-this-sqlite-tutorial","status":"publish","type":"post","link":"https:\/\/all-geo.org\/volcan01010\/2013\/12\/generate-volcano-trivia-with-this-sqlite-tutorial\/","title":{"rendered":"Generate volcano trivia with this SQLite tutorial"},"content":{"rendered":"

Excel is not a database. Even so, spreadsheets are commonly used as such. They are convenient places to enter and store data, but not to get it out again. This post aims to show how using a real database makes this easier.<\/p>\n

It uses an SQLite database, which is what many browsers (e.g. Firefox) use to store your bookmarks and history. These can also be read by other software e.g. Geographic Information Systems. It has none of the overly-complex wrappings of MS Access or LibreOffice Base and doesn’t need a server like MySQL or Oracle. Once the data are imported, typically from a comma separated value (csv<\/i>) file, it simply provides an interface so that we can ask questions using Structured Query Language (SQL).<\/p>\n

This example uses the Smithsonian Institute’s Global Volcanism Program catalogue of volcanoes, which can be downloaded as a csv file from their website<\/a>, as the database.\u00a0 It lists locations and recent eruptions of over 1,500 active volcanoes. Querying the list can generate a wealth of interesting (and less-interesting) volcano facts.<\/p>\n

The commands may look complicated at first, but hopefully you can see where the advantages in a real database lie.\u00a0 If so, there are instructions for getting started at the end.\u00a0 If not, just enjoy the trivia.<\/p>\n

Get an A-Z list of all the volcanoes in the world.<\/h3>\n
SELECT \"Volcano Name\" FROM GVPVolcano\r\nORDER BY \"Volcano Name\";<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Volcano name<\/th>\n<\/tr>\n
Abu<\/td>\n<\/tr>\n
Babuyan Claro<\/td>\n<\/tr>\n
Cabal\u00edan<\/td>\n<\/tr>\n
Dabbahu<\/td>\n<\/tr>\n
E-san<\/td>\n<\/tr>\n
Falcon Island<\/td>\n<\/tr>\n
Gabillema<\/td>\n<\/tr>\n
Hachijo-jima<\/td>\n<\/tr>\n
Iamalele<\/td>\n<\/tr>\n
Jailolo<\/td>\n<\/tr>\n
Kaba<\/td>\n<\/tr>\n
La Palma<\/td>\n<\/tr>\n
Ma Alalta<\/td>\n<\/tr>\n
NW Eifuku<\/td>\n<\/tr>\n
O’a Caldera<\/td>\n<\/tr>\n
Pacaya<\/td>\n<\/tr>\n
Qal’eh Hasan Ali<\/td>\n<\/tr>\n
Rabaul<\/td>\n<\/tr>\n
SW Usangu Basin<\/td>\n<\/tr>\n
Ta’u<\/td>\n<\/tr>\n
Ubehebe Craters<\/td>\n<\/tr>\n
Vailulu’u<\/td>\n<\/tr>\n
Waesche<\/td>\n<\/tr>\n
Xianjindao<\/td>\n<\/tr>\n
Yake-dake<\/td>\n<\/tr>\n
Zacate Grande, Isla<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The database contains information on 1555 volcanoes. That’s a big spreadsheet to manipulate by hand. This list is trimmed to give just the first example for each letter of the alphabet. There are 160 volcanoes whose name begins with ‘S’, but only one that begins with ‘X’ (Xianjindo in North Korea).<\/p>\n
Get a list of all the volcanoes in Iceland.<\/h3>\n
SELECT \"Volcano Name\" FROM GVPVolcano\r\n  WHERE \"Country\" IS \"Iceland\";<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Volcano Name<\/th>\n<\/tr>\n
Snaefellsj\u00f6kull<\/td>\n<\/tr>\n
Helgrindur<\/td>\n<\/tr>\n
Lj\u00f3sufj\u00f6ll<\/td>\n<\/tr>\n
Reykjanes<\/td>\n<\/tr>\n
Kr\u00edsuv\u00edk<\/td>\n<\/tr>\n
Brennisteinsfj\u00f6ll<\/td>\n<\/tr>\n
Hengill<\/td>\n<\/tr>\n
Hr\u00f3mundartindur<\/td>\n<\/tr>\n
Gr\u00edmsnes<\/td>\n<\/tr>\n
Prestahnukur<\/td>\n<\/tr>\n
Hveravellir<\/td>\n<\/tr>\n
Hofsj\u00f6kull<\/td>\n<\/tr>\n
Vestmannaeyjar<\/td>\n<\/tr>\n
Eyjafjallaj\u00f6kull<\/td>\n<\/tr>\n
Katla<\/td>\n<\/tr>\n
Tindfjallaj\u00f6kull<\/td>\n<\/tr>\n
Torfaj\u00f6kull<\/td>\n<\/tr>\n
Hekla<\/td>\n<\/tr>\n
Gr\u00edmsv\u00f6tn<\/td>\n<\/tr>\n
B\u00e1rdarbunga<\/td>\n<\/tr>\n
Tungnafellsj\u00f6kull<\/td>\n<\/tr>\n
Kverkfj\u00f6ll<\/td>\n<\/tr>\n
Askja<\/td>\n<\/tr>\n
Fremrinamur<\/td>\n<\/tr>\n
Krafla<\/td>\n<\/tr>\n
Theistareykjarbunga<\/td>\n<\/tr>\n
Tj\u00f6rnes Fracture Zone<\/td>\n<\/tr>\n
\u00d6raefaj\u00f6kull<\/td>\n<\/tr>\n
Esjufj\u00f6ll<\/td>\n<\/tr>\n
Kolbeinsey Ridge<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
If you wanted to plot them on a map, you can get their latitude and longitude, too.<\/p>\n
SELECT \"Volcano Name\", Longitude, Latitude FROM GVPVolcano\r\n  WHERE \"Country\" IS \"Iceland\";<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Volcano Name<\/th>\nLongitude<\/th>\nLatitude<\/th>\n<\/tr>\n
Snaefellsj\u00f6kull<\/td>\n-23.78<\/td>\n64.8<\/td>\n<\/tr>\n
Helgrindur<\/td>\n-23.25<\/td>\n64.87<\/td>\n<\/tr>\n
Lj\u00f3sufj\u00f6ll<\/td>\n-22.23<\/td>\n64.87<\/td>\n<\/tr>\n
Reykjanes<\/td>\n-22.5<\/td>\n63.88<\/td>\n<\/tr>\n
Kr\u00edsuv\u00edk<\/td>\n-22.1<\/td>\n63.93<\/td>\n<\/tr>\n
Brennisteinsfj\u00f6ll<\/td>\n-21.83<\/td>\n63.92<\/td>\n<\/tr>\n
Hengill<\/td>\n-21.32<\/td>\n64.08<\/td>\n<\/tr>\n
Hr\u00f3mundartindur<\/td>\n-21.202<\/td>\n64.073<\/td>\n<\/tr>\n
Gr\u00edmsnes<\/td>\n-20.87<\/td>\n64.03<\/td>\n<\/tr>\n
Prestahnukur<\/td>\n-20.58<\/td>\n64.6<\/td>\n<\/tr>\n
Hveravellir<\/td>\n-19.98<\/td>\n64.75<\/td>\n<\/tr>\n
Hofsj\u00f6kull<\/td>\n-18.92<\/td>\n64.78<\/td>\n<\/tr>\n
Vestmannaeyjar<\/td>\n-20.28<\/td>\n63.43<\/td>\n<\/tr>\n
Eyjafjallaj\u00f6kull<\/td>\n-19.62<\/td>\n63.63<\/td>\n<\/tr>\n
Katla<\/td>\n-19.05<\/td>\n63.63<\/td>\n<\/tr>\n
Tindfjallaj\u00f6kull<\/td>\n-19.57<\/td>\n63.78<\/td>\n<\/tr>\n
Torfaj\u00f6kull<\/td>\n-19.17<\/td>\n63.92<\/td>\n<\/tr>\n
Hekla<\/td>\n-19.7<\/td>\n63.98<\/td>\n<\/tr>\n
Gr\u00edmsv\u00f6tn<\/td>\n-17.33<\/td>\n64.42<\/td>\n<\/tr>\n
B\u00e1rdarbunga<\/td>\n-17.53<\/td>\n64.63<\/td>\n<\/tr>\n
Tungnafellsj\u00f6kull<\/td>\n-17.92<\/td>\n64.73<\/td>\n<\/tr>\n
Kverkfj\u00f6ll<\/td>\n-16.72<\/td>\n64.65<\/td>\n<\/tr>\n
Askja<\/td>\n-16.75<\/td>\n65.03<\/td>\n<\/tr>\n
Fremrinamur<\/td>\n-16.65<\/td>\n65.43<\/td>\n<\/tr>\n
Krafla<\/td>\n-16.78<\/td>\n65.73<\/td>\n<\/tr>\n
Theistareykjarbunga<\/td>\n-16.83<\/td>\n65.88<\/td>\n<\/tr>\n
Tj\u00f6rnes Fracture Zone<\/td>\n-17.1<\/td>\n66.3<\/td>\n<\/tr>\n
\u00d6raefaj\u00f6kull<\/td>\n-16.65<\/td>\n64.0<\/td>\n<\/tr>\n
Esjufj\u00f6ll<\/td>\n-16.65<\/td>\n64.27<\/td>\n<\/tr>\n
kolbeinsey ridge<\/td>\n-18.5<\/td>\n66.67<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
What can you tell me about Hekla?<\/h3>\n
SELECT * FROM GVPVolcano\r\n WHERE \"Volcano Name\" IS \"Hekla\";<\/pre>\n
There isn’t room to show all the columns as a table, but the data look like:<\/p>\n
Volcano Number =<\/em> 372070
\nVolcano Name =<\/em> Hekla
\nCountry =<\/em> Iceland
\nPrimary Volcano Type =<\/em> Stratovolcano
\nLast Known Eruption =<\/em> 2000 CE
\nRegion =<\/em> Iceland and Arctic Ocean
\nSubregion =<\/em> Iceland (southern)
\nLatitude =<\/em> 63.98
\nLongitude<\/em> = -19.7
\nElevation (m) =<\/em> 1491.0
\nDominant Rock Type =<\/em> Andesite \/ Basaltic Andesite
\nTectonic Setting =<\/em> Tensional Oceanic<\/p>\n
Which is taller, Mt Fiji or Mt Etna?<\/h3>\n
SELECT \"Volcano Name\", \"Elevation (m)\" FROM GVPVolcano\r\n  WHERE \"Volcano Name\" is \"Fuji\"\r\n  OR \"Volcano Name\" IS \"Etna\";<\/pre>\n\n\n\n\n\n
Volcano Name<\/th>\nElevation (m)<\/th>\n<\/tr>\n
Etna<\/td>\n3330.0<\/td>\n<\/tr>\n
Fuji<\/td>\n3776.0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Fuji wins! But Etna has been trying hard to catch up recently.<\/p>\n