Laurent Etienne
email: laurent.etienne@isen-ouest.yncrea.fr

Cyril Ray
email: cyril.ray@ecole-navale.fr

Elena Camossi
email: elena.camossi@cmre.nato.int

Clément Iphar
email: clement.iphar@cmre.nato.int

Maritime Data Processing in Relational Databases

published or not. The documents may come

L'archive ouverte

Introduction

The analysis and the processing of data from automated surveillance technologies like the Automatic Identification System (AIS), Synthetic Aperture Radar (SAR), satellite images and coastal radars (cf. the chapter of Bereta et al. [START_REF] Bereta | Maritime reporting systems[END_REF]) is an emerging topic. Once fused with contextual information like coastlines, nautical charts, fishing areas, maritime protected areas, sea state and weather conditions, these positioning data can be analysed in order to understand vessel mobility, monitor maritime traffic, uncover activities or risks for the environment, living resources and navigation, and so on [START_REF] Ray | Heterogeneous integrated dataset for maritime intelligence, surveillance, and reconnaissance[END_REF]. The research is expeditiously progressing, and many analysis techniques are applied to attain the above major goals using maritime navigational data. Some of the leading maritime informatics research topics include:

• Vessel trajectory analysis and prediction, recently focusing mainly on AIS and complementary sources to predict and analyse vessel trajectories [START_REF] Borkowski | The ship movement trajectory prediction algorithm using navigational data fusion[END_REF]. • Traffic forecasting, like port volume handling and cargo throughput forecast [START_REF] Jugović | Traffic demand forecasting for port services[END_REF].

• Collision prevention, analysing risk of ships collisions through the analysis of their navigational behaviour [START_REF] Tor A Johansen | Ship collision avoidance using scenario-based model predictive control[END_REF]. • Ship detection, classification and identification, analysing videos from cameras in delimited areas, for instance to detect small vessels [START_REF] Kim | Small infrared target detection by region-adaptive clutter rejection for sea-based infrared search and track[END_REF], or remote sensing images in larger areas [START_REF] Grasso | Ship classification from multi-spectral satellite imaging by convolutional neural networks[END_REF]. • Anomaly detection, mostly adopting data-driven methods to model normal traffic against which any irregular behaviour is associated to potential threats [START_REF] Riveiro | Maritime anomaly detection: A review[END_REF]. • Analysis of human activities at sea, such as fishing, illegal traffic (human beings, narcotics, goods), piracy [START_REF] Gohar A Petrossian | Preventing illegal, unreported and unregulated (iuu) fishing: A situational approach[END_REF]. • Multi-source information fusion, to reduce uncertainty in the data available and achieve better accuracy in analysis [START_REF] Mazzarella | Data fusion for wide-area maritime surveillance[END_REF]. • Dynamics of maritime transportation networks, where trajectory data are considered at an aggregated level through network abstractions designed to analyse behavioral patterns [START_REF] Yu | Massive automatic identification system sensor trajectory data-based multi-layer linkage network dynamics of maritime transport along 21st-century maritime silk road[END_REF].

Research work processing maritime data requires modelling and analysing moving objects associated to maritime navigation and traffic. Considering an existing batch of data, two main processing approaches exist. Some works consider direct processing of data files, using general-purpose data science environments (e.g. Python, R, Matlab) or developing customised tools for spatio-temporal data processing (e.g. [START_REF] Andrienko | Spatial generalization and aggregation of massive movement data[END_REF]). Other works use database management systems (DBMS), which nowadays offer a native support for modelling spatial information, optimised functionalities for spatial indexing and analysis, and efficient integration with the query language.

The latter approach, which is the one illustrated in this chapter, enables a quick development of moving object applications. Thanks to built-in spatial data types and extended DBMS support, developers can exploit the spatial dimension of the application entities. For instance, they can explicitly represent spatial objects and execute spatial queries to compare their topological properties or to calculate their geographical distance. The available spatial types of such DBMS can be extended with user-defined data types and functions, tailored to the specific application or task. The soundness and the efficiency of the resulting model are guaranteed by the DBMS.

This chapter focuses on the support offered by relational DBMS, which are proposed for maritime data storage and querying. While time-consuming for handling very large datasets, and while being mainly used for off-line analysis, relational DBMS suit well the requirements of applications dealing with average-size heterogeneous data that can be described according to a known structured and fixed schema, i.e. where data properties are known in advance and can be represented in tabular form.

Along the chapter, many SQL (Structured Query Language) [START_REF]2016 information technology[END_REF] examples are presented to the reader, who is asked to solve some exercises. 1 A basic knowledge of relational databases and SQL is necessary to understand the examples and solve the exercises. All together, examples and exercises illustrate the spatio-temporal capabilities of DBMSs that can be progressively combined to analyse maritime data for investigating vessel traffic. The examples in this chapter refer to PostgreSQL,2 a widespread relational DBMS. PostgreSQL is a very popular DBMS, fully open source, very robust, and its spatial support, which is provided by PostGIS [START_REF] Strobl | PostGIS[END_REF], is compliant to well established standards for representing spatial features. Post-greSQL also integrates a native temporal support, which is necessary to analyse vessel movements, that corresponds to Allens algebra operators [START_REF] Allen | Maintaining knowledge about temporal intervals[END_REF].

The rest of the chapter is organised as follows. In Section 2 we overview the existing approaches for representing and managing spatial data and moving objects. Section 3 introduces the software the reader needs in order to create, query and visualise a maritime database. Section 4 explains the steps required to create a maritime database, given the heterogeneous, integrated dataset for maritime intelligence, surveillance, and reconnaissance that is described in [START_REF] Ray | Heterogeneous integrated dataset for maritime intelligence, surveillance, and reconnaissance[END_REF]. Using this maritime dataset, section 5 illustrates typical queries and functions, useful to derive additional information and reason on vessel movements. Finally, Section 6 concludes the chapter.

Systems for spatial data and moving objects

In this section, we overview how spatial, specifically geographic data, and moving objects are handled, either using Geographic Information Systems (GIS), or DBMS. The approaches and software products supporting only spatial data are introduced first, followed by some of the existing systems for spatio-temporal data.

Handling spatial data

For decades, the representation and analysis of geographical information have been investigated in the area of Geographic Information Systems (GIS). GIS, like Ar-cGIS 3 , GRASS 4 , and QGIS5 naturally evolved from the digitalisation of cartographic maps. GIS have efficient geographic visualisation and rendering capabilities, which can be combined with multi-layer spatial analysis functions like distance/buffer and topological queries, overlay operations, surface and terrain analysis. However, traditional GIS alone cannot deal with the increasing quantity of data, and do not suite the semantically-driven integration of heterogeneous information that involves both spatial and non-spatial attributes, or complex knowledge discovery tasks like supervised classification. All these tasks require an integrated representation of geographic and descriptive data features, and a greater flexibility in low-level data manipulation than the one offered by traditional GIS, which keep the management of geospatial data and other data types separated [START_REF] Rigaux | Spatial Databases with Application to GIS[END_REF].

To address the needs of emerging spatial-driven applications, relational database systems, once geared towards providing efficient support for simple objects with discrete attributes, have been extended to represent and query geographic data in a natural way. For instance, Oracle 6 and Postgres (now PostgreSQL), were expanded with spatial modules, respectively Oracle Spatial [START_REF] Kothuri | Oracle Spatial, Geometries[END_REF] (now fully integrated in the main DBMS) and PostGIS. Nowadays, all the main DBMS, including Microsoft SQL 7 , MySQL8 , SQLite 9 with SpatiaLite, provide support to cope with geographic data.

The data model of these DBMS has been extended with data types and structures for managing geometric data, relying on established international standards and recommendations10, . 11 Spatial operations and types are fully integrated with the query language, and the DBMS engine is enhanced to map from the query language to the spatial features. Also, spatial indexing is provided for query optimisation. Most of the products include B-Tree+ and R-Tree [START_REF] Guttman | R-trees: A dynamic index structure for spatial searching[END_REF] indexes. PostgreSQL additionally implements the Generalized Search Tree (GiST) [START_REF] Hellerstein | Generalized search trees for database systems[END_REF] to develop customised indexing, mixing, for instance, spatial and non-spatial optimisation.

Most DBMS spatial extensions provide mature support for geometric data. Some products include also extensions for raster data (e.g. Oracle Raster and pgraster, which is integrated in PostGIS). Also, few products support 3D representations, including PostGIS.

This support can be further enhanced with novel abstract data types to represent spatial (and spatio-temporal) data, tailored to the application, analogously to objectoriented DBMS (OODBMS). These (object-) relational DBMS, taking advantage of both spatial and object-oriented functionalities, have been established as main players on the market for storing and querying spatial data. For this reason, nowadays most of GIS enable the use of a spatial DBMS as data storage layer. This hybrid solution combines the advantages of the efficient front-end of GIS and the robust and efficient storage and retrieval capabilities of spatial DBMS.

Few NoSQL, or Not-only-SQL, DBMS, i.e. all the DBMS that adopt alternative models to the (object-)relational one, offer spatial extensions. Among them, few research proposals exist to extend OODBMS to support spatial features, leveraging the model extensibility as described above. More recent NoSQL DBMS, like MongoDB and DocumentDB, which adopt a semi-structured document-based data model, enable the definition of spatial entities supporting GeoJSON objects and geo-spatial queries. ArangoDB is a multi-model (key value, graph and document) NoSQL DBMS that supports natively geo-spatial querying and indexing. Rasdaman (which stays for raster data manager) is a multi-dimensional DBMS for scientific data, which can be queried with an SQL-like language.

NoSQL DBMS relax some data modelling constraints to handle unstructured or semi-structured data, and improve scalability performance as required for big data and parallel processing. For instance, in order to process in parallel multiple vessel trajectories, and multiple areas, they enable to increase, seamlessly for the application, the number of nodes in the infrastructure that are used to host the database, and maintain replicas of the data. However, the gain in efficiency and flexibility comes at the price of relaxing some of the properties that, in traditional SQL databases, guarantee data consistency (namely, ACID properties: i.e. atomicity, consistency, isolation, and durability).

It should be noted that NoSQL solutions do not always outperform relational DBMS. For instance, the authors in [START_REF] Makris | Performance Evaluation of MongoDB and PostgreSQL for Spatio-temporal Data[END_REF] compare PostgreSQL/PostGIS and Mon-goDB performance on a maritime dataset of vessel positions, and show that Post-greSQL/PostGIS outperforms MongoDB. As shown in the Knowledge base of relational and NoSQL DBMS, 12 relational DBMS remain amongst the most popular DBMS.

Spatial-temporal and movement data in databases

While widely studied, the representation of the temporal dimension of spatiotemporal and moving object data in databases remains rather limited or in the research phase [START_REF] Siabato | An annotated bibliography on spatiotemporal modelling trends[END_REF]. In [START_REF] Huang | STOQL: An ODMG-based spatio-temporal object model and query language[END_REF] the authors illustrate a spatio-temporal extension of the Object Data Management Group (ODMG) model, which is a de facto standard model for OODBMS, and of its query language, OQL. A renowned spatio-temporal model is the SECONDO's one, which exploits abstract data types to model moving objects [START_REF] Xu | A generic data model for moving objects[END_REF] and spatio-temporal operations among them.

PostgreSQL natively integrates a support for temporal data that nicely combines with the spatial extension of PostGIS. Additionally, the reader may consider two groups of alternatives. As a first option, a time-series DBMS can be used. For instance, TimescaleDB 13 is an open-source time-series DBMS developed as PostgreSQL's extension, fully SQL and PostgreSQL compliant, which can integrate PostGIS to efficiently handle spatio-temporal time-series data like Internetof-Things observations. Given an SQL table with temporal and location attributes, it transforms it and partitions the data according to the temporal and spatial dimensions and adds indexes for improving access performance. It also offers a small set of analytic functions (e.g. first, which provides the first value of time ordered series) that can be used jointly with PostGIS's functions.

The second group of options implements a full spatio-temporal support. For instance, PostGIS-T extends PostgreSQL on the basis of a formal spatio-temporal algebra [START_REF] Simoes | Postgist: towards a spatiotemporal postgresql database extension[END_REF]. Analogously, Pg-Trajectory [START_REF] Kucuk | Angryk. Pg-trajectory: A postgresql/postgis based data model for spatiotemporal trajectories[END_REF] which is developed by the Data Mining Lab of the Georgia State University, is a PostgreSQL/PostGIS extension designed for spatio-temporal data.

Other spatio-temporal proposals, still based on PostgreSQL, are Hermes and Mo-bilityDB. Hermes [START_REF] Pelekis | Hermes -A framework for location-based data management[END_REF] is an in-DBMS framework for data mining, more particularly for the processing of moving objects. It is a Python library, which is event-driven, failure-handling and PostgreSQL-talking, and enables an easy implementation of Python processes that require PostgreSQL communication. MobilityDB [START_REF] Vaisman | Mobility data warehouses[END_REF] is a PostgreSQL extension for mobility data management, which follows the OGC Moving Features Access specification 14 and defines the operations applicable to timevarying geometries.

Building a maritime information system

A Maritime Information System can be defined as a system for the collection, processing, storage and delivering of maritime information through a visualisation interface. The main purpose of a DBMS, when integrated to such a Maritime Informa-OGC Moving Features Access http://www.opengis.net/doc/is/ movingfeatures-access/1.0 tion System, is to organise, store, access and process the maritime data. The database can be handled and queried through a management tool or an external programming environment (e.g. Python, R, Java). In order to visualise the data, dedicated applications and Application Programming Interface (API), e.g. Grafana, 15 Data Driven Documents (D3), 16 Kibana, 17 visual analytics tools (cf. the chapter of Andrienko et al. [START_REF] Andrienko | Visual analytics for maritime studies[END_REF]) or a GIS software like QGIS can be used.

Action required

Download and install the following software to prepare the working environment (let us note that PostGIS and pgAdmin are in general part of the last PostgreSQL bundles):

• PostgreSQL: https://www.postgresql.org; • PostGIS: https://postgis.net;

• pgAdmin: https://www.pgadmin.org;

• QGIS: https://www.qgis.org.

PostgreSQL DBMS

The DBMS employed in this chapter to illustrate the use of relational databases for maritime data is PostgreSQL. PostgreSQL is an open source, standard compliant and robust DBMS. It adopts and extends the Structured Query Language (SQL) [START_REF]2016 information technology[END_REF] for data manipulation and querying. PostgreSQL runs on all major operating systems and can handle big datasets. PostgreSQL is highly extensible: the users can define their own data types, build customised new functions and interact with the DBMS through the query language SQL, using different programming languages like C, Perl, Java, Python, R, JavaScript, etc., and from shell scripts.

PostGIS spatial extension

The spatial component of the maritime dataset plays a fundamental role for maritime situational awareness. PostGIS is a powerful add-on to PostgreSQL that adds geospatial capabilities to the DBMS. PostGIS integrates both raster and vector types of data which are fully compliant with the OGC Simple Features Specification for SQL, and a large number of spatial operations. PostGIS also implements multiple spatial indexing methods, including R-Tree and GiST. Although PostgreSQL and PostGIS are usually used with two-dimension (2-D) geometries (coordinates X and Y ), PostGIS supports the addition of a third dimension (Z), allowing to reason with three-dimensional geometries.

PGAdmin management tool

Once the DBMS is installed, management tools are useful to setup and query the database. pgAdmin is a multi-platform software that is dedicated to manipulate PostgreSQL databases. Once connected to a PostgreSQL cluster (i.e. a collection of PostgreSQL servers), pgAdmin can create and query new databases and tables. Its powerful query tool supports colour syntax highlighting and graphical query plan displaying.

QGIS visualization tool

QGIS is the last component of our Maritime Information System. It is a user-friendly open-source GIS, which can retrieve, manage, display and analyse the geographic data stored in the PostgreSQL/PostGIS database. In this chapter, QGIS will be used to exemplify the visualisation and creation of maps to show the results of the spatial queries (see Figure 1). Interested readers can refer to Anita Graser's blog, "Free and Open Source GIS Ramblings", 18 for practical illustrations on advanced functionalities for movement data. Moreover, the readers can refer to the QGIS online user guide 19 to get started with QGIS.

Fig. 1 The QGIS desktop visualising five geographical layers (all available in the dataset described in [START_REF] Ray | Heterogeneous integrated dataset for maritime intelligence, surveillance, and reconnaissance[END_REF]): fishing areas (by [START_REF] Vespe | Mapping eu fishing activities using ship tracking data[END_REF]), ports of Brittany, Europe coastline, fishing constraints and world seas.

Getting the maritime dataset

The SQL examples of this chapter are based on a freely available open dataset that has been processed using PostgreSQL and its spatial extension PostGIS. The dataset is heterogeneous, and contains four categories of data:

• Navigation data, i.e. historical AIS positions of vessels navigating around a major shipping route; • Vessel-oriented data (e.g. ship registers, i.e. lists of vessels with their characteristics); • Geographic data (i.e. cartographic, topographic and regulatory context of vessel navigation); • Environmental data (i.e. climatic and sea-state related information).

All data are temporally and spatially aligned to allow for efficient and advanced spatio-temporal analysis. The dataset covers a time period of six months (i.e. from October 1 st 2015 to March 31 st 2016) and provides around 18.6 million ship positions collected from 4,842 ships over the Celtic sea, the North Atlantic ocean, the English Channel, and the Bay of Biscay in France. For additional details on the dataset, see [START_REF] Ray | Heterogeneous integrated dataset for maritime intelligence, surveillance, and reconnaissance[END_REF].

Action required

Access the dataset at: https://zenodo.org/record/1167595

The AIS data included in the dataset aggregate different message types, and are stored in two separated files, which contain respectively the positioning messages of ships, and their nominative messages. Positioning messages are AIS messages of type ITU 1, ITU 2, ITU 3, ITU 18, and ITU 19, where ITU stands for International Telecommunication Union. Nominative messages are AIS messages of type ITU 5, ITU 19, and ITU 24, which contain static information about the emitting vessel, such as its name, dimensions or destination, identifiers like the International Maritime Organization -IMO -number and the Maritime Mobile Service Identity -MMSIcode. Two additional files, containing respectively search and rescue messages, i.e. ITU 9, and aids to navigation messages, i.e. ITU 21, have been also included.

Remark ! SQL queries

The SQL queries presented in this chapter focus on typical and useful features for maritime informatics. They complement the integration queries available in folder "[Q1] Integration Queries" of the maritime dataset. All the queries presented in this chapter have been prepared using local installations (on Windows x86-64) of PostgreSQL 11.5, PostGIS 2.5.3, and pgAd-min4 v4.18.

Creation of the Maritime Database

In this section, a new maritime database will be created and populated. Assuming that the software packages described in Section 3 have been installed, it is possible to connect to the PostgreSQL database server from pgAdmin interface, using the credentials provided during the installation. Once a connection to the database is established, the following command can be executed (in menu Tools -→ Query Tool) to create a new empty database called maritime informatics.

The database schema and the tables

A database is organised and structured using one or more database schemas and several tables. Each schema defines a workspace where new data types, functions and operators can be defined. Organising a database using different schemas is useful for various reasons:

• To organise database objects within logical groups and to ease their management;

• To isolate third-party applications and allow for duplicate objects or function names in different schemas; • To grant users (and groups of users) appropriate access to data and functions that belong to different schemas.

Schema and tables for AIS data

AIS transceivers on board of vessels broadcast vessel positions and interesting information about static ship characteristics such as the vessel name, callsign (i.e. unique vessel identifier for radio broadcasts), IMO number and ship type. Each AIS transceiver has its own Maritime Mobile Service Identity (MMSI), which is registered and attributed by country of flag. This number identifies the source of the AIS message (sourcemmsi). Usually, AIS transceivers broadcast this information to all the ships in the vicinity. However, some AIS messages can be addressed to one specific AIS transceiver, identified using its MMSI (destinationmmsi). Ship's tenders can also have their own AIS transceiver. The tender's transceiver reports in the AIS messages the MMSI of the ship it belongs to (mothership).

For the examples presented in this chapter, AIS information will be organised within a schema named ais data. The SQL instructions below show how to create the schema ais data into the database and create, inside this schema, a new table static ships to store voyage and nominative pieces of information. Note that each attribute has a specific type (integer, text, double, etc.).

( b a c k ) t o s t a r b o a r d i n t e g e r , --D i s t a n c e o f AIS a n t e n n a form s t a r b o a r d ( r i g h t ) t o p o r t i n t e g e r , --D i s t a n c e o f AIS a n t e n n a form p o r t ( l e f t ) e t a t e x t , --E s t i m a t e d Time o f A r r i v a l t o d e s t i n a t i o n d r a u g h t d o u b l e p r e c i s i o n , --S h i p d r a u g h t d e s t i n a t i o n t e x t , --D e c l a r e d s h i p d e s t i n a t i o n m o t h e r s h i p m m s i i n t e g e r , --MMSI o f t h e m o t h e r s h i p t s b i g i n t , --Timestamp o f t h e AIS f r a m e CONSTRAINT s t a t i c s h i p s p k e y PRIMARY KEY ( i d )

--P r i m a r y Key ) ;

Loading the data into the database

The new table can now be populated. Data can be manually inserted into the existing tables using the SQL statement INSERT INTO or, alternatively, directly from files using the PostgreSQL command COPY FROM. The following example shows how to load into the table static ship the static ship information stored in the Comma Separated Values (CSV) file nari static, which is included in the maritime dataset (in folder "[P1] AIS Data"). In the command below, <path to dataset> must be replaced with the local path to the file. As soon as the database table is filled with static data, it can be queried using SQL commands. The query in the next example counts how many different ship names are listed in the table static ships ([Answer: 4,824]).

SQL

Q4.2.2 SELECT DISTINCT s h i p n a m e FROM a i s d a t a . s t a t i c s h i p s ;

The table static ships contains duplicated information about ships, transmitted at different timestamps, because nominative messages (ITU 5, ITU 19, and ITU 24) are automatically repeated at regular time intervals. The duplicated rows in this table can be grouped together using a SQL view, as shown in the following example.

SQL

Q4.2.3

CREATE OR REPLACE VIEW a i s d a t a . s h i p s AS SELECT sourcemmsi , --MMSI i d e n t i f i e r o f t h e s h i p , a t t r i b u t e d by c o u n t r y o f f l a g imo , --IMO number o f t h e s h i p , l i n k e d t o t h e v e s s e l s t r u c t u r e c a l l s i g n , --C a l l s i g n o f t h e s h i p s h i p n a m e --S h i p name FROM a i s d a t a . s t a t i c s h i p s GROUP BY sourcemmsi , imo , c a l l s i g n , s h i p n a m e ;

Using the view ais data.ships, let assess some quality aspects of static AIS information, as proposed in the following exercise. The view ais data.ships and the queries proposed above show that the static ship information is not always accurate, because both fields shipname and sourcemmsi have duplicates and null or empty values. Indeed, this information can be manually modified. Being unreliable and inconsistent, none of these fields can used as a primary key in our tables.

In order to analyse the movement of ships, it is necessary to load their positions in the database. In the maritime dataset, vessel positions are stored in the file nari dynamic.csv.

Do it yourself ! A4.2.2

Within the schema ais data, create a new table named dynamic ships. Fill it with the data of the file nari dynamic.csv.

The following query counts the number of the ships positions contained in the table dynamic ships ([Answer: 19,035,630]).

SQL

Q4.2.4

SELECT COUNT( * ) FROM a i s d a t a . d y n a m i c s h i p s ;

The temporal dimension of data

PostgreSQL defines types, functions and operators to represent, handle and query dates and time based on Allen's interval algebra [START_REF] Allen | Maintaining knowledge about temporal intervals[END_REF]. They enable, for instance, to test temporal relations and manipulate temporal events and intervals (e.g. the function overlaps checks if two temporal intervals overlap; "-" and "+" enable to add or subtract days or hours from a time or a timestamp value).

In the table dynamic ships, the time of each vessel position is represented as an integer (in attribute ts), which expresses the corresponding epoch UNIX timestamp, i.e. the time, in seconds, elapsed since 1970-01-01 00:00:00 UTC (Coordinated Universal Time). In the following example, this numerical value is converted into timestamp, in order to facilitate its interpretation and to express timeoriented queries. In the following example, a new column is created to store the new representation.

SQL

Make it faster !

The queries of the previous section may take a while to complete. This is due to the size of the table dynamic ships and because the table needs to be fully scanned for reading and sorting every timestamp in order to execute the queries. Querying very large tables can take a long time to execute. In order to optimise the queries, especially if they are executed frequently, the PostgreSQL commands EXPLAIN and ANALYZE can be used to understand how the DBMS plans to execute them. For example, the following command asks the PostgreSQL planner what is the execution plan for the last query in the previous section.

PostgreSQL

Q4.4.1

EXPLAIN ANALYZE SELECT min ( t ) , max ( t ) FROM a i s d a t a . d y n a m i c s h i p s ;

In the answer, the PostgreSQL planner indicates that it would perform a sequential scan (Seq Scan) of the table. The sequential scan can be avoided if the column t, which appears in the SELECT clause of the query, is previously sorted and indexed. Indexes can require time to build up, and use storage space on the Post-greSQL server, but they can drastically decrease the time required to execute a query.

In the SQL example below, a Binary Tree (BTREE) index is created on column t. This index is particularly efficient for reading attributes whose values have a linear ordering like temporal, numerical or string attributes.

SQL

Q4.4.2 CREATE INDEX i d x d y n a m i c s h i p s t -name o f t h e i n d e x ON a i s d a t a . d y n a m i c s h i p s -name o f t h e t a b l e t o i n d e x USING b t r e e ( t ) ; -i n d e x i n g t e c h n i q u e ( c o l u m n s t o i n d e x )

After creating the index, a new investigation of the query plan (as for the example above) shows that the Index Scan of the query would take a few milliseconds.

Remark !

In order to optimise the queries on very large tables, do not forget to define indexes on the columns that are often queried.

Do it yourself ! A4.4.1

Find how many different ships have broadcast their positions on AIS on January 1 st 2016 and display their ship names. Which tables are required to answer this question? Do you require indexes on these tables? [Answer: 79 rows, 78 different ship names]

Make it geographic !

The geo-spatial component is an important feature of the maritime dataset. Ships move in a specific spatial context made of coastlines, straights, ports, mooring areas, restricted areas and bathymetry. This information should be considered when analysing the maritime situation. In order to support this variety of geographic objects, PostgreSQL can be extended with PostGIS that adds geographic types, functions and indexes to the DBMS. In order to activate the PostGIS extension on a PostgreSQL database, it is sufficient to execute the command below.

PostgreSQL Q4.5.1

CREATE EXTENSION p o s t g i s ;

Geographic objects

A way to represent spatial objects, such as moving vessels, is to use simple geometric features such as points, lines and polygons along with extra alpha-numerical attributes, e.g. the name of the spatial features. This way of modelling spatial objects is called vector representation. The coordinates of spatial objects are expressed in a given Coordinate Reference System (CRS). Thanks to PostGIS, PostgreSQL can handle the following geometric data types:

• POINT, defined using spatial coordinates in a CRS;

• LINESTRING, ordered set of POINTs;

• POLYGON, defined based on a collection of rings, at least one outer and possibly inner rings (representing, e.g. enclaves in countries, reservoirs), given as LINESTRINGs; • MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, i.e. sets of geometric objects of the same base type; • GEOMETRYCOLLECTION, a set of geometric objects of various nature.

In table dynamic ships, vessel positions are given by their coordinates, i.e. longitude and latitude, specified in two separated columns, lon and lat, respectively. The PostGIS function ST MakePoint creates a POINT from two coordinates, given their CRS. In the example below, the World Geodesic System 1984 (WGS84) is used. It is identified in PostGIS through its Spatial Reference Identifier (SRID) as defined by the European Petroleum Survey Group (EPSG:4326 20 ). The World Geodetic System 1984 is extensively used to define worldwide locations by longitude and latitude. This CRS is used by the GPS satellite navigation system integrated into AIS transceivers. The PostGIS function ST SetSRID is used to specify the CRS' SRID of the newly created column. As the table dynamic ships has 19 million positions, the update process may take a while (about 10 minutes). The database must be aware of the data's CRS before executing the commands in the example above. Data's CRS must be listed in the system table spatial ref sys, which is included in the PostgreSQL database public schema. The public schema is automatically created in every PostgreSQL database and contains system tables and functions. It is also used to store all the user-defined tables that are created without referring to any specific schema.

Remark !

Geographic positions on Earth can also be projected on a flat plane, introducing some distortions. Each map projection preserves important properties (direction, angle, shape, area, distance, etc.) while distorting others. Once projected, distances between objects can be computed using the reference metric of the map projection (e.g. meters). Each CRS is usually customised for a specific area. The maritime dataset covers Europe and provides data expressed using CRS WGS84, and ETRS89 / LAEA Europe (EPSG:3035 a ). The CRS ETRS89/LAEA Europe typically suits statistical mapping at all scales and covers the European Union (EU) countries onshore and offshore. PostGIS can project geometric objects from one CRS to another. In the example below, the function ST Transform is used to project vessel positions expressed in WGS84 into the projection ETRS89/LAEA Europe CRS (EPSG:3035).

Integrating contextual data

The maritime dataset contains also complementary data like ports' positions, restricted and protected areas, coastlines, weather and ocean conditions. These data have a different nature than AIS data, which are continuously streamed, and provide information useful to contextualise and understand the vessel movements. Conveniently, a dedicated database schema, with name context data, can be created for these data.

Action required

Create a new schema context data in the database maritime informatics .

Loading spatial Shapefile data

All contextual (geographic) data in the dataset are in the same format, i.e. Shapefile, which is a well-known format for vector (i.e. geometric) data. Shapefile data are stored in multiple files, which have the same name and different extensions and represent different geometric aspects, as follows:

• SHP: is the shape of the geometric objects;

• DBF: contains alpha-numerical attributes;

• PRJ: is the coordinate reference system of the geometric objects;

• SHX: is a shape geometric index. Shapefile data can be imported in a spatially enabled PostgreSQL database using the PostGIS tool shp2pgsql. The example below uses shp2pgsql to load the Shapefile data stored in Europe Coastline (Polygone).shp. Data are loaded into the database maritime informatics. A coordinate system (ETRS89 / ETRS-LAEA, 3035) must be specified to correctly import them. Figure 3 shows the interface of the shp2pgsql tool.

Action required

In order to load the European coastline in the maritime informatics database, execute the following steps:

1. start the shp2pgsql interface (shp2pgsql-gui); 2. load the file Europe Coastline (Polygone).shp; 3. rename the target table into coastlines; 4. select the schema context data; 5. set the coordinate system to ETRS89 / ETRS-LAEA (3035); 6. once the import parameters are set up, import the data.

Upon a successful upload, pgAdmin can be used to preview the first 100 rows of the newly populated table.

Action required

Using pgAdmin, to preview the first 100 rows of table coastlines:

1. right click on the table coastlines; 2. select View/Edit data, then First 100 rows.

Remark !

In pgAdmin, the "eye" button on a geometric column header enables to see the geometric shape of the first 100 rows in a spatial table.

Action required

In pgAdmin, click on the "eye" button on the column geom3035 of the

Action required

From the dataset, select the following Shapefiles and load them in the database maritime informatics as explained above:

•

Action required

In QGIS, add the ports, fishing areas and fishing constraints geographic layers in your map as illustrated in Figure 1. Layers can be ordered using the QGIS Layer Panel. Layers should be positioned wisely. To be visible, the Port layer should be displayed above the coastline layer.

Executing spatial queries

Spatial queries evaluate the relationships that hold among objects' geometries. Post-GIS supports the 8 region connection calculus (RCC8) [START_REF] David A Randell | A spatial logic based on regions and connection[END_REF] and the Dimensionally Extended nine-Intersection Model (DE-9IM) [START_REF] Egenhofer | A mathematical framework for the definition of topological relations[END_REF], and complies with the OGC Simple Feature Access ( [START_REF]Geographic information. simple feature access. sql option[END_REF]), which defines the supported routines to test spatial relationships between two geometric objects. PostGIS functions that output a value, either a distance (i.e. ST Distance) or a Boolean value (all the others) are shown in Figure 4. PostGIS functions that output a geometry are shown in Figure 5. All functions can be applied between geometries of various types (i.e. POINTS, LINESTRINGS or POLYGONS), as far as the coordinates of these objects are defined in the same CRS. These spatial relationship functions can be combined with temporal operators, enabling to express complex spatio-temporal queries. The functions ST Equals, ST Disjoint, ST Intersects, ST Touches, ST Crosses, ST Within, ST Contains, and ST Overlaps evaluate whether the corresponding spatial relationship holds between two input geometries, and return a boolean value. The function ST Distance returns a real number representing the minimum distance between the two geometries. Other functions return the geometries resulting from the application of geometric operations (ST Buffer and ST ConvexHull) (cf. Figure 5) or set-based operations (e.g. ST Intersection, ST Difference, ST Union, ST SymDifference) (cf. Figure 6) . An exten- sive presentation of all these geometry processing functions is available on PostGIS website. 22The query in the following SQL example illustrates the use of such spatial functions. It searches for ships (name and unique identifier, i.e. the ship MMSI) that stopped within 500m from a port in Brittany on January 1 st , 2016. For each ship, the name of the port and the time spent by the vessel in the port is also shown.

Remark !

The distance between ship positions and port locations is calculated using the PostGIS function ST dWithin. The distance threshold (500m) can be visualised by creating a buffer around each port location using the function ST Buffer.

SQL

Spatial indexes

Similar to classical SQL queries, spatial queries can be optimised using indexes applied to spatial columns. PostGIS supports the GiST index, which indexes the bounding boxes, or minimum rectangles, enclosing the spatial extent of the geometries in a geometric column. For instance, the bounding boxes of the geometries in Figure 7.a are depicted in Bounding boxes allow for a compact, while simplified, representation of geometries, that can be used for efficiently comparing the geometries in spatial queries. When a SQL query refers to an indexed geometry, a spatial relationship is firstly evaluated using the spatial extent of the geometry (Figure 7.c). Afterwards, only for the subset of the potentially matching geometries, the exact spatial relationship is processed and refined. In the example of Figure 7, a query on (a) searching for intersecting geometric objects would exclude the yellow circle, because its bounding box does not intersect with any other geometric object's bounding box. The precise computation of the intersection is performed only between the blue pentagon and both the red line and green triangle (as they are the only boxes to overlap). With the index, the computation of the complex polygon intersection requires only two comparisons (first between the blue pentagon and the red line, then between the blue pentagon and the green triangle). Checking every geometric object with each other would have required Execute again the SQL query of the example (looking for the ships that stopped at less than 500m from a port in Brittany on January 1 st , 2016). After creating a spatial index on column geom3035, you should get the answer faster than without the spatial index. Then, search for the vessels (names and identifiers) that fished more than 15 minutes on January the 22 nd 2016 within any fishing area. Usually, a fishing vessel fishes at a speed between 2.5 and 3.5 knots.

[Answer: 7 vessels].

Extending PostgreSQL functions

As described in the previous section, PostGIS defines functions to manipulate and query geographic data. These functions extend the set of data management functions provided by PostgreSQL. Novel functions may be created using procedural programming languages (PL) like PL/pgSQL, PL/Python, PL/Tcl, PL/Perl, PL/Java, PL/R, PL/sh.23

Remark !

You can view the PostgreSQL and PostGIS functions in the folder "Functions" which is included in every database schema.

In the following example, a PL/pgSQL function is defined to search for the name and the identifier, i.e. the IMO number, of a vessel, given its MMSI. The function returns the result in formatted text.

Understanding vessel movements with trajectory-based queries

A trajectory can be defined as "a record of the evolution of the position (perceived as a point) of an object moving in space during a given time period" [START_REF] Spaccapietra | A conceptual view on trajectories[END_REF]. Positionbased queries (i.e. relying on geometric data of type POINT) are easy to formalise and can provide meaningful information and statistics about these movement data. However, they have several limitations. First, there is a computational limit, as such spatial queries are very expensive. Second, the information they provide is sometimes limited by the update rate or the coverage of the sensor that measures the object's position. 24 In the maritime domain, for instance, it is difficult to identify with certainty a vessel that has crossed a narrow passage, in order to check whether it has entered a restricted area or to calculate exactly the minimum distance to the coast. The notion of trajectory, for instance as discussed in [START_REF] Parent | Semantic trajectories modeling and analysis[END_REF], whilst sometime complex to implement, has been introduced to address these limitations. It underlies the use of filtering and clustering techniques that make it possible to clearly define the starting, intermediate and ending points of a trajectory.

In this section we will create vessel trajectories by connecting the points of the same ship between them, in the form of polylines. This is a simple implementation of a stop-move model of trajectories [START_REF] Spaccapietra | A conceptual view on trajectories[END_REF]. Ship trajectories are segmented using time intervals. During these time intervals, the ship positions can stay still (stop) or change (move).

In PostGIS, the move part of the trajectory can be represented as a LINESTRING, which connects sequences of positions. In order to derive stationary (stop) areas, multiple AIS positions of anchored ships may be spatially grouped together, which can be spatially represented by cluster centroids. The overall ship movements can be modeled with a graph, whose nodes depict the stop locations (e.g. ports, mooring areas), and edges link stops. Ship trajectories can be grouped along edges and aggregated using statistics. This node-edges model can itself be manipulated, queried, analysed, for instance to analyse ships' life cycle as shown in [START_REF] Itani | Mining Ship Motions and Patterns of Life for the EU Common Information Sharing Environment (CISE)[END_REF].

In order to store the trajectories and the results of the data analysis queries that will be presented in the following sections, the creation of a new schema named "data analysis" is required.

Action required

Define a schema data analysis to store all the results of the data analysis queries.

Characterising port areas through spatial partitioning

Spatial partitioning enables to understand the essential characteristics of movement data. With this aim, Andrienko & Andrienko [START_REF] Andrienko | Spatial generalization and aggregation of massive movement data[END_REF] partition movement data according to a Voronoi tessellation [START_REF] Aurenhammer | Voronoi diagrams: a survey of a fundamental geometric data structure[END_REF]. A Voronoi diagram is a partition of a plane into regions (Voronoi cells) around a set of seed points. The voronoi cell around a seed point encompass all points of the plane closer to that seed point.

In this section, a Voronoi tessellation will be used to get an approximation of the area of competence of each port. In most official databases, ports are represented by geometrical points. This is also the case of the data in table ports in schema context data. This representation is not effective, in extracting, for instance, from the database the vessels that are stopped in a port at a certain instant in time.

In order to answer this query, ports should be represented by geometrical regions, on which e.g. the PostGIS function ST Within could be applied. In order to derive ports' regions, a space partitioning must be created. Note that, since ports are not equally distributed along the coast, a uniform partition of the space would not be accurate to answer the query above.

By constructing a Voronoi tessellation of the space based on port locations, vessel positions can be dynamically associated to ports. The Voronoi Polygons around a set of points can be computed using PostGIS ST VoronoiPolygons function. For instance, given the tessellation in Figure 8, stopped vessels (i.e. with null or negligible speed over ground) can be associated to the closest ports using the spatial operators described above. Afterwards, the distance between the stop location and the port can be computed.

In the example below, a new table (ports voronoi) is created to contain the Voronoi tessellation based on port locations. A GiST index is created to optimise the access to the newly created geometry. The following SQL query creates a new table (non moving positions) dedicated to the storage of stopped vessel positions. Thanks to the Voronoi tessellation in table ports voronoi, each stop position can be matched to a unique port. Once matched, the distance between a vessel stop and the associated port can be computed using the built-in function ST Distance.

Detecting and clustering ship stops

As it will be illustrated in the chapter of Andrienko et al. [START_REF] Andrienko | Visual analytics for maritime studies[END_REF], clustering techniques can be applied to group vessels stops outside ports and for detecting the different docking areas within a port. In the example of the previous section, many vessel stops are located outside the ports, in areas which are likely mooring areas.

Detecting ship stops

The analysis of vessel movement begins with the identification of stops. A stop can be defined as a temporally ordered sequence of vessel positions with speed (i.e. speed over ground, SOG) equal to zero or below a very low threshold. The queries presented in the next examples create, for each vessel, the sequences of stops extracted from the history of the vessel's positions.

To build this sequence, first, an auxiliary table of successive position pairs, namely segments is created. For each ship, this table enables us to connect with a line segment every consecutive pair of AIS positions. The table is ordered according to the MMSI number and the position timestamp (ORDER BY mmsi, t), and the PostgreSQL function LEAD will be used to access the data in the next row of an ordered table.

From this table, for each vessel, stop events can be extracted and filtered according to the vessel speed that is associated to the preceding and the following positions of each segment. This table is also useful to detect transmissions gaps between vessel positions (cf. the chapter of Patroumpas et al. [START_REF] Patroumpas | Online mobility tracking against evolving maritime trajectories[END_REF]) and GPS malfunctions (e.g. detecting unfeasible speeds. A new index combining the vessels' MMSI and the timestamp of the vessels' positions is created to optimise the query. The SQL commands for creating the index and the table are given in the example below. Once the table segments is created, stop events can be detected. Aligned with the chapter of Andrienko et al. [START_REF] Andrienko | Visual analytics for maritime studies[END_REF], each stop starts immediately after a move, and ends as soon as the vessel starts moving again. Starting from the data in table segments, segments whose initial speed is above the speed threshold, and whose final speed is below the speed threshold, represent the beginning of stops (i.e. the vessel decreases its speed and goes steady). The end of a stop event is detected conversely.

The following example creates the auxiliary tables stop begin and stop end, which contain the potential starting and ending positions of vessel stops, selected as just described. In this example, a speed threshold of 0.1 knot is used. To optimise the future access to these tables, indexes are created. In the example above, LATERAL is a reserved PostgreSQL keyword that enables cross-references between the main query and the subquery (at the right of LATERAL. In this case, it is used to compare the time of stop begin.t begin with stop end.t end.

Not all the detected stops are meaningful. For instance, stops that last for a short time, or are spatially too spread, could be filtered out. Similarly, stops with few vessel positions can be discarded. In the example below, the centroid of each vessel' stop cluster and the number of associated positions are computed. In the following example, indicators on the dispersion of the vessel's positions around the centroid of the cluster they belong to are computed. Once aggregated, this information offers some intuition about the nature of the stop. Additional columns are created in the table stops to store the computed indicators (average and maximum distances from the centroid). In order to illustrate to use of the structures built above, the next query calculates how many of the vessel' stops lasted more than 5 minutes, involved more than 5 consecutive positions and occurred in average within 10 m of the centroid of the vessel' stop cluster.

Clustering ship stops

The auxiliary structures created in the previous section calculated, for each vessel, its stop clusters, i.e. the areas where the vessel was steady. In this section a densitybased algorithm will be used to detect stationary areas. Density-based clustering algorithms [START_REF] Millefiori | Scalable and distributed sea port operational areas estimation from ais data[END_REF][START_REF] Vespe | Mapping eu fishing activities using ship tracking data[END_REF] can be used to extract from data stationary areas, turning points in trajectories, activities areas, and so on. PostGIS implements the well-known Density-Based Clustering of Applications with Noise algorithm (DBSCAN) [START_REF] Ester | A density-based algorithm for discovering clusters in large spatial databases with noise[END_REF]. This algorithm clusters geometric objects together provided there are more than a minimum number (n) of neighbouring objects located within a threshold distance (d). These parameters should be tailored to the area under consideration.

In the following example, DBSCAN is used to cluster stop centroids in order to detect frequent stop areas. In the example, n defines the minimum number of stops to form a cluster; d defines the minimum distance between stops belonging to two different clusters. The smaller the d and n parameters, the bigger the number of resulting clusters. If d is large, then multiple mooring areas on a same dock will be clustered together. A similar approach is also presented in the chapter of Andrienko et al. [START_REF] Andrienko | Visual analytics for maritime studies[END_REF] to detect stopping areas.

In the example below, groups of stops lasting more than one minute and having at least 5 different occurrences within 50 m distance range are clustered and stored in table cluster stops. DBSCAN generated 354 different clusters that can be visualised using QGIS. These clusters are precise enough to detect different mooring locations within big ports like the one of Brest as illustrated in Figure 9. In order to to define the location of frequently used docking and mooring areas, the spatial hull of these clusters can be computed.

The convex (respectively, concave) hull of a set of points represents the minimum convex (respectively, concave) geometry that encloses all the points within the set. The Minimum Bounding Circle represents the smallest circle that fully contains all the points of the set. All these polygons can be computed using various PostGIS functions.

The area of the spatial hull can also give hints about the clustering process quality. Clusters with high spatial dispersion will have a bigger polygon surface. This can be acceptable for mooring areas outside a port, but within a port smaller clusters are likely more adequate to match docking areas. The following query shows how to compute different spatial hulls of vessel stops. For each cluster, statistics are also included. In Figure 10, the convex hulls of vessel stops in the port of Brest are visualised with QGIS.

Extracting trajectory tracks and the navigation graph

Maritime traffic can be modelled as a graph whose nodes correspond to the stationary areas (e.g. mooring or docking areas) and edges represent the vessel movements between these areas. Relying on such a graph, vessel movement can be analysed [START_REF] Itani | Mining Ship Motions and Patterns of Life for the EU Common Information Sharing Environment (CISE)[END_REF][START_REF] Laddada | Graph-based analysis of maritime patterns of life[END_REF][START_REF] Carlini | Uncovering vessel movement patterns from ais data with graph evolution analysis[END_REF]. For example, it is possible to count and identify the incoming and outgoing destinations from each stationary area, to calculate the traffic density, and to identify the most frequently used tracks.

In the examples that follow, and similarly to the computation of stops, vessel tracks will be created considering the successive positions of the same ship between a track start and a track stop. By definition:

• the start of a vessel track is the end of the previous stop event; and • the end of a track is the start of the next stop event.

In the following example, a new table tracks is created and populated with stop events selected from the table cluster stops, which already contains all the filtered stops with associated cluster identifiers. These will be used as the nodes of the vessel traffic graph. An index is added to optimise the selection of temporally ordered points. As before, the reserved word LATERAL enables to cross reference the elements of the first sub-query with alias q1 in the second sub-query q2 (q1.mmsi=q2.mmsi AND q2.t begin>q1.t end).

In the next query, the geometry of each track move is represented as a segment connecting consecutive track stops. Ship positions between the t start and t end timestamps of the trajectory are selected, ordered with respect to time and connected to form a LINESTRING using the ST makeline function of PostGIS. The SQL example that follows creates a new table (graph edges) to represent the traffic graph of the Brest area. For each graph edge, statistics on the underlying traffic, like the number of associated vessel tracks, the number of ships navigating it, the average trajectory duration and length, are extracted. These indicators are stored in the table and can be visualised for analysis. The traffic along the edges of the graph is oriented. In order to improve the visualisation of the traffic statistics on a map, the lines of the graph edges can be bent using a custom PL/pgSQL function. Figure 12 illustrates the maritime traffic graph obtained using this function. The edges thickness is proportional to the number of trajectories associated to the track (i.e. thickest edges represent the most frequent routes between stationary areas). The navigation graph shown in Figure 12 is a simplified, summarised version of the density map shown in Figure 11.

Managing data quality using constraints

The previous sections highlight typical PostgreSQL and PostGIS features to process moving object data. The readers should be aware that the results of these queries may be affected by the quality of data. Apart from errors and irrelevant messages, AIS data in the given maritime dataset have been provided as received, including duplicates and other veracity issues. The coverage of the data is also not uniform, with 70.5% of vessel positions located in a range of 10 km from the AIS receiver [START_REF] Ray | Heterogeneous integrated dataset for maritime intelligence, surveillance, and reconnaissance[END_REF]. Erroneous or missing AIS positions influence the quality of results but raise interesting algorithmic and processing challenges, therefore these veracity issues have been maintained in the dataset, which realistically represents the situation in the area and the period it refers to.

The accuracy of maritime clustering and trajectory tracks depend on data quality. For instance, the readers may compare the movement of passenger ships within the Brest roadstead versus the movement of passenger ships travelling to the Ushant and Molène islands where the data quality is lower. Pre-filtering, error-checking algo- rithms can affect the results as much as the variation of the analysis parameters (e.g. the speed threshold). Reasoning on intermediate structures can provide meaningful data quality information. For instance, the table segments can help detecting abnormal situations that can be ignored, like movements with extremely long duration, excessive distances between positions, or unfeasible speeds. Trajectories with unknown or erroneous MMSI identifiers could also be flagged as abnormal and discarded by the analysis.

In the database, integrity constraints may be used to manage data quality issues. Figure 13 presents two of the database schemas we built along the chapter, i.e. ais data and context data. The structure of the tables in these schemas is reported, with column names and types. Adding relations and constraints which are another essential part of database model design allows to manage data properties and to encompass the aforementioned quality issues in accordance with the database application. For example, integrity constraints may prevent that a ship appears travelling in two locations at the same time; may ensure that the vessel speed is always a positive value; and may guarantee that the vessel heading has either a default value (i.e. correctly setting it to 511), or is between 0 and 360 degrees.

constraints express integrity rules on the data in the specified columns. For instance, a NOT NULL constraint prevents the data in a column to assume the NULL value. Table-level constraints define additional rules that apply to all the data in a table. For instance, a PRIMARY KEY constraint combines a NOT NULL and a UNIQUE constraints, ensuring that all the data in a column (or in multiple columns, in combination) have unique values over the table. Figure 13 includes PRIMARY KEY constraints in both ais data and context data schema.

Remark ! Modelling database constraints

Incorporating constraints in the database requires altering the tables' structure. Preferably, this modelling step should be accomplished altogether with the generation of the database, preceding the data insertion. Constraints can also be integrated along an iterative process, throughout the creation and the subsequent modification of the database.

Figure 14 presents the entities created to analyse the data along the chapter, and stored in the schema data analysis. In the following, we are going to extend this part of the maritime database data model with constraints that preserve the data integrity when new operations (e.g. INSERT, UPDATE, DELETE) on tables and columns are executed. Cardinality constraints define how many entities may participate in a relationship. A many-to-many relationship is realised by a table that connects the entities in two other tables. One-to-many relationships are achieved with foreign key constraints, which maintain the referential integrity of data between two related tables. A FOREIGN KEY specifies that the values of the data in one or more columns must exist in a related table.

In the following example, a foreign key constraint is defined to ensure that all the ports for which a Voroni cell has been calculated, i.e. the ports in the table data analysis.ports voronoi, exist in table context data.ports. In the example, after defining the tables' primary keys, a FOREIGN KEY constraint is specified altering the table data analysis.ports voronoi. This constraint realises a one-to-many relation between the two tables, as illustrated in Figure 15. The condition ON DELETE CASCADE in the SQL example avoids the creation of inconsistent, orphan Voronoi cells, which may be created when ports are removed from table context data.ports but the corresponding Voronoi cell still exists. The constraint avoids this situation triggering the automatic deletion of the Voronoi cell that is associated to the deleted port. As a result of the activation of the foreign key constraint in the example, when creating a Voronoi cell for a port, this port must exist, i.e. all the ports in the table data analysis.ports voronoi must match some port in the referenced table context data.ports (cf. keyword REFERENCES). However, the contrary may not hold, i.e. adding a new port without recomputing the Voronoi tessellation is allowed. In order to implement the dual constraint, a one-to-one relation must be defined. In this case, adding a new port would require generating the associated Voronoi cell.

Remark ! A note on one-to-one relations

One-to-one relations are not well represented in standard SQL, because they lead to a logical union of two tables. As a solution, reciprocal foreign keys may be used, i.e. each table in the relation has a foreign key reference to the primary key of the other table.This solution creates a circular dependency that may block the insertion of new data in the tables. Luckily, constraints may be deferred, i.e. lazily validated, allowing to temporarily violation of the integrity of the relation to enable the data insertion. The option DEFERRABLE can be used with this aim when defining the constraint.

As an alternative to the definition of reciprocal foreign keys, a one-to-one relation may be obtained by defining a database trigger to enforce it. This solution is illustrated in the following example. The trigger uses the database function context data.rebuild table ports voronoi(), which automatically generates a voronoi tessellation on the basis of the ports existing in table context The trigger can be tested by deleting a port from the database, as in the following example. As a result of the following SQL, the Voronoi table should be automatically updated.

Action required

Study the database tables for identifying potential relations among them. Study the table columns, their types and expected values (cf. the files README in the maritime dataset) in order to define additional constraints using the aforementioned NOT UNIQUE, PRIMARY KEY, FOREIGN KEY keywords. Consider also the CHECK constraint, which allows to evaluate and check a condition when inserting new data or updating existing ones, and DEFAULT, which permits to assign a default value when a new row is inserted in a table.

Summary and Conclusion

More than two decades ago, in an essay entitled Marine Informatics: a new discipline emerges, Roger Bradbury postulated that the scientific community was on the way to creating a new holistic discipline addressing the challenges of marine data integration and analysis [START_REF] Bradbury | Marine informatics: a new discipline emerges[END_REF]. One of the major issues he identified at that time was the lack of regularity and sparsity in the data collected, which were also temporally scattered. Nowadays, the large variety of maritime sensors together with scientific background and techniques for the monitoring, analysis, and visualisation of sea data has revolutionized the domain (cf. the chapter of Bereta et al. [START_REF] Bereta | Maritime reporting systems[END_REF]). Positioning data correlated with contextual heterogeneous data as provided by the maritime dataset used in this chapter makes Bradbury's vision at with respect to ships' movements. This chapter, in line with Bradbury's vision, takes advantage of the navigation data brought by the maritime dataset to illustrate the benefits of relational database for maritime informatics. Specifically, the chapter addresses the design, storage and querying of maritime data through a spatio-temporal DBMS. In particular, the functionalities of a relational DBMS have been illustrated, due to the ability thereof to find, define, sort, modify, link and transform data in complex databases, while guaranteeing the user a robust layer for spatial analysis.

The open-source relational DBMS PostgreSQL, enhanced with the extension PostGIS for manipulating spatial features, was used to exemplify the concepts presented in the chapter. It is worth noticing that the proposed technical environment is open source, freely available and standard based, and is used in many academic and professional applications. As such, the reader may easily find additional material to extend the examples proposed herein. Furthermore, this technical choice is not to be considered as a limitation, because the examples of commands proposed in the chapter can find an easy correspondence in other DBMS that offer a spatial support.

The spatial representation, analysis and visualisation techniques presented in this chapter are a useful basis to understand the analytics approaches that are presented in the rest of the book, in particular in the chapters of Tampakis et al. [START_REF] Tampakis | Maritime data analytics[END_REF] and of Andrienko et al. [START_REF] Andrienko | Visual analytics for maritime studies[END_REF], which discuss data analytics and visual analytics, respectively.

Bibliographical Notes

The interested reader can refer to some additional material to get practical illustrations on the functionalities offered by DBMS. The authors in [START_REF] Rigaux | Spatial Databases with Application to GIS[END_REF] provide an introduction on the theoretical aspects of spatial databases, useful to better understand the different spatial object models and the available formats. An overview of the most important aspects of spatio-temporal databases is given in [START_REF] Koubarakis | Spatio-Temporal Databases -The CHOROCHRONOS Approach[END_REF], which presents the main research results of the CHROROCRONOS project. 27 For the last research updates, the reader may refer to the series of the International Symposium on Spatial & Temporal Databases (SSTD).

For additional material on OODBMS, the works extending the SECONDO DBMS offer many examples on how to extend an OODBM model to support complex spatio-temporal queries like group spatio-temporal patterns [START_REF] Attia | Group spatiotemporal pattern queries[END_REF] and rangequeries [START_REF] Xu | Range queries on multi-attribute trajectories[END_REF]. [START_REF] Hartmut | Symbolic trajectories[END_REF] shows also an extension of the SECONDO model to support symbolic trajectories. The reader interested in distributed computation can have a look to Parallel SECONDO , 28 developed to improve the performance of mobility data analysis that supports a specialised version of the R-Tree index called TM-RTree.

Practical examples of the combined used of PostgreSQL/PostGIS and QGIS for movement data analysis are available in Anita Graser's blog. 29 We also refer to the official PostgreSQLPostGIS documentation for a detailed description of spatial and temporal database functionalities. Note that, although PostgreSQL and PostGIS are mainly used for two dimensions geometries (X and Y coordinates), PostGIS also supports the handling of three-dimensional (3-D) geometries. This extra dimension, namely Z, is added to each vertex in the geometry, and the geometry type itself is enhanced accordingly, to enable the correct interpretation of the additional dimension. For instance, the 2-D geometries Point, Linestring and Polygon become 3-D PointZ, LinestringZ and PolygonZ, respectively. Also, the use of the 3-D only Polyhedralsurface makes possible the generation of volumetric objects in the database. Special PostGIS functions for spatial relationships have been adapted to 3-D geometries. The reader can refer to the official PostGIS documentation. 30 Parallel SECONDO http://dna.fernuni-hagen.de/secondo/ ParallelSecondo/ 29 Anita Graser's blog https://anitagraser.com/ 30 PostGIS 3-D https://postgis.net/workshops/postgis-intro/3d.html

The NoSQL Database management website 31 provides a useful overview of the capabilities of existing NoSQL systems, including spatial extensions. Recently, [START_REF] Ben Brahim | Spatial data extension for cassandra nosql database[END_REF] described a proposal to extend column-based Cassandra stores to the spatial dimension. Another solution to spatio-temporal objects persistence for large-scale data and geo-spatial analytics is GeoMesa, 32 an open-source suite of tools that interfaces with NoSQL databases like Google Bigtable and Cassandra, among others. GeoMesa supports near-real time analytics for streaming data and distributed data processing, and relies on GeoServer, 33 a well known data server for geographic data, and OGC application programming interfaces for map server integration.

Port and stationary areas detection, which is discussed in the chapter, is a hot topic in maritime related research. Most of the proposed approaches use unsupervised learning. For instance, Millefiori et al. use a data-driven approach (i.e. kernel density estimation (KDE) on AIS data) to define the extended areas of operation of seaports [START_REF] Millefiori | Scalable and distributed sea port operational areas estimation from ais data[END_REF]. Similarly, Vespe et al. [START_REF] Vespe | Mapping eu fishing activities using ship tracking data[END_REF] also use density estimates on AIS data to map fishing activities at European scale. This topic is also addressed in the chapter of Andrienko et al. [START_REF] Andrienko | Visual analytics for maritime studies[END_REF] in this book. Taking advantage of the identified stationary areas, vessel trajectory analysis and prediction [START_REF] Gan | Ship trajectory prediction for intelligent traffic management using clustering and ann[END_REF][START_REF] Xu | Ship trajectory online prediction based on bp neural network algorithm[END_REF], identification of human activities at sea [START_REF] Silveira | Use of ais data to characterise marine traffic patterns and ship collision risk off the coast of portugal[END_REF], anomaly detection [START_REF] Roberts | Anomaly detection in vessel track data[END_REF]31,[START_REF] Richard O Lane | Maritime anomaly detection and threat assessment[END_REF][START_REF] Behrouz | Anomaly detection in maritime data based on geometrical analysis of trajectories[END_REF] are also widely developed in the literature.

The reader interested in uncertainty representation and reasoning in maritime data and information fusion can also refer to [START_REF] Guerriero | Radar/ais data fusion and sar tasking for maritime surveillance[END_REF][START_REF] Roy | Sensor integration, management and data fusion concepts in a naval command and control perspective[END_REF][START_REF] Battistello | Knowledge-aided multi-sensor data processing for maritime surveillance[END_REF] and the chapter of Jousselme and Pallotta [START_REF] Jousselme | Dissecting uncertainty handling techniques for maritime anomaly detection[END_REF].

Fig. 2

 2 Fig. 2 AIS navigation data in the open dataset of [41]. The purple polygons represent fishing zones computed from navigation data by Vespe et al. [56].

a

  https://epsg.io/3035

Fig. 3

 3 Fig.3The shp2pgsql interface is used to load Shapefile data into a spatially enabled Post-greSQL database.

  [C1] Ports of Brittany (EPSG:4326); • [C4] Fishing Areas (European commission) (EPSG:4326); • [C5] Fishing Constraints (EPSG:4326). Once loaded, as for the examples in Section 4.5, add to all tables a new geometric column named geom3035 and project the geometric data into the CRS EPSG 3035.

Fig. 4

 4 Fig. 4 PostGIS functions that output a numerical (distance) or a boolean value. On top of each box, the PostGIS function and the corresponding spatial relation are reported (relation -PostGIS function).

Fig. 5

 5 Fig. 5 Examples of PostGIS functions that output a spatial geometry (buffer and convex hull). Output geometries are shown in grey. On top of each box, the PostGIS function and the corresponding spatial function are reported.

Fig. 6

 6 Fig. 6 Example of PostGIS functions that apply set-based operations and output a geometry. Output geometries are shown in grey. On top of each box, the corresponding PostGIS function is reported.

Figure 7 .Fig. 7

 77 Fig. 7 Use of the GiST index for computing spatial queries. When geometries (a) are indexed (c), spatial queries use the geometries' bounding boxes to approximate the query.

SQL Q4. 8 . 1 CREATE

 81 OR REPLACE FUNCTION g e t v e s s e l i n f o ( -name o f t h e f u c t i o n mmsi i n t e g e r -l i s t o f i n p u t a r g u m e n t w i t h t y p e s ) RETURNS t e x t AS $$ -t y p e o f t h e r e t u r n e d v a l u e DECLARE v e s s e l i m o i n t e g e r ; -i n t e g e r l o c a l v a r i a b l e v e s s e l n a m e t e x t ; -t e x t l o c a l v a r i a b l e BEGIN SELECT shipname , imo INTO v e s s e l n a m e , v e s s e l i m o FROM a i s d a t a . s t a t i c s h i p s WHERE s o u r c e m m s i =mmsi LIMIT 1 ; RETURN ' [ ' | | v e s s e l i m o | | ' ] ' | | v e s s e l n a m e ; -r e t u r n v a l u e END; $$ LANGUAGE p l p g s q l ; -programming l a n g u a g e u s e d Once defined, the function can be called in a query, as shown below. SQL Q4.8.2 SELECT g e t v e s s e l i n f o ( 2 2 7 7 0 5 1 0 2 ) ; [Answer: "[262144] BINDY"]

Fig. 8 SQL Q5. 1 . 1 CREATE

 811 Fig. 8 Voronoi tessellation of the geographical space, partitioning sea and land to identify the nearest port of any maritime location in Gulf of Morbihan. The Voronoi cells have random colours. Land is depicted in white, with the black line representing the coastline. Yellow stars represent ports' positions.

SQL Q5. 2 . 6

 26 SELECT c o u n t ( * ) FROM d a t a a n a l y s i s . s t o p s WHERE d u r a t i o n s >=(5 * 60) --5 m i n u t e s l o n g AND n b p o s >5 -minimum 5 c o n s e c u t i v e p o s i t i o n s AND a v g d i s t c e n t r o i d <=10; -w i t h i n 10m from c e n t r o i d [Answer: 22,987]

Fig. 9

 9 Fig. 9 DBSCAN clustering of vessel stops (random colours, one per cluster) in Brest.

SQL Q5. 2 . 7 CREATE

 27 INDEX i d x s t o p s c e n t r o i d ON d a t a a n a l y s i s . s t o p s USING b t r e e ( c e n t r 3 0 3 5 ) ; CREATE TABLE d a t a a n a l y s i s . c l u s t e r s t o p s AS SELECT * , ST ClusterDBSCAN ( c e n t r 3 0 3 5 , e p s : = 5 0 , m i n p o i n t s : = 5 ) OVER ( ) AS c i d FROM d a t a a n a l y s i s . s t o p s WHERE d u r a t i o n s >=60; --1 m i n u t e l o n g

Fig. 10

 10 Fig. 10 Convex hulls of vessel stop clusters (random colours) in the area of the Brest port.

SQL Q5. 2 . 8 CREATE

 28 TABLE d a t a a n a l y s i s . c l u s t e r s s t o p s h u l l s AS SELECT c i d , -c l u s t e r i d ST ConvexHull ( s t c o l l e c t ( c e n t r 3 0 3 5 ) ) a s c o n v e x h u l l , S T C o n c a v e H u l l ( s t c o l l e c t ( c e n t r 3 0 3 5 ) , 0 . 7 5 ) a s c o n c a v e h u l l , ST Mi nim umB oun din gCi rcl e ( s t c o l l e c t ( c e n t r 3 0 3 5 ) ) a s b o u n d i n g c i r c l e , S T C e n t r o i d ( s t c o l l e c t ( c e n t r 3 0 3 5 ) ) a s c e n t r o i d , c o u n t ( * ) a s n b s t o p s , -number o f s t o p s i n t h i s c l u s t e r ( a r e a ) sum ( n b p o s ) a s n b p o s , -sum o f s t o p s i n t h e c l u s t e r c o u n t ( DISTINCT mmsi ) a s n b s h i p s , -num o f u n i q u e s h i p s min ( d u r a t i o n s ) a s m i n d u r , avg ( d u r a t i o n s ) a s a v g d u r , max ( d u r a t i o n s ) a s max dur FROM d a t a a n a l y s i s . c l u s t e r s t o p s WHERE c i d I S NOT NULL -e x c l u d e o u t l i e r s GROUP BY c i d ; -g r o u p a l l t h e s t o p s c e n t r o i d s w i t h i n t h e same c l u s t e r

Figure 11 2 ALTERFig. 11

 11211 Fig. 11 Vessel trajectory tracks in the Brest area (yellow stars are ports of Brittany).

Fig. 12

 12 Fig. 12 Traffic graph. Graph nodes (green hexagons) represent stationary areas (e.g. ports, mooring areas), while edges (curved lines, one random colour per line) approximate tracks of vessels moving between stops.

Fig. 13

 13 Fig.13Entities' structure (tables and views) of the database schemas ais data and context data. The data model is extracted from the PostgreSQL maritime database using pg-Modeler.26

Fig. 14

 14 Fig. 14 Entities' structure (tables and views) of the database schema data analysis. The data model is extracted from the PostgreSQL maritime database using pgModeler.

-

  -T a b l e p o r t s v o r o n o i ALTER TABLE d a t a a n a l y s i s . p o r t s v o r o n o i ADD CONSTRAINT p o r t s v o r o n o i p k e y PRIMARY KEY ( p o r t i d ) ; --Column p o r t i d i s now t h e p r i m a r y key o f t h e t a b l e ALTER TABLE d a t a a n a l y s i s . p o r t s v o r o n o i ADD CONSTRAINT p o r t s v o r o n o i f k e y FOREIGN KEY ( p o r t i d ) REFERENCES c o n t e x t d a t a . p o r t s ( p o r i d ) ON DELETE CASCADE ; --Column p o r t i d r e f e r e n c e s t h e p o r t s t a b l e u s i n g a f o r e i g n key .

Fig. 15

 15 Fig. 15 Use of foreign keys to implement a one-to-many relation between ports and Voronoi tessellation (extracted from the PostgreSQL maritime database using pgModeler).

  p o r t s v o r o n o i AS SELECT p o r i d a s p o r t i d , l i b e l l e p o a s p o r t n a m e , geom3035 , v o r o n o i z o n e 3 0 3 5 FROM c o n t e x t d a t a . p o r t s LEFT JOIN ( SELECT ( ST Dump ( S T V o r o n o i P o l y g o n s ( S T C o l l e c t ( geom3035 ) ) ) ) . geom a s v o r o n o i z o n e 3 0 3 5 FROM c o n t e x t d a t a . p o r t s ) a s vp ON ( S T W i t h i n ( p o r t s . geom3035 , vp . v o r o n o i z o n e 3 0 3 5 ) ) ; --Add p r i m a r y and f o r e i g n key c o n s t r a i n t s ALTER TABLE d a t a a n a l y s i s . p o r t s v o r o n o i ADD CONSTRAINT p o r t s v o r o n o i p k e y PRIMARY KEY ( p o r t i d ) ; -p r i m a r y key ALTER TABLE d a t a a n a l y s i s . p o r t s v o r o n o i ADD CONSTRAINT p o r t s v o r o n o i f k e y FOREIGN KEY ( p o r t i d ) REFERENCES c o n t e x t d a t a . p o r t s ( p o r i d ) --Column p o r t i d r e f e r e n c e s p o r t s . p o r i d u s i n g a f o r e i g n key ON DELETE CASCADE ; --D e l e t e s on p o r t s a r e p r o p a g a t e d CREATE INDEX i d x p o r t s v o r o n o i z o n e ON d a t a a n a l y s i s . p o r t s v o r o n o i USING g i s t ( v o r o n o i z o n e 3 0 3 5 ) ;RETURN NEW; -r e t u r n t h e u p d a t e d rows END; $$ LANGUAGE p l p g s q l ; -programming l a n g u a g e u s e d--C r e a t e a t r i g g e r t o e x e c u t e t h e f u n c t i o n w h e n e v e r p o r t s i s u p d a t e d DROP TRIGGER I F EXISTS t r i g g e r p o r t s v o r o n o i ONc o n t e x t d a t a . p o r t s ; CREATE TRIGGER t r i g g e r p o r t s v o r o n o i AFTERt h e t r i g g e r i s e x e c u t e d a f t e r a t a b l e u p d a t e INSERT OR UPDATE OR DELETE OR TRUNCATE -any t y p e o f u p d a t e ON c o n t e x t d a t a . p o r t s -t h e m o n i t o r e d t a b l e FOR EACH STATEMENT -a f t e r e a c h s t a t e m e n t EXECUTE PROCEDURE c o n t e x t d a t a . r e b u i l d t a b l e p o r t s v o r o n o i ( ) ; -e x e c u t e t h e f u n c t i o n

SQL Q5. 4 . 3 DELETE

 43 FROM c o n t e x t d a t a . p o r t s WHERE l i b e l l e p o = ' S e i n ' ; -d e l e t e t h e p o r t o f t h e S e i n I s l a n d

SQL Q4.0.1

	CREATE DATABASE " m a r i t i m e i n f o r m a t i c s " ;

Remark ! Important note about literals

	PostgreSQL automatically converts to lower case all tables and fields names. If you want to force
	the use of capitals in tables or fields names, you must quote them (i.e. ''tableName'' or
	''fieldName''). To distinguish field names and strings in queries, PostgreSQL uses double
	quotes ('' and '') for field names and single quotes (' and ') for string values.

  How

	Do it yourself !	A4.2.1

many ships have null, empty or space filled shipname ? [Answer: 14] How many MMSI numbers are used more than once? [Answer: 327]

  This timestamp column facilitates visualising the temporal component of the table dynamic ships and expressing temporal queries. The query in the example below asks for the temporal range of the table (i.e. minimum and maximum timestamp) ([Answer: "2015-10-01 00:00:01" -"2016-03-31 23:59:59"]).

	SQL	Q4.3.2

Q4.3.1 ALTER TABLE a i s d a t a . d y n a m i c s h i p s ADD COLUMN t t i m e s t a m p w i t h o u t t i m e z o n e ; UPDATE a i s d a t a . d y n a m i c s h i p s SET t = t o t i m e s t a m p ( t s ) ; SELECT min ( t ) , max ( t ) FROM a i s d a t a . d y n a m i c s h i p s ;

Do it yourself ! A4.6.1 Download

the file "[C2] European Coastline.zip" from the dataset. Unzip the folder in your working directory and look at the different files. Have a look at the PRJ file. What is the coordinate reference system of the "Europe Coastline (Polygon)" shapefile (SHP)?

[Answer:

GRS 1980]

  table coastlines to visualise their geometric shape. Now, start QGIS, connect to PostgreSQL database using the Data Source Manager then, add the coastlines geographic layer in a new map. Readers having trouble to connect QGIS to PostgreSQL can refer to QGIS user manual.

21 Do it yourself ! A4.6.2 Using pgAdmin, find the type of the column geom3035 in the table coastlines. What is the primary key of the table? [Answer: MULTIPOLYGON -column gid]

  With the execution of the following queries, additional indexes are created to optimise the access to this new table non moving positions (on position geometry, position identifiers and associated timestamp).

	SQL	Q5.1.2
	CREATE TABLE d a t a a n a l y s i s . n o n m o v i n g p o s i t i o n s AS
	SELECT i d , mmsi , t , q1 . geom3035 , p o r t i d , p o r t n a m e ,
	S T D i s t a n c e ( q1 . geom3035 , p o r t s v o r o n o i . geom3035 ) a s p o r t d i s t
	FROM (
	SELECT *
	FROM a i s d a t a . d y n a m i c s h i p s
	WHERE s p e e d =0 --non moving s h i p p o s i t i o n s o n l y
	) a s q1
	LEFT JOIN d a t a a n a l y s i s . p o r t s v o r o n o i
	ON S T W i t h i n ( q1 . geom3035 , p o r t s v o r o n o i . v o r o n o i z o n e 3 0 3 5 ) ; --s h i p s i n
	v o r o n o i a r e a
	SQL	Q5.1.3
	CREATE INDEX i d x n o n m o v i n g p o s i t i o n s g e o m 3 0 3 5
	ON d a t a a n a l y s i s . n o n m o v i n g p o s i t i o n s
	USING g i s t ( geom3035 ) ;
	CREATE INDEX i d x n o n m o v i n g p o s i t i o n s p o r t i d
	ON d a t a a n a l y s i s . n o n m o v i n g p o s i t i o n s
	USING b t r e e ( p o r t i d ) ;

CREATE INDEX i d x n o n m o v i n g p o s i t i o n s t ON d a t a a n a l y s i s . n o n m o v i n g p o s i t i o n s USING b t r e e ( t ) ; Do it yourself ! A5.1.1 Using the results of table non moving positions, compute the average distance of vessel stops in the Voronoi area of the Brest port. [Answer: 1434.75m]

  Afterwards, the table stops is created by coupling, for each ship, stop begin and stop end, as illustrated in the following example (note that stops are ordered by time, that is, ORDER BY t end).

	SQL	Q5.2.2
	CREATE TABLE d a t a a n a l y s i s . s t o p b e g i n AS --f i r s t s t o p p o s i t i o n
	SELECT mmsi , t 2 a s t b e g i n --s t o p s t a r t s a t f i r s t s t e a d y p o s i t i o n
	FROM d a t a a n a l y s i s . s e g m e n t s
	WHERE s p e e d 1 >0.1 AND s p e e d 2 <=0.1; --s p e e d t h r e s h o l d i s 0 . 1 kn
	CREATE INDEX i d x s t o p b e g i n m m s i t ON d a t a a n a l y s i s . s t o p b e g i n
	USING b t r e e ( mmsi , t b e g i n ) ;
	CREATE TABLE d a t a a n a l y s i s . s t o p e n d AS --l a s t s t o p p o s i t i o n
	SELECT mmsi , t 1 a s t e n d --s t o p e n d s a t l a s t s t e a d y p o s i t i o n
	FROM d a t a a n a l y s i s . s e g m e n t s
	WHERE s p e e d 1 <=0.1 AND s p e e d 2 >0.1; --s p e e d t h r e s h o l d i s 0 . 1 kn
	CREATE INDEX i d x s t o p e n d m m s i t ON d a t a a n a l y s i s . s t o p e n d
	USING b t r e e ( mmsi , t e d ) ;
	SQL	Q5.2.3
	n g
	ORDER BY t e n d LIMIT 1 --s e l e c t o n l y t h e f i r s t s t o p end
	) AS q2 ON ( t r u e ) ;

CREATE

TABLE d a t a a n a l y s i s . s t o p s AS SELECT mmsi , -s h i p i d e n t i f i e r t b e g i n , -s t a r t o f t h e s t o p e v e n t t e n d , -end o f t h e s t o p e v e n t e x t r a c t ( e p o c h FROM ( t e n d -t b e g i n ) ) a s d u r a t i o n s -s t o p i n s e c o n d s FROM d a t a a n a l y s i s . s t o p b e g i n INNER JOIN LATERAL ( -k e e p o n l y s t o p s t h a t h a v e an end SELECT t e n d FROM d a t a a n a l y s i s . s t o p e n d WHERE s t o p b e g i n . mmsi= s t o p e n d . mmsi AND t b e g i n <=t e n d -s t o p f o l l o w s t h e b e g i n n i

  data.ports. The trigger trigger ports voronoi is defined for table context data.ports to execute the function, rebuilding the Voronoi tessellation, whenever the table context data.ports is modified (AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE on context data.ports).Database triggers are SQL procedures that are executed upon the occurrence of a monitored database event, such as the insertion of a row in a table or view. In PostgreSQL, triggers functions, which are automatically invoked by the DBMS whenever an insert, update, delete or truncate event occurs on a specified table.

	Remark ! A note on triggers
	SQL	Q5.4.2
	--C r e a t e a f u n c t i o n t o r e b u i l d t h e V o r o n o i t e s s e l l a t i o n
	CREATE OR REPLACE FUNCTION c o n t e x t d a t a . r e b u i l d t a b l e p o r t s v o r o n o i ( )
	RETURNS TRIGGER AS $$ --t h i s f u n c t i o n r e t u r n a t r i g g e r
	BEGIN
	--Same a s Q5 . 1 . 1
	DROP TABLE I F EXISTS d a t a a n a l y s i s . p o r t s v o r o n o i ;
	CREATE TABLE d a t a a n a l y s i s .

Exercise solutions are available, with additional material (e.g. resulting geographic data), online [NEW DOI OBJECT TO CREATE ON ZENODO], together with the reference dataset used in the examples.

https://www.postgresql.org

https://arcgis.com

https://grass.osgeo.org

https://qgis.org

https://www.oracle.com

https://www.microsoft.com/en-us/sql-server/sql-server-2017

https://www.mysql.com

https://sqlite.org

Open Geospatial Consortium (OGC) Simple Features Specification for SQL: Simple Feature Access -Part 1: Common Architecture https://www.opengeospatial.org/standards/ sfa; Simple Feature Access -Part 2: SQL Option https://www.opengeospatial.org/ standards/sfs

[START_REF] Egenhofer | A mathematical framework for the definition of topological relations[END_REF] International Standard Organization's (ISO) ISO19107:2003 Geographic InformationSpatial Schema https://www.iso.org/standard/26012.html

https://db-engines.com/en/ranking

TimescaleDB https://www.timescale.com

Grafana https://grafana.com

Data Driven Documents (D3) https://d3js.org

Kibana https://www.elastic.co/products/kibana

"Free and Open Source GIS Ramblings" https://anitagraser.com/

https://epsg.io/4326

https://postgis.net/docs/reference.html#Geometry_Processing

https://www.postgresql.org/docs/11/xplang.html

In the case of AIS, vessel positions are reported only in the areas covered by AIS receivers, and at sparse time intervals. The AIS data in the open dataset are collected from a terrestrial receiver.

CHROROCRONOS project http://chorochronos.datastories.org