Development of Spatial Database for Regional Development in Romania

Geographical Information Systems are used in solving many regional development related problems, around the world. Starting from some national programs to famous international ones, such as INSPIRE program, each such initiative uses geospatial data as well in the process of building regional development strategies. This paper presents the main technical components of a geographical information system, meaning the spatial database, the web mapping server and the APIs used to embed the maps into web applications. The development steps for a pre-alpha version of a web GIS application dedicated to the regional development in Romania are also shown. The software tools which were integrated in order to develop the online application were Oracle Spatial, where geospatial data was stored, GeoServer, an open source web mapping server used to generate the map out of the data from Oracle Spatial’s tables and ASP.NET as a web framework for building the website.


Introduction
In the new knowledge based economy, the information systems for data handling, and for knowledge discovery became a "must have" for making decisions and elaborate development strategies in both public and private sectors.Among the classical data types, the geospatial data became very interesting for the governmental decision factors, especially after the launch of the INSPIRE (Infrastructure for Spatial Information in the European Community) directive of the European Parliament in 2007.This kind of data shows "where" certain objects are placed on the surface of the Earth, from the pipes of a city to the best possible location to open a new business.The Geographical Information Systems (GIS) have the technical capacity of storing, manipulating and visualizing the geospatial data, together with the descriptive/economic data.GIS is used for example in urban and regional development by creating different planning maps [1], in utilities management (electricity, gas, water) [2], when choosing the best locations for a certain investment ( [3], [4]), in the environment studies [5], in assuring national health programs ( [6], [7], [8]).The essential role of geospatial information in pressing issues such as: land cover, access to water, food production and distribution, biodiversity, is the main topic in many conferences in this field such as AGIT symposium "Geoinformation for Development (gi4dev)" held in 2006 in Austria.This paper discusses the use of GIS tools in regional development and details the GIS architecture by analyzing its main software components: spatial databases, web mapping servers and the application programming interfaces (APIs) used to embed maps in web applications.In the last chapter we propose system architecture for building an online GIS for regional development in Romania.The application will offer the possibility of maintaining both spatial data and economical/statistical data regarding different variables of interest in different counties and to visualize statistical thematic maps which could be the basis of the decision making process.
2 Regional Development and GIS Regional development's aim is to offer assistance and to modernize regions which are less economically developed than others, 1 at international or domestic level.In order to comply with EU regional policy, in Romania there were identified eight regions which are administrated by the nongovernmental, nonprofit organizations called Regional Development Agencies (ADR) which are functioning in compliance to the Law no.315/2004 on the regional development of Romania.The purposes of the Regional Development Agencies are detailed in paper [9] but all the tasks could be included into the main one and that is the creation of regional development plans.A regional development plan starts with an analysis of the existing situation in a certain region by investigating the natural resources, infrastructure, physical capital, human capital, knowledge capital and the productive capital of the region.All this information (descriptive, statistical, geospatial) could be successfully integrated into a geographical information system.The GIS system could not only show the data on the maps, but it can also make complex statistical reports based both on economical and spatial / topological information.The advantages and disadvantages of using the GIS technology in regional development are synthesized in [10] as shown in Table 1.

Advantages Disadvantages
 Through GIS, one can make the connection between a regional element and its location;  The maps offer objectivity when showing the geographical information;  Maps could be generated for every geographical problem (where data is available);  The visualization of the data through a geographical context;  The creation of the spatial data and inclusion of the non-spatial data as well;  The possibility of the geo-statistical analysis of the information;  Finding simple solutions for complex issues;  One can build forecasts, predictions, trends of a phenomenon in time and space;  Increase the level of detailed data;  Making decision process is improved;  Facilitates the communication between the stakeholders in regional development;

GIS Software Tools
Open Geospatial Consortium (OGC) is a nonprofit organization who develops publicly available interface standards in order to enable geo-processing technologies to interoperate (www.opengeospatial.org).OGC provides specifications for both geospatial data formats and for GIS software tools.According to these standards, there are the following types of encoding geospatial data:  XML-based encodings: Geography Markup Language (GML) for transport and storage, Keyhole Markup Language (KML) which encodes both what to show and how to show in an Earth browser, Scalable Vector Graphics (SVG) for specifying how to visualize vector geospatial data and others;  encodings for raster geospatial data: Joint Photographic Experts Group (JPEG) -a .jgw(world file) has to be added in order to geo-reference an image, Geo Tagged Image File Format (GeoTIFF);  Well Known Binary (WKB) and Well Known Text (WKT) representationsthe Well Known Binary Representation for Geometry (WKBGeometry) provides a portable representation of a geometric object as a continuous stream of bytes.It permits geometric object to be exchanged between an SQL/CLI client and an SQLimplementation in binary form.

1. Spatial Data Representation
One way to represent spatial data is exposed in [20], where spatial data is modeled with constraints.
Bi-dimensional spatial data are primarily represented by points (x, y) or set of points (xi, yi).These set of points can be infinite, which imposes the problem of storing this kind of data in a database.For solving this situation, we must define the finite representation of infinite relations.
In a relational database table, the rows must be seen as simple logical formulas.For example, for a finite set of four points (x, y), we have the expression: In the database table the points look like in table 2. Using predicates like <= and arithmetic operators (+, *), complex spatial data (with infinite points, for example, x {1, 1.001, 1.002 ...}, y {3, 3.001, 3.002 ...}) can be represented.For a polygon, the constraints can be: (y <= x) ∧ (x<=7) ∧ (y>=1) ∧ (x+3y-16<=0) By using these four constraints, a polygon (considered as infinite spatial data) was represented in a finite way.Basically, all these constraints have a standard definition, which is αx+βy+γ<=0, and must be logically intersected through the "and" operator (∧).This works only for spatial convex objects.A non-convex object can be vectorial represented like in the following example: N= <[4,3], [6,5], [8,2], [10,5]> which will be represented using constraints and logical disjunction (∨): After considering these examples, we can rank the representational level of spatial data, thus:  abstract level, which can be finite or infinite (when having a multitude of points that compose an object);  symbolic level, which allows a finite representation of infinite relations;  physic level, which allows storing relations in a database and querying the data.Therefore, no matter the nature of the spatial data (finite or infinite), we showed that, at symbolic level, data can be processed and brought to a finite representation, in order to be properly stored and analyzed through a Database Management System.

2. Spatial databases
The spatial databases are a recent development in the databases software.They are extensions of the classical databases which contain special objects, procedures, mechanisms in order to store and manipulate geospatial data.We will further make an inter-comparison between the two most known spatial databases: and the result is: "GEOGCS["WGS84",DATUM["WGS_1984",SPHE ROID["WGS84",6378137,298.257223563,AUTHO RITY["EPSG","7030"]],TOWGS84[0,0,0,0,0,0 ,0],AUTHORITY["EPSG","6326"]],PRIMEM["Gr eenwich",0,AUTHORITY["EPSG","8901"]],UNI T["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326" ]]" The data types used in PostGIS database are: POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, and MULTIPOLYGON.Both databases Oracle Spatial and PostGIS offer the possibility for mass upload of the data from shapefiles (world's most famous commercial GIS company, ESRI's proprietary file format storing geospatial data), by providing special tools such as: shp2sdo (Oracle -Figure 1) or shp2pgsql (PostGIS).Among the usual database constraints (primary keys, foreign keys, semantic constraints, and user defined constraints), the spatial databases are also characterized by the topological consistency relations, which are listed in paper [15] as follows: everything must be bounded by two nodes (start node and end node); for every arc, there exist two polygons (left polygon and right polygon); every polygon has a closed boundary consisting of an alternating sequence of nodes and arcs; around every node, there exists an alternating closed sequence of arcs and polygons; arcs do not intersect except at nodes.RTree indexes are used in both databases for the indexing of geospatial data.This index approximates each geometry with a Minimum Bounding Rectangle (MBR).Create an index on a "geometry" column in Oracle Spatial: GiST (Generalized Search Trees) indexes break up data into "things to one side", "things which overlap", "things which are inside" and can be used on a wide range of data-types, including GIS data.PostGIS uses an R-Tree index implemented on top of GiST to index GIS data (http://postgis.refractions.net/docs/ch04.html#id2638756).Spatial analysis functions are very important feature of these databases and the one that makes a difference between the databases.For example, the most advanced spatial database from this point of view is Oracle Spatial, which offers a special package SDO_SAM with many subprograms which can be used in spatial analysis and data mining.PostGIS does not have a special package with functions to be used in the spatial analysis but it can be done by writing some smart SQL queries using the spatial functions ST_DISTANCE, ST_INTERSECTS, ST_OVERLAPS [16].

Web Mapping Servers
OGC International Standard for web mapping servers, WMS, [17] defines the output generated by a web mapping service as maps of spatially referenced data dynamically from geographic information.In reference work [18], it is specified that the OGC's services are based on a set of standards that are popularly called RESTful web services.This means that all the queries are going to be simple HTTP GET requests; the service will be called using an URL prefix to which additional parameters are appended in order to construct a valid operation request.The structure of WMS request using HTTP GET is described in OGC standard as: http://host[:port]/path[?{name[=value]&}].The reference standard defines three operations for a WMS: mandatory GetCapabilities and GetMap, and optional GetFeatureInfo.
The definition of GetCapabilities operation according with [17] is operation to obtain service metadata, which is a machine readable (and humanreadable) description of the server's information content and acceptable request parameter values.A GetCapability operation is a XML document showing what the server offers.In simple words, the WMS is a bridge between a geospatial data source and a client application showing the map out of the stored data, as it was shown in [19].It is the piece of software that "reads" the data from the spatial data source (database or file), and it "draws" the map of the data in different formats (png, gif, jpeg and others).Table 3 shows a comparative study between two web mapping servers: Oracle MapViewer and GeoServer from different point of views.The standard architecture of GIS is the three layer architecture containing:  the spatial database, core of every GIS, where the geospatial data is stored and maintained;  the web mapping server which generates the map out of the spatial database;  APIs, on client side, to embed the GIS maps into web applications.In order to embed a map generated by a WMS out of a spatial database, into a web application, one must use APIs, which are programs library for map displaying in web browser.APIs are available for specific geospatial servers such as ArcGIS JavaScript API, MSN Virtual Earth APIs, or Google Maps API, and for general geospatial servers, such as OpenLayers JavaScript library which implements industry-standard methods for geographic data access, such as the OpenGIS Consortium's Web Mapping Service (WMS) and Web Feature Service (WFS) protocols.Our goal was to develop a first version of a web GIS application to be used in the optimization of the regional development strategy making process.The main functional requirements for the system were:  maintain the data regarding the statistics macroeconomic indicators (population, labor market, education) of each county;  maintain geospatial information regarding the counties of Romania, the main points of interest, cities and main roads;  visualize thematic statistics maps;  visualize reports with economical and geospatial data needed in the process taking decision.The technical architecture of the new system is integrating a spatial database, Oracle Spatial, where the geospatial data will be stored, a web mapping server, GeoServer, which will generate the map out of the data stored in the Oracle Spatial and a web framework which is used to develop the web application which will embed also the maps generated by GeoServer.First, we have created the conceptual model of the new system by drawing the Entity-Relationship diagram with Oracle Designer software product (Figure 2).

Fig. 2. Entity-Relationship Diagram
This diagram has to be read as follows: the new system will store the geospatial data regarding the counties of Romania (COUNTIES_RO); each county has roads (ROADS_RO) and different points of interest (POI_RO) for which descriptive and geospatial data will be stored as well; for each county there are available a set of macroeconomic indicators describing the economic development of the county, regarding the population (STAT_POP_RO), labor market (STAT_LABOUR_RO), or education level (STAT_EDU_RO).The normalization technique was then used in order to refine the conceptual model and obtaining minimal redundancy database tables.After creating the structure of the database we have loaded the tables with geospatial data regarding the counties, roads and points of interest and also with statistic indicators values; the data sources which were used to load the initial data are listed in Table 4.The next step was to configure GeoServer in order to connect to the Oracle Spatial database which was previously built.Also, there were defined the styles in which the maps will be rendered by writing style layer descriptor (SLD) files.After connecting GeoServer with Oracle Spatial, the web application was developed in ASP.NET who was also connected with both Oracle Spatial using ADO.NET technology and with GeoServer, using the web services.The users have the possibility to upload the macroeconomic data in the database by using the web application (Figure 3).In order to visualize a certain statistic thematic map (Figure 4), it was called the GeoServer "GetMap" operation, from the web application page, as follows: Other web pages show information about the counties area (calculated from the database using sdo_geom.sdo_areaOracle Spatial's function) or the points of interest from a certain county (using a join between COUNTY_RO and POI_RO table and sdo_geom.sdo_relatefunction).

Conclusions
The following software technologies: Oracle Spatial (to store the geospatial data), ASP.NET (to develop web applications) and the open source web mapping server, GeoServer (to generate the maps), could successfully be integrated in order to build a web GIS application, as support for regional development strategies making process.The first version of the developed web application described in chapter 4 was only intended to explore the capacity of using the technologies together in order to visualize in a web page the map generated from the data stored into the "geometry" columns of a spatial database.We further intend to develop the web application, by embedding an interactive map using OpenLayers library, being then able to release an alpha version of the proposed web site.

Fig. 1 .
Fig. 1.Loading a shapefile into the Oracle Spatial database

Table 2
(x, y) points stored in a table

Table 3 .
Comparative study between: Oracle MapViewer and GeoServer

Table 4 .
Input data for the web application prototype