How to access the e-Ecology database

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

Judy Shamoun-Baranes Last updated: February 6, 2012 This page provides information on how to set up an Open Database Connection (ODBC) which will enable many software packages to extract data directly from the database.

General Information

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

The UvA-BiTS database can be directly accessed through the UvA-BiTS phpPgAdmin web service ([2]).

You can also access the database can from other programs by installing an ODBC driver for postgreSQL and establishing a "User Data Source"

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

The public server is at

 server : public.flysafe.sara.nl
 port : 5432
 ssl : required

The tracking data can be found in schema gps. However these instructions can be followed for any of the data included in the flysafe database.

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 an 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)
2. go to User DSN (first tab in Data sources)
3. click ADD
4. in window create new data source select PostgreSQL ANSI click FINISH
5. The ODBC Driver setup window opens. Data source name: give your data source a name (eg GPS). Keep it simple, easily identifiable, 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 services.flysafe.sara.nl or public.flysafe.sara.nl, User Name your user name, SSL Mode require, Port 5432, Password your password. When the window is completed click SAVE
7. Back in the User DSN window you should now see your 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 to the database in Access

You can now 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 data that is in the actual SARA database.

1. Open a new database in Access
2. go to file | Get external data | Link tables
3. in files of type select ODBC databases
4. in select data source click on tab Machine Data Source
5. select the data source name you created when setting up the ODBC connection (eg GPS) click OK
6. Select the tables you want to import click OK

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

Original document Media:Connect_flysafe.doc