A MySQL-Based Software System of Urban Land Planning Database of Shanghai in China

1  Introduction

With the developments of data science and big data, the digital city has been paid more attention. As a part of the digital city, the urban land planning database should be developed. For data analysis of urban land planning, it is difficult to obtain the corresponding data because the quantity of data is large, the data format is disorder, and the corresponding data sources are various. Therefore, it is necessary to develop a software system of the urban land planning database to improve the efficiency of storing and managing land data, eliminating redundant data and sharing data. It is easy to analyze the influencing factors of urban land leasing based on the developed urban land planning database [1–3]. And the database can provide an excellent data source for the corresponding studies on urban analysis [4,5].

Since 1995, some developed countries, such as the United States, the United Kingdom and Canada, have successively implemented the national infrastructure planning of land information resources and developed spatial database frameworks. The United States, Australia, the European Union, Japan, Singapore and other developed countries have also proposed to develop a database management system (DBMS) for land planning [6]. In 2009 in China, Article 27 of the Measures for the Review of the Formulation of Overall Land Use Planning stipulated that the planning database should be submitted as the information for review and approval of the overall land use planning. In 2011, the Ministry of Land and Resources pointed out that it is urgent to develop a planning database and improve the ‘One Map’ of land resources [7]. Li et al. [8] proposed a comprehensive geographic information platform for digital land planning to manage the data of basic land resources and planning in Wuhan, China. Based on WebGIS, Lin et al. [9] designed a land management information system for the Lujiazui area of Shanghai in China. Yan et al. [10] presented the DBMS of Changzhou International High-Tech District in China. And Li et al. [11] designed a land planning DBMS based on ArcSDE.

Regarding database technology, with the continuous accumulation and rapid growth of data, the demand for the corresponding databases, which support storing big data, is increasing. Some government departments and companies have developed database clusters based on MySQL. McLaughlin et al. [12] examined the demand for information products and services in the surveying profession. Denton et al. [13] discussed the advantages and disadvantages of commercial enterprise software and personal databases, such as Oracle, IBM’s DB2, Microsoft Access, PostgreSQL and MySQL. Gao et al. [14] organized the oriC of bacterial genomes into the MySQL database DoriC, which provided an extensive informative and graphical view of oriC regions. Yu et al. [15] analyzed PHP and MySQL, and illustrated the implementation process of the ZhuoQi website. Epifania et al. [16] completed the design of multimedia interactive system of digital learning based on MySQL. Bravo et al. [17] designed a document management system by applying MySQL and Apache 2 Server to the process of document storage, control, management and distribution. Peng et al. [18] analyzed the functional requirements of college dormitories and designed a student dormitory management system by using Java, SSM framework and MySQL. Based on the data of the districts in Beijing from 2004 to 2015, Cheng [19] presented mathematical models and analyzed the residential land leasing behavior of district governments of Beijing in China.

In China, currently, there is no district-level database of land planning in megacities, such as Beijing and Shanghai. Meanwhile, there is an abundance of database technology to develop the land planning database. A complete data management platform should contain not only basic data, but also a complete and practical DBMS.

In this paper, in order to solve the above problems, an urban land planning database is presented based on MySQL. The conceptual model of the urban land planning database is proposed, and the entities, attributes and connections of this model are discussed. SpringBoot is used as the back-end framework, MySQL and Java API are used as the development tools, then a management platform with data management, information sharing, map assistance and other functions is established. The method for designing a land planning database is discussed in detail, and the functions and specific operation instructions of each module are described. In addition, a geographic information system (GIS) is introduced into the map platform with JSP API interface. The database platform of urban land planning in this paper can be used in land planning and management and the corresponding data analysis.

The contributions of this study are that: (1) An urban land planning database is designed based on MySQL; (2) The conceptual model of the urban land planning database is proposed; (3) Based on the design of a conceptual and logical structure, a management platform with data management, information sharing, map assistance and other functions is established.

2  The Method to Develop Land Planning Database

2.1 Design of the Database Based on MySQL

Conceptual structure is a key step in the entire process of designing the database. Conceptual structure design uses abstraction mechanisms to organize the data obtained in the stage of requirements analysis. The entities, the related attributes of entities, and the connections between entities are formed [20]. The entities, attributes and connections of the database in this paper are discussed as follows.

In the database, the entities are City, Land Location, District and Planned Uses. Attributes are the characteristics of an entity. An entity can be described by several attributes. For example, the attributes of Land location entity include land characteristics, location effects, and economic and political indices.

The land characteristic index includes longitude, latitude, city, construction land area (m2), land location, transfer method, degree of development, detailed planning, planned construction area (m2), floor area ratio, transaction date, transfer date, transaction price (10 thousand Yuan), etc. The location effect index includes the distances from a piece of land to the important points, such as transportation hub stations (m), the urban landmark buildings (m), the nearest industrial parks (m), the nearest key schools (m), the nearest tertiary hospitals (m), the nearest parks (m), the nearest subway stations (m) and the nearest streets, and the value which shows whether the district is the central district, suburban or county. The economic index includes CPI of the city, land grade of the district, permanent population (10 thousand people), residential housing area (hm2), area of houses in buildings more than eight floors (hm2), GDP of the district (100 million Yuan), government deficit (100 million Yuan), foreign direct investment (100 million Yuan), number of unemployment, employees of industrial enterprises above designated size (10 thousand people), the total output value of industrial enterprises above designated size (100 million Yuan), the main business income of industrial enterprises above designated size (100 million Yuan), etc. The political index includes the tenure of the district chief executive, the tenure of the district party secretary, etc.

Based on the conceptual structure, the logical structure of the database is designed to convert the E-R diagram of the entity class to be consistent with the data model of MySQL. When converting the conceptual structure to the logical structure, the relationship between entities should be transformed into the corresponding model [21]. In this paper, a hierarchical model is proposed to form the logic structure. The E-R conceptual model of the urban land planning database is shown in Fig. 1.

Figure 1: The E-R conceptual model of urban land planning database

According to the relationship between the above entities, a database table is designed. The primary key is defined for each entity to set the primary attribute as a non-null value, and the corresponding foreign key should also be defined [17]. The database developed in this paper is named ‘db_land’, then various data tables are created in the database. In order to facilitate the modification and expansion of the table, after the urban districts and counties table is established, the user information management table, land planning use table, land characteristic index table, location effect index table, economic index table and political index table are established, respectively.

In the database, an efficient and achievable physical data structure should be designed [22]. The data storage structure, data access path, reasonable data storage location and storage allocation are developed. And the relational database developed by MySQL is used to design the urban land DBMS by organizing the data in different tables in the system.

2.2 Design of the DBMS

The DBMS is designed for managing urban district-level data. It is the core of the urban land planning database. The webpage of the DBMS sends requests to the Controller. The Service Layer is used to analyze the coding logic. The Logic Layer transmits a requirement to the Persistence Layer (Mybatis), which interacts with the data tables in MySQL. Then the results are returned to the Service Layer. Finally, the Service Layer sends the processing logic to the Controller, which is ultimately controlled by the View Layer to show the data. Fig. 2 shows the structure of the DBMS. Based on the DBMS, it is convenient to output data files in the formats, such as XLS, XLSX, CSV and HTML, so that the data information can be analyzed and used further [23].

Figure 2: The structure of the DBMS

A database management system should meet actual needs and provide an efficient and convenient method for data input and output. Users can apply the function of the fuzzy query to accurately find the specified land data. After logging into the database, users can input the data which does not exist in the database, and after being checked and approved by the administrator, the data can be permanently stored in the database system. The system provides a module for data editing, and users can view and modify various data in the window of the module. The map platform in the system is to introduce GIS functions to obtain the coordinate position, passenger travel time and distance, map marks of comprehensive hub stations in rail lines, point of interest (POI) notes, road congestion index and air quality index.

This system is developed by using SpringBoot and SSM patterns. The Web applications are developed based on the traditional JavaWeb frameworks, such as SSH, SSM, SpringMVC and Hibernate, which have the advantages of low development cost, short development cycle, easy expansion and post-maintenance. But a lot of configurations need to be prepared. Especially, in the early stage of software development, a large number of XML configuration files need to be written. These configurations seriously affect the efficiency of writing codes. The SpringBoot framework can be used to solve these problems easily. By using the concept of ‘convention over configuration’ in SpringBoot, a JavaWeb project is obtained quickly. And it is easy to create a stand-alone project of spring framework and simplify the process of configuration [24]. Table 1 is the maven optimized components of SpringBoot and their descriptions.

3  Modules of the Urban Land Planning DBMS

In this section, 7 functional modules of the urban land planning DBMS are presented. These modules are the user registration and login module, the user information management module, the land use category management module, the urban district and county management module, the Shanghai land data module, the map platform and the data audit management module.

3.1 User Registration and Login Module

One of the basic purposes of designing a database platform is to help users to share data [25]. After receiving the login information, the platform will search the information table of the administrator, and judge whether the user name and the corresponding password are correct. If the information is detected correctly, users can log in to the system and access the permissions assigned by the system administrators.

The function of user login and registration is defined in the BaseAdminController, UserController and UserService class. The AccountController class is mainly for data parsing and page redirecting. User registration and login are handled by the UserService class by defining ‘register ( )’ and ‘login ( )’. The identities of users are divided into administrators and common users. The rights of administrators include adding, deleting, modifying the information of the data, maintaining the system and reviewing the application items of common users. The rights of common users include browsing, querying, outputting, printing the information of the land data and applying to the administrator for adding data or files. Fig. 3 shows the flow chart of user management, and Fig. 4 shows the interface of user login and registration.

Figure 3: Flow chart of user management

Figure 4: Interface of user login and registration

3.2 User Information Management Module

The interface of the user information management module shows the user’s login name, e-mail, user nickname, user name, user type, gender, date of birth, mobile phone number, user status and other information. In the user table of SQLyog, the corresponding contents are shown in the ID, Tenant ID, User Name, Password, Email, Nick Name, Real Name, Sex, Mobile, Birthday, Identity Flag and Status. For new users, after inputting the name and other information in the ‘Add’ pop-up window, the information of the new users will be listed in the user information management module.

The main classes involved in the user information management module include the control layer class ‘UserController’, the business logic classes ‘UserService’ and ‘UserserviceImpl’, and the persistent object class ‘UserMapper’. Fig. 5 shows the interface of user information management.

Figure 5: Interface of user information management module

3.3 Land Use Category Management Module

Land use types include industrial land, commercial/office land, residential land, and comprehensive land. By double-clicking each piece of data, users can open a pop-up window to browse the information of the number and remarks of land use. By clicking the button ‘Add’, the planned utilization route can be added. The button ‘Modify’ is to modify the information, and the button ‘Delete’ is to delete the selected data.

The function of fuzzy query is added to each module. By inputting the classification number, name or remarks of the planning route in the search box, a fuzzy query of the required information and data can be obtained, and the shared information that meets the query conditions will be displayed. By default, all shared information in the last three months is stored in this module. The QueryOperator( ) is the function to obtain a fuzzy query and return a list of the corresponding information. Fig. 6 shows the interface of the land use category management module.

Figure 6: Interface of the land use category management module

3.4 Urban District and County Management Module

The urban district and county management module currently have two sub-modules: Shanghai land data and land data of other cities. In this module, the data information of city number, city name, county name, year of data and description can be added, deleted or modified by the administrators. Because of the reform of the land system and the merger of urban districts, when adding or inputting land data information, it is necessary to indicate the applicable year of the data. If the information needs to be explained, the corresponding description can be added in the Description column. By clicking the button ‘Add’, one can add the city level. By clicking the button ‘Modify’, one can modify the information. And by clicking the button ‘Delete’, one can delete the selected data. Fig. 7 shows the interface of urban district and county management module. And Fig. 8 shows the interface of city districts and counties modification.

Figure 7: Interface of the urban district and county management module

Figure 8: Interface of urban district and county modification

3.5 Shanghai Land Data Module

The land data module is the core of the urban land planning DBMS. According to the land use types, 4 sub-modules are contained in this module. In the city and county folders on the left side of the window, it is convenient for users to find the corresponding land data according to the districts in Shanghai. The administrator can manage the land data information of various indicators by adding, modifying, deleting or outputting the data.

By double-clicking the data in the module, the specific information of the selected data can be browsed. By clicking the button ‘Add’, the data of land planning can be added. By clicking ‘Modify’, the information can be modified. And by clicking the button ‘Delete’, the selected data can be deleted. For adding data, data tables in XLS, CSV and other formats can be input in batches, and the system will automatically transmit and input large amounts of data according to the headers of the tables. The data can be fuzzy queried according to the guidance, which is convenient for users to quickly locate and obtain the required data. The city and county folders on the left side of the interface classify all land data at the district level, which helps users output the data in batches. During the development of the platform, the LandDataController class is mainly to perform the functions of land data management.

Figs. 9 and 10 show the interfaces of land characteristic index management and the detailed data information in the land characteristics index module, respectively. Figs. 11 and 12 show the interfaces of location effect index management and the detailed data information in the location effect index module, respectively. Figs. 13 and 14 show the interfaces of economic index management and the detailed data information in the economic index module, respectively. And Figs. 15 and 16 show the interfaces of political index management and the detailed data information in the political index module, respectively.

Figure 9: Interface of Shanghai land data (land characteristic index) management

Figure 10: Detailed data information in the land characteristics index module

Figure 11: Interface of Shanghai land data (location effect index) management

Figure 12: Detailed data information in the location effect index module

Figure 13: Interface of Shanghai land data (economic index) management

Figure 14: Detailed data information in the economic index module

Figure 15: Interface of Shanghai land data (political index) management

Figure 16: Detailed data information in the political index module

3.6 Map Platform

The technology of the application programming interface (API) is used in the map platform. By using the GIS function in the platform, the land locations, nearby transportation hubs and other important points can be obtained conveniently according to the corresponding coordinates. And the distances between the land locations to any important point can be obtained. It can also help users calculate the travel time and distance, mark the POI of important points and obtain the information about the traffic jam [26,27]. Fig. 17 shows the interface of map platform. And Fig. 18 presents the functions to compute the time and distance for passenger travel.

Figure 17: Interface of the map platform

Figure 18: The functions to compute the time and distance for passenger travel

3.7 Data Audit Management Module

In the process of inputting the data into the system, by using the audit function, the administrator can check the new data and table information. Functional operations, such as batch query, single query, batch review, single review and revocation of review, can be performed. For the data information which is rejected or returned, users will be prompted to revise and resubmit it for review. In the platform of Eclipse, the audit function is in the LandDataController class.

During the process of sending packets, the user id will be identified first. If it is identified as the administrator id, the audit process will be accessed. Fig. 19 shows the interface of the administrator audit management.

Figure 19: Interface of administrator audit management

4  System Performance Test, Results and Analysis

Performance test can analyze the bottleneck of the system, optimize the system, predict the future performance and expand the capacity [28,29]. There are a lot of performance test softwares, such as JMeter, kylinTOP, LoadRunner, kylinPET, NeoLoad, WebLOAD and Loadster. JMeter is an open-source pure Java desktop to test the software system. It can provide more approaches for performance test, and can save a lot of time for script modification. JMeter can be used to test system performance when servers, networks or objects are under different loads.

Fig. 20 shows the schematic diagram of JMeter server system performance test.

Figure 20: Schematic diagram of JMeter performance test

4.1 System Performance Test

The real environment test is named DB_Land test. The human factors, such as user thinking, inputting URL and logging into the homepage, are considered. The number of virtual concurrent users is set to 10 within 5 min. Virtual users can repeat the operations of ‘System login’, ‘Add new user’, ‘Add the cities’, ‘Add land location data information’ and ‘Fuzzy query’. Table 2 is the report of DB_Land test case, and Fig. 21 shows the corresponding response time.

Figure 21: Event response time for DB_Land test case

4.2 Results and Analysis

From the results of the system performance test, when the number of concurrent users is 10, the test machine sends a JMeter request, and the performance shows that ‘System login’ throughput is 10.3 tps with the average response time is 0.077 s; ‘Add new user’ throughput is 49.4 tps with an average response time of 0.003 s; ‘Add the cities’ throughput is 49.4 tps with an average response time of 0.019 s; ‘Add land location data information’ throughput is 67.9 tps with an average response time of 0.032 s; and ‘Fuzzy query’ throughput is 20.7 tps with the average response time of 0.145 s. In this case, the CPU usage of the server is 60%∼75%. At the same time, the error rate of page requests is 0.00%, and the throughput can meet the requirements of larger-scale systems. The browser and the server run correctly without causing a heavy load on the server.

In terms of the stress test, if setting the number of concurrent users to 10, the response speed of the system is fast. Then the database system completely meets the requirements of users.

The performance index Apdex is an international standard, which is a quantitative value of users’ satisfaction. From the page analysis report, it can be seen that 97% of users are satisfied with the system’s performance.

5  Conclusions

In this paper, an urban land planning database is developed based on MySQL. The conceptual model of the urban land planning database is proposed. Based on the design of the conceptual and logical structure, the DBMS with data management, information sharing, map assistance and other functions is obtained.

The system can manage the urban land data and the related database functions. Various types of data, such as land characteristic index, location effect index, economic index and political index, are included. The system contains the data information on the district level in Shanghai from 2004 to 2015. The corresponding data after 2015 will be added. The land information and socio-economic data are large, but the corresponding data can be obtained easily by using the function of fuzzy query.

The land planning platform developed in this paper provides powerful support for the government to manage and plan urban land. It helps the government to formulate reasonable and effective land policies to promote land use and sustainable development of a city. In addition, this land planning platform can provide references for other cities and counties to develop similar databases.

Funding Statement: This research was funded by Start-Up Funds for Scientific Research of Shenzhen University, Grant No. 000002112313.

Conflicts of Interest: The authors declare that they have no conflicts of interest to report regarding the present study.

References

 1.  Cheng, J. (2020). Analyzing the factors influencing the choice of the government on leasing different types of land uses: Evidence from Shanghai of China. Land Use Policy, 90, 104303. DOI 10.1016/j.landusepol.2019.104303. [Google Scholar] [CrossRef]

 2.  Cheng, J. (2021). Analysis of commercial land leasing of the district governments of Beijing in China. Land Use Policy, 100, 104881. DOI 10.1016/j.landusepol.2020.104881. [Google Scholar] [CrossRef]

 3.  Cheng, J., Luo, X. W. (2022). Analyzing the land leasing behavior of the government of Beijing, China, via the multinomial logit model. Land, 11, 376. DOI 10.3390/land11030376. [Google Scholar] [CrossRef]

 4.  Cheng, J., Xie, Y., Zhang, J. (2022). Analyzing the urban hierarchical structure based on multiple indicators of economy and industry: An econometric study in China. Computer Modelling in Engineering & Sciences, 131(3), 1831–1855. DOI 10.32604/cmes.2022.020178. [Google Scholar] [CrossRef]

 5.  Cheng, J., Xie, Y., Zhang, J. (2022). Industry structure optimization via the complex network of industry space: A case study of Jiangxi Province in China. Journal of Cleaner Production, 338, 130602. DOI 10.1016/j.jclepro.2022.130602. [Google Scholar] [CrossRef]

 6.  Hallett, S. H., Sakrabani, R., Keay, C. A., Hannam, J. A. (2017). Developments in land information systems: Examples demonstrating land resource management capabilities and options. Soil Use and Management, 33(4), 514–529. DOI 10.1111/sum.12380. [Google Scholar] [CrossRef]

 7.  Fang, C. G., Chen, X. W., Xu, Z., Huang, C. Y. (2015). Study on “one Map” organizational model for land resources data used in supervision and service. The Open Cybernetics & Systemics Journal, 9(1), 105–109. DOI 10.2174/1874110X01509010105. [Google Scholar] [CrossRef]

 8.  Li, D., Peng, M., Shao, Z. (2006). Design and implementation of urban management and service grid based on spatial database. Geomatics and Information Science of Wuhan University, 31(6), 471–475. [Google Scholar]

 9.  Lin, S., Xu, J., Li, Z., Yang, Y. (2010). Design and implementation of Lujiazui land management information system based on WebGIS. 2010 International Conference on E-Business and E-Government, pp. 667–670. Guangzhou, China. [Google Scholar]

10. Yan, S., Li, M., Wang, Y., Ma, X., Zhang, J. et al. (2012). Database construction of general land use plan: A case study of Changzhou National Hi-Tech District (CND). 2012 20th International Conference on Geoinformatics, pp. 1–6. Hong Kong, China. [Google Scholar]

11. Li, K., Zhou, W. T., Wang, T. J., He, W., Shi, H. H. (2020). Research on spatial database technology based on Arcsde. 2020 IEEE International Conference on Power, Intelligent Computing and Systems (ICPICS), pp. 523–525. Shenyang, China. [Google Scholar]

12. McLaughlin, J., Masry, S. (1982). Multipurpose land information systems: A Canadian perspective. Journal of the Surveying and Mapping Division, 108(1), 1–9. DOI 10.1061/JSUEAX.0000559. [Google Scholar] [CrossRef]

13. Denton, J. W., Peace, A. G. (2003). Selection and use of MySQL in a database management course. Journal of Information Systems Education, 14(4), 401–408. [Google Scholar]

14. Gao, F., Zhang, C. T. (2007). DoriC: A database of oriC regions in bacterial genomes. Bioinformatics, 23(14), 1866–1867. DOI 10.1093/bioinformatics/btm255. [Google Scholar] [CrossRef]

15. Yu, X., Yi, C. (2010). Design and implementation of the website based on PHP & MYSQL. 2010 International Conference on E-Product E-Service and E-Entertainment, pp. 1–4. Henan, China. [Google Scholar]

16. Epifania, F., di Milano, P. (2011). Design and development of multimedia interactive systems for digital learning. In: DMS, pp. 238–241. USA: Knowledge Systems Institute Graduate School Press. [Google Scholar]

17. Bravo, Z., Maron, K. (2018). Aplicación de un sistema de gestión documental (DMS) para almacenamiento histórico de archivos de predios urbanos del GAD municipal de Isidro Ayora en el 2018. http://repositorio.ug.edu.ec/handle/redug/36743. [Google Scholar]

18. Peng, Z., Liu, T., Mai, L. (2020). Design and implementation of dormitory management system based on SSM framework. 2020 International Conference on Information Science, Parallel and Distributed Systems (ISPDS), pp. 321–325. Xi’an, China. [Google Scholar]

19. Cheng, J. (2021). Mathematical models and data analysis of residential land leasing behavior of district governments of Beijing in China. Mathematics, 9, 2314. DOI 10.3390/math9182314. [Google Scholar] [CrossRef]

20. Zaitsev, P., Tkachenko, V. (2012). High performance MySQL: Optimization, backups, and replication. USA: OReilly Media Press. [Google Scholar]

21. Banos, O., Villalonga, C., Garcia, R., Saez, A., Damas, M. et al. (2015). Design, implementation and validation of a novel open framework for agile development of mobile health applications. Biomedical Engineering Online, 14(2), 1–20. DOI 10.1186/1475-925X-14-S2-S6. [Google Scholar] [CrossRef]

22. Ren, Y., Ma, Y. (2016). Database design of security CT center after-sales service system based on MYSQL. 3rd International Conference on Management, Education Technology and Sports Science (METSS 2016), pp. 535–539. USA, Atlantis Press. [Google Scholar]

23. Panyayai, T., Ngamphiw, C., Tongsima, S., Mhuantong, W., Limsripraphan, W. et al. (2019). FeptideDB: A web application for new bioactive peptides from food protein. Heliyon, 5(7), e02076. DOI 10.1016/j.heliyon.2019.e02076. [Google Scholar] [CrossRef]

24. Wang, J., Wang, W. L., Lang, B., Liu, Y. G., Zhang, Y. et al. (2020). New design and implementation of power distribution network management system. 15th IEEE Conference on Industrial Electronics and Applications (ICIEA), pp. 850–854. Kristiansand. [Google Scholar]

25. Fox, J., Warber, K. M. (2015). Queer identity management and political self-expression on social networking sites: A co-cultural approach to the spiral of silence. Journal of Communication, 65(1), 79–100. DOI 10.1111/jcom.12137. [Google Scholar] [CrossRef]

26. Xue, Y., Li, C. (2020). Extracting Chinese geographic data from Baidu Map API. The Stata Journal, 20(4), 805–811. DOI 10.1177/1536867X20976313. [Google Scholar] [CrossRef]

27. Liu, M., Wang, J., Wang, J., Tang, G., Sun, M. (2018). Design and implementation of emergency and evacuation system based on Baidu Map API. 2018 26th International Conference on Geoinformatics, pp. 1–5. Kunming, China. [Google Scholar]

28. Erinle, B. (2017). Performance testing with JMeter 3. UK: Packt Press. [Google Scholar]

29. Wang, J., Wu, J. (2019). Research on performance automation testing technology based on JMeter. 2019 International Conference on Robots & Intelligent System (ICRIS), pp. 55–58. Haikou, China. [Google Scholar]