View source for How to access the e-Ecology database

=General Information=

The UvA-BiTS database is a spatial postgreSQL database([http://www.postgresql.org/]). In order to access the database you must first have a user name and password provided to you by SURF.

=How to access the database=
'''Through the web portal'''

The database is no longer accessible via the phpPgAdmin web service but via a new PgAdmin portal. You can access the database through an ODBC (see below) or via the PgAdmin portal following these instructions:

1. Go to pgadmin.e-ecology.nl and log in with your e-Ecology credentials

2. Click on "Add New Server”

3. In "General" tab fill out the fields as following: 
     Name: UvA-BiTS
4. In the "Connection" tab fill out the fields as following:
     Host name / address: pub.e-ecology.nl
     Port: 5432
     Maintenance DB: eecology
5. Fill in your username and password in the “Connection" tab
 
6. Press “Save"

Once you create a connection to the database you can view the database structure in the left panel. You will have to write your own SQL queries to retrieve data since this service no longer has a GUI to build queries.

'''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.

=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.
[http://www.postgresql.org/ftp/odbc/versions/msi/]

Step 2: Set up an ODBC connection


Do the following (variations may exist between windows operating systems):
# 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.
# Go to '''User DSN''' (first tab in Data sources).
# Click '''ADD'''.
# In the window '''Create''' '''New Data Source''' select '''PostgreSQL ANSI''' and click '''FINISH'''.
# 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'''.
# In the same window, fill in the following information: '''database''' ''eecology'', '''Server'''  ''pub.e-ecology.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'''.
# 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. 

If you are having problems setting up the connection, go back to Step 1 above and make sure that you have installed the most recent version of the PostgreSQL ODBC driver.

=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. Download the zip file found here [http://horizon.science.uva.nl/public/pgodbc_macosx/]. Unpack the .zip file 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.

=Setting up an ODBC connection (Linux) =

1. Install unixODBC and its -dev file
$ sudo apt-get install unixODBC unixODBC-dev

2. Install the postgresql package

  $ sudo apt-get install odbc-postgresql

3. Configure the PostgreSQL ODBC driver in the /etc/odbcinst.ini file

  $ gksudo gedit /etc/odbcinst.ini

4. On Ubuntu, the /etc/odbcinst.ini file will be blank, so you’ll need to add the data to that configuration file. Add the following to the odbcinst.ini file:

  [PostgreSQL]
  Description = ODBC for PostgreSQL
  Driver = /usr/lib/odbc/psqlodbca.so
  Setup = /usr/lib/odbc/libodbcpsqlS.so
  FileUsage = 1
'''Be careful,''' on x64, the path for the driver and setup should look
something like that /usr/lib/x86_64-linux-gnu/odbc/ (use the command $
mlocate psql to find the appropriate path...)


5. Verify that the system is able to see the driver by running the
following command. It should return the label name [PostgreSQL] if all is
well:

  $ odbcinst -q -d

6. Next, configure the /etc/odbc.ini

  $ gksudo gedit /etc/odbc.ini

Here is what the text file should look like

  [GPStracker]
  Driver       = PostgreSQL
  Description  = PostgreSQL connection to the UvA BiTS DataBase
  Servername   = pub.e-ecology.nl
  Port         = 5432
  UserName     = user_name
  Password     = yourpassword
  Database     = eecology
  SSLMode      = prefer
  OPTION       = 3
  SOCKET       =

You should now be able to connect to the database, for example via RODBC.

= 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.

==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 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 provided by SURF:
 my_username <- "user"
 my_password <- "password"

Define the connection and connect from within R:
 l.connstring <- 'DRIVER=PostgreSQL;'
 l.connstring <- paste(l.connstring, 'SERVER=pub.e-ecology.nl;', sep = '') 
 l.connstring <- paste(l.connstring, 'PORT=5432;', sep = '')
 l.connstring <- paste(l.connstring, 'SSLMODE=require;', sep = '') 
 l.connstring <- paste(l.connstring, 'DATABASE=eecology;', 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.

To 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.ee_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 SURF database. The first set of instructions is for Access 2007-2010, while the second is for the older version of Access.


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


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


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