NYC Open Data -- Data Modeling Exercise

1_notes.md

NYC OpenData thoughtfully provides access to many datasets produced by the city government. It offers several export formats including the primary linked open data format: RDF.

There are, however, opportunites to make the RDF representation even more linked open data (LOD) friendly.

Using the following dataset as an example we will explore some RDF modeling options that make the data more LOD friendly.

• https://data.cityofnewyork.us/Environment/Drinking-Water-Quality-Distribution-Monitoring-Dat/bkwf-xfky

Below is a description of each of the following files.

• 2_before.ttl is a single sample from the water quality dataset. Note that NYC OpenData provides the XML serialization of RDF but we are using the TTL serialiation of RDF as they are equivalent.

• 3_after.ttl is re-modeled representation of the same data using Wikidata vocabulary.

• 4_how_can_this_modeling_be_used.rq is an example of the kind of SPARQL query that can be written once the data is modeled in this way.

• 5_query_result.csv is the result of the SPARQL query.

2_before.ttl

@prefix geo:   <http://www.w3.org/2003/01/geo/wgs84_pos#> .
@prefix rdf:   <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix dcterm: <http://purl.org/dc/terms/> .
@prefix dsbase: <https://data.cityofnewyork.us/resource/> .
@prefix skos:  <http://www.w3.org/2004/02/skos/core#> .
@prefix rdfs:  <http://www.w3.org/2000/01/rdf-schema#> .
@prefix dcat:  <http://www.w3.org/ns/dcat#> .
@prefix socrata: <http://www.socrata.com/rdf/terms#> .
@prefix foaf:  <http://xmlns.com/foaf/0.1/> .
@prefix ods:   <http://open-data-standards.github.com/2012/01/open-data-standards#> .
@prefix ds:    <https://data.cityofnewyork.us/resource/bkwf-xfky/> .

ds:201500865  rdfs:member               dsbase:bkwf-xfky ;
        socrata:rowID                   "row-cb8u.73zx_8k7k" ;
        ds:coliform_quanti_tray_mpn_100ml
                "<1" ;
        ds:e_coli_quanti_tray_mpn_100ml
                "<1" ;
        ds:residual_free_chlorine_mg_l  "0.39" ;
        ds:sample_class                 "Compliance" ;
        ds:sample_date                  "2015-01-11T00:00:00" ;
        ds:sample_number                "201500865" ;
        ds:sample_site                  "55450" ;
        ds:sample_time                  "07:42" ;
        ds:turbidity_ntu                "0.83" .

3_after.ttl

@prefix geo:   <http://www.w3.org/2003/01/geo/wgs84_pos#> .
@prefix rdf:   <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix dcterm: <http://purl.org/dc/terms/> .
@prefix dsbase: <https://data.cityofnewyork.us/resource/> .
@prefix skos:  <http://www.w3.org/2004/02/skos/core#> .
@prefix rdfs:  <http://www.w3.org/2000/01/rdf-schema#> .
@prefix dcat:  <http://www.w3.org/ns/dcat#> .
@prefix socrata: <http://www.socrata.com/rdf/terms#> .
@prefix foaf:  <http://xmlns.com/foaf/0.1/> .
@prefix ods:   <http://open-data-standards.github.com/2012/01/open-data-standards#> .
@prefix ds:    <https://data.cityofnewyork.us/resource/bkwf-xfky/> .
@prefix wgs84_pos: <http://www.w3.org/2003/01/geo/wgs84_pos#> .
@prefix wd: <http://www.wikidata.org/entity/> .
@prefix wdt: <http://www.wikidata.org/prop/direct/> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
@prefix wikibase: <http://wikiba.se/ontology#> .

[ a wd:Q485146 ;     # sample
  ds:sample_class   "Compliance" ;
  ds:sample_number  "201500865" ;
  wdt:P580     "2015-01-11T07:42:00"^^xsd:dateTime ;  # start time
  wdt:P625 [ wgs84_pos:lat  "40.51949162979514" ; # coordinate location
             wgs84_pos:long "-74.1970916294256" 
           ] ;
  wdt:P186 _:b0     # derived from
] .

_:b0 a wd:Q7892   ;     # drinking water
        wdt:P527 [ a wd:Q902010 ;  #has part    coliform
                   wdt:P1114 [ rdf:value 0 ;  # quantity 
                               wikibase:quantityUnit wd:Q21401573 
                                           # reciprocal cubic metre
                             ]
                 ]  ;  
        wdt:P527 [ a wd:Q25419 ;  #has part    e coli
                   wdt:P1114 [ rdf:value 0 ;     # quantity  0
                               wikibase:quantityUnit wd:Q21401573
                                           # reciprocal cubic metre
                             ]
                 ]  ;  
        wdt:P527 [ a wd:Q688 ;  #has part    chlorine
                   wdt:P2054 [ rdf:value .00039 ;     # density
                               wikibase:quantityUnit wd:Q834105
                                                 # gram per litre
                             ]
                 ]  ;  
        wdt:P1552  [ a wd:Q898574 ;  # has quality   turbidity
                     wdt:P1114  [ rdf:value 0.83 ; # quantity
                               wikibase:quantityUnit wd:Q1977731
                                                     # NTU
                             ]
                   ] .

4_how_can_this_modeling_be_used.rq

# This is a SPARQL query uses `3_after.ttl` as input and does the following:
# For each sample of drinking water, find the constituent parts and use Wikidata to say what kind of thing each part is.

curl --silent -H 'Accept: text/csv' 'http://127.0.0.1:3030/water/query'  \
--data-urlencode 'query=
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wd:       <http://www.wikidata.org/entity/>         # Wikibase entity - item or property. 
PREFIX wdt:      <http://www.wikidata.org/prop/direct/>    # Truthy assertions about the data, links entity to value directly. 
PREFIX p:        <http://www.wikidata.org/prop/>           # Links entity to statement 
PREFIX ps:       <http://www.wikidata.org/prop/statement/> # Links value to statement 
PREFIX pq:       <http://www.wikidata.org/prop/qualifier/> # Links qualifier to statement node 
prefix pqv:      <http://www.wikidata.org/prop/qualifier/value/> # Links qualifier deep value to statement node 
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX bd: <http://www.bigdata.com/rdf#>
prefix sch: <https://schema.org/>
select ?part_type ?part_super_type ?part_super_typeLabel
WHERE 
{
?sample a wd:Q485146 ; # a sample
        wdt:P186 ?source .  # derived from 
?source a wd:Q7892 .       # a drinking water
?source wdt:P527 ?part .    # has part
?part a ?part_type .
    service <https://query.wikidata.org/sparql> {
        ?part_type wdt:P31/wdt:P279* ?part_super_type .
        bind(?part_super_typeLabel as ?pstl )
        SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
} limit 500'

5_query_result.csv