STATE-OF-THE-ART REVIEW OF COLLAPSING SOILS Science and Technology, 6 (2001) 45-53 ©2001 Sultan Qaboos University The Development of a Database Management in GIS Applications in Oman Osman Abdallah Osman Akif Directorate General of Roads, Ministry of Communications, P.O. Box 27, P.C. 114, Muttrah, Sultanate of Oman. في عمان بناء قاعدة بيانات لتطبيقات نظام المعلومات الجغرافي ثمان عبداهللا عاكفع إدارِة قاعدة بياناِت ويعتبر بناء . سلطنة ُعمان جغرافيِة في المعلوماِت الضمن أنظمة قاعدة البيانات يجرى االن تطويرِ :خالصـة جموعة البرامِج على تَوحيِد م النظام قَْد ُبِنى و. جغرافيالمعلوماِت ال ضمن مشروع نظاِم الرئيسىفي ُعمان العمود الفقرِي وطنيِة .امِج إدارِة قاعدة بياناتِ ن كبر )ORACLE(كنظام معلومات جغرافى مع برنامج ) ARC/INFO ( الّسـلطنةِ فـى المسـتعملِة وزارات ال َستُعالُج و . ُعمان سلطنة فينظام المعلومات الجغرافى مشروع في وزارات الُمشَاَركَة ال فى كل ِم لنظَتَسـتعمُل هذه ا و البياناتُ الَمنُْسوبةُ التي تُعّرفُ هى تلك َركة بين الوزاراِت المختلفةِ تالمعلومات الُمش وهذه . ا بينه كةرتشالممعلومات ل ا الُمشَـاَركَة ). فع امنخرائط ال (خرائط الوزاراِت الخرائط الطبوغرافية و جُد عادة على ومن الميزات الجغرافيِة التي تَ تؤخذ كمعلوماِت وصفيِة األهداف الّرئيسية فان وفقاً لذلك، و .)ARC/INFO( برنامج طبقات مختلفة طبقاً لمبادئ ميزات الخريطِة في فَُيمكُن َأْن تَُصنّ و ـ الهـذه ل عالَج وُيديَر المعلومات ُي لتَأسيِس نظام إدارِة قاعدة بياناِت وطنِي ُيمكُن َأْن ةقترحطريقة الم ال حي تُوض ية هو ورقـِة البحث ـ تنظام إدارِة قاعدة بيانا باستعمال ةتصميم قاعدة البياناِت الُمقتََرح وسيتم . ُمشَاَركَِة في المشروعِ ال َركة بيـن الـوزارات تالُمش (ORACLE) ،خْلق وذلك لبيانات القاعدة بالخريطِة الجغرافيِة طبقات لَرْبطالمقترحة الورقة َستُعالُج الفكرة فان عالوة على ذلك ختام يعتبر أحد أهم الفوائد الرئيسية في تطبيق قاعدة بيانات وطنية في مجال أنظمة الوفى . جغرافيِةالمعلوماِت التطبـيقات نظاَم .المعلومات الجغرافية أن الوزارات المستخدمة لهذه القاعدة سوف تستخدم نفس المعلومات مما يفّعل تبادل المعلومات ABSTRACT: The development of applying database management applications is currently underway within Geographical Information Systems (GIS) applications in the Sultanate of Oman.The establishment of a national database management system in Oman is considered as the backbone that is required within the Geographic Information System project in Oman. The principal of the GIS project in Oman was built upon unifying the used software packages over the sultanate which is ARC/INFO as GIS Package and ORACLE as database management software. These packages are used within the different participating Ministries in the GIS project in Oman.The participating ministries will handle information that are shared between them. The shared information among the different ministries is the attributed data which is defined as descriptive information extracted from geographic features that usually existed on the topographic maps and the ministries maps (utility maps). The map features can be classified into different layers according to the principles of ARC/INFO GIS Package. Accordingly, the main objectives of this research paper is to explain the technique proposed for establishing a national database management system that can handle and manage the shared information amongst the participating ministries in the project. The proposed database design will be made using the ORACLE database management system. Moreover, the paper will handle a proposed idea for linking the geographic map layers with the corresponding attributed database in order to create Geographic Information System applications. As a Conclusion, it is Proposed that all the government GIS departments in the Sultanate will share the same information within a unique national database management which can be considered one of the major benefits in applying database management in the field of Geographic Information Systems. 1. Introduction The rapid increase of using computers in mappings and related applications has created a rich variety of information on maps and their related attributes. Such information is required to be 45 OSMAN ABDALLAH OSMAN AKIF stored and handled in the proper manner. Accordingly, the use of database management systems can be used to solve such data handling problems. On this basis, the production and use of maps has widely increased over the last decade in the form of Geographic Information System “GIS”. The GIS can be defined as a computerized integrated system consisting of hardware, software and third party software for capturing, processing, analyzing and presenting the map data in digital form. In addition, it has a facility to capture data from different sources and can handle such data in two different forms: vector or raster data. The core of any GIS system is its database management system, in which the map (graphic) data is stored and the attributed data are also entered and manipulated. Thus the data base management system is defined by different authors (burrough, 1986) as a computerized record keeping system which has the facility to maintain information and to make such information available on demand in the shortest possible time. Certainly, the use of database management systems has solved numerous problems encountered in using flat files for data storage such as data redundancy, non-sharing data, integrity and security considerations (Date, 1990). The data base in any GIS holds spatial and non-spatial information which is extracted from the maps or any other sources of data acquisition. The spatial information reflects the geometric representation of the graphic features on the maps. The non-spatial information compiles the attributed data that is attached to the geographic features as tabular data. It also contains information about the spatial relationship among different geographic objects which is called topographical relationships (Akif et al, 1992). Such relationships are: Sharing relation, connectivity relation and adjacency relation. Such topographical information are usually stored explicitly within the geographic data base to give satisfactory information about different objects or polygons. Other types of topology such as containments, near-by, belong-to can be extracted from the above mentioned types (Egenhofer et al, 1989). Oman has moved towards producing maps in digital form in 1987 using the AutoCAD Software Package on Personal Computers. In 1990, the relevant authorities started to establish Geographic Information Systems throughout the Sultanate. The establishment of database management system for the Geographic Information System was one of the major requirements within the project. This is mainly due to the massive amount of attributed data resulting from the movement from the AutoCAD to GIS Environment. Accordingly, our main objective here within this investigation is to develop a technique of designing and handling the attributed database management within a selected pilot study area in the GIS project in Oman. 2. CAD Systems Versus GIS Environment The AutoCAD Software is a developed module based upon the ideas of Computer Aided Design (CAD) and drafting systems. Such systems are largely concerned with the display and manipulation of the visible material in which they do not pay much attention to the non-graphic attributes that the graphic entities may or may not have. In CAD systems, the various types of geographical entities are usually limited to point and line entities while surfaces are not explicitly defined. The topographical information between lines and points are usually missing. However, it is not difficult to link the geographic features to the related attributed data base (Hassan, 1992). Developments have been made in the direction in which the ability to link the Computer Mapping System to a separate attributed database is established within a system known as Automated Mapping and Facility Management (AM/FM). Thus these systems have the facility to provide geographical data and related attributes. In addition, the AM/FM system can corporate topological data via a separate database in which it needs to establish a linkage between the graphics and the topological databases (Akif, 1991). The benefits gained from the above described systems are: the GIS would record the spatial information of object locations, shape data of identifiable geographical features and the attached 46 DATABASE MANAGEMENT IN GIS APPLICATIONS attributes. In addition, the GIS is not limited to describing the real world objects as in CAD systems but can cover all various basic shapes (points, lines, areas, surfaces) and relating them in the attributed database. 3. Database Management and its Development in GIS As it is obviously clear, the database management is considered as the backbone component of any geographic information system. The previous database structure types were based upon handling data through specific form of queries at the data entry level such as hierarchical and network data structures (Date, 1990). Actually, each of these data structures has its advantages and disadvantages. Thus, the technology of the relational data structure took the advantages of these two systems and developed in an easy form, (Codd, 1969). The relational idea is based upon handling and maintaining data through the existing relations. The simplicity of the relational database structure lies in its easy form which is a table. Each table is a collection of numbers of rows and columns. The most beneficial aspect in the tabular database is that the tables can be linked together or related to each other via relational keys which can gather data from different related tables. This principle is similar to the real world objects of relations to each other. More developments were adopted for the relational data structure and the relational model by using the Entity Relationship Model, ERM, in which it reflects the major component of any relational database of entities, relationships and their attributed values. In the area of the GIS, the relational database is the most commonly used for storing the attributed information. Now-a-days, almost all those using relational database management are already object oriented. The object oriented Database Management System supports an abstract concept object, entity or feature having existence independent of any attributes that the object may or may not have. The most important issue in any GIS is the linkage amongst geometric and non- spatial attributes (Herring, 1989a). 4. Database Model in ARC/INFO In this investigation, the ARC/INFO GIS Commercial Package from Environmental Systems Research Institute ESRI in California will be used. Within this package, the topographical attributed tables are separated from the graphic files. This is much desirable in order to avoid any massive data to be stored in the attributed database due to the geometric (locational) information. ARC/INFO is an object oriented system that can create the topological attributes automatically in separate tables and relate these tables with the geometric features via a unique identification number created and maintained only in Arc/Info. This number is used to link the geometric and topological attributes together. Within the Arc Cartographic package, the ARC macro language (AML) can be used and extended to perform searches and updating the topological attributes behind a power graphic cartographic interface. This can assemble the major aspects of GIS environment (Chapman, et al, 1989). The concept of the Arc/Info GIS software is based upon classifying the geographic features into a number of primary coverages. They are: Arc Coverage: represents linear features such as road center lines and borders of area features. The Arch features can be topologically linked to their end points (nodes) or to area on each side of them. Nodes Coverage: represents Arc end points and intersections of line features. Topologically linked to Arcs. Polygons coverage: represents area features. Topoligically defined by Arcs which compose their borders. Label Points: represent point features. Accordingly, any geographic features can be classified into one of the above coverages. From this concept and based upon the principle of the topological relationships between different spatial 47 OSMAN ABDALLAH OSMAN AKIF geographic features on the map, the attributed data are stored in attributed tables which are automatically created and related to the primary coverages. These tables are: PAT : Polygon Attribute Table AAT : Arc Attribute Table NAT : Node Attribute Table XAT : point Attribute Table These tables include relational keys to relate the geometric attributes (locations of different entities) with the topological attributes. According to this concept, the handling of maps within the ARC/info is based on separating the map into different layers. 5. Applications of Database Management in GIS Within a Pilot Area in Oman In the Sultanate of Oman a national project to establish a Geographic Information System over a wide area network is now underway. This project will firstly be examined within a selected pilot study area. Most of the ministries, in the Sultanate, are involved in this project and in particular the utility ministries and regional municipalities. Thus, the proposed database design scheme within the GIS project is based upon classifying the map into a number of layers and attribute files (as in ARC/Info criteria). Such layers and their attributed files would ultimately be needed to cover all the potential requirements of GIS users in Oman. These layers are called geographic layers. They are: a. Base Mapping layer which is concerned with the topographic features on the ground including control, landmarks and contours. b. Land records: Include land parcels, land sub-divisions, land records and land ownerships. c. Facilities: Include different utilities of electricity, water lines, sewer lines, telephone lines and gas pipelines. Information about the connectivity of a facility network is also included. d. Environmental: Includes features of natural environment such as soil, geology, hydrography and vegetation. It contains man-made features of archeological sites or environment. e. Administrative Areas: Define the various administrative boundaries, enumeration service and statistical analysis areas. f. Transportation: Includes road segment, intersection or road network, traffic signals and signs. According to this classification, the base mapping layer is concerned with the geographic and topographic features, while the other five layers are concerned with the different coverages of various requirements. By using the facilities of ARC/info in overlaying different coverages together, the base mapping layer will be fixed (original) for all other layers to be overlaid on the base mapping. Accordingly any or all layers can be combined with the base mapping layer according to usage and requirement. Having explained the geographical model of handling the geographic features on the map within GIS project, the attributed database will then be established. The attributed database is divided into two main types. 6. Base Mapping Specification The Base Mapping Specification is established for the base mapping layers and any new production of mapping in the GIS format in Oman (ESRI, 1992). The Environmental Systems Research Institute, has developed such base mapping specifications. The specification is designed as a Tabular Management System in which it includes information about: a) Topographical attributes of the geographic entities. 48 DATABASE MANAGEMENT IN GIS APPLICATIONS b) Non-graphic attributes attached to the geographic entities. Figure 1. illustrates a sample of the polygon attribute table and its content of different attributes. Layer: Cultural, Miscellaneous Table name: CULTMISP.PAT Table Description: The CULTMISP table contains polygon representing miscellaneous man- made features. Polygon Attribute Table (PAT) Variable Begin Column Defined Item Name Item Definition Area 1 Area 8,18F5 Perimeter 9 Perimeter 8,18F5 Internal Number 17 CULTMIS# 4,5B User_ID 21 CULTMISP_ID 4,5B Feature Code 25 F_CODE 6,6C Existance Category 31 EXS 3,31 English Name 34 NAM 40,40C Arabic Name 74 ANAM 40,40C Product Category 114 PRO 3,31 Item Description and Codes AREA Area of polygon in square coverage units PERIMETER Perimeter of polygon in coverage units CULTMISP# Internal number of polygon assinged by ARC/INFO CULTMISP_ID Unique User_ID used to relate attribute tables. F_CODE FEATURE CODES : AA010 MINE, AA011 QUARRY, AB000 DISPOSAL SITE, AL012 ARCHEOLOGICAL SITE, AL130 MONUMENT, AL260 WALL Figure 1. Sample of base mapping specifications (after ARC/INFO specification, 1992). 7. National Database Mangement System The national database management system is required to be established to cover the attributed data of the other five layers. The basic idea of such database is to handle the shared information among the participating ministries in the GIS project. The benefit of such database is to avoid any redundancy or duplication of information. The term of information or shared information means, common data that is required by specific ministries. Thus, it is not necessary for each ministry to store, manage and update such data. The handling of the data will be according to the principle of distributed database, in which each ministry will be responsible for creating and maintaining its own data which can be used by other ministries via the wide area network. Two further aspects were required to be considered in the design of the database which are the linkage between the base mapping specifications and the national database management system as well as the compatibility between these two database types. 49 OSMAN ABDALLAH OSMAN AKIF Based upon these assumptions, a user survey is made to identify the common sharing information between the different ministries. These data are then analysed and organized in a form of application. Each application is designed separately to identify the number of tables it contains and the related attributes. The relational keys for each application are identified to link the related tables within the application. The linkage between the geographic layers and the attributed database is established to support the graphic layers in front of sharing database. Furthermore, from the user survey each participating ministry will be responsible for creation, storage and maintenance of one or more applications. Thus, the relational database management system will be created and maintained by the participating ministries in the project according to the principle of distributed database. For example, Ministry of Electricity and Water is responsible for creating and maintaining the attributed data related to electric lines shown in Figure 2. Accordingly, this ministry is responsible for creating such table, entering, maintaining and updating the data. On the other hand, a different ministry have the right to access the data without making any processing to the data. The national database management system is established to cover the large, medium and small map scales. The format of this database is similar to the base mapping specifications to simplify the interchange and access to these databases. Layer: transmission lines Application, Electrical Data Table: Transmission Lines Table Description: Table contains the attributes related to electric transmission lies characteristics Variable Begin Column Defined Item name Item Defintion Segment_ID 1 Seg_ID 6,61 Line Name 7 Line_Type 15,15C No. of Circuits 22 Crt_No 2,21 Trans. Vlotage 24 Volt. 3,31 Overhead/ Cable 27 OHL/CAB 3,3C Depth/Height 30 D/H 4,5B Minimunm Clear 34 Min_Clear 3,4B Dist./Road CL 37 Dist CL 4,5B Length KM 41 Length 5,6B Capacity (MAV) 46 CAP 5,6B Cross_Sec.Area 51 Area 4,8F2 Item Descriptions and Codes Figure 2. National database management sample table. 8. Implementation and Further Developments Implementation of the above mentioned database types is underway within the GIS project in Oman. The base mapping specifications is implemented in a draft copy, in which it has to be examined within a pilot study area. A pilot study area was selected to cover different areas with different topographic features. This area has large scale maps (1:5000) produced in the AutoCAD format (DXF format). In order to apply the GIS to a selected area, it is required to convert the relevant maps from AutoCAD to 50 DATABASE MANAGEMENT IN GIS APPLICATIONS GIS format. The process of such conversion is to transfer the AutoCAD data into the Arc Cartographic package. Then, some manual editing are required to amend the geometric errors that are created due to the separation of the geographic layers. The manual editing can be replaced by developing number of macros to be written using the Arc Macro Language (AML). These macros, have the facility to search automatically all existing features in specified layer and to apply certain rules which are written within the macro itself. After removing the existing errors from the geometric data, the topological attributes can be created using the facilities and ARC/Info. At this stage, the other non-graphic attributes such as feature codes can then be stored in the created attributed tables. These attributes can either be stored by manual or automatic technique. Once, these specifications are examined after the pilot study it will be finalized to be used in the production of new maps in the GIS format directly. The implementation of the national database management is started by establishing a draft copy of such database for a pilot study area. The process of the implementation is to let each responsible ministry create its tables as explained in the previous section. Then, storage of the attributes will be made by manual or automatic technique. This draft copy will be examined using the products of the base mapping conversion in GIS format. The graphic layers will be linked to the shared database through identifiable numbers that relate the graphics with shared database. In order to make all the governmental GIS Departments in the Sultanate share and use the above database, a wide area network is currently being established. This wide area network will link all the participating ministries in the GIS Project and allow them to use the national database. Once the databases have been implemented and examined between sharing ministries via the wide area network, some developments can be made to handle the national database in a form of customized applications. The customized applications are development data base management application tools that can handle the attributed data in a form of screens and driven menus. Such applications can be developed using high level of computer programming languages such as C or 4GL besides the query languages of the database commercial package. For example, the facilities or utilities information can be considered as an application which consists of a number of aspects such as: a. Electric Facilities b. Water Facilities c. Sewer Facilities d. Telecom Facilities e. Gas and Petrol Facilities f. Street Lighting Facilities The attributed data of each of the above facilities are stored in a number of tables. For instance, in the case of electric facilities, the tables are: i) Electric Lines (transmission) ii) Electric Lines (distribution) iii) Electric Maintenance (lines) Furthermore, inside each table there are a number of attributes that can be helpful for other users and planners. Accordingly, a customized application provided the facilities which can simplify the handling of data by presenting them in a form of screens or reports instead of tables. Figure (3) depicts a proposed example of such application and its driven menus for the utilities data base. 51 OSMAN ABDALLAH OSMAN AKIF MAIN MENU ELECTRIC FACILITIES WATER FACILITIES SEWER FACILITIES TELECOM FACILITIES GAS AND PERTROL FACILITIES STREET LIGHTING FACILITIES Transmission Lines Distribution Lines Mantinance of Lines Primary stations Primary sub_stations DRIVEN MENU Segment_ID Line Name: Transmission_Voltage Length: Capacity: Area Overhead /Cable: No. of Circuits Depth/Height: Conductor Type: Clearance: Reservation: Screen for single record of the transmission line Figure 3. Proposed utility application. 9. Conclusions With respect to the database management system, the implementation proved that the development of a database management for handling and managing the massive amount of attributed data can easily be adopted. In addition, the proper analysis of data and the design of the relational database in this project and capabilities of the attributed data can be handled and shared between the participating ministries to make the GIS project simple to use and more efficient to implement with a high degree of success. Sharing data between participating ministries can be considered as one of the most important applications of using the database management in the GIS. Furthermore, the principle of distributed database, simplified handling and managing the attributes is preferred to gathering all attributed data in a central data bank. Certainly, by developing customized applications, the handling of data will be much easier and does not need highly qualified staff. The compatibility of the GIS environments hardware and software in the different ministries saved considerable time and effort. The establishments of the wide area network is also considered as one of the major items that helped in the success of the GIS project because it avoided data redundancy and time consumption. 52 DATABASE MANAGEMENT IN GIS APPLICATIONS References AKIF, O.A. NASSER, A.A. KADY, M.M. and HASSAN, K.M., 1992. An alternative technique for updating digital map data within pilot project in Egypt, paper presented at the 17th Congress of ISPRS (2-14 August), Washington D.C. AKIF, O.A., 1991. Digital Mapping, Automatic Structuring and classification for Urban Area Using a Graphic Workstation, Ph.D. dissertation, Faculty of Eng., Ain Shams Univ., Cairo, Egypt. BURROUGH, P.A., 1986. Principle of Geographic Information System for Land Resources Assessment, Oxford University Press, Oxford. CHAPMAN, D.P. and DALTON, N., 1989. A compact representation of Cartographic Topology Using Nested Binary Tree, Auto-Carto/ARICS, Education Trust, Project Report. DATE, C.J., 1990. An introduction to Database Systems, Vol 1, Fifth Edition Addison-Wesly Publication Company. EGENHOFER, M.J. and FRANK, U.A., 1989. Object Oriented Modeling in GIS: inheritance and propagation, Auto-Carto 9 Proceedings, Ninth International Symposium on Computer Assisted Cartography Held in Baltimore, Maryland U.S.A., April 2 – 7. ESRI, Environment System for Research Institute., 1992. Geographic Information System – Digital Basemapping Specification, Sultanate of Oman. HASSAN, K.M., 1992. Towards Automatic handling and Updating of Digital Map Data for Land Information System in Egypt, M.Sc. Thesis, Faculty of Eng., Ain Shams Univ., Cairo, Egypt. HERRING R.J., 1989a. The Definition and Development of a Topological spatial data system, Photogrammetry and LIS, 2-17 March, Swiss Federal Institute of Technology (EPEL), Eucblens, Lausanne. Received 4 March 2000 Accepted 1 June 2000 53 ÈäÇÁ ÞÇÚÏÉ ÈíÇäÇÊ áÊØÈíÞÇÊ äÙÇã � Introduction CAD Systems Versus GIS Environment Database Management and its Development in GIS Database Model in ARC/INFO Base Mapping Specification Layer: Cultural, Miscellaneous Table name: CULTMISP.PAT Variable Defined Item Name Layer: transmission lines Application, Electrical Data Item Descriptions and Codes Implementation and Further Developments ELECTRIC FACILITIES Transmission Lines Conclusions References