Application of TOPSIS method for flood susceptibility mapping using Excel and GIS

This study elaborately manifests a simplified Technique for Order Preference by Similarity to Ideal Solution (TOPSIS) multicriteria decision-making (MCDM) approach that goals to determine the disparity among the distances between the positive and negative ideal solutions. MCDM methods evaluate options based on a variety of criteria by using mathematical and analytical methodologies. This promotes a more transparent and objective decision-making process by removing human biases and subjective judgements. By considering the comparative proximity to the optimal situation, TOPSIS considers the distances between the ideal and the negative-ideal alternatives. This study has concentrated on the normalization process, the appropriate determination of the ideal and the anti-ideal solution, and the metric utilized to compute the euclidean distances from the ideal best and the ideal worst.• This study expresses the simplified TOPSIS methodology as stated by Hwang and Yoon (1981). The categorization and weight assignments of the criteria have been executed from the expert's opinion and based on existing literatures.• Integration of the TOPSIS technique with GIS has been properly performed for the production of a flood susceptibility map of a highly vulnerable region and visual interpretation of the TOPSIS algorithm.• This kind of investigation saved time by sufficiently skilled specialized personnel in this field.

• This study expresses the simplified TOPSIS methodology as stated by Hwang and Yoon (1981).
The categorization and weight assignments of the criteria have been executed from the expert's opinion and based on existing literatures. • Integration of the TOPSIS technique with GIS has been properly performed for the production of a flood susceptibility map of a highly vulnerable region and visual interpretation of the TOPSIS algorithm. • This kind of investigation saved time by sufficiently skilled specialized personnel in this field.

Method details
Background Human beings generally aspire to make calculated decisions when faced with a choice between several options. Scientifically, the goal is to create numerical and analytical techniques that consider several options and a variety of variables. The Technique for Order Preference by Similarity to Ideal Solution method is shortly known as the TOPSIS method, which is a numerical technique for multicriteria decision-making (MCDM) analysis. It was first postulated by Hwang and Yoon (1981), which relies on determining the distances between every alternative and the ideal and anti-ideal solutions [1] . It is a simple technique with clear instructions that solves MCDM issues well. This strategy often focuses on the euclidean distance between the many decision-making options that are provided. According to this method, proximity is used to rank the alternative. It is regarded as one of the traditional MCDM techniques that have drawn a significant amount of attraction from both researchers and academics [2] . It's been employed efficiently in a variety of fields, viz., energy management, water resource management, environment management, human resource management, business, and marketing management. The TOPSIS MCDM approach has been selected for this study since it can find applications in a wide range of areas, analyze trade-offs, incorporate subjective judgements, give transparency, and enable data-driven settings. It provides decision-makers with an organized and efficient framework to solve complicated decision problems involving several factors. By employing TOPSIS, decision-makers can make informed and evidence-based decisions in identifying areas susceptible to flooding and implementing appropriate measures for prevention and mitigation. Several studies considered the integration of TOPSIS MCDM models with GIS, viz., Mitra and Das [3] , Ramya and Devadas [4] , Pathan et al. [5] , Yildirim et al. [6] .

Steps to integrate the TOPSIS with GIS for the construction of FSM
TOPSIS is not a pixel-based method; it is a discrete method. In the present day, it is necessary to integrate MCDM techniques with the GIS software. In this study, the integration has been conducted between the TOPSIS MCDM method and ArcGIS software to produce a flood susceptibility map (FSM). This methodological paper basically describes the overall steps utilized to produce the flood susceptibility map of the Koch Bihar district by the TOPSIS method in the published article by Mitra and Das (2022) [3] . The hierarchical representation of the flood susceptibility modeling using the TOPSIS MCDM model is manifested in Fig. 1 . The description of all steps of the TOPSIS MCDM is given as follows: (i) Construction of the thematic layers of the FSM criteria In order to generate the theme layers in the ArcGIS software, this work first developed the spatial database of 21 flood triggering variables. After deriving the vector and raster data of 21 criteria, the thematic layers were prepared. A total of 20 thematic layers were taken into account after the multicollinearity issues were resolved, and all layers were then resampled at an equal spatial resolution.
(ii) Extraction of the values from the thematic layers Then for the calculation of TOPSIS, random points were created in the 20 selected flood triggering variables for the extraction of the values. In this study, 15,000 random points were generated for the 20 criteria by employing the 'Create Random Points' tool of  Data Management Tools ( Fig. 2 a). Next, utilize the 'Extract Multi Values to Points' feature of the Spatial Analyst Tools of the ArcGIS system to retrieve the data of these sample locations from all thematic layers ( Fig. 2 b). Additionally, using these extracted random points values of 15,000 sample points, Microsoft Excel 2019 was used to compute TOPSIS. Primarily, in this study, 15,000 sample points were taken into consideration, but there are observed some missing values of 152 random points and therefore removed these 152 random points. Further work was carried out with values of 14,848 random points.

v) Establishment of a decision matrix
In the first stage, for calculation of the TOPSIS algorithm, must need to construct the decision matrix X, i.e., X ij . Eq. (1) illustrates the decision matrix with alternatives and attributes.

Fig. 3.
Assign weight for the criteria in the construction flood susceptibility map based on TOPSIS methodology.

Table 3
Evaluation matrix of TOPSIS MCDM technique.
Sample No. S1 S2 S3 S4 S5 … S14844 S14845 S14846 S14847 S14848 Criteria  Aspect  3  3  3  3  4  …  3  2  2  2  3  Curvature  1  1  1  3  1  …  1  1  The table contains the exported values of 14,848 random points in each criterion representing the decision matrix of the present study as stated in Table 3 and Supplementary Material (SM) 1. The table of the decision matrix is also called the evaluation matrix of TOPSIS.
(vi) Calculation of normalized matrix Table 4 Calculation matrix of X 2 ij .
Sample No. S1 S2 S3 S4 S5 … S14844 S14845 S14846 S14847 S14848  After that, the standardization of this decision matrix ( X ij ) is made by vector normalization matrix, i.e., X ij . For the calculation of the normalized matrix following equation ( Eq. (2) ) is employed: For the calculation of Eq. (2) , X ij are the obtained 14,848 random points values, and using these values X 2 ij was computed for all values ( Table 4 ). All the obtained X 2 ij values are summed by the SUM function of MS Excel and then square rooted by the SQRT function ( Table 5 ). Thus, dividing the opted values of the numerator with the denominator assigned the values of the normalized vector as illustrated in Table 6 and SM 2. (

vii) Calculation of weighted normalized matrix
The formula for calculation of the weighted normalized matrix is described in Eq. (3) as follows:

Table 6
Calculation sheet of the normalized matrix of the TOPSIS.
Sample No. S1 S2 S3 S4 S5 … S14844 S14845 S14846 S14847 S14848 where ̄ is the normalized vector and is the assigned weight for the 20 criteria. To obtain the values of weighted normalized matrix simple multiple, the values of ̄ and in MS Excel, as stated in Table 7 and SM 3.

(viii) Calculation of ideal best and ideal worst values
The ideal best or positive ideal solution ( + ) and ideal worst or negative ideal solution ( A − ) are calculated relying upon the values of weighted normalized, as stated below Eqs. (4) and (5) : where, J 1 manifest the beneficial criteria and J 2 demonstrate the non-beneficial criteria.  Table 9 Euclidean distance from the ideal best and ideal worst. It is an important task in TOPSIS MCDM to assign the values of ideal best and ideal worst. In this study, for beneficial criteria (14 criteria), calculated the ideal best value based on the maximum weighted normalized value among the 14,848 sample points weighted normalized values in each criterion using the 'MAX' function in MS Excel, as manifested in Table 0. The ideal worst for these 14 beneficial criteria is the minimum weighted normalized value among all random point's weighted normalized values. On the other hand, for non-beneficial criteria (6 criteria), the minimum weighted normalized value among the 14,848 sample points in each criterion is the ideal best, which has been performed employing the 'MIN' function in MS Excel.
The maximum weighted normalized value from 14,848 sample points is the ideal worst for NB criteria ( Table 8 ).

(ix) Calculation of euclidean distance from ideal best and euclidean distance from ideal worst
The euclidean distance from ideal best ( + ) and ideal worst ( S − i ) are computed based on Eqs. (6) and (7) : where, S + i is the euclidean distance from the ideal best, S − i is the euclidean distance from the ideal worst, V ij is the value of the weighted normalized matrix, V + j is the value of ideal best and V − j is the value of the ideal worst. Based on Hwang and Yoon (1981), a diagrammatic depiction of the Euclidean distance from the ideal best and ideal worst is shown Fig. 4 . Eqs. (6) and (7) were performed in MS Excel using several functions, and the result has been presented in Table 9 and SM 4.  0.556046 S14845 0.506005 S14846 0.612994 S14847 0.557212 S14848 0.550341 (x) Calculation of performance score Determination of the performance score is the main task in TOPSIS MCDM for establishing the proximity to the ideal solution. The performance score has been assigned in this study based on Eq. (8) .
where, P i describe the performance score, S − i represent the euclidean distance from the ideal worst and S + i manifest the euclidean distance from the ideal best. After obtaining the values of S − i and S + i , the P i has been calculated in MS Excel, as illustrated in Table 10 and SM 5.
(xi) Ordering of the sequence of preference The alternatives are then ordered from best to worst (greater relative closeness number). The alternative that is ideal and the answer to the issue is at the beginning of the list.
(xii) Mapping of the + , − and in GIS platform After the establishment of three important algorithms (i.e., S + i , S − i and P i ) of TOPSIS MCDM, it is the final task to map them to visualize the spatial differentiation of produced flood susceptibility map. For this purpose, the ArcGIS software is again employed. The procedure of map preparation is given as follows: • Firstly, the Study area map is open in the ArcGIS platform, and it is necessary that the map have only a Geographic Coordinate System, i.e., CGS_WGS_1984 Geographic Coordinate System used for the present study area ( Fig. 5 a). • The obtained values of S + i , S − i and P i are extracted from MS Excel and added on ArcGIS from the 'Add XY data' option in File Menu Bar ( Fig. 5 b). Then exported the data in ArcGIS to permanently save the XY data and further worked on it. When exporting the XY data, it is important that the study area map must have a Projected Coordinate System, i.e., WGS_1984_UTM_ZONE_45 N Projected Coordinate System had been utilized in the current research area ( Fig. 6 a).
• For mapping the criteria of positive effect ( S + i ), criteria of negative effect ( S − i ) and performance score ( P i ), the interpolation technique is utilized. Therefore, the Inverse Distance Weighting ('IDW') tool of Spatial Analyst Tools is employed in ArcGIS ( Fig. 6 b). In the 'IDW' dialog box, after assigning the 'Input point features', 'Z value field', 'Output raster', 'Output cell size',

Conclusions
The vulnerable and distressing conditions at a certain place can be initially assessed with the help of flood modeling. It has been challenging to model in developing nations because of a paucity of information and reliable calibration techniques. This investigation demonstrates the basic steps of the original TOPSIS MCDM technique using 21 criteria with 15,000 random points generation in each thematic layer, and then it integrates this model with GIS to visually interpret the flood susceptibility mapping of the Koch Bihar district of India. The study calibrates the model with all steps and, finally, the values of S + i , S − i and P i of 14,848 points were computed, and lastly established, the ultimate ordering of the sequence of preference based on the relative distance of P i . MS Excel 2019 and ArcGIS (10.4.1.) both the resources were simultaneously utilized for developing the model and then integrating it with GIS software.
Research on the detailed methodological description of MCDM models is limited globally. Specifically, an overall explanation of the integrated TOPSIS model with GIS is attempted throughout the present study for the first time, which demonstrates the novelty of this research.
TOPSIS is presently widely employed for modeling purposes in geographical and environmental studies. The original technique was adaptable sufficiently to enable numerous experiments and modifications. The expression of all the steps of integration will be helpful for academicians, engineers, and governmental agencies for the execution of the similar algorithm in other highly vulnerable places. The MCDM technique is crucial now as it facilitates complicated decision-making, encourages objectivity and stakeholder participation, tackles sustainability concerns, controls risk and uncertainty, optimizes resource allocation, and takes advantage of technological improvements.