How to access the e-Ecology database

General Information

The UvA-BiTS database is a spatial postgreSQL database([1]). In order to access the database you must first have a user name and password provided to you by surfSARA. (Current contact person is Lykle Voort.)

How to access the database

Via the UvA-BiTS Virtual Lab : http://www.uva-bits.nl/virtual-lab/ The database can be accessed from any computer with an internet connection by clicking on the database icon on the Virtual Lab webpage. Access to the postgreSQL database is provided via a phpPgAdmin web service. Log into the database. To see the tables and views available in the database, expand the 'FlySafe GPS Mirror' folder by clicking directly on it or on the + next to it, then click the + next to the 'flysafe' folder, next expand 'Schemas' and finally 'gps'. Now you should be able to expand the database tables and views. Most users will only have access to the data included in tables or views that end with “_limited”; these are subsets of the entire database and include only the data you officially have access to.

Via PgAdmin client software PgAdmin must installed on your computer: http://www.pgadmin.org/download/ Once you have installed the PgAdmin software on your computer, go to File - Add Server. In the 'New Server Registration' window, enter the following:

 Properties tab: 
    Name: flysafe
    Host: db.e-ecology.sara.nl
    Port : 5432
    Maintenance DB: flysafe
    Enter Username and Password as provided to you by surfSARA.
    Leave all other fields blank. Generally it is not recommended to store the password, especially on shared computer.
 SSL tab: SSL = require
 Advanced tab: Check "Connect now" and "Restore env?" boxes.

Connecting via other programs You can also connect to the database via other programs(Access, R, etc.) by setting up an ODBC driver for postgreSQL and establishing a "User Data Source". (See "Setting Up an ODBC Connection" below.)

Now that I have connected, what do all these tables and views really mean? See the UvA-BiTS Data Model page of this wiki.

UvA students and employees working on the UvA network The database can be directly accessed from computers connected to the university network through PgAdmin software. The computer you are working on must first have the PgAdmin software installed. You can also access the database from other programs (Access, R, etc.) by installing an ODBC driver for postgreSQL and establishing a "User Data Source". (See "Setting Up an ODBC Connection" below.) In PgAdmin go to File - Add Server. In the 'New Server Registration' window, enter the following:

 Properties tab: 
    Name: eecology
    Host: db.e-ecology.sara.nl
    Port : 5432
    Maintenance DB: eecology
    Enter Username and Password as provided to you by SARA.
    Leave all other fields blank. Do not store your user name and password on shared computers.
 SSL tab: SSL = require
 Advanced tab: Check "Connect now" and "Restore env?" boxes.

Setting up an ODBC connection (Windows)

ODBC (Open Database connectivity) is a standard method of sharing data between databases and other programs. ODBC drivers use SQL to gain access to data from sources outside the program. Therefore, in order to connect between your database and other programs you must set up an ODBC connection.

Step 1: Download and install the most recent ODBC driver for postgreSQL. [2]

Step 2: Set up an ODBC connection

Do the following (variations may exist between windows operating systems):

1. Go to start | Control panel | Administrative tools | Data sources (ODBC). If you are having trouble finding it, type "ODBC" into the Search box at the top of the Control Panel window.
2. Go to User DSN (first tab in Data sources).
3. Click ADD.
4. In the window Create New Data Source select PostgreSQL ANSI and click FINISH.
5. The ODBC Driver Setup window opens. Data source name: give your data source a name (e.g. GPS). Keep it simple and easily identifiable since this is the name you will use to identify your database from other applications; this will be the ODBC driver name.
6. In the same window fill in the following information: database flysafe, Server public.e-ecology.sara.nl, User Name your user name, SSL Mode require, Port 5432, Password your password. Click TEST to test your connection. When the window is completed click SAVE.
7. Back in the User DSN window you should now see the ODBC driver you just named. Click OK.

After completing these steps many other programs will now recognize your database and be able to communicate with it.

Setting up an ODBC connection (MacOSX 10.8.5 )

The PostgreSQL ODBC driver provided here is a 64-bit driver. It will probably only work if the other components involved in accessing the UvA-BiTS database are also 64-bit. This includes R, iODBC, and libpq. It has been tested on MacOSX 10.8.5 (Mountain Lion) with 64-bit R 3.0.2 (Frisbee Sailing). If you have an older version of MacOSX, and/or a 32-bit version of R installed, this driver may or may not work for you. There's no harm in trying. If it does not work, and you have a 32-bit version of R installed, try using the 64-bit version instead (this will allow R to use more memory as well, possibly speeding up your calculations).

1. Download the ODBC Administrator Tool for Mac OS X v1.0 using the following URL http://support.apple.com/kb/DL895

2. Unpack the .zip file found here [3] into your home directory by double clicking it. Your home directory will usually have your name and is located in Macintosh HD > Users next to the little symbol of a house. This will create a folder called "pgodbc".

3. Open the iODBC Administator.app from within Applications.

4. Select the tab "ODBC Drivers" and click on "Add a driver". A new window will open.

5. The first field is "Description of the driver". Type in "PostgreSQL".

6. Click on "Browse" next to the second field to locate and select the driver file you unpacked from the .zip file you downloaded.

7. Go to folder "pgodbc" in your home directory, and select the driver file "psqlodbcw.so"

8. Make sure the driver is defined as "System", i.e., that the "System" option is selected and not the "User" option.

9. Click on "Add" at the bottom of this window, and "OK"

You should now be able to access the database through R using the R script provided.

Connecting other programs to the UvA-BiTS database (e.g. R, Access, ArcGIS)

This section provides information on how to set up an Open Database Connectivity (ODBC) which will enable many software packages to extract data directly from the database.

Last updated by Judy Shamoun-Baranes on February 6, 2012.

Connecting to the database in R

Here is a very short example that will give an idea of how to connect to the database via the ODBC driver you have just established.

Here you provide the name of the database file or the user DSN name for a given ODBC data source (as described above).

db.file <-"GPS"

Load the RODBC library. Visit the CRAN repository <http://cran.r-project.org/web/packages/> for a list of contributed packages and instructions for installation. Packages may be installed using the GUI interface or using ?install.packages.

library(RODBC)

Establish a connection to the database, the following code is used if reading from an ODBC data source.

db <- odbcConnect(db.file) 

View the table names in the database.

sqlTables(db)

View the field (column) names of a specific table.

sqlColumns(db, table.name)

Query the database, returning data from table gps.uva_tracking_speed_limited, for device_info_serial 1 as dataframe "test".

Connecting to the database in R (mac)

Here is a very short example that will give an idea of how to connect to the database via the ODBC driver you have just established.

Connect to the flysafe database in R with the package RODBC, using your username and password Install the RODBC package and any dependencies

install.packages("RODBC", dep=T)
require(RODBC)

Replace "user" and "password" with your own username and password for the flysafe database

my_username <- "user"
my_password <- "password"

Define the connection and connect from within R

l.connstring <- 'DRIVER=PostgreSQL;'
l.connstring <- paste(l.connstring, 'SERVER=public.e-ecology.sara.nl;', sep = ) 
l.connstring <- paste(l.connstring, 'PORT=5432;', sep = )
l.connstring <- paste(l.connstring, 'SSLMODE=require;', sep = ) 
l.connstring <- paste(l.connstring, 'DATABASE=flysafe;', sep = )
l.connstring <- paste(l.connstring, paste("USERNAME=", my_username, ";", sep=), sep = ) 
l.connstring <- paste(l.connstring, paste("PASSWORD=", my_password, ";", sep=) , sep = ) 

GPS <- odbcDriverConnect(l.connstring, case = "tolower")

From here you can write SQL queries and retrieve the data through R. Here is an example.

Extract GPS data, remember to replace <insert bird id> with the device serial number of one of your tracked birds

gps_test <- sqlQuery(GPS, query = "SELECT * 
FROM gps.uva_tracking_speed_limited s  
WHERE s.device_info_serial = <insert bird ID>", as.is=c(2))

Connecting to the database in Access

After completing the steps for setting up an ODBC connection, you can connect to the database via Access. You have two options. One is to import the data; once this is done your database is not automatically updated but does not require a link to the database.

The second option (described below) is to create a dynamic link to the database. (You must be connected to the network.) In this case you are always viewing updated data that is in the actual SARA database. The first set of instructions is for Access 2007-2010, while the second is for the older version of Access.

1. Open a new database in Access 2007-2010.
2. Go to External data | ODBC Database.
3. Select Link to the data source by creating a linked table. Click OK.
4. In Select Data Source click on the tab Machine Data Source.
5. Select the data source name you created when setting up the ODBC connection (e.g. GPS), then click OK.
6. Select the tables you want to import and click OK.

1. Open a new database in Access 2003.
2. Go to File | Get external data | Link tables.
3. In Files of type, select ODBC databases.
4. In Select data source click on the tab Machine Data Source.
5. Select the data source name you created when setting up the ODBC connection (e.g. GPS), then click OK.
6. Select the tables you want to import and click OK.

You now have access to your selected tables in the database. Have fun.