1 Introduction

Landslides are one of the geological natural risks that concern researchers around the world due to their extension and impact at social and economic level. Hence, a great effort is made by competent administrations at national and regional level to collect information about this type of events and study them.

Increasingly, emerging technologies incorporate a large amount of spatial information that can be used in natural disaster management to define hypothetical scenarios that lead to the design and application of meaningful, effective solutions (Blahut et al. 2012; Yu et al. 2018) and to understand the phenomenon and advance in its research (Saharia et al. 2021). Due to the rapidly increasing amount of multidimensional data that is collected, efficient data management, analysis and visualization are likely to become a key issue. Efficient data handling is needed to archive original data (Breunig et al. 2016). The relationship between natural risk and geospatial information is clear (Saha et al. 2005; Huabin et al. 2005; Bobrowsky 2013). In addition, an increase in the use of the spatial component in information management has led to the creation of databases and geographic information systems (GIS). GIS tools can be used for data analysis and to simulate and visualize the trajectories of rockfalls (Aye et al. 2017; National Research Council 1999). Some tools, such as RockGIS, consider block fragmentation (Matas et al. 2017, 2020).

Harmonized databases are needed to provide data for these studies, since it is essential to have consistent, internationally recognized standards for data so that they can be shared (Wirtz et al. 2014). In this way, information can be obtained through services such as those provided by spatial data infrastructure (SDI). This is one of the objectives of the European directive INSPIRE (Infrastructure for Spatial Information in Europe). According to it, an SDI involves metadata; spatial datasets and spatial data services; network services and technologies; agreements on sharing, access and use; and coordination and monitoring mechanisms (European Parliament and the Council 2007). SDI is a system for gathering, sharing and disseminating geographic information in a coordinated manner with technological support. These kinds of systems are designed to ease policy formulation and decision-making, in order to meet societal needs at local, national and global scale (Coleman and McLaughlin 1998; Pashova and Bandrova 2017). Sharing information is possible through services that are standardized by the Open Geospatial Consortium (OGC) (2006), the INSPIRE (Infrastructure for Spatial Information in the European Community; Directive 2007/2/EC (European Parliament and the Council 2007) and ISO (2006) (Gröger and Plümer 2012). These services include publication services such as Web Map Services (WMS), download services in vector format such as the Web Feature Service (WFS) and download services in raster format like Web Coverage Services (WCS).

The European directive INSPIRE 2007/60/EC required harmonization of European Union (EU) countries’ databases for the first time with the objective of obtaining interoperable services to access to the databases. Services to access various levels and formats of information follow the same principles as those established by the Open Geospatial Consortium (OGC), which were described with applications for risk management in Reichardt (2010). INSPIRE establishes several commissions, one for each theme in its annexes. To achieve harmonization, the commissions establish technical guides and specifications on what data to collect and how to organize it. The Data Specification on Natural Risk Zones is currently in force is Version 3: D2.8.III.12 Data Specification on Natural Risk Zones-Technical Guidelines (INSPIRE Thematic Working Group Natural Risk Zones 2013).

Standards and harmonization have enabled further progress in the development of artificial intelligence applications, which are an excellent framework for the prevention of natural risk (Roberti et al. 2020).

In this line, under the umbrella of INSPIRE, one European initiative is for most countries to work together with geological surveys to harmonize their data. In Herrera et al. (2018), 34 databases were analysed from 24 countries, since geological surveys in each country have resulted in several databases. For example, Croatia, Greece and Norway have two databases; Spain has four; and Germany has five landslide databases. Another example of a more complex INSPIRE database is the Italian LAND-deFeND, in which landslides and flood risk are combined (Napolitano et al. 2018).

Other European countries that are Member States of the European Economic Area (EEA) (Iceland, Lichtenstein, Norway and Switzerland) have largely accepted the INSPIRE directive and accordingly built National Spatial Data Infrastructure (NSDI). One example is the harmonized databases for the Geological Survey of Norway (Oppikofer et al. 2015). There are only 10 European countries outside the authority of the INSPIRE directive: Belarus, Ukraine, Moldova, Albania, Macedonia, Kosovo, Serbia, Montenegro, Bosnia and Herzegovina, and Turkey. Although the INSPIRE directive is not obligatory for them, all are aspiring to EU Membership and are thus making efforts to build NSDI according to this directive (Poslončec‐Petrić and Bačić 2014). Other examples outside Europe are New Zealand (Rosser et al. 2017) and the Andes region that includes various countries such as Bolivia, Peru, Colombia and Ecuador (Molina and Bayarri 2011).

In the aforementioned INSPIRE data specification, the types of natural hazards are classified according to the landslide classification by Varnes (1978) included in the extended version of the Register codified list of INSPIRE for Natural Hazards (Minerva intelligence, sd) (https://minerva.codes/codelist/NaturalHazardCategoryLandslideExtension). In this study, we will only focus on one kind of movement, rockfalls.

Only 10% of records in the European landslides database correspond to rockfalls (Herrera et al. 2018). Most of these occurred in Norway. However, in the Spanish database at national level, the percentage of rockfalls is approximately 30%; the same as the percentage for slides (Herrera et al. 2017). Worldwide, most rockfalls occur in mountain areas (Oppikofer et al. 2015) and in one-off locations, which makes their detection difficult, unlike other types of geological hazards that cover large, broader areas. Such hazards can be detected from aerial or even satellite images with high resolution (Lv et al. 2018) or by comparing images from two moments (Lei et al. 2019). However, rockfalls often go unnoticed, since blocks fall from vertical walls and in most cases mobilize less mass volume, which is not appreciated from the air unless through oblique images.

Generally, rockfalls are only detected when they occur near exposed elements such as infrastructures or urban areas, causing human and economic losses. A clear example of the impact of this type of event was that occurred in the municipality of Cortés de Pallás (Spain) in 2001. It mobilized a volume of approximately 8000 m3 of the rocky massif located in the basin of the reservoir that served a power plant. The rockfall affected not only the hydroelectric power plant but also the village that was cut off for eight months (Riquelme et al. 2017; Sarro et al. 2018). In other cases, rockfalls are detected long after they have occurred, which makes it difficult to draw up an inventory that helps to study the event.

In a rockfall, we can distinguish three main spatial components: the source, which is the wall area from which the blocks detached; the deposit where most of the final fragments and blocks stop; and the scattered blocks, which are those that go out of the deposit zone. Figure 1 shows these three parts. The source area is shown in red, the deposit is delimited by a yellow line, and finally, the scattered blocks are highlighted with a red circle in the image on the bottom left. The geospatial information about these three components helps us to better understand this kind of phenomenon.

Fig. 1
figure 1

Scheme of the spatial components of a rockfall event with fragmentation (modified from Matas et al. 2017)

Full size image

In rockfalls, the mass that is initially mobilized or detached from the source area of the wall may be formed by a set of intact rock blocks bounded by pre-existing discontinuities, depending on the fracturing pattern. The volumetric distribution of blocks in the massif is characterized by the distribution of blocks according to their volume, called the in situ block size distribution (IBSD). During the propagation of the mass downhill, the original distribution may be altered due to the disintegration of the rock by pre-existing discontinuities or by the rupture of the parent rock. The resulting distribution after fragmentation on the deposit, including disintegration and breakage, is called the rockfall block size distribution (RBSD). The term “fragmentary rockfalls” is used to refer to this phenomenon (Corominas et al. 2019).

Detailed, reliable inventories help us to advance in the study of hazard and risk assessment and to calibrate and validate rockfall models. However, few inventories offer fragmentation information.

The objective of the database and subsequent WMS service described in this article is not only to collect general data on inventories, but also to go much further to provide structured and georeferenced information on the fragmentation process of the specific event. To obtain this information, the zone must be visited to estimate the number of blocks that have detached from the wall and measure the resulting rock fragments found in the deposit. These data are essential to establish the fragmentation laws (Ruiz-Carulla 2018) and to calibrate their trajectories with simulation programmes that incorporate the fragmentation process (Matas et al. 2017, 2020). In addition, all this information is open data for researchers, private professionals and policymakers, for whom it can be of great help to have detailed information stored effectively, to study these events. This advance in the hazard and risk assessment will help to take decisions to mitigate their effects and on recovery and reconstruction processes.

2 RockDB design and implementation

The database designed and developed on the framework of the RockModels project (Lantada et al. 2020), called RockDB (Rockfall Database), has three components: a) a spatial database, b) a Web Map Service (WMS) and c) a web mapping viewer (Fig. 2). All of them were implemented using open-source software, PostGIS, Open Layers libraries and Geoserver.

Fig. 2
figure 2

Scheme of the RockDB components and implementation: database, WMS and Web –mapping

Full size image

In this section, we focus on spatial database structure. We describe the design of a conceptual and logical model according to INSPIRE, and its implementation in a physical model.

Since all the data were collected as part of a publicly funded project of a European country, the global structure of the database must conform to the data specifications defined by INSPIRE for natural hazards, and more specifically geological hazards. In this way, and by subsequently adjusting to the standards for WMS services, we can obtain an interoperable system to share information.

2.1 Conceptual model

The conceptual model is the first abstraction of reality that allows all the real aspects that must be structured in a database to be collected. To achieve this, the entities and their basic properties must be identified. To be able to organise the information in the database, a basic schema must be defined in which the entities of the model, the relationships between them and the attributes that make up the entities are defined.

For RockDB, this model is highly conditioned by the unified model language (UML) diagrams of the INSPIRE Technical Guide (INSPIRE Thematic Working Group Natural Risk Zones 2013) and its interoperability with the existing LLISCAT database (González et al. 2017), established in Catalonia (Spain). The LLISCAT database was developed by the Department of Civil and Environmental Engineering of the Universitat Politècnica de Catalunya (UPC. BarcelonaTech) with the support of the Institut of Catalan Studies (IEC). It is currently managed by the Cartographic and Geological Institute of Catalonia (ICGC) (González et al. 2017), according to the technical guidelines of data specifications for natural risk zones (Harrison et al. 2013). This database provides information on several kind of land movements for official bodies and administrations; the scientific, technical and professional community; and citizens in general. Meanwhile, RockDB only registers a single type of event, rockfalls.

The core of the database structure involves the entities and the relations among them. We can distinguish four groups: nature-related entities (NE), information-source entities (ISE), geoinformation-related entities (GIE), and social-economic-related entities (SEE). These groups are the same as those defined in LLISCAT (González et al. 2017). We distinguish three main elements in the boxes at the centre of Fig. 3 that form the core of the nature entities:

  • Event contains basic information about the area where a rockfall has occurred. It takes the initial date from the first movement produced in this area.

  • Movement each reactivation in the event area is considered a movement. Therefore, we can have several movements for an event.

  • Movement data contains complete information on the rockfall.

Fig. 3
figure 3

Scheme of the simplified conceptual model. Text in white, the Nature-related Entities and in black the main components for the other entities

Full size image

Geoinformation-related entities (GIE) provide the georeferenced localization of the event and movement, considering the different parts of a rockfall: the source, deposit and blocks. They include information about the cover and lithology of these areas. All these entities are considered polygons, but their representation varies from point to polygon, depending on the visualization scale. A trigger function has been implemented to automatically complete the georeferencing of these entities in several coordinated reference systems (CRS). Social-economic-related entities represent the goods, infrastructures and humans damaged by the event. Finally, information source-related entities (ISE) represent the source of the data included in RockDB, from inventories and field campaigns to bibliographic and media information. The main entity in this group is movement data. Several relations are established among all these entities. The three that are considered the most important are:

  • Event–movement an event can have different reactivations. These are called movements and will conserve the same event identifier, which is assigned with the first movement. The date of this first movement is considered the initial date for the event, and the last date is marked by the last reactivation. An event can include single or multiple movements.

  • Movement–social-economic entities (SEE) the damage caused derives from the movement with a cardinality of 0–1, as each movement may or may not cause damage. These entities also need to be specified in the exposed elements that have been affected. Thus, the damage is defined specifically by basic information. Subsequently, it will have affected multiple structures (relation 0–N) that will be defined by their own dictionary table in the logical model.

  • Movement–information sources entities (ISE) The movement data with a cardinality from 1 to 1, fragmentation data and finally the bibliographical references can be null or multiple for each movement. The relation between each movement and its data will always be univocal since the data belong to the movement.

2.2 Logical model

The logical model or relational model was based on the conceptual model. In addition to the entities, the relationships among them and the attributes, the logical model defines: (i) how the entities become tables, (ii) attributes as table fields, indicating the type of data and their length, and (iii) primary and foreign keys, which allow the relationship among tables to be established. We maintained the logical model of LLISCAT to facilitate the integration. Therefore, the tables and fields are the same as those described in González et al. (2017), with slight modifications that we detail after a general description. Two tables with the same entity name were derived from the two entities of the group called nature-related entities (NE). The event table contains fields to store these four groups of information: (1) id_unique for the event, (2) the data of activation (the date of the first movement that happened in this area) and the last movement, (3) the geographical localization, the city and region, (4) geological data, such as the morphological units and the type of natural hazard. All events are rockfalls in RockDB, but we register this information to integrate it into LLISCAT or another national database. The movement table contains: (1) the id of the movement, (2) photography, and (3) initial and final data.

The information source tables include one with bibliographic references linked to each movement. However, the main table is related to the movement data. This table includes fields for: (1) the id of the movement, (2) the dimensions of the scar area: width, depth and length, (3) the presence of water, and (4) the runout distance. In this last field, we have introduced another change regarding technical specifications, since this information for rockfalls is usually given by the reach angle. It is important remark that each event must have a unique identifier (id) according to INSPIRE. The id for the event has 10 digits, with a structure 0000X00000. The first four digits are the number of the Spanish topographic mapsheet at 1:25000 scale, a letter to define the type of event (for rockfall it is a “D”) and five further numbers related to the chronological order of the event.

Moreover, the  id for the movement is created by adding–00X to the id of its event. For example, the id for the Mallorca event is 0697d0001, and the id for its respective movement is 0697d0001_001. This unique identifier (id) allows us to avoid overlap common information in other situation (Spencer and Ashley 2011).

Other aspect to bear in mind is the fact that the technical specifications indicate which fields should appear, their type and their maximum length or format. An example of these specifications is found in the life cycle, defined with the date format (dd/mm/yyyy). This can be a drawback because rockfalls sometimes occur in uninhabited areas. Therefore, if only an approximate date is known, it was decided to add an observation field in which if only the month and year are known, the first day of the month is assigned, and if only the year is known, the first day of the year would be assigned.

The Information Source Tables include one with the bibliographic references linked to each movement. However, the main one is related to the movement data. This table includes fields about: (1) id of the movement, (2) dimensions of the scar area: width, depth and length, (3) the presence of water, (4) runout distance. In this last field, we have introduced other change regarding the technical specifications since this information for rockfalls is usually given by the reach angle.

Social-economic tables include fields describing the damage to civil infrastructures or private properties and the human impact, with the number of dead and injured people. They also include the mitigation measures to protect goods and the population.

Geoinformation tables include tables to store the geographical localization of the event in relation to the administrative divisions of the country: municipalities, regions and provinces. Moreover, we include the geometry of the source, deposit and blocks, if available, and information about the block volume. Table 1 details the attributes of these elements. All the geometries in this table correspond to polygons. The fragmentation information is linked to the geometry of the detachment envelope. These tables are related to the NE through the id of the movement (Fig. 4).

Table 1 Characteristics of the fields in the table “mov_frag”
Full size table
Fig. 4
figure 4

Relationships among tables derived from INSPIRE data specification with fragmentation data

Full size image

Eventually, two more tables were added to those containing the required data specification information (Fig. 4). This was because the data content was insufficient to record detailed information on the distribution of fragmentation. One of these tables contains the IBSD, and the other contains the RBSD in volume intervals for each of the inventoried rockfalls.

In the RockDB model, the main tables are those corresponding to the event, movement, movement data, and those corresponding specifically to the fragmentation data. In the event and movement tables, the main or primary key is the identifier, which is the id of the foreign key event for the movement table together with the id corresponding to the dictionaries. In the case of the events table, the foreign key is linked to the geometry table of the municipalities and to the dictionaries that describe the kind of event.

In the case of tables corresponding to movement data, each table has each foreign key that links to the movement and to the mov_frag table that contains the geometry of all the components of the event zones.

2.3 Physical model

RockDB was implemented in a spatial database on PostGIS v.2.1, an open-source software programme that adds support for geographic objects to the free, open-source, object-relational database management system PostgreSQL 9.2. The tables with their attributes were created according to the logical model. Moreover, primary and foreign keys and constraints were established to guarantee semantic consistency and integrity. One constraint is that a movement cannot be defined if an event was not created previously.

According to the defined logical model, the structure of the database is comprised of three views, 39 tables (20 of which are dictionaries) and over 200 attributes. The most relevant of these dictionaries are:

  • Morphological-structural units dictionary this is a simplification of relief units from the Geological Atlas of Catalonia (ICGC 2022).

  • Movement causes dictionary this allows us to distinguish between causes, due to the physical properties of the material, human factors or natural causes.

  • Lithology dictionary this consists of 1,047 lithologies from the geological map 1/50,000 of Catalonia.

  • Movement status dictionary the classification used is that proposed by Glade et al. (2012) and Mather et al. (2003).

The main tables are the ones that contain the basic data about each movement. These tables are: event, movement, data movement, mov_frag, vol_ISBD and vol_RSBD. The geometry is stored in the mov_frag table always as a polygon (Fig. 4).

The main tables are those that contain the basic data on each movement. These tables are event, movement, data movement, mov_frag, vol_ISBD and vol_RSBD. The geometry is always stored in the mov_frag table as a polygon (Fig. 4).

We only have the convex hull for some events in the inventory. For others, we defined with high accuracy the shape of the blocks, source and deposit. Finally, for some events, we have the position of the blocks but not their shape. In this situation, we use a cartographic representation criterion to draw a buffer of 1 m, to represent the blocks as a polygon in the detail view.

The geometries in all cases were collected in-plane coordinates in the official reference and coordinate systems, i.e. ETRS89 and UTM projection zone 31 and 30 N (EPSG codes 25831 and 25830, respectively), and in geographic coordinates ETRS89 (EPSG 4326). The transformation was carried out automatically using the trigger function explained in the conceptual model section.

In SQL language, and by extension in PostGIS, a view is the presentation of a dataset from one or more tables. This view behaves as if it was a table. The same functions can be applied, and it will be updated dynamically when the fields and records of the tables that form it are updated. Its use allows the information to be presented in a more adequate way, hiding fields that are not necessary for the end-user but which must appear in the database to meet the specifications or to execute intermediate functions.

In our case, each view is obtained from the combination of the fields of several tables. The database information is filtered to show it in WMS views, because many of the fields do not contain relevant information. Three levels of information were defined in these views. The view related to the first level shows all the inventory locations as a point, using the centroid of the envelope (Fig. 5). The alphanumeric information shown is: (a) municipality, (b) date, (c) source volume, (d) deposit volume and (e) damage.

Fig. 5
figure 5

Geometry of each element according to the level of visualization

Full size image

The second view corresponds with the second level, the envelope was symbolized as a polygon and the other zones as a point. Therefore, the field "geometry" in the view is completed as the centroid of the polygons for blocks, source and deposit (Fig. 5). The attributes in this case are: (a) municipality, (b) date, (c) source volume, (d) deposit volume, (e) maximum volume, (f) dimensions (length, depth and width), (g) lithology and h) damage.

Finally, in the last level of detail, the third view, all the elements are shown as polygons with their correct shape and area, if available (Fig. 5). The additional attributes of the second level are: a) IBSD, b) RBSD and c) type of measure (for example, sample plot or “true” for direct block measures).

3 Web map service and web interface

Once the three views were defined, Geoserver was used to create the WMS from the RockDB database. Geoserver has the option of working with shape files or directly from PostGIS. One of the advantages of working with views is the automatic updating of the WMS service when the data change or new events are added in the tables.

In the context of spatial data infrastructures (SDIs), the WMS service is the most widely used among organizations that provide geographic information via the internet due to its ease of implementation and the existence of several software solutions.

To ensure interoperability between map services, the International Organization for Standardization (ISO) has developed the ISO 19128:2005 Web Map Server Interface Standard, which is based on the Web Map Service specification (WMS) Implementation Specification of the Open Geospatial Consortium (2006).

The WMS service is defined equally for INSPIRE and the Open Geospatial Consortium (OGC), although there are small differences in the implementation syntax. INSPIRE’s technical guide recommends the use of OGC’s Web Map Service specification for visualization services. The European directive defines five types of generic services: discovery, visualization, access, transformation and invocation.

The visualization service is implemented as WMS which allows the visualization, overlay and timely consultation of maps generated from one or more servers in different public or private entities.

Maps produced by WMS are normally generated in an image format such as PNG, GIF or JPEG and can be invoked by any corporate platform or software qualified for the visualization of this type of services.

WMS operations are usually invoked using a standard web browser (e.g. Firefox) or desktop applications (e.g. QGIS), and requests are made in the form of URLs. The content is the service concatenated with the parameters of the requested operation. The standard defines two mandatory operations:

  • GetCapabilities, it returns a document with the service level metadata, where the capabilities, operations and valid parameters of the WMS service are described.

  • GetMap, it returns a map whose geographic and dimensional parameters have been well defined.

In addition to these operations, we have implemented the optional operation, defined in the WMS 1.3.0 standard (Open Geospatial Consortium 2006), GetFeatureInfo, which returns information on particular features displayed on the map, including the geometry and values of the attributes of a pixel.

For correct visualization of the graphical information, we have defined in Geoserver groups of layers to be worked with. This grouping can be used to define the order of drawing, and therefore the overlapping of the elements. Likewise, a style was assigned to each type of element following the SLD standard of the OGC (Open Geospatial Consortium 2007). This style varies depending on the characteristics and geometry of the zones but maintains the same colour. As this service collects elements in such a detailed way, it does not include a mandatory style to comply with the standardized data specifications. Each style will be visible regarding the visualization scale, level 1 from 1/100,000 to 1/1,6000,000, level 2 from 1/1,000 to 1/100,000 and finally level 3 from 1/100 to 1/1,000.

Only essential information was selected to create the views. However, fields had to be kept that allow the tables to be joined. These fields were filtered and hidden in Geoserver using the FTL filters in order to show with the GetFeatureInfo operation the information related to each level.

Once the WMS services had been created, the viewer was implemented using the OpenLayers v.5 JavaScript library and creating the basic query functions on the available inventories (Núñez-Andrés et al 2021). The WMS service is hosted on a server of the Universitat Politècnica de Catalunya (UPC.BarcelonaTech) and is available through the URL https://geoserver.rockdb.upc.edu/geoserver/Rockdb/wms and the Web Mapping viewer at the URL https://rockdb.upc.edu.

4 Implementation of RockDB

The database RockDB currently contains information about 10 inventories (Table 1) located in the regions of Catalonia, Aragon and the Balearic Islands (Spain). We have for all of them, the geometry of the envelope, which includes the source and deposit area and the isolated blocks, gathers approximately 5900 registers, mostly corresponding to information on individual blocks. This information was collected from 2006 to 2019 using several geomatics techniques such as photogrammetry from a remotely piloted aircraft system (RPAS), or terrestrial laser scanning (TLS) (Fig. 6).

Fig. 6
figure 6

Flowchart about the data capture and sources of information for the rockfall fragmentation database

Full size image

4.1 Data capture

To obtain information on the main components of the rockfall (Fig. 1), the 3D model of the rocky massif, the fragment volume distributions (ISBSD and RBSD), and the volume of the existing blocks in the deposit should be surveyed.

For each of the movements detected, a field campaign was carried out to collect not only the required spatial information but also the characteristics that could be evaluated on the ground such as type of ground cover, the presence of water and damage caused.

The flowchart in Fig. 6 sums up graphically the data capture techniques, methods and sources that are usually applied to obtain the spatial information required to describe the components of each rockfall. Topographical methods, total stations or GNSS positioning, allow georeferencing of the delimited areas (source, deposit, etc.) and blocks. Products, such as orthophotos and high-resolution 3D models of the event, generated by digital photogrammetry from RPAS or TLS of the rockfall affected area (Buill et al. 2016; Núñez-Andrés et al. 2019) allow deepened knowledge about rock failure and fragmentation mechanisms (Spreafico et al. 2017; Ruiz-Carulla and Corominas 2021). The volumes of blocks detached from the rock wall are quantified by analysing the TLS or the photogrammetric point clouds, to obtain the in situ block size distribution (ISBD) (Fig. 6).

In this study, we developed methodology to obtain the RBSD in the case of large fragmental rockfalls (Ruiz-Carulla et al. 2015). Mid-size to large fragmental rockfalls often generates more or less continuous young debris cover (YDC) of smaller debris and large scattered blocks (LSB). All the LSB were measured and georeferenced individually by GNSS directly or on a georeferenced orthophoto. Therefore, the relative position of all the elements was accurate enough at the local scale of this work (in a range from 1/200 to 1/1000). Most of the volumes of the LSB were measured in the field with a tape, assuming that their shape is prismatic, as the product of three dimensions. However, sometimes the blocks’ shape was drawn on the orthophotographs built from images taken from the RPAS (Matas et al. 2020). In these cases, the pixel resolution is in centimetre range (Ruiz-Carulla et al. 2015). However, given the difficulty of measuring the volume of all the blocks in the YDC, we defined several size-homogeneous zones and sampling plots (SP) inside them. Figure 7 shows in yellow the limit of the areas. Inside them, the blue squares are SP edges. The SP are square and have variable dimensions that are proportional to the size of the blocks. All blocks (over a certain size) inside each SP (Fig. 7) were measured. The block size distribution obtained in each SP was extrapolated to the corresponding size-homogeneous zone, according to the ratio between the respective areas (Ruiz-Carulla et al 2015, 2017). The areas of the SP ranged between 4 and 400 m2. The minimum and maximum block sizes measured were 0.015 and 1800 m3, respectively. By adding all of them and the LSB distribution, we obtained the final RBSD representative of the whole deposit. This distribution of the fragmented rock mass after failure can be assessed by comparison with the total volume of the detached rock mass (Ruiz-Carulla 2018).

Fig. 7
figure 7

Example of deposit sketch with areas and their sample plots. Picture on the top right side is a picture of a real measured sample plot (size 10 × 10 m)

Full size image

Two strategies were followed to delimit the deposit areas and the block location. On the one hand, the survey of the affected area was carried out by digital photogrammetry from a RPAS platform or using TLS. This strategy is applied for rockfalls that cover large areas such as those in the municipalities or urban areas of Malanyeu, Vilanova de Banat, Monasterio de Piedra, Gurp and Andratx, the latter on a Balearic island and all the others in Catalonia,

In the other hand, code GNSS receivers are used. With the current constellation, these receivers allow metric absolute precision and sub-metric relative precision among areas and blocks.

4.2 Data management in GIS

Given their scant planimetric dimension, in three inventoried cases (Esterri, Pont de Gulleries and Lluça events), only information on the general envelope of the affected area is available. In one cases (Andratx), we have information on the location of the source and runout zones in the deposit area. In other two (Vilanova de Banat and Monasterio de Piedra), more detailed information is available including the existing coordinates of the positions of each block but no information on their geometry. In both cases, a point is taken as the centroid of the block in the GIS cartography edition, and then, a buffer has been drawn over it. The buffer distance is the same in all the regions since it is only used to reflect the existence of a block at detailed scales. For Vilanova de Banat, moreover, of the total number of blocks, we have wanted to distinguish the large number of them that are scattered, running out of the deposit area (Table 2). Finally, when possible, the surface covered by each of the blocks has been drawn on the orthophotos obtained by photogrammetric surveys (four inventories).

Table 2 General information about the inventoried rockfalls
Full size table

Table 2 shows the characteristics of each inventory. Each of them takes the name of the municipality or urban towns where the event occurred. They show the type of measurement that was carried out, the volume released, the number of blocks measured, which areas within the detachment are drawn, and the availability of the projected blocks’ shape.

To sum up, information is available on the general characteristics of all the events and the fragmentation distribution data, which are the most relevant information in this database. All these data were introduced in the tables defined in PostGis. Moreover, the shape files were added to the mov_frag table through pgl sentences. Previously, we added values of the id_block automatically, using a script that concatenates the id_mov with a sequential number for each movement in QGIS.

The reference system to register all the inventories was defined in geographic coordinates in ETRS89, code EPSG: 4258. When the UTM projection is used for the cartography, for the peninsular part of Spain, we have data in the EPSG reference system: 25831 to EPSG: 25829. In this case, a trigger must be created that helps to update the data in the new geometry field that is created (geom4258). For this reason, we rely on the power of triggering functions and thus automatically update the new geometry in the table.

Triggers are composed of two parts: a function that carries out the transformation to the target reference system and updates the records, and the trigger proper, which shoots the function each time that a new zone is inserted in the mov_frag table. Once all the information has been included in the tables, the three views, according to the three information and visualization levels, are updated automatically.

Previously, we defined the visualization styles for each visualization scale for all the rockfall areas (source, deposit, blocks and envelop) and levels in QGIS, saving the file as an SLD format (Open Geospatial Consortium 2007). Figure 8 shows several examples of visualization, depending on the zoom level, of the existing graphic elements.

Fig. 8
figure 8

Types of geographic information in the inventories: a envelope, b source and run out, c position of blocks without geometry and d detailed information on the block distribution and geometry

Full size image

These files and the PostGIS views are the base to build our WMS in Geoserver, where we define the characteristics of the Map Service such as the coordinated reference systems (CRS) that are allowed, the possibility of opacity on the layers, and whether they are queryable, i.e. the GetFeatureInfo operation is possible, and available styles.

Once the vector layers have been defined, it is the turn of the raster layers. We have only added one since other raster layers will be called directly in the web mapping as a base layer. The raster to be used to represent the marine space is a WMS service offered by the French Research Institute for the Exploitation of the Sea (IFREMER).

The viewer (https://rockdb.upc.edu) has three vectorial layers available, according to the three views defined in PostGIS and the possibility of changing the base raster from an orthophoto, the topographic map from the IGN (National Geographic Institute) or the Open Street Map service. In all the views, the sea area is covered by the IFREMER raster layer, which is defined in our WMS service.

Regarding the interface tools, in the toolbar, the bottom area from left to right can be used to manage the extension zoom and display information, display the coordinates, and display the information associated with each inventory. This information will be different for each level (Fig. 9). Inside the left box with all inventoried rockfalls, the bottom area with the name of each event allows the user to zoom to level 2 of that event, and the right bottom area, to download a file with the IBSD and RBSD fragmentation information grouped by bins. In Fig. 9, part of the table for the Vilanova de Banat event is shown.

Fig. 9
figure 9

Web interface of the WMS at level 3 of visualization with the orthophoto as background and a query about a movement

Full size image

5 Discussion

When RockDB was designed, one of the main objectives was interoperability with other databases. One of the conditions to accomplish this is that the general information must be in line with the INSPIRE Data Specification on Natural Risk Zones-Technical Guidelines (INSPIRE Thematic Working Group Natural Risk Zones 2013). Moreover, RockDB must be able to be integrated directly into the LLISCAT database, which was established as the official landslide database in Catalonia (Spain) and Andorra. For this reason, we considered both conditions in the entities and tables, fields and relationships during the design phase.

Now, the integration of alphanumeric information into LLISCAT is immediate. Only the common fields are exported and need to be added to the tables in LLISCAT. This process is done by PgSQL sentences in the PostGIS database. The name of some fields must be modified in the INSERT sentences, with the advantage that this operation can be stored, repeated and is not time consuming. In the case of geometry, a previous treatment is required, since LLISCAT works at regional scale, and therefore, blocks are represented by points. These geometries can be obtained easily from polygon blocks of RockDB with the PostGIS spatial function to calculate the polygon centroid.

In the design phase, the INSPIRE Data Specification is the base. However, it reflects only general characteristics of all the phenomena. Consequently, some modifications have had to be made to register more specific data. For example, we have added new tables on fragmentation.

Regarding data fragmentation, one point of discussion is how to obtain the rock block size distribution (RBSD), since the volume of each block was measured in a field campaign with a tape. It was measured individually for small events and using sample plots for bigger events. The next step is to classify this RBSD data in bins, according to their volume in cubic metres, and to establish the appropriate bins. We tested different numbers of bins to compare the shape distribution that was obtained, in terms of cumulative number of blocks versus block volume, to the original distribution. Our results show low precision in the distribution shape using five bins, whereas the shape was well defined using 12 or 19 bins. Based on these results, we classified the blocks into bins covering four orders of magnitude (Ruiz-Carulla 2018). Table 3 shows an example of these bin tables, which can be downloaded from the viewer.

Table 3 Example of fragmentation data available to download for each movement
Full size table

Regarding the completeness of the database, we have to considered that due to the characteristics of the rockfalls, it is difficult to know when the event happened. Therefore, only some recent movements are inventoried. Caution should be taken when data are used to study these zones. Despite the lack of historic data, they can reoccur in the same place. Qualitatively, regarding attributes, the fact that it is not always possible to establish the date of an event means that it is impossible to determine the required information for some fields, for example, whether they are triggered by a precipitation episode.

The information in the RockDB database is much more detailed than that proposed in the INSPIRE model and therefore could be more useful to study rockfall events, especially, when fragmentation of the blocks occurs. At that point, it is interesting to highlight the fact that the items of the majority of the tables in the data base are mandatory for the Technical Data specifications of the Directive INSPIRE. The information contained from these items, for example, the lithology of the scar and deposit, is not limited in our WMS to some areas of Catalonia, Aragon and the Balearic Islands. It could be used in other countries, since the dictionaries contain information of all the geological classes. Moreover, the database is scalable and adaptable to new data.

6 Conclusions and outlook

In this article, we present 3D geoinformation and spatio-temporal data on some recent fragmentary rockfalls and the Web Map Service developed for easy web-based data access. The data capture process was long due to the detailed fragmentation data and the difficulty of obtaining it. This information was gathered over five years in the framework of the RockRisk and RockModels projects. This is the first rockfall database that includes such detailed information about the event, with data related to the fragmentation of the blocks. The current version meets the requirements for collecting this information, and it satisfies the INSPIRE database specifications for geological hazards. However, as new events occur, and depending on the new information that is generated, it may be necessary to incorporate slight modifications to the tables or expand the tables in the database.

It is currently made up of a total of 39 tables, with more than 200 fields, and approximately 6000 registers of several geometries of source areas, envelopes, deposits and mostly individual blocks.

Web mapping developed is a useful tool to access database information, through the WMS service generated, by any user and from any computer, which helps to improve knowledge and understanding of this type of event to academics and professionals. Incorporation of 3D models of the registered events is foreseen in the viewer, using free tools that are available such as Optree. The models are obtained either with digital photogrammetry or with TLS surveys. The use of free software will facilitate the continuity of the project with the incorporation and updating of the database.

Currently, the number of events is low, due to: i) the cost of technical personnel and time required to obtain information related to fragmentation, especially that related to the deposit or RBSD; ii) the risk involved in obtaining this information. The progress of research in the use of geomatic techniques will enable us to obtain this information more automatically very soon, and consequently, at a lower cost and in a safe way. Therefore, in our future research projects, we will focus on expanding the database with information on future rockfalls.

To facilitate the extension and management of this database, especially the geographical part, the development of a tool is planned that will facilitate the direct incorporation of geometric information of an event in the database, through direct digitization of base information such as orthophotos.

By adding spatial distributed information about fragmentation to the rockfall inventories, we bring added value for further scientific research in rockfall risk assessment and provide a service to protect stakeholders from risk.