Skip to main content
SpringerLink
Log in
Book cover

Guide to Maritime Informatics pp 73–118Cite as

Maritime Data Processing in Relational Databases

  • Chapter
  • First Online:

Abstract

Maritime data processing research has long used spatio-temporal relational databases. This model suits well the requirements of off-line applications dealing with average-size and known in advance geographic data that can be represented in tabular form. This chapter explores off-line maritime data processing in such relational databases and provides a step-by-step guide to build a maritime database for investigating maritime traffic and vessel behaviour. Along the chapter, examples and exercises are proposed to build a maritime database using the data available in the open, heterogeneous, integrated dataset for maritime intelligence, surveillance, and reconnaissance that is described in [42]. The dataset exemplifies the variety of data that are nowadays available for monitoring the activities at sea, mainly the Automatic Identification System (AIS), which is openly broadcast and provides worldwide information on the maritime traffic. All the examples and the exercises refer to the syntax of the widespread relational database management system PostgreSQL and its spatial extension PostGIS, which are an established and standard-based combination for spatial data representation and querying. Along the chapter, the reader is guided to experience the spatio-temporal features offered by the database management system, including spatial and temporal data types, indexes, queries and functions, to incrementally investigate vessel behaviours and the resulting maritime traffic.

This is a preview of subscription content, log in via an institution.

Chapter
USD   29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD   59.99
Price excludes VAT (USA)
  • Available as EPUB and PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD   79.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info
Hardcover Book
USD   99.99
Price excludes VAT (USA)
  • Durable hardcover edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Learn about institutional subscriptions

Notes

  1. 1.

    The solutions of the exercises are available, with additional material (e.g. resulting geographic data), online, together with the reference dataset: https://zenodo.org/record/3930660.

  2. 2.

    https://www.postgresql.org.

  3. 3.

    https://arcgis.com.

  4. 4.

    https://grass.osgeo.org.

  5. 5.

    https://qgis.org.

  6. 6.

    https://www.oracle.com.

  7. 7.

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

  8. 8.

    https://www.mysql.com.

  9. 9.

    https://sqlite.org.

  10. 10.

    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.

  11. 11.

    International Standard Organization’s (ISO) ISO19107:2003 Geographic Information—Spatial Schema https://www.iso.org/standard/26012.html.

  12. 12.

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

  13. 13.

    TimescaleDB https://www.timescale.com.

  14. 14.

    OGC Moving Features Access http://www.opengis.net/doc/is/movingfeatures-access/1.0.

  15. 15.

    Grafana https://grafana.com.

  16. 16.

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

  17. 17.

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

  18. 18.

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

  19. 19.

    https://docs.qgis.org/3.4/en/docs/user_manual/.

  20. 20.

    https://epsg.io/4326.

  21. 21.

    https://docs.qgis.org/3.4/en/docs/user_manual/managing_data_source/opening_data.html#database-related-tools.

  22. 22.

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

  23. 23.

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

  24. 24.

    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.

  25. 25.

    https://pgmodeler.io.

  26. 26.

    CHOROCHRONOS project http://chorochronos.datastories.org.

  27. 27.

    Parallel SECONDO http://dna.fernuni-hagen.de/secondo/ParallelSecondo/.

  28. 28.

    Anita Graser’s blog https://anitagraser.com/.

  29. 29.

    PostGIS 3-D https://postgis.net/workshops/postgis-intro/3d.html.

  30. 30.

    NoSQL Database management website http://nosql-database.org/.

  31. 31.

    GeoMesa https://www.geomesa.org/.

  32. 32.

    Geoserver http://geoserver.org/.

References

  1. Allen, J.F.: Maintaining knowledge about temporal intervals. Commun. ACM 26(11), 832–843 (1983). https://doi.org/10.1145/182.358434

    Article  MATH  Google Scholar 

  2. Andrienko, N., Andrienko, G.: Visual analytics of vessel movement. In: Artikis, A., Zissis, D. (eds.) Guide to Maritime Informatics, chap. 5. Springer, Berlin (2021)

    Google Scholar 

  3. Andrienko, N.V., Andrienko, G.L.: Spatial generalization and aggregation of massive movement data. IEEE Trans. Vis. Comput. Graph. 17, 205–219 (2011)

    Article  Google Scholar 

  4. Aurenhammer, F.: Voronoi diagrams: a survey of a fundamental geometric data structure. ACM Comput. Surv. 23(3), 345–405 (1991). https://doi.org/10.1145/116873.116880

    Article  Google Scholar 

  5. Battistello, G., Koch, W.: Knowledge-aided multi-sensor data processing for maritime surveillance. GI Jahrestag. 2, 796–799 (2010)

    Google Scholar 

  6. Ben Brahim, M., Drira, W., Filali, F., Hamdi, N.: Spatial data extension for cassandra nosql database. J. Big Data 3(1), 11 (2016). https://doi.org/10.1186/s40537-016-0045-4

    Article  Google Scholar 

  7. Bereta, K., Chatzikokolakis, K., Zissis, D.: Maritime reporting systems. In: Artikis, A., Zissis, D. (eds.) Guide to Maritime Informatics, chap. 1. Springer, Berlin (2021)

    Google Scholar 

  8. Borkowski, P.: The ship movement trajectory prediction algorithm using navigational data fusion. Sensors 17(6), 1432 (2017)

    Article  Google Scholar 

  9. Bradbury, R.: Marine informatics: a new discipline emerges. Marit. Stud. 1995(80), 15–22 (1995). https://doi.org/10.1080/07266472.1995.10878407

    Article  Google Scholar 

  10. Carlini, E., Monteiro, V., Soares, A., Etemad, M., Machado, B., Matwin, S.: Uncovering vessel movement patterns from ais data with graph evolution analysis. In: Proceedings of the MASTER Workshop, 23rd International Conference on Extending Database Technology (EDBT), pp. 1–7 (2020)

    Google Scholar 

  11. Egenhofer, M.: A mathematical framework for the definition of topological relations. In: Proceedings the 4th International Symposium on Spatial Data Handing, pp. 803–813 (1990)

    Google Scholar 

  12. Ester, M., Kriegel, H.P., Sander, J., Xu, X.: A density-based algorithm for discovering clusters in large spatial databases with noise. In: Proceedings of 2nd International Conference on Knowledge Discovery and Data Mining, pp. 226–231 (1996)

    Google Scholar 

  13. Gan, S., Liang, S., Li, K., Deng, J., Cheng, T.: Ship trajectory prediction for intelligent traffic management using clustering and ann. In: 2016 UKACC 11th International Conference on Control (CONTROL), pp. 1–6. IEEE (2016)

    Google Scholar 

  14. Grasso, R.: Ship classification from multi-spectral satellite imaging by convolutional neural networks. In: Proceedings of the 27th European Signal Processing Conference, A Curuna, Spain, 2–6 Sep 2019

    Google Scholar 

  15. Guerriero, M., Willett, P., Coraluppi, S., Carthel, C.: Radar/ais data fusion and sar tasking for maritime surveillance. In: 2008 11th International Conference on Information Fusion, pp. 1–5. IEEE (2008)

    Google Scholar 

  16. Güting, R.H., Valdés, F., Damiani, M.L.: Symbolic trajectories. ACM Trans. Spatial Algorithms Syst. 1(2), 7:1–7:51 (2015). https://doi.org/10.1145/2786756

  17. Guttman, A.: R-trees: A dynamic index structure for spatial searching. In: Proceedings of the 1984 ACM SIGMOD International Conference on Management of Data, SIGMOD ’84, pp. 47–57. ACM, New York, NY, USA (1984). https://doi.org/10.1145/602259.602266

  18. Hellerstein, J.M., Naughton, J.F., Pfeffer, A.: Generalized search trees for database systems. In: Proceedings of the 21th International Conference on Very Large Data Bases, VLDB ’95, pp. 562–573. Morgan Kaufmann Publishers Inc., San Francisco, CA, USA (1995). http://dl.acm.org/citation.cfm?id=645921.673145

  19. Huang, B., Claramunt, C.: STOQL: An ODMG-based spatio-temporal object model and query language. In: Richardson, D.E., van Oosterom, P. (eds.) Advances in Spatial Data Handling, pp. 225–237. Springer, Berlin (2002)

    Google Scholar 

  20. ISO Central Secretary: Geographic information. simple feature access. sql option. Standard ISO 19125-2:2006, International Organization for Standardization, Geneva, CH (2006)

    Google Scholar 

  21. Itani, A., Ray, C., Falou, A.E., Issa, J.: Mining ship motions and patterns of life for the EU common information sharing environment (CISE). In: OCEANS 2019, Marseille, France, pp. 1–6 (2019)

    Google Scholar 

  22. Johansen, T.A., Cristofaro, A., Perez, T.: Ship collision avoidance using scenario-based model predictive control. IFAC-PapersOnLine 49(23), 14–21 (2016)

    Article  Google Scholar 

  23. Jousselme, A.L., Iphar, C., Pallotta, G.: Uncertainty handling for maritime route deviation. In: Artikis, A., Zissis, D. (eds.) Guide to Maritime Informatics, chap. 9. Springer, Berlin (2021)

    Google Scholar 

  24. Jugović, A., Hess, S., Poletan Jugović, T.: Traffic demand forecasting for port services. Promet-Traffic Transp. 23(1), 59–69 (2011)

    Google Scholar 

  25. Kim, S., Lee, J.: Small infrared target detection by region-adaptive clutter rejection for sea-based infrared search and track. Sensors (Basel) 14, 13210–13242 (2014). https://doi.org/10.3390/s140713210

    Article  Google Scholar 

  26. Kothuri, R., Ravada, S.: Oracle Spatial, Geometries, pp. 821–826. Springer US, Boston (2008). https://doi.org/10.1007/978-0-387-35973-1_936

  27. Koubarakis, M., Sellis, T., Frank, A., Grumbach, S., Güting, R., Jensen, C., Lorentzos, N., Manolopoulos, Y., Nardelli, E., Pernici, B., Schek, H.J., Scholl, M., Theodoulidis, B., Tryfona, N. (eds.): Spatio-Temporal Databases - The CHOROCHRONOS Approach. Lecture Notes in Computer Science, vol. 2520. Springer, Berlin (2003)

    Google Scholar 

  28. Kucuk, A., Hamdi, S.M., Aydin, B., Schuh, M.A., Angryk, R.A.: Pg-trajectory: A postgresql/postgis based data model for spatiotemporal trajectories. In: Proceedings of the 2016 IEEE International Conferences on Big Data and Cloud Computing (BDCloud), Social Computing and Networking (SocialCom), Sustainable Computing and Communications (SustainCom), 8-10 Oct 2016, Atlanta, GA, USA, pp. 81–88 (2016). https://doi.org/10.1109/BDCloud-SocialCom-SustainCom.2016.23

  29. Laddada, W., Ray, C.: Graph-based analysis of maritime patterns of life. In: Proceedings of the GAST Workshop, 20th Journées Francophones Extraction et Gestion des Connaissances (EGC), pp. 1–14 (2020)

    Google Scholar 

  30. Lane, R.O., Nevell, D.A., Hayward, S.D., Beaney, T.W.: Maritime anomaly detection and threat assessment. In: 2010 13th Conference on Information Fusion (FUSION), pp. 1–8. IEEE (2010)

    Google Scholar 

  31. Liu, B., de Souza, E.N., Hilliard, C., Matwin, S.: Ship movement anomaly detection using specialized distance measures. In: 2015 18th International Conference on Information Fusion (Fusion), pp. 1113–1120. IEEE (2015)

    Google Scholar 

  32. Makris, A., Tserpes, K., Spiliopoulos, G., Anagnostopoulos, D.: Performance Evaluation of MongoDB and PostgreSQL for Spatio-temporal Data. In: 2nd International Workshop on Big Mobility Data Analytics (BMDA2019), Lisbon, Portugal (2019). https://doi.org/10.5281/zenodo.2649876

  33. Mazzarella, F., Alessandrini, A., Greidanus, H., Alvarez, M., Argentieri, P., Nappo, D., Ziemba, L.: Data fusion for wide-area maritime surveillance. In: Proceedings of the COST MOVE Workshop on Moving Objects at Sea, Brest, France, pp. 27–28 (2013)

    Google Scholar 

  34. Millefiori, L.M., Zissis, D., Cazzanti, L., Arcieri, G.: Scalable and distributed sea port operational areas estimation from ais data. In: 2016 IEEE 16th International Conference on Data Mining Workshops (ICDMW), pp. 374–381. IEEE (2016)

    Google Scholar 

  35. Organisation, I.S.: Iso/iec 9075:2016 information technology database languages sql

    Google Scholar 

  36. Parent, C., Spaccapietra, S., Renso, C., Andrienko, G., Andrienko, N., Bogorny, V., Damiani, M.L., Gkoulalas-Divanis, A., Macedo, J., Pelekis, N., et al.: Semantic trajectories modeling and analysis. ACM Comput. Surv. (CSUR) 45(4), 42 (2013)

    Article  Google Scholar 

  37. Patroumpas, K.: Online mobility tracking against evolving maritime trajectories. In: Artikis, A., Zissis, D. (eds.) Guide to Maritime Informatics, chap. 6. Springer, Berlin (2021)

    Google Scholar 

  38. Pelekis, N., Theodoridis, Y., Vosinakis, S., Panayiotopoulos, T.: Hermes - A framework for location-based data management. In: Advances in Database Technology - EDBT 2006, 10th International Conference on Extending Database Technology, Munich, Germany, 26–31 Mar 2006, Proceedings, pp. 1130–1134 (2006). https://doi.org/10.1007/11687238_75

  39. Petrossian, G.A.: Preventing illegal, unreported and unregulated (iuu) fishing: a situational approach. Biol. Conserv. 189, 39–48 (2015)

    Article  Google Scholar 

  40. Randell, D.A., Cui, Z., Cohn, A.G.: A spatial logic based on regions and connection. In: Proceedings of the 3rd International Conference on Principles of Knowledge Representation and Reasoning, pp. 165–176. Morgan Kaufmann Publishers Inc. (1992)

    Google Scholar 

  41. Ray, C., Dréo, R., Camossi, E., Jousselme, A.L.: Heterogeneous integrated dataset for maritime intelligence, surveillance, and reconnaissance (version 0.1) [data set] (2018). https://doi.org/10.5281/zenodo.1167595

  42. Ray, C., Dréo, R., Camossi, E., Jousselme, A.L., Iphar, C.: Heterogeneous integrated dataset for maritime intelligence, surveillance, and reconnaissance. Data Brief 25, 104141 (2019). https://doi.org/10.1016/j.dib.2019.104141. http://www.sciencedirect.com/science/article/pii/S2352340919304950

  43. Rigaux, P., Scholl, M., Voisard, A.: Spatial Databases with Application to GIS. Morgan Kaufmann Publishers Inc., San Francisco (2002)

    Google Scholar 

  44. Riveiro, M., Pallotta, G., Vespe, M.: Maritime anomaly detection: a review. Wiley Interdisciplinary Reviews: Data Mining and Knowledge Discovery e1266 (2018). https://doi.org/10.1002/widm.1266

  45. Roberts, S.: Anomaly detection in vessel track data. Ph.D. thesis, Oxford University, UK (2014)

    Google Scholar 

  46. Roy, J., Bosse, E.: Sensor integration, management and data fusion concepts in a naval command and control perspective. Technical report, Defence Research Establishment Valcartier (Québec) (1998)

    Google Scholar 

  47. Sakr, M.A., Güting, R.H.: Group spatiotemporal pattern queries. Geoinformatica 18(4), 699–746 (2014). https://doi.org/10.1007/s10707-013-0198-7

  48. Siabato, W., Manso-Callejo, M., Camossi, E.: An annotated bibliography on spatiotemporal modelling trends. Int. J. Earth Environ. Sci. 2(135), 26 (2017)

    Google Scholar 

  49. Silveira, P., Teixeira, A., Soares, C.G.: Use of ais data to characterise marine traffic patterns and ship collision risk off the coast of Portugal. J. Navig. 66(6), 879–898 (2013)

    Article  Google Scholar 

  50. Simoes, R., Queiroz, G., Ferreira, K., Vinhas, L., Câmara, G.: Postgis-t: towards a spatiotemporal postgresql database extension. In: XVII Brazilian Symposium on Geoinformatics (GeoInfo 2016) (2016)

    Google Scholar 

  51. Soleimani, B.H., De Souza, E.N., Hilliard, C., Matwin, S.: Anomaly detection in maritime data based on geometrical analysis of trajectories. In: 2015 18th International Conference on Information Fusion (Fusion), pp. 1100–1105. IEEE (2015)

    Google Scholar 

  52. Spaccapietra, S., Parent, C., Damiani, M.L., de Macedo, J.A., Porto, F., Vangenot, C.: A conceptual view on trajectories. Data Knowl. Eng. 65(1), 126 – 146 (2008). https://doi.org/10.1016/j.datak.2007.10.008. http://www.sciencedirect.com/science/article/pii/S0169023X07002078. Including Special Section: Privacy Aspects of Data Mining Workshop (2006) - Five invited and extended papers

  53. Strobl, C.: PostGIS, pp. 891–898. Springer US, Boston, MA (2008). https://doi.org/10.1007/978-0-387-35973-1_1012

  54. Tampakis, P., Sideridis, S., Nikitopoulos, P., Pelekis, N., Theodoridis, Y.: Maritime data analytics. In: Artikis, A., Zissis, D. (eds.) Guide to Maritime Informatics, chap. 4. Springer, Berlin (2021)

    Google Scholar 

  55. Vaisman, A., Zimányi, E.: Mobility data warehouses. ISPRS Int. J. Geo-Inf. 8(4) (2019). https://doi.org/10.3390/ijgi8040170

  56. Vespe, M., Gibin, M., Alessandrini, A., Natale, F., Mazzarella, F., Osio, G.C.: Mapping eu fishing activities using ship tracking data. J. Maps 12(sup1), 520–525 (2016)

    Article  Google Scholar 

  57. Xu, J., Güting, R.H.: A generic data model for moving objects. Geoinformatica 17(1), 125–172 (2013). https://doi.org/10.1007/s10707-012-0158-7

    Article  Google Scholar 

  58. Xu, J., Lu, H., Guting, R.H.: Range queries on multi-attribute trajectories. IEEE Trans. Knowl. Data Eng. 30(6), 1206–1211 (2018). https://doi.org/10.1109/TKDE.2017.2787711

  59. Xu, T., Liu, X., Yang, X.: Ship trajectory online prediction based on bp neural network algorithm. In: 2011 International Conference on Information Technology, Computer Engineering and Management Sciences (ICM), vol. 1, pp. 103–106. IEEE (2011)

    Google Scholar 

  60. Yu, H., Fang, Z., Lu, F., Murray, A.T., Zhao, Z., Xu, Y., Yang, X.: Massive automatic identification system sensor trajectory data-based multi-layer linkage network dynamics of maritime transport along 21st-century maritime silk road. Sensors 19(19) (2019). https://doi.org/10.3390/s19194197. https://www.mdpi.com/1424-8220/19/19/4197

Download references

Author information

Authors and Affiliations

  1. LabISEN, Brest, France

    Laurent Etienne

  2. Naval Academy Research Institute, Brest, France

    Cyril Ray

  3. NATO STO Centre for Maritime Research and Experimentation, La Spezia, Italy

    Elena Camossi & Clément Iphar

Authors
  1. Laurent Etienne
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Cyril Ray
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Elena Camossi
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Clément Iphar
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Laurent Etienne .

Editor information

Editors and Affiliations

  1. Department of Maritime Studies, University of Piraeus, Piraeus, Greece

    Alexander Artikis

  2. Department of Product and Systems Design Engineering, University of the Aegean, Syros, Greece

    Dimitris Zissis

Rights and permissions

Reprints and permissions

Copyright information

© 2021 Springer Nature Switzerland AG

About this chapter

Check for updates. Verify currency and authenticity via CrossMark

Cite this chapter

Etienne, L., Ray, C., Camossi, E., Iphar, C. (2021). Maritime Data Processing in Relational Databases. In: Artikis, A., Zissis, D. (eds) Guide to Maritime Informatics. Springer, Cham. https://doi.org/10.1007/978-3-030-61852-0_3

Download citation

  • DOI: https://doi.org/10.1007/978-3-030-61852-0_3

  • Published:

  • Publisher Name: Springer, Cham

  • Print ISBN: 978-3-030-61851-3

  • Online ISBN: 978-3-030-61852-0

  • eBook Packages: Computer ScienceComputer Science (R0)

Publish with us

Policies and ethics

Search

Navigation