Building an urban drainage database (GIS) for urban planning and management: A case study in Cao Bang Province Viet Nam

. The management of urban drainage systems faces many shortcomings in many urban areas in developing countries, including Vietnam. This poor management is causing economic and environmental consequences, especially flooding. One of the reasons is that urban areas have not applied technology in management, do not have a database of the status of current urban drainage system, so the management skills is not scientific and there is no forecasting technique. GIS technology is a highly effective management support tool in many cities around the world, and the Vietnamese government has also issued regulations requiring the use of GIS in urban management. This paper studies the process of establishing a GIS database for urban drainage in ArcGIS sofware and a case study in Cao Bang city, Vietnam. The results of this study are a reference for cities that want to use GIS technology in urban management and the database of this study is for Cao Bang City in Vietnam to be used to integrate into the database of city (big data) in Viet Nam government-oriented urban management.


Introduction
Flooding is responsible for the highest number of disaster-related fatalities of any natural hazard globally [1].Besides urbanization and climate change, Urban flooding during heavy rainfall needs to be considered in terms of urban drainage system management [2,3].
For a long time, urban drainage systems have existed as a vital city infrastructure to collect and convey stormwater and wastewater away from urban areas [4].Urban drainage systems have prevailed as an essential infrastructure of cities to gather and transport stormwater and wastewater [5].
The rapid growth of cities with complex master plans and infrastructural facilities in cities in developing countries has resulted in drainage system problems with status data for design, planning, analysis, operation, and management.
In poorly drained areas, urban runoff mixes with sewage from overflowing latrines and sewers, causing pollution and a wide range of problems associated with the increased risk of waterborne diseases [6].
Urban drainage system is composed of drainage network (pipes, culverts, canals, ditches, regulating lakes...), pumping stations to drain rain water, wastewater, wastewater treatment works, and other ancillary works for the purpose of collecting, conveying, draining rainwater and wastewater, preventing flooding and treating wastewater [6].
Drainage systems are divided into the following 03 categories: a) General drainage system is a system in which wastewater and rainwater are collected in the same system; b) Separate drainage system means a separate drainage system for rain water and wastewater; c) Semi-private drainage system is a common drainage system with an enclosed sewer line to separate wastewater and bring it to the treatment plant.
Currently, the management of urban wastewater systems in developed countries has not yet applied technology but by traditional methods.There is no database to provide necessary information for urban managers such as spatial information and attributes of objects belonging to the urban drainage system.When there is a flood, managers do not have the space and time view to have all the information needed to make decisions.This reduces the accuracy and timeliness of incident handling and increases the risk and consequences of flooding.
Geographic information systems (GIS) are defined as software systems, and their relationships to other activities connected with geographic information are reviewed [7].GIS was developed to provide a powerful tool to analyze large volumes of geographic data [8,9].It stores a considerable amount of spatial information in a compact and accessible form and it has the ability to work with spatial and non-spatial data and create information by integrating data layers [10].
GIS is a combination of software, hardware, and experts, who stores, transforms and displays the spatial data [9].Data are stored digitally in GIS, therefore, they need less space compared to traditional systems like paper maps.
In this paper, the GIS of the urban drainage system is developed to be able to update information easily and perform various queries.The objective of this study is to develop a data structure in a GIS environment for urban drainage systems for application in flooded cities.
In Vietnam, big cities are flooded when heavy rain or high tide occurs, flooding occurs on a large scale and lasts for many days.Urban managers, authorities at all levels cannot visit the exact location to check the drainage system or have the best plans to operate the drainage system to reduce flooding.A database containing information about the drainage system, as well as information such as traffic population in these cities has just begun to be built.
Before 2020, for urban management, the Government of Vietnam as well as the Ministry of Construction have no legal documents to put GIS application into practice.Although Vietnam's planning law has requirements on building a database and mapping system, there are no specific regulations on using GIS and ArcGIS software [11].Until now, the government has regulations on the application of GIS in urban management, but the construction of structured data sets and information layers is also being researched.
Cao Bang is a mountainous province in the north of Vietnam, with such a terrain, flooding is impossible, but in this city, there is always local flooding when it rains heavily.So what is the responsibility of urban management here and how to limit the risks?In this study, we propose to build a database of the drainage system, using a smart data management method to avoid risks caused by flooding.In addition, it is possible to analyses to reduce flooding in Cao Bang city.
A Database Management System (DBMS) is a collection of programs that enable users to create and maintain a database [12].The database structure and GIS dataset of the urban drainage system in Cao Bang city have been formed with two types of spatial and non-spatial data.This database is immediately applied to the management of Cao Bang city, where there is many local flooding in the rainy and stormy season.This dataset is designed according to regulations, so it can meet the connection requirements with the overall data of Cao Bang Province.
The results of this study provides information for water planners and decision-makers, and it is also the best reference for other cities applying GIS technology in urban management at the request of the Government adaptation options to increase drainage systems efficiency under changing climate and urbanization [13,14] 2

Study area
Cao Bang is a city in northern Viet Nam.It is the city and largest settlement of Cao Bằng Province.It also has a common international border with Guangxi province in China.Cao Bang city covers an area of 107.6 km2, and has 8 wards and 3 communes.The location of Cao Bang city is 22°40′00″N 106°15′30″E (fig. 1)

Data and methodology
The data collected and used in this study include National geographic database of Cao Bang city at scale 1:10.000including 7 data layers (Measurement base, Boundary, Population, Traffic, Water system, Surface cover, Topography).Current drawing, planning of drainage system in .dwgformat collected from state agencies in Cao Bang city.Report documents and data related to the drainage system of Cao Bang city.In addition, in order gathering data completetly and detailed information for the GIS data of the Cao Bang city, the research team conducted a survey and gathering data by other technology.The process of building a drainage GIS database structure includes the following main steps: a) determining the scale of database; b) Collecting data; c) transform data to the GIS environment; d) Building an architecture of GIS drainage system; e) building spatial; f) building non-spatial Determining the size of the database is the base for the data collection process, data collection needs to be coordinated by the parties, especially the urban drainage system management agency of city.For each different area and urban level, the database structure will be different in terms of completeness and detail.
To integrate into the city's database, the mathematical foundations of the GIS database of the drainage system according to the National Geographic Database.
The process of building a GIS database framework for the urban drainage system is the first step to design a GIS database structure.In this step, several layers of information such as: canals, drainage system, land use, farms, groundwater surface level ..... etc have been imported in ArcGIS (fig.3).The data after entering the GIS must be classified according to the correct data type in the GIS management environment: Attribute Data and Spatial Data.
In which the data also needs to be grouped according to current drainage system data and planning drainage system data with the specified contents for updating, storing, organizing and maintaining of data.

Fig.3. Spatial data of water drainage in GIS
Collected spatial data is standardized mathematically correct with the National Coordinate System VN-2000, Elipsoid WGS-84; projection zone 6°central meridian 1050 (zone 48); the National Elevation system (Hon Dau -Hai Phong).Spatial data and attribute data are classified according to the specified objects belonging to the urban drainage system and described in Table 1.

Results and discussion
The results of the study are the architectural model of the urban drainage database GIS, a database of drainage system, and maps that other cities can use as a reference.Objects of the urban drainage system are spatial objects, and their properties are also separated in this study with many layers.
From the database in the study, maps, diagrams or tables on the current state of the drainage system are formed in many different formats.Figure 3 shows the urban drainage system of Cao Bang city which is established from a GIS database in .mxdor .pgnformat.Each object in the layers has its attribute that are different from previous urban drainage drawings using autoCad software.Using a database that can control flood points in the city, combined with a high-level information layer to bring timely spatial information to managers, especially in the rainy season.
The GIS database of Cao Bang City includes spatial and attribute data helps managers find exactly the location of the drainage objects in the office during the time of flooding or stormy weather.In addition, managers can find the attribute information of drainage system object such as at name of flood points, how many times is this location flooded, which year is the most flooded.
Besides, combined with the digital elevation model, it is also possible to create a flood simulation model, which is the forecast information to prevent damage caused by storms and floods.

Conclusion
Management of urban drainage systems is a necessity for cities that are frequently flooded, and large cities need to apply technology to promote more efficient water drainage [15].The application of GIS technology to build a database to serve the management of urban drainage systems from the current status to planning stage will bring high economic efficiency, provide accurate solutions in a timely manner time.
There have been many studies on the application of gis in drainage management [15][16][17], this study once again confirms the application of GIS technology to build a database to serve the management of urban drainage systems will bring high economic efficiency, offer solutions to limit risks on time.Besides, building a database, updating spatial information and attributes needs to be periodically, this keeps the dataset alive.With the results of this study, in the next studies we will aim to use GIS and digital elevation models to simulate flooding in urban areas in Vietnam.
Determine the geometry is an important step in determining the structure of spatial data.Geometry of spatial data depends on the scale of the map, the scale of the management.Setting up an urban GIS database is laborious and expensive, determining the geometry in the correct database will bring high economic efficiency.
One of the ways we do it very effectively is that the collection of spatial and attribute information such as location of drainage system objects by field survey and using GPS technology.

Fig 1 .
Fig 1. Location of study area

Fig 4 .
Fig 4. Map of urban drainage systems of Cao Bang city

Table 1 :
Spatial and attribute data