Spatial database applications for network analysis: Case study of bicycle accessibility of forested areas in the Poznań Metropolitan Area, Poland

Kostecki, Robert

doi:10.2478/rgg-2024-0014

Abstract

The main aim of this paper is to introduce a solution for network analysis based on pgRouting to resolve the bicycle accessibility of forested green spaces. The proposed application uses open-source software tools such as PostgreSQL with PostGIS extension. The solution includes a complete description of how to perform network analysis using a spatial database with SQL and pgRouting. The implemented functionalities consist of solutions for finding the equidistance or isochrone area for any selected point location. The method is tested on case study data drawn from a total of 9,500 km of roads suitable for cyclists in the Poznań Metropolitan Area, located in western Poland. The results of the analysis were isochrones determining the bicycle accessibility of forested areas. The accessibility analysis was performed considering an urbanised residential area. As a result of the analysis, locations with the best and limited access to forested green areas were identified. Moreover, the described methodology is ready to be used to solve various accessibility problems.

References

Achuthan, K., Titheridge, H., and Mackett, R. L. (2010). Mapping accessibility differences for the whole journey and for socially excluded groups of people. Journal of Maps, 6(1):220–229, doi:10.4113/jom.2010.1077.
Search in Google Scholar Back to article
Afnarius, S., Akbar, F., Hasanah, Z., Putra, H. Y., et al. (2023). Development of GIS-based Rumah Gadang Tools as a tourism data integrator for Halal Tourist Villages in West Sumatra, Indonesia. International Journal on Advanced Science, Engineering & Information Technology, 13(1):34–41, doi:10.18517/ijaseit.13.1.16714.
Search in Google Scholar Back to article
Akay, A. E. and Tas, I. (2020). Using GIS techniques for assessment of accessible forest lands by firefighting teams considering fire risk degrees. European Journal of Forest Engineering, 6(2):87–95, doi:10.33904/ejfe.843889.
Search in Google Scholar Back to article
Almohamad, H., Knaack, A. L., and Habib, B. M. (2018). Assessing spatial equity and accessibility of public green spaces in Aleppo City, Syria. Forests, 9(11):706, doi:10.3390/f9110706.
Search in Google Scholar Back to article
Amaguaya, F. R. O. and Hernández, J. R. H. (2020). Improvement of Public Transport Routes with ArcGIS Network Analyst. Case Study: Urban Center of Milagro, Ecuador, pages 31–36. Springer International Publishing, doi:10.1007/978-3-030-51566-9_5.
Search in Google Scholar Back to article
Botha, T. L., Bamuza-Pemu, E., Roopnarain, A., Ncube, Z., De Nysschen, G., Ndaba, B., Mokgalaka, N., Bello-Akinosho, M., Adeleke, R., Mushwana, A., van der Laan, M., Mphahlele, P., Vilakazi, F., Jaca, P., and Ubomba-Jaswa, E. (2023). Development of a GIS-based knowledge hub for contaminants of emerging concern in South African water resources using open-source software: Lessons learnt. Heliyon, 9(1):e13007, doi:10.1016/j.heliyon.2023.e13007.
Search in Google Scholar Back to article
Brinkhoff, T. and Kresse, W. (2012). Springer Handbook of Geographic Information. Springer Berlin Heidelberg, doi:10.1007/978-3-540-72680-7.
Search in Google Scholar Back to article
Campos-Sánchez, F. S., Valenzuela-Montes, L. M., and Abarca-Álvarez, F. J. (2019). Evidence of green areas, cycle infrastructure and attractive destinations working together in development on urban cycling. Sustainability, 11(17):4730, doi:10.3390/su11174730.
Search in Google Scholar Back to article
Choosumrong, S., Humhong, C., Raghavan, V., and Fenoy, G. (2019). Development of optimal routing service for emergency scenarios using pgRouting and FOSS4G. Spatial Information Research, 27(4):465–474, doi:10.1007/s41324-019-00248-2.
Search in Google Scholar Back to article
Comber, A., Brunsdon, C., and Green, E. (2008). Using a GIS-based network analysis to determine urban greenspace accessibility for different ethnic and religious groups. Landscape and Urban Planning, 86(1):103–114, doi:10.1016/j.landurbplan.2008.01.002.
Search in Google Scholar Back to article
Cyril, A., Mulangi, R. H., and George, V. (2019). Development of a GIS-based composite public transport accessibility index. Journal of Urban and Environmental Engineering, pages 235–245, doi:10.4090/juee.2019.v13n2.235245.
Search in Google Scholar Back to article
Duarte, L., Teodoro, A. C., Lobo, M., Viana, J., Pinheiro, V., and Freitas, A. (2021). An open source GIS application for spatial assessment of health care quality indicators. ISPRS International Journal of Geo-Information, 10(4):264, doi:10.3390/ijgi10040264.
Search in Google Scholar Back to article
Escobar, D. and Garcia, F. (2012). Territorial accessibility analysis as a key variable for diagnosis of urban mobility: A case study Manizales (Colombia). Procedia - Social and Behavioral Sciences, 48:1385–1394, doi:10.1016/j.sbspro.2012.06.1114.
Search in Google Scholar Back to article
Ford, A., Barr, S., Dawson, R., and James, P. (2015). Transport accessibility analysis using GIS: Assessing sustainable transport in London. ISPRS International Journal of Geo-Information, 4(1):124–149, doi:10.3390/ijgi4010124.
Search in Google Scholar Back to article
Janeczko, E., Wójcik, R., Kędziora, W., Janeczko, K., and Woźnicka, M. (2019). Organised physical activity in the forests of the Warsaw and Tricity agglomerations, Poland. International Journal of Environmental Research and Public Health, 16(20):3961, doi:10.3390/ijerph16203961.
Search in Google Scholar Back to article
Juba, S., Vannahme, A., and Volkov, A. (2015). Learning PostgreSQL: create, develop, and manage relational databases in real-world applications using PostgreSQL. Packt Publishing Ltd.
Search in Google Scholar Back to article
Kabolizadeh, M., Karimian, R., Rangzan, K., Alizadeh, B., and Maroufi, K. (2023). Development of a geodatabase for retrieval of geochemical data from oil wells: A case study from the Gach-saran oilfield; SW Iran. Geoenergy Science and Engineering, 225:211621, doi:10.1016/j.geoen.2023.211621.
Search in Google Scholar Back to article
Kaczynski, A. T., Potwarka, L. R., and Saelens, B. E. (2008). Association of park size, distance, and features with physical activity in neighborhood parks. American Journal of Public Health, 98(8):1451–1456, doi:10.2105/ajph.2007.129064.
Search in Google Scholar Back to article
Kostecki, R. (2018). Application of the spatial database for shoreline change analysis and visualisation: Example from the Western Polish Coast, Southern Baltic Sea. Quaestiones Geographicae, 37(3):25–34, doi:10.2478/quageo-2018-0023.
Search in Google Scholar Back to article
Krzywnicka, I. and Jankowska, P. (2021). Accessibility of public urban green space. A case study of Białystok city. Acta Scientiarum Polonorum Administratio Locorum, 20(3):203–214, doi:10.31648/aspal.6794.
Search in Google Scholar Back to article
Luo, W. (2004). Using a GIS-based floating catchment method to assess areas with shortage of physicians. Health and Place, 10(1):1–11, doi:10.1016/s1353-8292(02)00067-9.
Search in Google Scholar Back to article
Maas, J., Verheij, R. A., Spreeuwenberg, P., and Groenewegen, P. P. (2008). Physical activity as a possible mechanism behind the relationship between green space and health: A multilevel analysis. BMC Public Health, 8(1), doi:10.1186/1471-2458-8-206.
Search in Google Scholar Back to article
Merciu, C., Stoian, D., Merciu, G., and Saghin, I. (2013). Using GIS for calculating the accessibility to hospitals in the city of Bucharest and its metropolitan area (Romania). Geographica Pannonica, 17(4):106–113.
Search in Google Scholar Back to article
Neuvonen, M., Sievänen, T., Tönnes, S., and Koskela, T. (2007). Access to green areas and the frequency of visits – A case study in Helsinki. Urban Forestry and Urban Greening, 6(4):235–247, doi:10.1016/j.ufug.2007.05.003.
Search in Google Scholar Back to article
Obe, R. O. and Hsu, L. S. (2021). PostGIS in Action, Third Edition. Manning Publications.
Search in Google Scholar Back to article
Obe, R. O., Hsu, L. S., and Sherman, G. E. (2017). pgRouting: A practical guide. Locate Press.
Search in Google Scholar Back to article
O’Sullivan, D., Morrison, A., and Shearer, J. (2000). Using desktop GIS for the investigation of accessibility by public transport: An isochrone approach. International Journal of Geographical Information Science, 14(1):85–104, doi:10.1080/136588100240976.
Search in Google Scholar Back to article
pgRouting (2024). pgRouting Documentation. https://docs.pgrouting.org/.
Search in Google Scholar Back to article
PostGIS (2024). PostGIS Documentation. https://postgis.net/documentation/.
Search in Google Scholar Back to article
PostgreSQL (2024). PostgreSQL Documentation. https://www.postgresql.org/docs/.
Search in Google Scholar Back to article
Połom, M., BEGER, M., and Topa, E. (2017). Research of the walking availability and the public transport to urban parks on the example of GdaŃsk. Studia Miejskie, (27):25–38, doi:10.25167/sm2017.027.02.
Search in Google Scholar Back to article
Sarmah, S., Yadav, R., and Rathore, P. (2019). Development of vehicle routing model in urban solid waste management system under periodic variation: A case study. IFAC-PapersOnLine, 52(13):1961–1965, doi:10.1016/j.ifacol.2019.11.490.
Search in Google Scholar Back to article
Vías, J., Rolland, J., Gómez, M. L., Ocaña, C., and Luque, A. (2018). Recommendation system to determine suitable and viable hiking routes: A prototype application in Sierra de las Nieves Nature Reserve (southern Spain). Journal of Geographical Systems, 20(3):275–294, doi:10.1007/s10109-018-0271-8.
Search in Google Scholar Back to article
Wei, X., Zhao, C., Yan, L., Fu, J., Bao, Y., and Liu, X. (2022). Spatial accessibility analysis of green space from a health-benefit perspective: Implications for healthy urban development. Frontiers in Ecology and Evolution, 10, doi:10.3389/fevo.2022.1083563.
Search in Google Scholar Back to article
Widaningrum, D. L. (2015). A GIS-based approach for catchment area analysis of convenience store. Procedia Computer Science, 72:511–518, doi:10.1016/j.procs.2015.12.158.
Search in Google Scholar Back to article
Widyantoro, B. A. and Santosa, P. B. (2021). Network analysis to determine the optimal route for firefighters in Makassar City. IOP Conference Series: Earth and Environmental Science, 936(1):012005, doi:10.1088/1755-1315/936/1/012005.
Search in Google Scholar Back to article
Wysmułek, J., Hełdak, M., and Kucher, A. (2020). The analysis of green areas’ accessibility in comparison with statistical data in Poland. International Journal of Environmental Research and Public Health, 17(12):4492, doi:10.3390/ijerph17124492.
Search in Google Scholar Back to article

Spatial database applications for network analysis: Case study of bicycle accessibility of forested areas in the Poznań Metropolitan Area, Poland

Abstract

Paradigm

My account