How to access the e-Ecology database

General Information

The UvA-BiTS database is a spatial postgreSQL ([1]).

There are two ways to access the UvA-BiTS database:

- If you are a UvA student or worker, 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 order to access the database you must first have a user name and password provided to you by SARA. (Current contact person is Lykle Voort.) Once you have installed the PgAdmin software on your computer, go to File - Add Server. In the 'New Server Registration' window, enter the following:

 Name: flysafe
 Host: services.flysafe.sara.nl
 Port : 5432
 Maintenance DB: flysafe
 Leave all other fields blank, except for the Username and Password fields where you should enter the information provided to you by SARA.

- If you are not affiliated with UvA, you can access the tracking data and metadata related to your project through the UvA-BiTS Virtual Lab: http://www.uva-bits.nl/virtual-lab/ [2]. Here, the database can be accessed from any computer with an internet connection by clicking on the database icon on the Virtual Lab webpage. Your user name and password provided by SARA will be needed to access the data. The data is stored in a postgreSQL database and access is provided through 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.

Setting up an ODBC connection

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. [3]

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

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

This page 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".

test <- sqlQuery(db, query = paste("SELECT * FROM gps.uva_tracking_speed_limited 
 WHERE device_info_serial = 1 AND latitude IS NOT NULL ORDER BY date_time ASC", sep = '' ))

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.