-Open data are has an high economic potential, allowing a transparent and democratized access to data. At the same time, geographic data are playing a fundamental role since it can easily filter pertinent data among the wide multitude of available data, such as finding the nearest wifi networks or the nearest museums. In this work we present a solution to exploit geolocalization on mobile devices in order to find, among the available Open Data, the most relevant data according to the position of the user. Starting from some public datasets, an initial data augmentation has been introduced to include location information whereas it was possible and useful. Then an optimized filtering system was developed for geographic contents through the design of a specific back-end. Index Terms-open-data, geographic information, LBS
In the last decade, the Open Government doctrine has been
spreading more and more founding its cultural model on the
principle according to which all the activities of governments
and state administrations must be open and available in order
to promote effective actions and guarantee widespread control
over the management of public affairs. This movement pushes
a new interaction model between citizen and Public
Administration: from the ”classic” user to a citizen that is directly
involved in the government choices [
Transparency: institutions are required to provide citizens with data and information on decisions taken and on their actions, in order to create a system of trust within the local community towards the work and choices made. Participation: citizen participation in the Public Administration ©2019 for this paper by its authors. Use permitted under Creative Commons License Attribution 4.0 International (CC BY 4.0) choices increases the effectiveness of administrative actions and improves the quality of decisions. Collaboration: the collaboration envisage a direct involvement of citizens in the activities of the Public Administration and tends to include the institutions within a collaborative and participatory network composed of public bodies, non-profit organizations and a community of citizens.
To implement these principles it is therefore necessary that
the P.A. make the widest possible amount of data available
to the public available to it: such data, if released in specific
ways, are called open data (”Open Data”) [
Open Data are data collections (datasets), publicly
accessible, without patents or proprietary licenses that limit their
diffusion or re-use. Open Data is a specific type of Open
content that can be considered its father focused on the spread
of creative works [
To distinguish the different formats that can be used in the
coding of datasets, W3C has proposed a cataloging model that
classifies them based on their characteristics on a scale of
values from 1 (one star) to 5 (five stars) [
As can be easily understood, the latest levels indicate rather high Open Data quality, as these data can be easily integrated.
A problem for the effective explotation of open data is that there are often too many and the downloading and processing in particular on mobile terminals is really expensive. Thus in this work we propose a system architecture to associate positions with the linked open data in order to limit the
Fig. 1. Geolocalized OD system model transfer of information to mobile devices, and increase the effectiveness of the information published by the various administrations
This paper we present a solution to ”exploit” this data in the most useful way for the citizen, introducing the use of geolocalization on mobile devices in order to find, among the available Open Data, those more interesting or those located nearby, such as a museum, a library or a Wifi access point. Starting from some public datasets, a filtering system was then developed for these contents through a specific back-end. Particular attention was paid to the way in which the queries on these datasets are made, in order to improve their efficiency and execution time.
The proposed system architecture is depicted in figure 1. The system cose is the GEO Server that associte the location data elaborating the dataset information. The
It includes a ”client side” that periodically takes care of operating the data augmentation downloadning the files from the open data providers and insertig it in the local database; The”server side” expose the new augmented infromation layer to the clients that can make geolocalized queries on the open database. Each dataset will have its own table - dynamically created based on predefined xml layouts present and each row of these tables contains the latitude and longitude field.
Severl Open Dataset provide the location information in an implict form. Thus to inlcude this data in the augemnted dataset, a geocoding service is used offered. A popular ”free” service is offered by Google: it converts a specific address into geographical coordinates (and vice versa, called reverse Geocoding). Request and response are made on the HTTP protocol and the output is produced in JSON.
A requested example is: https://maps.googleapis.com/maps/api/ geocode/json?address=1600+Amphitheatre +Parkway,+Mountain+View,+CA
This request produces a complex JSON output that can be parsed to acquire the goegraphical coordinates of the data.
Since the GEO Server stores a copy of open datasets augmented with the geographical coordinates, it is possible to query the server to receive the open data filtered on their location (and that of mobile client).
Each request is serverd by an independent thread which operates according to the following life cycle:
Starting from equation 3, we define the basic listing that operates the filterign orperation:
SELECT *, 2 * 6371 * ASIN(SQRT(POWER(
SIN(((lat2-lat1) * pi()/180)/2),2) + COS(psi1 * pi()/180) * COS(psi2 * pi()/180) * POWER(SIN(((long2-long1) * pi()/180)/2),2) )) as distance FROM dataset WHERE distance < range
ORDER BY distance ASC The problem with this query is that we need to compte It process the HTTP request and extract: selected dataset, the distance on every single record in the table to know latitude, longitude, range, limit and offset. whether it falls within the desired range or not, and this is too It perform the geolocated query on the database; computational expensive. To significantly lower the timewe It convert the query results into an JSON file; limit the distance calculation only to records that fall within It set up an appropriate Response Header HTTP; a rectangular area on the earth shere. For this reason, the four It send the generated JSON file to the client; vertices are defined according to the following listing:
In this section we describe the server query methodology used to filter in a efficient way all the reconds that are nearby the requesting client. The query result is the result-set of geolocated records sorted in ascending order based on the distance from a geographic point. We assume that the user provide in the query its goegraphical coordinates (latitude and longitude) and that each dataset is augmented with (lat,lon) information.
Thus, to calculate the distance between two points on the earth’s surface, we resort to the so-called haversine formula: d H R = H ( ) + KH ( ) where d is the distance between two points, R is the terrestrial radius, is the difference between the latitudes of the two points ( 1 and 2 respectively), and the difference between the two longitudes ( 1 and 2 respectively) and K = cos( 1)cos( 2). The haversine corresponds to the half of the versine of angle, defined as:
H ( ) = sin2 from which we can calculate the distance between two points on the earth’s surface: d = 2R arcsin s sin2 2 + K sin2 2 ! (1) (2) (3)
SET @lat1 = lat - (range / 111.044736); SET @lat2 = lat + (range / 111.044736); SET @long1 = lng - (range /
abs(cos(radians(lat) * 111.044736))); SET @long2 = lng + (range /
abs(cos(radians(lat) * 111.044736))); SELECT *, 2 * 6371 * ASIN(SQRT(POWER(
SIN(((lat-db_lat) * pi()/180)/2),2) + COS(lat * pi()/180) * COS(latitudine * pi()/180) * POWER(SIN(((lng-db_lon) * pi()/180)/2),2) )) as distance FROM tableName WHERE db_lat BETWEEN @lat1 and @lat2 AND db_lon BETWEEN @long1 and @long2 HAVING distance < range ORDER BY distance asc
Listing 1. Simple Query
To further optimize the queries to reduce their execution time we used a Stored Procedures (SP), i.e. a programs stored within the database, written in languages different (often derived from SQL) depending on the DBMS in use, which allow users to perform complex functions defined by the database administrator. SPs must not return values but can accept input and output parameters as well as generate Result Sets.
The defines SP for the geolocated query is: CREATE PROCEDURE geoquery(IN lat DOUBLE, IN lng DOUBLE IN tableName VARCHAR(100), IN v_range INT, IN v_limit INT,
IN v_offset INT) BEGIN
DECLARE lat1 FLOAT; DECLARE long1 FLOAT;
To evaluate the effectiveness of the proposed solution we performed 1000 requests to a database MySql with a variable number of stored Open DataSetsa and we report the average measured response time from the server. In the comparison we also include a stored procedure query that is executed by a prepared statement. It similar to the SP one but since it require a string concatenation its average response time si in the miggle of the other two queries.
Figure 2 reports the query execution time for the simple
case, for the SP case and the SP case with prepared statement.
It shows that the SP is faster query possible and that the
execution time clearly increses with the number of records
in the DB. Note that since each query operates on a table
and that all the tables are independent, the proposed solution
well scale in cloud architecture partitionag the dataset among
a cluster of servers as in [
Open data augmentation is an approach pursued by several
works such as [
Geographic data has been extensively used in research in
the context of Location Based Services, Proximity services
[
Dedicated techniques [
Providing open-data with storage and mining solutions
requires dedicated architecture and system design that involve
the recent findings in the field of Big Data processing [
High speed machine learning process [
We presented a system architecture able to provide the open data framewotk with an augmentation service that add the location information to the published data. This alows the mobile client to access only the useful dataset and thus to optmize the networking performance. We investigated the SQL solution for geolocated queires evaluating typical dealys that can be expected by the server. Future work can adapt the architecture to a NoSQL database.