{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<div class=\"notebook-buttons\" style=\"display:flex; padding-top: 5rem;padding-bottom: 2.5rem;line-height: 2.15;\">\n",
" <a href=\"https://raw.githubusercontent.com/zazuko/notebooks/master/notebooks/statistics_zurich/data_model.ipynb\" download>\n",
" <div id=\"download-link\" style=\"display: flex;padding-right: 3.5rem;padding-bottom: 0.625rem;border-bottom: 1px solid #ececed; height: auto;align-items: center;\">\n",
" <img class=\"call-to-action-img\" src=\"img/download.svg\" width=\"22\" height=\"30\" style=\"margin-right: 10px;margin-top: auto;margin-bottom: auto;\">\n",
" <div class=\"call-to-action-txt\">Download Notebook</div>\n",
" </div>\n",
" </a>\n",
" <a href=\"https://github.com/zazuko/notebooks/blob/master/notebooks/statistics_zurich/data_model.ipynb\">\n",
" <div id=\"github-link\" style=\"display: flex;padding-right: 3.5rem;padding-bottom: 0.625rem;border-bottom: 1px solid #ececed; height: auto;align-items: center;\">\n",
" <img class=\"call-to-action-img\" src=\"img/github.svg\" width=\"25\" height=\"30\" style=\"margin-right: 10px;margin-top: auto;margin-bottom: auto;\">\n",
" <div class=\"call-to-action-txt\">View on GitHub</div>\n",
" </div>\n",
" </a>\n",
"</div>\n",
"\n",
"# Data model for Zürich datasets \n",
"\n",
"The Zürich Statistical Office collects data on the city and its residents. This data is published as [Linked Data](https://en.wikipedia.org/wiki/Linked_data). \n",
"\n",
"In this tutorial, we will **explain the linked data model** behind it. Mainly, we will guide you through the data structure. The tutorial will show you available datasets, and the shape they take. We will look into queries explaining the data structure as well as available dimensions."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"[1. Graphs](#Graphs) \n",
"[2. Graph by Zürich Statistical Office](#Graph-by-Zürich-Statistical-Office) \n",
"[3. Data cubes](#Data-cubes) \n",
"[4. Observation set](#Observation-set) \n",
"[5. Observations](#Observations) \n",
"[6. Data slices](#Data-slices) \n",
"[7. SPEX](#SPEX) \n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### SPARQL endpoint\n",
"\n",
"Data by the Zürich Statistical Office is published as Linked Data. It can be accessed with [SPARQL queries](https://www.w3.org/TR/rdf-sparql-query/). You can send queries using HTTP requests. The API endpoint is **[https://ld.stadt-zuerich.ch/query](https://ld.stadt-zuerich.ch/query).** \n",
" \n",
" \n",
"We use the `SPARQL` kernel in a jupyter notebook to communicate with the database (you may need to configure this via menu Kernel -> Change kernel -> SPARQL). First, let's configure the endpoint."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><div class=\"magic\">Endpoint set to: https://ld.stadt-zuerich.ch/query</div><div class=\"magic\">HTTP authentication: method=basic, user=public, passwd set</div><div class=\"magic\">Display: table</div><div class=\"magic\">Result maximum size: 50</div></div>"
],
"text/plain": [
"Endpoint set to: https://ld.stadt-zuerich.ch/query\n",
"HTTP authentication: method=basic, user=public, passwd set\n",
"Display: table\n",
"Result maximum size: 50\n"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"#Params\n",
"%endpoint https://ld.stadt-zuerich.ch/query\n",
"%auth basic public public\n",
"%display table\n",
"\n",
"%show 50"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Graphs\n",
"\n",
"One endpoint can contain multiple [named graphs](https://en.wikipedia.org/wiki/Named_graph). \n",
" \n",
" \n",
"You can see what graphs are available using `WHERE` and `GRAPH` statements. "
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>graphs</th></tr><tr class=odd><td class=val><a href=\"https://linked.opendata.swiss/graph/eCH-0071\" target=\"_other\">https://linked.opendata.swiss/graph/eCH-0071</a></td></tr><tr class=even><td class=val><a href=\"https://linked.opendata.swiss/graph/BK/FederalDirectory\" target=\"_other\">https://linked.opendata.swiss/graph/BK/FederalDirectory</a></td></tr><tr class=odd><td class=val><a href=\"https://linked.opendata.swiss/graph/sfa/meta\" target=\"_other\">https://linked.opendata.swiss/graph/sfa/meta</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/nl/isil\" target=\"_other\">https://lindas.admin.ch/nl/isil</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/sbb/didok\" target=\"_other\">https://lindas.admin.ch/sbb/didok</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/sbb/nova\" target=\"_other\">https://lindas.admin.ch/sbb/nova</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/sbb/setactual\" target=\"_other\">https://lindas.admin.ch/sbb/setactual</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/sbb/constructionsite\" target=\"_other\">https://lindas.admin.ch/sbb/constructionsite</a></td></tr><tr class=odd><td class=val><a href=\"https://linked.opendata.swiss/fsvo/animalpest\" target=\"_other\">https://linked.opendata.swiss/fsvo/animalpest</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/meta\" target=\"_other\">https://lindas.admin.ch/meta</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/sfa/opendataswiss\" target=\"_other\">https://lindas.admin.ch/sfa/opendataswiss</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/sfa/stapfer_enquete\" target=\"_other\">https://lindas.admin.ch/sfa/stapfer_enquete</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/ontologies\" target=\"_other\">https://lindas.admin.ch/ontologies</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/fsvo/themes\" target=\"_other\">https://lindas.admin.ch/fsvo/themes</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/elcom/electricityprice\" target=\"_other\">https://lindas.admin.ch/elcom/electricityprice</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/fsvo/plazi\" target=\"_other\">https://lindas.admin.ch/fsvo/plazi</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/stadtzuerich/stat\" target=\"_other\">https://lindas.admin.ch/stadtzuerich/stat</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/stadtzuerich/stat/views\" target=\"_other\">https://lindas.admin.ch/stadtzuerich/stat/views</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/cube/dimension\" target=\"_other\">https://lindas.admin.ch/cube/dimension</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/fso/agvch\" target=\"_other\">https://lindas.admin.ch/fso/agvch</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/egad/ric-o\" target=\"_other\">https://lindas.admin.ch/egad/ric-o</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/foj/zefix\" target=\"_other\">https://lindas.admin.ch/foj/zefix</a></td></tr><tr class=odd><td class=val><a href=\"https://lindas.admin.ch/territorial\" target=\"_other\">https://lindas.admin.ch/territorial</a></td></tr><tr class=even><td class=val><a href=\"https://lindas.admin.ch/fch/staatskalender\" target=\"_other\">https://lindas.admin.ch/fch/staatskalender</a></td></tr></table><div class=\"tinfo\">Total: 24, Shown: 24</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# List graphs in endpoint\n",
"SELECT DISTINCT ?graphs \n",
"WHERE {\n",
" GRAPH ?graphs {\n",
" ?s ?p ?o;\n",
" }\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"These graphs have potentilly nothing in common, they are simply stored in the same database. \n",
"\n",
"Here, we have one endpoint storing information on subject as diverse as the train timetable, electricity prices, or animal diseases:\n",
"\n",
"* trains timetable (https://lindas.admin.ch/sbb/setactual)\n",
"* electricity prices (https://lindas.admin.ch/elcom/electricityprice)\n",
"* animal diseases (https://linked.opendata.swiss/fsvo/animalpest)\n",
"\n",
"All those graphs can be accessed from the same SPARQL endpoint."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Graph by Zürich Statistical Office"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Datasets from the Zürich Statistical Office are published in the following named graph: `<https://lindas.admin.ch/stadtzuerich/stat>`\n",
"\n",
"You can limit your queries to one named graph using `WHERE` and `FROM` statements. "
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>s</th>\n",
"<th>p</th>\n",
"<th>o</th></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEO0663\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEO0663</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Frauenkappelen</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/GEO0663\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/GEO0663</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Frauenkappelen</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/ORT0880\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/ORT0880</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Rüeggisberg</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEO0880\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEO0880</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Rüeggisberg</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/GEO0880\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/GEO0880</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Rüeggisberg</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/ORT1024\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/ORT1024</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Emmen</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEO1024\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEO1024</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Emmen</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/GEO1024\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/GEO1024</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Emmen</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/ORT1701\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/ORT1701</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Baar</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEO1701\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEO1701</a></td>\n",
"<td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td>\n",
"<td class=val>Baar</td></tr></table><div class=\"tinfo\">Total: 10, Shown: 10</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# List random triples in the graph\n",
"\n",
"SELECT *\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {?s ?p ?o}\n",
"LIMIT 10\n",
"OFFSET 100"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Data cubes\n",
"Datasets published by Zürich Statistial Office are stored as [data cubes](https://en.wikipedia.org/wiki/OLAP_cube). Let's check the cube definition in its [documentation](https://cube.link/):\n",
"\n",
"**Cube** \n",
"*Represents the entry point for a collection of observations, conforming to some common dimensional structure.*\n",
"\n",
"\n",
"We can find all data cubes by defining `<https://cube.link/Cube>` as object."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>cubes</th></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHG</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHA-ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHA-ZIM</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZIM</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZIM-WHA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZIM-WHA</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHA</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/APZ\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/APZ</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHG-ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHG-ZIM</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-SEX</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ANT-GGH-HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ANT-GGH-HEL</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-ALT\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-ALT</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/GES-ALT-SEX-TOU\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/GES-ALT-SEX-TOU</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/GES-SEX-TOU\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/GES-SEX-TOU</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/GES-ALT-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/GES-ALT-SEX</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/GES-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/GES-SEX</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-ALT-HEL-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-ALT-HEL-SEX</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-ALT-HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-ALT-HEL</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-ALT-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-ALT-SEX</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/AST-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/AST-BTA</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/TIA-BTA-TIG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/TIA-BTA-TIG</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WRT-BTA-EAP\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WRT-BTA-EAP</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/TII-BTA-TIG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/TII-BTA-TIG</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/TII-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/TII-BTA</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/TIA-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/TIA-BTA</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/AST-BEW-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/AST-BEW-BTA</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-SEX</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZUS-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZUS-BTA</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-HEL</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BES-BTA-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BES-BTA-SEX</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BES-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BES-BTA</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-ZSA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-ZSA</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/QMP-EIG-HAA-OBJ-ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/QMP-EIG-HAA-OBJ-ZIM</a></td></tr></table><div class=\"tinfo\">Total: 34, Shown: 34</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# List all data cubes\n",
"\n",
"SELECT ?cubes\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" ?cubes a <https://cube.link/Cube> .\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"All cubes have the same data structure. This means we can write very similar queries to get data from different cubes.\n",
"\n",
"Data cubes have the following properties:"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>properties</th></tr><tr class=odd><td class=val><a href=\"http://www.w3.org/1999/02/22-rdf-syntax-ns#type\" target=\"_other\">http://www.w3.org/1999/02/22-rdf-syntax-ns#type</a></td></tr><tr class=even><td class=val><a href=\"http://schema.org/name\" target=\"_other\">http://schema.org/name</a></td></tr><tr class=odd><td class=val><a href=\"http://schema.org/identifier\" target=\"_other\">http://schema.org/identifier</a></td></tr><tr class=even><td class=val><a href=\"https://cube.link/observationSet\" target=\"_other\">https://cube.link/observationSet</a></td></tr><tr class=odd><td class=val><a href=\"https://cube.link/observationConstraint\" target=\"_other\">https://cube.link/observationConstraint</a></td></tr></table><div class=\"tinfo\">Total: 5, Shown: 5</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Cube properties\n",
"\n",
"SELECT DISTINCT ?properties\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" ?s a <https://cube.link/Cube> ;\n",
" ?properties ?o.\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's learn more about available data cubes. We can access cube description using `<http://schema.org/name>` property. We will also get its id using `<http://schema.org/identifier>`. By defining schema and cube as `PREFIX`, we can save some typing."
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>cube</th>\n",
"<th>description</th>\n",
"<th>id</th></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHG</a></td>\n",
"<td class=val>Statistische Wohnungen</td>\n",
"<td class=val>WHG</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHG</a></td>\n",
"<td class=val>Statistische Wohnungen nach Zeit</td>\n",
"<td class=val>WHG</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHA-ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHA-ZIM</a></td>\n",
"<td class=val>Wohnungen (ohne Appartements) nach Zeit, Zimmerzahl einer Wohnung</td>\n",
"<td class=val>WHA-ZIM</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZIM</a></td>\n",
"<td class=val>Zimmer</td>\n",
"<td class=val>ZIM</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZIM</a></td>\n",
"<td class=val>Zimmer nach Zeit</td>\n",
"<td class=val>ZIM</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZIM-WHA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZIM-WHA</a></td>\n",
"<td class=val>Zimmer nach Zeit, Wohnungsart</td>\n",
"<td class=val>ZIM-WHA</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHA</a></td>\n",
"<td class=val>Wohnungen (ohne Appartements)</td>\n",
"<td class=val>WHA</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHA</a></td>\n",
"<td class=val>Wohnungen (ohne Appartements) nach Zeit</td>\n",
"<td class=val>WHA</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/APZ\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/APZ</a></td>\n",
"<td class=val>Appartementzimmer</td>\n",
"<td class=val>APZ</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/APZ\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/APZ</a></td>\n",
"<td class=val>Appartementzimmer nach Zeit</td>\n",
"<td class=val>APZ</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WHG-ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WHG-ZIM</a></td>\n",
"<td class=val>Statistische Wohnungen nach Zeit, Zimmerzahl einer Wohnung</td>\n",
"<td class=val>WHG-ZIM</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung</td>\n",
"<td class=val>BEW</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung nach Zeit</td>\n",
"<td class=val>BEW</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-SEX</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung nach Geschlecht, Zeit</td>\n",
"<td class=val>BEW-SEX</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung nach Heimatland, Geschlecht, Zeit</td>\n",
"<td class=val>BEW-HEL-SEX</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ANT-GGH-HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ANT-GGH-HEL</a></td>\n",
"<td class=val>Anteil nach Grundgesamtheit, Heimatland, Zeit</td>\n",
"<td class=val>ANT-GGH-HEL</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung nach Heimatland, Zeit</td>\n",
"<td class=val>BEW-HEL</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-ALT\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-ALT</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung nach Alter, Zeit</td>\n",
"<td class=val>BEW-ALT</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/GES-ALT-SEX-TOU\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/GES-ALT-SEX-TOU</a></td>\n",
"<td class=val>Sterbefälle (wirtschaftlich) nach Alter, Geschlecht, Zeit, Todesursachen</td>\n",
"<td class=val>GES-ALT-SEX-TOU</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/GES-SEX-TOU\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/GES-SEX-TOU</a></td>\n",
"<td class=val>Sterbefälle (wirtschaftlich) nach Geschlecht, Zeit, Todesursachen</td>\n",
"<td class=val>GES-SEX-TOU</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/GES-ALT-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/GES-ALT-SEX</a></td>\n",
"<td class=val>Sterbefälle (wirtschaftlich) nach Alter, Geschlecht, Zeit</td>\n",
"<td class=val>GES-ALT-SEX</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/GES-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/GES-SEX</a></td>\n",
"<td class=val>Sterbefälle (wirtschaftlich) nach Geschlecht, Zeit</td>\n",
"<td class=val>GES-SEX</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-ALT-HEL-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-ALT-HEL-SEX</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung nach Alter, Heimatland, Geschlecht, Zeit</td>\n",
"<td class=val>BEW-ALT-HEL-SEX</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-ALT-HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-ALT-HEL</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung nach Alter, Heimatland, Zeit</td>\n",
"<td class=val>BEW-ALT-HEL</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-ALT-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-ALT-SEX</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung nach Alter, Geschlecht, Zeit</td>\n",
"<td class=val>BEW-ALT-SEX</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/AST-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/AST-BTA</a></td>\n",
"<td class=val>Arbeitsstätten nach Betriebsart, Zeit</td>\n",
"<td class=val>AST-BTA</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/TIA-BTA-TIG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/TIA-BTA-TIG</a></td>\n",
"<td class=val>Tierarten nach Betriebsart, Tiergattung, Zeit</td>\n",
"<td class=val>TIA-BTA-TIG</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/WRT-BTA-EAP\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/WRT-BTA-EAP</a></td>\n",
"<td class=val>Wert nach Betriebsart, Erfolgsrechnung, Zeit</td>\n",
"<td class=val>WRT-BTA-EAP</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/TII-BTA-TIG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/TII-BTA-TIG</a></td>\n",
"<td class=val>Tierindividuen nach Betriebsart, Tiergattung, Zeit</td>\n",
"<td class=val>TII-BTA-TIG</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/TII-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/TII-BTA</a></td>\n",
"<td class=val>Tierindividuen nach Betriebsart, Zeit</td>\n",
"<td class=val>TII-BTA</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/TIA-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/TIA-BTA</a></td>\n",
"<td class=val>Tierarten nach Betriebsart, Zeit</td>\n",
"<td class=val>TIA-BTA</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/AST-BEW-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/AST-BEW-BTA</a></td>\n",
"<td class=val>Arbeitsstätten nach Bewilligung, Betriebsart, Zeit</td>\n",
"<td class=val>AST-BEW-BTA</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-SEX</a></td>\n",
"<td class=val>Zuschauer/innen, Besucher/innen nach Betriebsart, Geschlecht, Zeit</td>\n",
"<td class=val>ZUS-BTA-SEX</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZUS-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZUS-BTA</a></td>\n",
"<td class=val>Zuschauer/innen, Besucher/innen nach Betriebsart, Zeit</td>\n",
"<td class=val>ZUS-BTA</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-HEL</a></td>\n",
"<td class=val>Zuschauer/innen, Besucher/innen nach Betriebsart, Heimatland, Zeit</td>\n",
"<td class=val>ZUS-BTA-HEL</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BES-BTA-SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BES-BTA-SEX</a></td>\n",
"<td class=val>Beschäftigte nach Betriebsart, Geschlecht, Zeit</td>\n",
"<td class=val>BES-BTA-SEX</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BES-BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BES-BTA</a></td>\n",
"<td class=val>Beschäftigte nach Betriebsart, Zeit</td>\n",
"<td class=val>BES-BTA</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-ZSA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/ZUS-BTA-ZSA</a></td>\n",
"<td class=val>Zuschauer/innen, Besucher/innen nach Betriebsart, Zeit, Zuschauer- bzw. Besucherart</td>\n",
"<td class=val>ZUS-BTA-ZSA</td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/QMP-EIG-HAA-OBJ-ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/QMP-EIG-HAA-OBJ-ZIM</a></td>\n",
"<td class=val>Quadratmeterpreis nach Eigentümerart, Handänderungsart, Objektart, Zeit, Zimmerzahl einer Wohnung</td>\n",
"<td class=val>QMP-EIG-HAA-OBJ-ZIM</td></tr></table><div class=\"tinfo\">Total: 39, Shown: 39</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"\n",
"SELECT *\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" ?cube a cube:Cube ;\n",
" schema:name ?description ;\n",
" schema:identifier ?id .\n",
"} "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can see that some cubes have more than one description."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Observation set\n",
"\n",
"Observations in data cubes are stored in observation set (`<https://cube.link/observationSet>`). Let's check the observationSet definition in its [documentation](https://cube.link/):\n",
"\n",
"**observationSet** \n",
"*Connects a cube with a set of observations.*\n",
"\n",
"\n",
"We can find all observations in a cube using `cube:observationSet/cube:observation` as predicate. \n",
"Let's query all observations in `<https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX>` cube."
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>obs</th></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0002-R00012-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0002-R00012-Z31122017</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0001-R00013-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0001-R00013-Z31122017</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL1000-SEX0002-R00014-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL1000-SEX0002-R00014-Z31122017</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0002-R00024-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0002-R00024-Z31122017</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL1000-SEX0002-R00033-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL1000-SEX0002-R00033-Z31122017</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0002-R00042-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0002-R00042-Z31122017</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0001-R00044-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0001-R00044-Z31122017</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL1000-SEX0001-R00052-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL1000-SEX0001-R00052-Z31122017</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0002-R00072-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL2000-SEX0002-R00072-Z31122017</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL1000-SEX0002-R00074-Z31122017\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX/observation/HEL1000-SEX0002-R00074-Z31122017</a></td></tr></table><div class=\"tinfo\">Total: 10, Shown: 10</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# List all observations in a cube\n",
"\n",
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"\n",
"SELECT ?obs\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs.\n",
"}\n",
"LIMIT 10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can count the number of observations using `COUNT` statement"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>count</th></tr><tr class=odd><td class=val>9512</td></tr></table><div class=\"tinfo\">Total: 1, Shown: 1</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Count observations in a data cube\n",
"\n",
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"\n",
"SELECT (COUNT(?obs) AS ?count)\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs.\n",
" # Equivalent to:\n",
" # cube:observationSet ?observationSet.\n",
" #?observationSet cube:observation ?obs.\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"So in `BEW-HEL-SEX` cube we have almost 10k observations about the population of Zürich! "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Observations\n",
"\n",
"Let's take a closer look at the observations. We can get all observation properties using this query:"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>obsProperty</th></tr><tr class=odd><td class=val><a href=\"http://www.w3.org/1999/02/22-rdf-syntax-ns#type\" target=\"_other\">http://www.w3.org/1999/02/22-rdf-syntax-ns#type</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/ZEIT\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/ZEIT</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/RAUM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/RAUM</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/attribute/KORREKTUR\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/attribute/KORREKTUR</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/WHG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/WHG</a></td></tr><tr class=even><td class=val><a href=\"http://schema.org/identifier\" target=\"_other\">http://schema.org/identifier</a></td></tr><tr class=odd><td class=val><a href=\"https://cube.link/observedBy\" target=\"_other\">https://cube.link/observedBy</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/TIME\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/TIME</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/ZIM</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/WHA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/WHA</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/ZIM</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/WHA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/WHA</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/APZ\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/APZ</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/BEW\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/BEW</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/SEX</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/HEL</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/GGH\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/GGH</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/ANT\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/ANT</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/ALT\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/ALT</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/GES\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/GES</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/TOU\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/TOU</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/BTA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/BTA</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/AST\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/AST</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/TIG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/TIG</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/TIA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/TIA</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/WRT\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/WRT</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/EAP\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/EAP</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/TII\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/TII</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/BEW\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/BEW</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/ZUS\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/ZUS</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/BES\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/BES</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/ZSA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/ZSA</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/OBJ\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/OBJ</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/EIG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/EIG</a></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/HAA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/HAA</a></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/QMP\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/QMP</a></td></tr></table><div class=\"tinfo\">Total: 36, Shown: 36</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Observation properties\n",
"\n",
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"\n",
"SELECT DISTINCT ?obsProperty\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" ?dataset a cube:Cube;\n",
" cube:observationSet/cube:observation ?observation. \n",
" ?observation ?obsProperty ?obsVal.\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Quite a bit! Now, not every dataset will have all those properties. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's get a list of properties for the `<https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX>` data cube. We will also get its labels using `schema:name`, and descrption using `schema:description`.\n",
"\n",
"Since not all properties have a description, we will use the `OPTIONAL` statement to query it."
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>obsProperty</th>\n",
"<th>label</th>\n",
"<th>description</th></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/ZEIT\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/ZEIT</a></td>\n",
"<td class=val>Zeit</td>\n",
"<td class=val>Repräsentation der Zeit als Konzept, repräsentiert die vollständige Komplexität.</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/RAUM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/RAUM</a></td>\n",
"<td class=val>Raum</td>\n",
"<td class=val></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/BEW\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/BEW</a></td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung</td>\n",
"<td class=val>Wirtschaftliche Wohnbevölkerung</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/HEL\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/HEL</a></td>\n",
"<td class=val>Heimatland</td>\n",
"<td class=val></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/SEX\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/SEX</a></td>\n",
"<td class=val>Geschlecht</td>\n",
"<td class=val></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/TIME\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/TIME</a></td>\n",
"<td class=val>Zeit</td>\n",
"<td class=val>Repräsentation der Zeit als ISO 8601 Datum (vereinfacht).</td></tr></table><div class=\"tinfo\">Total: 6, Shown: 6</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"\n",
"SELECT DISTINCT ?obsProperty ?label ?description \n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX> a cube:Cube;\n",
" cube:observationSet/cube:observation ?observation. \n",
" ?observation ?obsProperty ?obsVal.\n",
" ?obsProperty schema:name ?label .\n",
" OPTIONAL {\n",
" ?obsProperty schema:description ?description .\n",
" }\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can see that `BEW-HEL-SEX` data cube contains data on the population across:\n",
"* time (german: *Zeit*)\n",
"* place (german: *Raum*)\n",
"* gender (german: *Geschlecht*)\n",
"* origin (german: *Heimatland*)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Knowing properties, we can directlty query the measurements. Now, we are able to find the actual data: number of inhabitants across time, place, gender, and origin.\n",
"\n",
"We will find the measurements using `<https://ld.stadt-zuerich.ch/statistics/measure/BEW>` as the observation's property. We will also the cube's dimensions using:\n",
"* `<https://ld.stadt-zuerich.ch/statistics/property/RAUM>` for place \n",
"* `<https://ld.stadt-zuerich.ch/statistics/property/TIME>` for time \n",
"* `<https://ld.stadt-zuerich.ch/statistics/property/HEL>` for origin\n",
"* `<https://ld.stadt-zuerich.ch/statistics/property/SEX>` for gender\n",
"\n",
"And again, making use of `PREFIX` makes the query more readable."
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>time</th>\n",
"<th>place</th>\n",
"<th>origin</th>\n",
"<th>gender</th>\n",
"<th>count</th></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00012\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00012</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL2000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL2000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0002\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0002</a></td>\n",
"<td class=val>94.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00013\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00013</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL2000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL2000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0001\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0001</a></td>\n",
"<td class=val>157.0</td></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00014\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00014</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL1000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL1000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0002\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0002</a></td>\n",
"<td class=val>267.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00024\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00024</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL2000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL2000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0002\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0002</a></td>\n",
"<td class=val>1686.0</td></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00033\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00033</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL1000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL1000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0002\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0002</a></td>\n",
"<td class=val>4694.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00042\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00042</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL2000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL2000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0002\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0002</a></td>\n",
"<td class=val>1709.0</td></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00044\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00044</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL2000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL2000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0001\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0001</a></td>\n",
"<td class=val>2687.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00052\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00052</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL1000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL1000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0001\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0001</a></td>\n",
"<td class=val>2256.0</td></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00072\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00072</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL2000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL2000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0002\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0002</a></td>\n",
"<td class=val>1613.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/R00074\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/R00074</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/HEL1000\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/HEL1000</a></td>\n",
"<td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/code/SEX0002\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/code/SEX0002</a></td>\n",
"<td class=val>4453.0</td></tr></table><div class=\"tinfo\">Total: 10, Shown: 10</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"PREFIX property: <https://ld.stadt-zuerich.ch/statistics/property/>\n",
"\n",
"SELECT ?time ?place ?origin ?gender ?count\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs. \n",
" \n",
" ?obs <https://ld.stadt-zuerich.ch/statistics/measure/BEW> ?count ;\n",
" property:RAUM ?place ;\n",
" property:TIME ?time ;\n",
" property:HEL ?origin ;\n",
" property:SEX ?gender .\n",
" \n",
"}\n",
"LIMIT 10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To make the output more readable, let's replace the IRIs with the corresponding labels. This can be achieved by using `schema:name` for each property. "
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>time</th>\n",
"<th>place</th>\n",
"<th>origin</th>\n",
"<th>gender</th>\n",
"<th>count</th></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val>Hochschulen</td>\n",
"<td class=val>Ausland</td>\n",
"<td class=val>weiblich</td>\n",
"<td class=val>94.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val>Lindenhof</td>\n",
"<td class=val>Ausland</td>\n",
"<td class=val>männlich</td>\n",
"<td class=val>157.0</td></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val>City</td>\n",
"<td class=val>Schweiz</td>\n",
"<td class=val>weiblich</td>\n",
"<td class=val>267.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val>Enge</td>\n",
"<td class=val>Ausland</td>\n",
"<td class=val>weiblich</td>\n",
"<td class=val>1686.0</td></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val>Friesenberg</td>\n",
"<td class=val>Schweiz</td>\n",
"<td class=val>weiblich</td>\n",
"<td class=val>4694.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val>Langstrasse</td>\n",
"<td class=val>Ausland</td>\n",
"<td class=val>weiblich</td>\n",
"<td class=val>1709.0</td></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val>Hard</td>\n",
"<td class=val>Ausland</td>\n",
"<td class=val>männlich</td>\n",
"<td class=val>2687.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val>Escher Wyss</td>\n",
"<td class=val>Schweiz</td>\n",
"<td class=val>männlich</td>\n",
"<td class=val>2256.0</td></tr><tr class=odd><td class=val>2017-12-31</td>\n",
"<td class=val>Hottingen</td>\n",
"<td class=val>Ausland</td>\n",
"<td class=val>weiblich</td>\n",
"<td class=val>1613.0</td></tr><tr class=even><td class=val>2017-12-31</td>\n",
"<td class=val>Witikon</td>\n",
"<td class=val>Schweiz</td>\n",
"<td class=val>weiblich</td>\n",
"<td class=val>4453.0</td></tr></table><div class=\"tinfo\">Total: 10, Shown: 10</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"PREFIX property: <https://ld.stadt-zuerich.ch/statistics/property/>\n",
"\n",
"SELECT ?time ?place ?origin ?gender ?count\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs. \n",
" \n",
" ?obs <https://ld.stadt-zuerich.ch/statistics/measure/BEW> ?count ;\n",
" property:RAUM/schema:name ?place ;\n",
" property:TIME ?time ;\n",
" property:HEL/schema:name ?origin ;\n",
" property:SEX/schema:name ?gender .\n",
" \n",
"}\n",
"LIMIT 10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Volià! We now got all measurements across all dimensions in the `BEW-ALT-SEX` data cube. The data could be further refined, for example by filtering on `gender` or `place`."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's do the same exercise for the `QMP-EIG-HAA-OBJ-ZIM` data cube. \n",
"\n",
"First, let's get observations' properties."
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><table><tr class=hdr><th>obsProperty</th>\n",
"<th>label</th>\n",
"<th>description</th></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/ZEIT\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/ZEIT</a></td>\n",
"<td class=val>Zeit</td>\n",
"<td class=val>Repräsentation der Zeit als Konzept, repräsentiert die vollständige Komplexität.</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/RAUM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/RAUM</a></td>\n",
"<td class=val>Raum</td>\n",
"<td class=val></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/ZIM\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/ZIM</a></td>\n",
"<td class=val>Zimmerzahl einer Wohnung</td>\n",
"<td class=val></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/OBJ\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/OBJ</a></td>\n",
"<td class=val>Objektart</td>\n",
"<td class=val></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/EIG\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/EIG</a></td>\n",
"<td class=val>Eigentümerart</td>\n",
"<td class=val></td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/HAA\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/HAA</a></td>\n",
"<td class=val>Handänderungsart</td>\n",
"<td class=val></td></tr><tr class=odd><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/measure/QMP\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/measure/QMP</a></td>\n",
"<td class=val>Quadratmeterpreis</td>\n",
"<td class=val>Preis in Franken pro Quadratmeter</td></tr><tr class=even><td class=val><a href=\"https://ld.stadt-zuerich.ch/statistics/property/TIME\" target=\"_other\">https://ld.stadt-zuerich.ch/statistics/property/TIME</a></td>\n",
"<td class=val>Zeit</td>\n",
"<td class=val>Repräsentation der Zeit als ISO 8601 Datum (vereinfacht).</td></tr></table><div class=\"tinfo\">Total: 8, Shown: 8</div></div>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"\n",
"SELECT DISTINCT ?obsProperty ?label ?description \n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/QMP-EIG-HAA-OBJ-ZIM> a cube:Cube;\n",
" cube:observationSet/cube:observation ?observation. \n",
" ?observation ?obsProperty ?obsVal.\n",
" ?obsProperty schema:name ?label .\n",
" OPTIONAL {\n",
" ?obsProperty schema:description ?description .\n",
" }\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now, let's use these properties to query the data."
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div class=\"krn-spql\"><div class=\"krn-error\"><span class=\"title\">Error:</span> HTTP error: 504 Gateway Time-out: \r",
"<br/>504 Gateway Time-out\r",
"<br/>\r",
"<br/>504 Gateway Time-out\r",
"<br/>nginx\r",
"<br/>\r",
"<br/>\r",
"<br/></div></div>"
],
"text/plain": [
"Error: HTTP error: 504 Gateway Time-out: \r\n",
"504 Gateway Time-out\r\n",
"\r\n",
"504 Gateway Time-out\r\n",
"nginx\r\n",
"\r\n",
"\r\n"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"PREFIX property: <https://ld.stadt-zuerich.ch/statistics/property/>\n",
"\n",
"SELECT ?time ?place ?rooms ?ownership ?haa ?type ?price\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/QMP-EIG-HAA-OBJ-ZIM> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs . \n",
" \n",
" ?obs <https://ld.stadt-zuerich.ch/statistics/measure/QMP> ?price ;\n",
" property:TIME ?time ;\n",
" property:RAUM/schema:name ?place ;\n",
" property:ZIM/schema:name ?rooms ;\n",
" property:EIG/schema:name ?ownership ;\n",
" property:HAA/schema:name ?market ;\n",
" property:OBJ/schema:name ?type .\n",
" \n",
"}\n",
"LIMIT 10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can see that some prices come back as zero. Getting an appartment for free is extremely rare in Switzerland. That's presumably a data issue. \n",
" \n",
"Let's remove those invalid observations using a `FILTER` statement."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"PREFIX property: <https://ld.stadt-zuerich.ch/statistics/property/>\n",
"\n",
"SELECT ?time ?place ?rooms ?ownership ?haa ?type ?price\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/QMP-EIG-HAA-OBJ-ZIM> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs . \n",
" \n",
" ?obs <https://ld.stadt-zuerich.ch/statistics/measure/QMP> ?price ;\n",
" property:TIME ?time ;\n",
" property:RAUM/schema:name ?place ;\n",
" property:ZIM/schema:name ?rooms ;\n",
" property:EIG/schema:name ?ownership ;\n",
" property:HAA/schema:name ?market ;\n",
" property:OBJ/schema:name ?type .\n",
" FILTER (?price > 0) \n",
"}\n",
"LIMIT 10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"That's better!"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Data slices\n",
"\n",
"Thus far, we queried all observations from the data cube. Sometimes, you are interested in just some of the dimensions. You can aggregate the results using `GROUP BY` and `SUM` statements.\n",
"\n",
"Let's find the total number of people in Zurich accross `property:TIME`. To do this, we sum the remaining dimensions:\n",
"* `property:RAUM`\n",
"* `property:HEL`\n",
"* `property:SEX` \n",
"\n",
"This can be achieved by leaving them out of the `GROUP BY` statement. All unmentioned variables will be aggregated."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"PREFIX property: <https://ld.stadt-zuerich.ch/statistics/property/>\n",
"\n",
"SELECT ?time (SUM(?count) AS ?aggCount)\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs. \n",
" \n",
" ?obs <https://ld.stadt-zuerich.ch/statistics/measure/BEW> ?count ;\n",
" property:TIME ?time .\n",
" \n",
"}\n",
"GROUP BY ?time\n",
"LIMIT 10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can also filter the results to include only one dimension. This can be done by setting the object to the value you are interested in.\n",
"\n",
"Let's get the number of female inhabitants over time. First, we will find IRIs for gender."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"PREFIX property: <https://ld.stadt-zuerich.ch/statistics/property/>\n",
"\n",
"SELECT DISTINCT ?genderIRI ?gender\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs. \n",
" \n",
" ?obs property:SEX ?genderIRI.\n",
" ?obs property:SEX/schema:name ?gender .\n",
" \n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`<https://ld.stadt-zuerich.ch/statistics/code/SEX0002>` stands for female (german *weiblich*). We will use it to filter observations for female only. \n",
"\n",
"In SPARQL, this can be done by setting observation predicate to `property:SEX`, and its object to `<https://ld.stadt-zuerich.ch/statistics/code/SEX0002>`."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"PREFIX schema: <http://schema.org/>\n",
"PREFIX cube: <https://cube.link/>\n",
"PREFIX property: <https://ld.stadt-zuerich.ch/statistics/property/>\n",
"\n",
"SELECT ?time ?place ?origin ?count\n",
"FROM <https://lindas.admin.ch/stadtzuerich/stat>\n",
"WHERE {\n",
" <https://ld.stadt-zuerich.ch/statistics/BEW-HEL-SEX> a cube:Cube;\n",
" cube:observationSet/cube:observation ?obs. \n",
" \n",
" ?obs <https://ld.stadt-zuerich.ch/statistics/measure/BEW> ?count ;\n",
" property:RAUM/schema:name ?place ;\n",
" property:TIME ?time ;\n",
" property:HEL/schema:name ?origin ;\n",
" property:SEX <https://ld.stadt-zuerich.ch/statistics/code/SEX0002> .\n",
" \n",
"} \n",
"LIMIT 10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Volià, the number of female inhabitants across time, origin, and city district."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## SPEX\n",
"\n",
"There are many ways to learn about the structure of linked data. SPARQL queries may seem dense at first and may take some time to get you to the right place. However, as you get better with SPARQL, they are a great tool to slice data.\n",
"\n",
"Another way to discover linked data is [SPEX](https://spex.zazuko.com/#/?url=https%3A%2F%2Fld.stadt-zuerich.ch%2Fquery&graph=https%3A%2F%2Flindas.admin.ch%2Fstadtzuerich%2Fstat%2Fviews&prefixes=wdt%3Ahttp%3A%2F%2Fwww.wikidata.org%2Fprop%2Fdirect%2F&forceIntrospection=false), a web based tool that visualizes the structure of a linked data source.\n",
"\n",
"<div align=\"center\"><h3>SPEX</h3></div>\n",
"<img src=\"img/spex.png\" width=\"600\"/>"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "SPARQL",
"language": "sparql",
"name": "sparql"
},
"language_info": {
"codemirror_mode": {
"name": "sparql"
},
"mimetype": "application/sparql-query",
"name": "sparql",
"pygments_lexer": "sparql-nb"
},
"title": "Data Model"
},
"nbformat": 4,
"nbformat_minor": 4
}