Having access to high quality and recent data is crucial both for decision makers in cities as well as for informing the public, likewise, infrastructure providers could offer more tailored solutions to cities based on such data. However, even though there are many data sets containing relevant indicators about cities available as open data, it is cumbersome to integrate and analyze them, since the collection is still a manual process and the sources are not connected to each other upfront. Further, disjoint indicators and cities across the available data sources lead to a large proportion of missing values when integrating these sources. In this paper we present a platform for collecting, integrating, and enriching open data about cities in a re-usable and comparable manner: we have integrated various open data sources and present approaches for predicting missing values, where we use standard regression methods in combination with principal component analysis to improve quality and amount of predicted values. Further, we re-publish the integrated and predicted values as linked open data.
Nowadays governments have large collections of data available for decision support.
Public administrations use these data collections for backing their decisions and
policies, and to compare themselves to other cities, and likewise infrastructure providers
like Siemens could offer more tailored solutions to cities based on these data. Having
access to high quality and current data is crucial to advance on these goals. Studies like
the Green City Index [
Even though there are many relevant data sources which contain quantitative based indicators about cities available as open data, it is still cumbersome to collect, clean, integrate, and analyze data from these sources: obstacles include different indicator specifications, different languages, formats, and units. Example sources of city data include DBpedia, Geonames, or the UrbanAudit data set included in Eurostat; Urban Audit 4 for example, provides over 250 indicators on several domains for 258 European cities. Furthermore, several larger cities provide data on their own open data portals, e.g., 4 http://ec.europa.eu/eurostat/web/cities/ London, Berlin, or Vienna.5 Data is published in different formats such as RDF, XML, CSV, XLS, or just as HTML tables. The specifications of the individual data fields – (i) how indicators are defined and (ii) how they have been collected – are often implicit in textual descriptions only and have to be processed manually for understanding.
Moreover, data sources like Urban Audit cover many cities and indicators (e.g.,
population), but show a large ratio of missing values in their data sets. The impact of
missing values is even aggravated when we combine different data sets, since there is
a fair amount of disjoint cities and indicators across those data sets, which makes them
hard to integrate. Our assumption though – inspired also by works that suspect the
existence of quantitative models behind the working, growth and scaling of cities [
UNSD Demographic and Social Statistics;
- We provide a system architecture for an “Open City Data Pipeline” including a crawler,
wrappers, and ontology-based integration components;
- We evaluate two prediction approaches for filling in missing values, where we
combine different standard regression methods and PCs to maximize prediction accuracy;
- We re-publish the integrated and predicated values as linked open data (LOD).
Section 2 describes the imported data sources and the challenges arising when
processing/integrating their data. Section 3 presents an architecture overview of the “Open City
Data Pipeline” and a lightweight extensible ontology used therein. Section 4 explains
methods we used for predicting missing values as well as corresponding prediction
error rates. Our Linked Open Data interface to republish the integrated and predicted data
is documented in Section 5. Section 6 concludes with several possible future extensions.
Related Work. QuerioCity [
The Open City Data Pipeline’s database contains data ranging from the years 1990 to 2014, but most of the data concerns the years after 2000. Not every indicator is covered over all years, where the highest overlap of indicators is between 2004 and 2011. Most European cities are contained in the Urban Audit data set, but we also include the capital cities and cities with a population over 100 000 from the United Nations Demographic Yearbook (UNYB). 8
Before integration, locations have varying names in different data sets (e.g., Wien
vs. Vienna), a Uniform Resource Identifier (URI) for every city is essential for the
integration and enables to link the cities and indicators back to DBpedia and other LOD
data sets. We choose to have an one-to-one (functional) mapping of every city from our
namespace to the English DBpedia resource, which in our republished data is encoded
by sameAs relations. We identify the matching DBpedia URIs for multilingual city
names and apply a semi-automated technique with three steps using the city’s names
from Urban Audit and UNYB:
- Accessing the DBpedia resource directly and following possible redirects;
- Using the Geonames API 9 to identify the DBpedia resource;
- For the remaining cities, we manually looked up the URL on DBpedia.
DBpedia. DBpedia, initially released in 2007, is an effort to extract structured data
from Wikipedia and publish the data as Linked Data [
Urban Audit. The Urban Audit (UA) collection started as an initiative to assess the
quality of life in European cities. It is conducted by the national statistical institutes
and Eurostat. Currently, data collection takes place every three years (last survey in
November 2012) and is published via Eurostat. 10 All data is provided on a voluntary
basis which leads to varying data availability and missing values in the collected data
sets. Urban Audit aims to provide an extensive look at the cities under investigation,
since its a policy tool to the European Commission. “The projects’ ultimate goal is
8 http://unstats.un.org/unsd/demographic/products/dyb/dyb2012.htm
9 http://api.geonames.org/
10 http://ec.europa.eu/eurostat
Year(s)
to contribute towards the improvement of the quality of urban life” [
of topics, for example, on education, environment, health, technology, and tourism. Our main source is the UNSD Demographic and Social Statistics, 11 which is based on the data collected annually (since 1948) by questionnaires to national statistical offices. The UNSD data marts consist of the following topics: population by age distribution, sex, and housing; occupants of housing units / dwellings by broad types (e.g., size, lighting, etc.); occupied housing units by different criteria (e.g., walls, waste, etc.).
The collected data has over 650 indicators, wherein we dropped the most finegrained indicator level to keep a balanced set of indicators in favor of keeping a more course-grained set of indicators, e.g., keeping housing units total instead of housing units by room size. However, for future work it would be interesting to split these indicators and calculate new indicators regarding their granularity.
Future Data Sources. At the point of writing, the data sources are strongly focused on European cities and demographic data. Hence, we need to integrate further national and international data sources. A promising candidate is the County and City Data Book (CCDB) of U.S. Census Bureau. 12 The CCDB offers two data sets; one covering Area and Population, Crime, Government and Climate for cities larger then 20 000 inhabi11 http://unstats.un.org/unsd/demographic/default.htm 12 https://www.census.gov/statab/ccdb/ccdbcityplace.html
Wrapper components Semantic Integration
Data Storage Analytics UI & LOD
CSV
RDF
HTML
RTF
XLS
GML
OSM Crawler
Extensible City Data Model RDFS Reasoner SPARQL Engine
GIS Database
Clustering Integration Component
RDF Triple Store Aggregation
Interpolation Web UI
SPARQL Endpoint
Linked Open Data tants; and another covering Population by Age, Sex, and Race, Education, Income and Poverty for locations with 100 000 population and more.
The Carbon Disclosure Project (CDP) is an organization based in the UK aiming at
“[...] using the power of measurement and information disclosure to improve the
management of environmental risk” [
The City Data Pipeline collects data, organizes this data into indicators, and shows these indicators to the user. This section introduces the system which is organized in several layers (see Figure 1): crawler, wrapper components, semantic integration, data storage, analytics, and external interfaces (user interface, SPARQL endpoint, and linked data). Crawler. The City Data Pipeline semi-automatically collects data from various registered open data sources in a periodic manner dependent on the specific source. The crawler currently collects data from 32 different sources, e.g., DBpedia, UN open data, Urban Audit, as well as data sets of several cities. Adding new data sources is still a manual process, where the mapping of the usually tabular data has to be provided by mapping scripts. However, a semi-automatic process would be an appealing extension for future work.
Value
estimatedRMSEForPredictedValues URL source
Indicator dateRetrieved
CityDataContext TemporalContext dateValidity predictorForPredictedValues category datatype spatialContext
Country
City
District
Wrapper Components. As a first step of data integration, a set of wrapper
components parses the downloaded data and converts it to a source specific RDF. The set of
wrapper components include a CSV wrapper to parse and clean CSV data, a
wrapper for extracting HTML tables, a wrapper for extracting tables of RTF documents, a
wrapper for Excel sheets, and a wrapper for cleaning RDF data as well. All of these
wrappers are customizable to cater for diverse source-specific issues. These wrapper
components convert the data to RDF and preprocess the data before integrating the data
with the existing triple store. Preprocessing contains the usual data cleansing tasks, unit
conversions, number and data formatting, string encoding, and filtering invalid data.
Semantic Integration (Ontology). To be able to access a single KPI such as the
population number, which is provided by several data sources, the semantic integration
component unifies the vocabulary of the different data sources through an ontology (see
Figure 2). The semantic integration component is partly implemented in the individual
wrappers and partly by an RDFS [
Indicator is the super property of all the indicator properties mapping
CityDataContexts to actual values. Each Indicator of the ontology contains, a name, description,
a unit of measurement, a data type, and is grouped into one of the following categories:
(a) Demography, (b) Social Aspects, (c) Economic Aspects, (d) Training and Education,
(e) Environment, (f) Travel and Transport, (g) Culture and Recreation, and (h)
Geography. To integrate the source specific indicators the ontology maps data source specific
RDF properties to City Data Model properties, e.g., it maps dbpedia:population to
citydata:population by an RDFS subPropertyOf property. A CityDataContext is an
anchor connecting a set of data points to a spatial context, a temporal context, and a data
source. When importing an input CSV file containing the indicators as columns and
the cities as rows then each row corresponds to (at least) one CityDataContext. The
SpatialContext class collects all resources with spatial dimension especially,
country, province, region, city, and district. Depending on the available data the ontology
allows storage of data points at each of these levels. Furthermore entities of
differ13 http://citydata.wu.ac.at/ns#
ent granularity can be connected by the property locatedIn. The dateValidity property
maps a CityDataContext to a point in time where the values are valid. Additionally
the property periodValidity can indicate what the validity period is (possible values
are biannual, annual, quarterly, monthly, weekly, daily, hourly or irregular). Whereas
the dateRetrieved property records the date and time of the data set download. The
source property links a CityDataContext to the corresponding data source.
Data Storage. To store the processed data we use Jena TDB14 as triple store for
RDF data, and PostGIS/PostgreSQL as a GIS database for geographic information.
GIS databases allow us to compute missing information such as areas of cities or
districts, or lengths of certain paths. Subsequent subsystems can access the RDF data via
a SPARQL interface. The SPARQL engine provides RDFS reasoning support by query
rewriting (including reasoning over numbers [
Analytics, UI & LOD. The analytics layer includes tools to fill in missing data by
using statistical methods. Section 4 describes the missing value prediction in detail.
The results are also stored in the RDF triple store and the SPARQL engine provides
access to them. Section 5 explains the frontend, user interface, SPARQL endpoint, and
publishing data as Linked Open Data. Bischof et al. [
After integrating the different sources, we discovered a large number of missing values
in our data sets. We identified two reasons for that:
- As shown in Table 1 and 2, we can observe a large ratio of missing values due to
incomplete data published by the data providers;
- More severely, when we combine the different data sets even more missing values are
introduced, since there is a fair amount of disjoint cities and indicators.
Base Methods. Our assumption is that every indicator has its own distribution (e.g.,
normal, Poisson, etc.) and relationship to other indicators. Hence, we aim to evaluate
different regression methods and choose the best fitting model to predict the missing
values. We measure the prediction accuracy by comparing the root mean squared error
in % (RMSE%) [
n ymax ymin
A 100 (1) where n is the amount of predictions, yt is the observed (actual) value on t, y0t is the predicted value on t, and ymax (resp. ymin) the maximum (resp. minimum) value of the observed values.
In the field of Data Mining [
K-Nearest Neighbour Regression (KNN) denoted as MKNN is one of the most
wide-spread data mining techniques applied in a variety of domains. As stated in [
Multiple Linear Regression (MLR) denoted as MMLR has the goal to find a linear
relationship between one target and several predictor variables. The linear relationship
can be expressed as a regression line through the data points, where the most common
approach is ordinary least squares to measure & minimize the cumulated distances [
Random Forest Decision Trees (RFD) denoted as MRF D involve the top-down
segmentation of the data into multiple smaller regions represented by a tree with decision
and leaf nodes. Each segmentation is based on splitting rules, which are tested on a
predictor. Decision nodes have branches for each value of the tested attribute and leaf
nodes represent decision on the numerical target. A random forest is generated by a
large number of trees, which are build according to a random selection of attributes at
each node. We use the algorithm introduced by Breiman [
Preprocessing. The preprocessing starts with the extraction of the base data set from
our RDF triple store. We use SPARQL queries with the fixed period of 2004–2011
and produce an initial data set as a matrix with tuples of the form hCity; Indicator;
Year; Valuei. Based on the initial matrix, we perform a basic entity recognition similar
to Paulheim et al. [
Approach 1 - Building Complete Subsets. In the first approach, we try to build models
for a target indicator by directly using the available indicators as predictors. For this,
we are using the correlation matrix of the data to find indicators which are suitable
predictors. Subsequently, we build a complete subset from our data, i.e. we first perform
a projection on our data table, keeping only the predictors and the specific target as
columns. More detailed, our approach has the following steps on the initial data set, the
matrix A1 and a fixed number of predictors n (we test this approach on different n0s):
1. Select the target indicator IT ;
2. Calculate the corr. matrix AC of A1 between IT and the remaining indicators;
3. Create the submatrix A2 of A1 with IT and the n “best” indicators (called the
predictors). The predictors are selected according to the highest absolute correlation
coefficients in AC ;
4. Create the complete matrix A3 by deleting all rows in A2 with missing values;
5. Apply stratified tenfold cross-validation (see [
direct use of indicators as predictors. Instead, we first perform a Principal Component
Analysis (PCA) to reduce the number of dimensions of the data set and use the new
compressed dimensions, called principal components (PCs) as predictors. As stated
in [
AC of A4 between IT and the PCs; 5. Create the submatrix A5 of A4 on the selection of the PCs with the highest absolute correlation coefficients and limit them by n; 6. Create submatrix A6 of A5 for validation by deleting empty rows for IT ; 7. Apply stratified tenfold cross-validation on A6 with the Step 5 from Approach 1, which results in the best performing model MBest; 8. Use the method MBest to build a new model on A5 (not A6) for predicting the missing values of IT .
Figure 3b shows the RMSE% for the different methods and different number of predictors. On average over all indicators, KNN works best closely followed by MLR. However, for 80 predictors MLR performs best with an avg. RMSE% of 4.04%, where KNN has an avg. RMSE% of “only” 4.15%. The RFD algorithm provides reasonable results for a lower number of predictors, but starts yielding worse results for 20 predictors and more. MLR improves steady up to 80 predictors. KNN is performing best up to 9 8 7 6 E% 5 SRM 4 3 2 1 0
Knn Rforest Linreg Best 70 predictors. As assumed, the overall results improve by selecting the best performing regression method for each indicator. The red line in Figure 3b shows the avg. RMSE% with the best regression method chosen. We also can see, that the results improve with more predictors reaching the best result of 3.29% with 80.
As mentioned, we have two properties to evaluate the quality of our approaches. First, it is important to build models which are able to predict many (preferably) all missing values. Second, the prediction accuracy of the models is essential, since the Open City Data Pipeline can fulfill its purpose of publishing high-quality, accurate data and predictions. Prediction accuracy in Approach 1 is higher, which we relate to the reduced size of the data set. However in Approach 1, we fill in at the maximum 296 069 values with 2 predictors (having an avg. RMSE% of 2.01%), which is about 66% of Approach 2. Due to the reduced number of predictions, we will apply Approach 2 for publishing the filled in values. 5
City Data Context. The resources (cities) and properties in the City Data namespace (http://citydata.wu.ac.at/) are published according to the linked data principles. The ontology (as described in Section 3), contains all City Data property and class descriptions. Each city is assigned a dereferencable URI, e.g., http://citydata.wu.ac. at/resource/Ljubljana for the capital of Slovenia. Depending on the HTTP Accept header the server will return either an HTML, RDF/XML, or Turtle representation after a HTTP 303 redirect. The city resources are linked to the Linked Open Data cloud via owl:sameAs to the corresponding DBpedia resources.
Prediction Data. The whole prediction is based on the present data in the triple store.
The preprocessing is written in Python and prediction and evaluation is developed in
R [
Interface. A simple Java powered web interface allows users to select exactly which subset of the data should be shown. The interface provides programmatic access via HTTP GET to allow external tools such as data visualization frameworks, to query the database. The web application communicates with the Jena triple store via SPARQL 1.1. Users can select one or more of the 450 indicators. The list also shows how many data points are available per indicator and for how many cities data points are available for this indicator. Next the user can select one or several of more then 5 260 cities for which we collected data. For a few cities we even have information on the individual districts available. In these cases the user can select one or several of the districts. Optionally the user can specify a temporal context, for which year the database should be queried. This feature allows to compare several cities with each other at a certain point of time instead of listing data of all available times. 6
In this paper we have presented the City Data Pipeline, an extensible platform for collecting, integrating, and predicting open city data from several data providers including DBpedia and Urband Audit. We have developed several components including a data crawler, wrappers, an ontology-based integration platform, and a missing value prediction module, which is a crucial component since we have sparse data sets. For this, we have developed two approaches, one based on predicting a target indicators directly from other indicators, and one based on predictors from components calculated by Principal Components Analysis (PCA). We applied for both approaches three basic regression methods (e.g., Multiple Linear Regression) and selected the best performing one. They were compared regarding the number of filled in values and prediction accuracy, concluding that the PCA-based approach will be used for future predictions. Filled in missing values are then published as linked open data for further use.
Our future work includes variants and extensions of the presented data sets,
methods, and the system itself. Regarding the data sets, we already mention in Chapter 2
several sources, e.g., U.S. Census, which are needed to cover a wider range of cities
worldwide. Regarding the methods, we have not yet investigated how the different data
sets relate to each other, since our missing values prediction is based on the entire data
set. It would be interesting to investigate, if indicators can be predicted from one data
set to another. Crucial for this approach would be a source data set, i.e., Urband Audit,
which has a high amount of overlapping cities with the target data sets. Further, we aim
to extend our basket of base methods with other well established regression methods.
Promising candidates are Support Vector Machines with a linear/non-linear kernel [
Acknowledgements. Stefan Bischof has been partially funded by the Vienna Science and Technology Fund (WWTF) through project ICT12-015.