Accessing the OBIS database

pgAdmin

OBIS uses a PostgreSQL database, which can be accessed using a desktop tool such as pgAdmin.

Use the following configuration to access the OBIS stage database:

host: obisdb-stage.vliz.be
port: 5432
maintenance DB: obis
username: obisreader
password: [contact the OBIS data manager to obtain a password]

Tables

Schema geo

This schema is used for geographical features such as grid and area polygons.

cs5d: 5 degree c-squares
cs1d: 1 degree c-squares
cs6m: 0.1 degree c-squares
eezs: Exclusive Economic Zones (EEZ)
fao: FAO Major Fishing Areas
iho: IHO Sea Areas
lme: Large Marine Ecosystems (LME)
meow: Marine Ecoregions of the World (MEOW)
mwhs: Marine World Heritage Sites (MWHS)

Schema obis

This is the schema holding the main data tables.

drs: all occurrences
dxs: all occurrences (but more fields)
positions
resources
snames: original scientific names
tnames: validated scientific names

Schema portal

points_ex: denormalized view of the occurrences

Common queries

Obtaining taxon occurrence data

There are a few things to keep in mind when querying for occurrence data:

Some records will have a an unaccepted scientific name. This means we can not query the portal.points_ex table using the tname field, if we want to include occurrences recorded with an unaccepted taxon name.
We could use the valid_id field instead, but then our results will not include lower rank taxa.

For example, to retrieve all occurrences of Phocoena, we will need to:

Link all occurrences to their respective accepted taxon.
Check if these accepted taxa either:
- correspond to Phocoena: the valid_id equals Phocoena’s tnames.id
- have Phocoena as a parent taxon: the storedpath includes the full taxonomic path of Phocoena, i.e. the concatenation of Phocoena’s tnames.storedpath and tnames.id

All this can be translated into the query below:

with taxon as (
	select id, storedpath
	from obis.tnames
	where tname = 'Phocoena'
)
select t.tname as accepted, p.*
from portal.points_ex p
left join obis.tnames t
on t.id = p.valid_id
inner join taxon
on t.id = taxon.id or t.storedpath like taxon.storedpath || taxon.id || 'x%'
order by p.tname;

The results of this query will include:

Occurrences of Phocoena
Occurrences of Phocoena species and subspecies such as Phocoena phocoena and Phocoena phocoena relicta
Occurrences of Australophocaena dioptrica, which has Phocoena dioptrica as its accepted name

Manual