# rpostgisLT: Database model

#### Balázs Dukai, Mathieu Basille, David Bucklin, and Clément Calenge

#### 2018-03-02

## The pgtraj database model

### General remarks

This document describes the data model utilized for `pgtraj` as implemented in `rpostgisLT`. There are five tables that make up the core data model:

- pgtraj
- animal_burst
- step
- relocation
- s_b_rel

The tables pgtraj, animal_burst, step, and relocation each have a unique auto-incrementing integer identifier (primary key) column (id), which is referenced in other tables and views as the table name with an “_id" suffix (e.g., pgtraj_id). There are one of each of these tables in every pgtraj schema, which will store the core information that make up the trajectories. These tables are managed completely through `rpostgisLT` functions, and should not be manually altered.

Additional information about locations/steps in `pgtraj` data should be stored in an optional “infolocs” table, which is automatically created in `rpostgisLT` functions (one for each pgtraj). See further information in the table description.

The default schema name in `rpostgisLT` functions is traj, and can store as many pgtrajes as needed. For faster processing and retrieval, it is recommended to subdivide large datasets into multiple pgtrajes, and to create different pgtraj schemas for different projects. All functions default to schema traj, but the user can change the target schema, using the `schema` argument in all `rpostgisLT` functions.

There are two views that are created by default in every pgtraj schema:

- all_pgtraj_summary
- all_burst_summary

which provide important information about all pgtrajes and bursts currently stored in that pgtraj schema.

In addition, two views that are created for every pgtraj stored in the schema:

- parameters_(pgtraj_name)
- step_geometry_(pgtraj_name)

The first (parameters_(pgtraj_name)) calculates geometric parameters of each step in the trajectory, replicating the adehabitatLT ltraj. The step_geometry_(pgtraj_name) view creates (if applicable) PostGIS geometry linestring and points for the step and start and end point of the step, respectively.

### Database model

Fig 1. Tables and views that make up the pgtraj database model; this example is for a pgtraj schema containing two pgtrajes (“pgtraj1” and “pgtraj2”).

animal_burst table
Contains animal and burst names and their relation to pgtrajes.

Name | Type | Constraint | Description
--- | --- | --- | ---
id | serial | PK | Auto-generated numeric ID.
burst_name | text | Not Null | Name of the burst.
animal_name | text | Not Null | Name of the animal.
pgtraj_id | integer | Not Null, FK | ID of pgtraj.
info_cols | text array |  | R data types of infolocs columns, stored to ensure consistency when re-imported into R.

Table constraint: the combination of burst_name and pgtraj_id must be unique.

pgtraj table
Groups of trajectories, with unique names. The highest level in the pgtraj hierarchy; the PostgreSQL/PostGIS equivalent to the `ltraj` in `adehabitat`.

Name | Type | Constraint | Description
--- | --- | --- | ---
id | serial | PK | Auto-generated numeric ID of pgtraj.
pgtraj_name | text | Not Null, unique | Name or other identifier of trajectory group.
proj4string | text |  | Projection string of the ltraj, imported from R.
time_zone | text |  | Time zone of the corresponding ltraj.
note | text |  | Additional notes/comments about the ltraj
insert_timestamp | timestamp with time zone |  | Time the pgtraj was inserted (or last updated)

relocation table
Contains the geometry and time stamp of relocations.

Name | Type | Constraint | Description
--- | --- | --- | ---
id | serial | PK | Auto-generated numeric ID.
geom | geometry |  | Geometry of the relocation.
relocation_time | timestamptz |  | Time stamp of the relocation.
orig_id | integer |  | ID number from the original relocations table in the database

step table
Steps and their relocations.

Name | Type | Constraint | Description
--- | --- | --- | ---
id | serial | PK | Auto-generated numeric ID of step.
relocation_id_1 | integer | FK | ID of the first relocation that form a step
relocation_id_2 | integer | FK | ID of the second relocation that form a step
dt | interval |  | Duration of the step.
r_rowname | text |  | Row name in the ltraj. This value is used for backward referencing between pgtraj and ltraj.
r2n | float8 |  | R2n parameter copied from the ltraj on import from R.
rel_angle | float8 |  | Rel.angle parameter copied from the ltraj on import from R.

s_b_rel table
Relates step and burst, using the PKs of the step and animal_burst tables, respectively.

Name | Type | Constraint | Description
--- | --- | --- | ---
step_id | integer | PK, FK, Not Null | Pointing to the entry in step table.
animal_burst_id | integer | PK, FK, Not Null | Pointing to the entry in animal_burst table.

There can also be one “infolocs” table for each pgtraj, if it is desirable to store additional information with a pgtraj.

infolocs_(pgtraj_name) tables
Contain additional information on locations/steps for each pgtraj (optional).

Name | Type | Constraint | Description
--- | --- | --- | ---
step_id | integer | PK,FK | ID number of the trajectory step (from step.id)
… | any |  | Additional columns as in the infolocs of an ltraj object

### Storing geometries in PostGIS

PostGIS stores spatial data in either geometry or geography data types. The key difference between the two is that the geometry type is based on the Cartesian grid, while the geography type is based on the WGS 84 lon/lat SRID 4326 geodetic coordinate reference system (CRS)1. In order to accurately calculate parameters in trajectories that span across large areas and have steps of tens of kilometers long, the earth cannot be considered in two dimensions, and the data must be appropriately projected. Hence the geography type seems to be the obvious choice to store the relocation and step geometries in the database. Unfortunately, as of PostGIS 2.2, only a limited selection of functions support the geography type2, and many of the geometry-only functions are used to calculate the step parameters.

The R object class ltraj assumes that the coordinates stored in the object are projected and their projection is known. However, in many cases the projection is not known or ignored, therefore the data should be treated as unprojected. Because the geography type stores the data in the WGS 84 lon/lat CRS, this case cannot be handled and assuming the WGS 84 projection for each dataset would be clearly erroneous.

### Managing projections in PostGIS and R

From `adehabitat` version 0.3.21 an ltraj object stores the projection of the relocations in its proj4string attribute. The function `ltraj2pgtraj` tries to find the matching SRID (Spatial Reference Identifier) in the PostGIS database by using `rpostgis::pgSRID`, and if found, uses the SRID for creating geometries. The pgtraj table stores the original proj4string so it can be restored by `pgtraj2ltraj`.

### Managing time zones in PostgreSQL and R

In PostgreSQL all timezone-aware dates and times are stored internally in UTC. For display they are converted to local time in the zone specified on the server. Therefore the time zone of the input ltraj is stored in the pgtraj table’s time_zone field in order to restore the ltraj with the original time zone when using `pgtraj2ltraj`.

This also means that if for some reason the user lives in The Hague (NL), has the dates in his ltraj in EST (e.g. “2003-05-31 17:00:56 EST”), puts the ltraj into the database, then for the same date they will see “2003-06-01 00:00:56” in the database, because the date is displayed in CEST (his local time zone), however internally it is stored in UTC.

A note on managing time zones in R: Care must be taken when changing the time zone of a POSIXt object in R. Consider the following example:

    library(adehabitatLT)
    data(ibexraw)
    attr(ibexraw[[1]]$date, "tzone")
    # [1] "Europe/Paris"
    ibexraw[[1]][1, "date"]
    # [1] "2003-06-01 00:00:56 CEST"
    format(ibexraw[[1]][1, "date"], tz="America/Florida", usetz=TRUE)
    # [1] "2003-05-31 22:00:56 America"

Notice that the time zone ‘America/Florida’ is not valid, as it is not in the system’s time zone database. Therefore the dates are assumed to be in the standard time zone (UTC) and no warning is given. To check the valid time zone names in your system use `OlsonNames()`3.

1. Obe, R.O., Hsu, L.S., 2015. PostGIS in action, Second edition. ed. Manning, Shelter Island, NY.↩

2. http://postgis.net/docs/PostGIS_Special_Functions_Index.html#PostGIS_TypeFunctionMatrix↩

3. http://stat.ethz.ch/R-manual/R-devel/library/base/html/timezones.html↩