Data analysis has been at the core of the insurance industry since its inception. Insurance companies are ongoing an arms race to understand and apply advances in data science (Denuit et al., 2020; Wüthrich & Merz, 2023). Data science is a field of applied mathematics and statistics that extract information based on large amounts of complex data or big data. Data volume has exponentially grown in last years in the world. As more and more diverse data sources become available to insurance companies, techniques to link different databases to extract useful information for the insurance business become more important. However, there are cost-effective methods that enhance the understanding and pricing of risks that are not fully taken advantage of yet. One of them is the use of relational databases with internal private data and public available one.

In general, a data model is the formal way of expressing data relationships to a database management system (DBMS). The relational data model was introduced in 1970 by Edgar Frank Codd (1970). This model describes the world as a collection of inter-related tables, named relations (Watt & Eng, 2014). Databases that adhere to a relational data model are named relational databases. Therefore, a relational database is a database whose logical structure is made up of a collection of relations (Harrington, 2016). Relational databases work with base tables, i.e., actually stored tables, and virtual tables, which are a product of relational operations and only exist in main memory. Their structure is registered in a data dictionary or catalogue, which mirror data storage relations. The data within the data dictionary are referred to as metadata.

The aim of this study is to show that insurers have access to alternative data sources that are useful in pricing and risk management. We claim that relational models that integrate those data sources with internal data can be used by insurers in their risk analysis. There is abundant literature that provides interesting insights by combining various data sources with a relational model. Different aspects of mobility have been analyzed, all of which are of interest to insurance companies. There are several reviews that include research studies that have used a relational model to integrate the data. Ziakopoulos and Yannis (2020) wrote a literary review of spatial analysis approaches on road safety, where one can find studies that combine different data sources such as Liu & Sharma (2018), Moeinaddini et al. (2014) and Alarifi et al. (2017), as well as Ziakopoulos (2024) recently published research. In Zheng et al. (2021) we can find a review of studies modelling traffic conflicts that combine various data sources, including among others Xie et al. (2019) and Zheng et al. (2019). Finally, Wang et al. (2013) examined the impact of traffic and road characteristics, and referenced some research studies that combined alternative data sources such as Haynes et al. (2008) and Lord et al. (2005).

To illustrate the usefulness of leveraging public data in combination with private data in a relational data model, this paper will show three fields where they can be used for a better understanding of risks. The first application focuses on attributing an urbanization degree to the claims and/or the policyholders, specifically the Eurostat methodology, in line with research that have leveraged ZIP codes to study the relationship between crash characteristics and those injured (Clark & Cushing, 2004; Lee et al., 2014; Lerner et al., 2001). The second application uses climatic information from AEMET (Spanish Meteorology Agency) to model the number of claims in a municipality (AEMET, 2024). Finally, we examine the characteristics of all occupants inside crashed vehicles, with the focus on the severity of the injuries of the passengers involved in a motor crash. Note that occupants are defined as all persons who were in the vehicle at the time of the accident, and passengers as the persons other than the drivers who were in the vehicle. Our idea is to obtain a more accurate estimate of the total bodily injury (BI) cost associated with an accident based on the profile of the driver and the expected passengers’ profiles accompanying him.

This paper is structured into five sections. First, the methodology of relational databases is presented. Then, the three proposed applications are discussed. In the first one, we use relational data models in spatial analysis, specifically in segmenting geographical locations based on the European standard urbanization degree categorization (also considering the relevance of the analysis of geographical areas and their population structure). In the second application, we show how to use files with georeferencing raster climatic data to model the number of claims in a geographical area. Finally, relational databases are presented as a tool to link more data sets with the aim of better understanding the characteristics of occupant BI (driver plus passengers). The paper ends with the main conclusions on the relevance of using relational models in the insurance field. We also include the R codes making them available to the insurance sector and academia for use.

Methodologically speaking, it is necessary to distinguish different components in a relational model, see: a relation, also named table, defined as a subset of the Cartesian product of a list of domains characterized by a name (Wijnen et al., 2019); its columns or attributes; its domains, i.e., the set of permissible values a column can contain; and its rows or tuples, where each one represents a group of related data values. These relation’s rows and columns have some special properties:

Each table row is identified with a primary key, a value that uniquely identifies a specific row, stored in a column. If well specified, with unique primary keys and no null keys, only the table and column names, and the primary key of the row suffice to retrieve any specific data.

The relationships between the tables that conform the database can be of three types: one-to-one, where each row in one table is linked to at most one row in another table; one-to-many, where a single row in Table A can be related to one or more rows in Table B but each row in table B is only related to one row in table A; and many-to-many, where multiple rows in one table can relate to multiple rows in another table, using a third table named junction or join table to manage the relationship between the two tables. However, these relationships are not mandatory and will not be enforced by the DBMS unless specified. The most common relationship type in relational databases is one-to-many. In them, there are two tables (A and B), each containing a column with identifier variables from the same domain, one with primary keys, which uniquely identify each row within a table, and the other with foreign keys, which link one table to another by referencing a primary key. A foreign key is a column with the same primary keys as some table in the database. The relationship DBMS will use the relationship by matching data between primary and foreign keys to retrieve associated data, i.e., items from other columns. It is important to remark that relational data models place a constraint in one-to-may relationships, they require that each non-null foreign key value corresponds precisely to an existing primary key value. This is the most important constraint because it ensures the coherence of inter-table references.

To represent data relationship in a relational database we use entity-relationship (ER) diagram, which in practice is a diagram that shows relationship types among different tables (figure 1).

In European countries, according to Wijnen et al. (2019), the total costs of road crashes are equivalent to 0.4–4.1% of GDP. In the case of Spain, as the authors point out, the cost of road crashes stands at approximately 1% of the GDP. Moreover, Spanish insurance companies have seen their motor vehicle claims’ soar in the last years, as shown by their net combined loss ratio (Table 1).

The increases in their loss ratios underline the importance of understanding crashes better to reestablish profitability in the Motor Third Party Liability insurance. Incorporating additional spatial analysis could enhance their analytical process, allow for more geographical-based measures and contribute to the improvement of risk selection and pricing.

The riskiness of a driver is conditioned by the environment where he/she moves (Pljakić et al., 2022). Population density and its corresponding infrastructures not only heavily determines drivers’ maneuvers and behaviors, but also the expected consequences of their mistakes (Abdel-Aty et al., 2013; Bil et al., 2019; Prato et al., 2018; Keeves et al., 2019). Several studies have shown that even if urban areas tend to concentrate a bigger proportion of crashes, occupants suffer worse injuries in rural areas crashes (Keeves et al., 2019, Peura et al., 2015). In Spain, the profile of the driver in rural areas is also different, older driver represent a bigger share of the census, according to the Spanish driver census for 2017-2019.

The intersection of rurality, rural depopulation and population ageing is important for Spain. At the same time, it is one of the European countries with the highest life expectancy, with the highest percentage of population living in cities, and the highest percentage of older people concentrated in rural areas (Gutiérrez et al., 2023; Casado-Sanz et al., 2019; European Commission, 2023). Rural municipalities represent 84% of Spain's surface area but only the 16% of the Spanish population lives in rural municipalities. The 25% of the rural population is over 65 years old and almost a third of those over 65 are over 80 years old. From the point of view of road safety, this context represents a huge challenge when it comes to ensuring safe and sustainable mobility in rural areas, which are increasingly depopulated and where the incidence of population aging is higher. In Spain, the greatest number of traffic accidents occur in urban environments but 52% of traffic fatalities occurs on rural environments (Harland et al., 2014).

Incorporating these structural demographic changes into insurance companies’ models is important. However, adding new variables to a model is not trivial. It requires deciding how much resources to invest into making adjustments and ensuring accurate categorization. The difficulty increases with variables that do not have clear-cut classifications. Insurance companies are forced to choose between more variable customization or, when available, using standardized criteria. There are situations where opting for homogeneous criteria offers certain advantages, it makes market comparisons easier, and enables leveraging public available data if matched. In these situations, insurance companies could extract more insights from their policy holders and claims if they integrate their own (proprietary) data with information accessible from public sources.

An illustrative example is separating policyholders or claims by urbanization degree, i.e., whether it is urban or rural area. Although the use of geographical areas is very common in insurance, there is no harmonized widely-used criteria to determine the degree of urbanization of a location. Researchers and practitioners from different countries tend to use multiple measures (Harland et al., 2014), mostly related to population density or size, with different thresholds to determine urbanization and reflect their perspective on the urban – rural area dichotomy (Keeves et al., 2019). To ease international comparison and offer standardized classifications, Eurostat developed its own methodology (OECD, 2021). This European statistical institution defines cities, towns and rural areas based on a combination of population size, density and proximity and attributes a classification at the Local Administrative Unit (municipalities in the case of Spain). Using this methodology, it’s possible to map the level of urbanization across EU member countries, enabling standardized comparison among them.

In Figure 2 we show results of applying the Eurostat's methodology in our country, including two maps at the municipal level. The first map indicates urbanization levels, while the second uses a logarithmic scale to show vehicle crash numbers involving injured victims in each municipality for the 2016-2019 period. Data of the numbers of crashes were provided by the Spanish General Traffic Directorate (DGT). The comparison suggests that a higher degree of urbanization correlates with an increase in traffic accidents. Mapping different factors can help to reveal potential relationship that provide insights into traffic crash dynamics, even helping to predict crash occurrences based on certain criteria and to evaluate the potential correlation heterogeneity across different territories. For example, the impact of a factor on the number of crashes may vary, as it is the case in the maps of figure 2, where the rural areas (depicted in light green) correlate with a different magnitude in the northern and southern halves of Spain.

The climate of an area can be an important element in explaining the claims for insurance companies (Ashley et al., 2015; Eisenberg, 2004; Naik et al., 2016). Some examples in which weather may play a key role are motor insurance or home insurance, among others. Most of insurers already take meteorological information into account in their analysis of claim frequency and severity. In this section we show how insurers may incorporate public meteorological information in their claim analysis.

In this application we consider the annual number of accidents with victims per municipality in Spain for the year 2019. In total, there were 8,131 municipalities with at least 1 accident with victims. Figure 3 shows the number of accidents in the Spanish municipalities in the year 2019. Data are scaled per each thousand inhabitants to be comparable municipalities of different population size.

Our objective in the example is to investigate whether weather information can be useful for insurers to explain the number of motor accidents with casualties. The accident data of the municipalities are adjusted to the number of inhabitants of the municipality. To avoid possible variability in the municipal motor accident rate due to the small population size of the municipality, we select municipalities with at least 500 inhabitants or more. The size of the dataset is now 4,146 Spanish municipalities with more than 500 inhabitants in which at least one motor crash with injured victims occurred in the year 2019. Table 3 shows descriptive statistics of the numerical variables of the dataset. Note that in this and the next sections we follow the definition of injury severity used by the DGT in which a victim is seriously injured if at least one day of hospitalization was required. Otherwise, injured victims are classified as casualties with slight injuries.1 The degree of urbanization of the municipality following the European methodology used in the previous section is considered in the analysis. Table 4 shows the relative frequency of the degree of urbanization of the municipality (categorical variable with three categories).

Table 3. Descriptive statistics for numerical variables in municipalities with more than 500 inhabitants. Source: Own elaboration based on DGT data (year 2019) and section 3.

Table 4. Relative frequency of the degree of urbanization for municipalities with more than 500 inhabitants. Source: Own elaboration based on DGT data (year 2019) and section 3.

Now, we incorporate the climatic information of the municipalities in our dataset. We will use open data from the Spanish Agency of Meteorology (AEMET). In particular, we will use the normal climatological values corresponding to the period 1981-2010 for Spain for different climatic variables (AEMET, 2024).

The climatic variables of municipalities that can be consulted and that may be of interest to the insurance sector are:

The information available in AEMET is stored in GeoTIFF file format (.tif extension) that allows storing georeferenced information in an image file with TIFF format. Each GeoTIFF file corresponds to a raster image that refers to a climatological variable and period (monthly, annual or seasonal). The list of available files and the meteorological variables can be consulted in the appendix of Chazarra et al. (2018).

A raster image consists of a matrix of cells (pixels) organized in rows and columns in which each cell is represented by a color (Figure 4).

In the GeoTIFF files available at AEMET each cell is georeferenced. The images are projected according to the geographic coordinate system EPSG: 4326 (WGS 84 - WGS84 - World Geodetic System 1984). That is the most commonly used geographic coordinate system (used in Google Earth and GSP systems, for instance) and allows the geographic location of each cell of the image. The color of the cell represents the information of the value of the meteorological variable in that location. The colors of the image cells are defined in RGBA (Red, Green, Blue, Alpha) scale. Each parameter (Red, Green, and Blue) defines the intensity of the color between 0 and 255. The Alpha parameter represents the opacity/transparency, where 0 represents the maximum level of opacity (black), and 255 represents the maximum level of transparency. In our application, the alpha parameter would be particularly useful to distinguish between cells representing the land of the peninsula (or the island) and those that represent the sea. Finally, the GeoTIFF file provides scale information that links the RGBA colors of the cells with the values of the meteorological variable of interest.

To illustrate the use of meteorological information, we display the map of the average maximum daily rain precipitation in the 1981-2010 period in Canary Islands in Figure 5. The rainfall information was obtained from the GeoTIFF file downloaded from the AEMET website (AEMET, 2024). The equivalence between the RGBA space and the interval of the numerical values of the meteorological variable is provided at the bottom of the image, as a footnote.

Figure 5 was plotted downloading and reading the rainfall information from the raster image in RGBA space. The next step is to convert the RGBA information into a numerical value of the meteorological variable that insurers can incorporate in their insurance claim analysis. To achieve it, we previously convert the RGBA scale to the HSV scale. HSV space is a cylindrical-coordinate representation of points in an RGB color model. HSV stands for hue (type of color), saturation (quantity of color to be added), and value (brightness of the saturation of the color). Doing it, each color is represented by a single value and the numerical conversion of color values to the meteorological values becomes easier. Figure 6 represent the average maximum daily rainfall (in mm) in the Canary Islands for the 1981-2010 period after converting the color scale in a numerical value. The R code for downloading of the georeferencing raster image of the average maximum daily rainfall in Canary Islands and the steps to convert the information included in the GeoTIFF file into a numerical value of the meteorological variable of interest is provided in the Annex.

Insurance companies have access to many data sources and can analyze motor claims from multiple perspectives. They can consider the characteristics of the location where the crash took place, as well as the injuries and characteristics of all occupants of the vehicles involved. For claims in which vehicles with passengers are involved, important information is lost when they only pay attention to aggregated costs and do not consider the variations coming from their injuries, especially when there is a recurrent pattern in the driver-passenger(s) profiles. Given that crashes represent the subset of policies of the company’s portfolio where the risk has materialized, crash reports are useful, not just to understand the claim, but also to infer traits of policyholders that condition their risk. For instance, understanding the heterogeneity in passenger injuries could help claims and reserving teams to have a more tailored opening reserves that do not distort in excess their quarterly average costs estimation.

Vehicles with passengers involved in injury crashes represent a relevant proportion of the total number of vehicles involved in injury crashes. In this section we use the dataset of motor crashes involving victims in Spanish roads in the period 2017-2019. According to the DGT, for the years 2017 to 2019, 19.6% of passenger cars that were involved in a crash in which at least one person was injured had passengers additional to the driver (Table 8). Given that the vehicle involved in the crash has at least one passenger, mostly has 1 passenger (65.7%), followed by 2 (20.4%) and 3 (10.1%) passengers. If attention is paid to their proportion of injuries according to their severity (Table 9), the proportion of serious and fatal injuries by number of passengers is very stable, around 2.1% and 0.5% correspondingly, while slight or no injuries change notably, as illustrated by the rejection of the equal distribution of injuries by passenger number in the Pearson’s Chi-squared test. The more passengers there are in a car, the less likely they are to suffer slight injuries. This illustrates that it is important to consider the injury heterogeneity in the passenger vehicles to analyze the insured risk as a whole, both in terms of pricing and reserving. Understanding the characteristics of individuals with whom drivers tend to travel is a key issue, and from our knowledge, little treated in the automobile insurance context.

Table 8. Passenger frequency in motor vehicles. Source: Own elaboration from DGT databases 2017-2019.

Table 9. Proportion of injury types by number of passengers in passenger vehicles (%). Source: Own elaboration from DGT databases 2017-2019.

Rather than solely evaluating the total cost per vehicle for the claim, a more nuanced analysis can be conducted. It involves estimating the conditional expected cost of the claim based on both the driver’s characteristics and those of its expected passengers. For instance, if a crash occurred knowing that the vehicle had only one passenger alongside the driver, an initial reserve for bodily injury could be established based on the expected probabilities of severity levels according to the number of passengers (Table 9). Alternatively, if the company wanted to estimate the total expected costs of bodily injury claims, it could incorporate the expected probability that insured drivers will be accompanied by one or more passengers (Table 8) and the estimated severity of injuries in each case (Table 9). Matching all the tables can be easily done with a relational database, where keys are matched to relate passengers or all occupants with crash characteristics, enabling data manipulation and analysis. The composition inside the car varies and multiple combinations may be considered. For instance, the analysis could be made by gender of driver and passengers, by different age groups, or by geographical area, linking with research presented at previous sections.

The diversity of passenger profiles is easily appreciated in the data, as illustrated for example by Tables 10 and 11, based on DGT data. In Table 10, the relative frequency of the gender of passenger by injury type and gender of the driver can be seen; e.g., of all passengers with fatal injuries and a male driver, 46% were men, while the remaining 54% were women. In this table, it can be seen that women tend to have relatively better outcomes with a woman driver except for fatalities, where they represent a smaller proportion of all fatalities. Statistical independence between the driver-passenger gender pairing and the passenger’s injury type using the Pearson’s chi-square test for independence returned a p-value <0.01.

Table 10. Passenger injury by driver gender (%). Source: Own elaboration from DGT databases 2017-2019.

In Table 11, the relative frequency of the pairing of a driver and passenger by age and number of passengers in the crashed vehicles can be seen. Statistical independence between the driver-passenger age pairing and the passenger’s number using the Pearson’s chi-square test for independence returned a p-value <0.01. For vehicles with 1 passenger, 87% of them were driven by a person aged 18-64 years old, while the remaining 13% were accompanied by a driver older than 64 years old. Also, the more passengers there are in the vehicle, the bigger the proportion of drivers aged 18-64 years old: 93.9% for 2 passengers, 94.6% for 3, and 96% for vehicles with 4 passengers. Therefore, the first suggestion an observer would consider is that most passengers, regardless of age, go on the road with drivers younger than 65 years old. However, if passengers are grouped by age and the relative frequency by driver age is considered, as presented in Table 12, we get a different result. Older adult passengers go on the road with drivers older than 64 years old in a significant proportion. In 68.5% of vehicles with 1 passenger and a passenger aged 65 or more years, the driver is also and older adult. In vehicles with 2,3, and 4 passengers, if there is an older adult passenger, the likelihood of having an older driver are 35.6%, 43.3%, and 29.2% correspondingly.

Table 11. Relative frequencies (%) of the passenger - driver age by number of passengers. Source: Own elaboration from DGT databases 2017-2019.

Table 12. Relative frequencies (%) of the passenger - driver age by number of passengers and driver age. Source: Own elaboration from DGT databases 2017-2019.

To exemplify how to combine the different databases we are using in this paper, we select now passenger cars with 5 or less occupants. The insurer knows some drivers’ characteristics, such as residence and gender. If the Eurostat methodology is applied, we obtain, in Table 13, the following relative frequency of driver gender-residence-age combinations.

Table 13. Relative Frequencies (%) of the gender, residence degree of urbanization, and age of driver in this segmentation. Source: Own elaboration.

Now, we consider the proportion of occupants by each combination. It can be seen that proportions for the different groups seem to be different (Table 14).

Table 14. Proportion of vehicles with a determined number of occupants by driver with a combination of gender, residence urbanization degree, and age group. Source: Own elaboration.

The possibilities that open up in terms of segmenting risks and optimizing pricing and reservation processes are evident. Thanks to relational models and the combination of databases, risk characterization can now be done with a much higher level of detail.

The access to data has exponentially grown in last years and more diverse data sources become available to insurers. Techniques to link different databases to extract useful information become more important in pricing and risk management. As a previous step to delving into complex techniques, it is crucial to explore the interrelation of data to understand better the behaviour of our policyholders. We have observed there are cost-effective uses of resources already available to insurance companies, such as relational databases, but to leverage them, they must be creative and experiment to find useful relationships for them. Relational databases serve as a valuable tool in exploring and extending it to all areas of insurance companies.

There are potential applications all over the industry, combining in-house data with public available data can give them an edge, as evidenced in this paper by using spatial analysis and attributing a standard urbanization degree, or considering climatological variables such as rain. In both cases, we were able to add variables that helped us to grasp better the heterogeneity in our data, allowing for more adjusted general analysis and the possibility to split the database into more homogeneous segmentations. Also, the use of standardized categorizations can help companies to compare themselves against industry benchmarks, making comparisons faster and easier, by avoiding arbitrary specifications. Let us highlight the relevance of these analyzes in the new Sustainability framework, where factors such as climate change or population relocation take on a leading role.

Nevertheless, relational databases with only in-house data can be advantageous too, as long as companies are creative and curious, as illustrated by the study of the passengers’ heterogeneity of the same vehicle. In this study we use motor crash data from the DGT but similar analysis can be done by insurers with in-house data. Depending on the needs of the company, the depth of analysis can vary, but we have seen that there is room for conditional analysis, that some important variables have known-values beforehand, such as gender, age or residence urbanization degree, that can help to forecast crashes’ outcomes and estimate its variability based on past experience.

We are grateful to the Dirección General de Tráfico for access to their database. We also are grateful to the Spanish Ministry of Science and Innovation grant PID2019-105986GB-C21 and to the Departament de Recerca i Universitats, del Departament d'Acció Climàtica, Alimentació i Agenda Rural i del Fons Climàtic de la Generalitat de Catalunya (2023 CLIMA 00012).

R Software was used in the applications (R Core Team, 2024). R code used to plot Figures 3 and 4 is provided below.

To read the raster file and consult contained information.

To plot Figure 5.

To convert RGBA values in numerical values of the meteorological variable of interest.

To plot Figure 6.