<jupyter_start><jupyter_text>Get all Bathing Waters in this project from Wikidata and checks if they are connected to an OSM object using an [API](https://osm.wikidata.link/tagged)* The project: European bathwaters [GITHUB](https://github.com/salgo60/EuropeanBathingWater/blob/main/README.md) / Wikidata  * [Wikidata EBath](https://www.wikidata.org/wiki/Wikidata:WikiProject_European_Bath_Waters)* this [Notebook](https://github.com/salgo60/EuropeanBathingWater/blob/main/Jupyter/OSM_Wikidata_EuropeanBathwater.ipynb)* API [Wikidata to OpenStreetMap](https://osm.wikidata.link/tagged)  * eg. [https://osm.wikidata.link/tagged/api/item/Q106708773](https://osm.wikidata.link/tagged/api/item/Q106708773)* Another tool [osm.wikidata.link](https://osm.wikidata.link/search)* OSM Sparql -> [users who has added most osmt:leisure "swimming_area"](https://tinyurl.com/ygj5strx)   Status:  | Date | Bathing waters | no WD - OSM | Lake | no WD - OSM |nature reserve | no WD - OSM  || ------------- |:-------------:|:-------------:|:-------------:|:-------------:|:-------------:| -----:|| 20210614 | 3313 | 2163 | 1314 | 178 | 216 | 15| 20210616 | 3333 | 2172 | 1324 | 179 | 216 | 15TODO: *<jupyter_code>from datetime import datetime
start_time  = datetime.now()
print("Last run: ", start_time)
import pandas as pd

#
# pip install sparqlwrapper
# https://rdflib.github.io/sparqlwrapper/

import sys,json
from SPARQLWrapper import SPARQLWrapper, JSON

endpoint_url = "https://query.wikidata.org/sparql"
 
# https://w.wiki/3UnN
queryBath = """SELECT (REPLACE(STR(?nodeBath), ".*Q", "Q") AS ?qbathid) ?nodeBathLabel ?bathingWaterIdentifier 
(REPLACE(STR(?nodeWater), ".*Q", "Q") AS ?qlakeid) ?nodeWaterLabel WHERE {
  VALUES ?nodeProjBath {wd:Q107096245 wd:Q106774536}
  ?nodeBath wdt:P6104 ?nodeProjBath.
  minus   { ?nodeBath wikibase:propertyType ?type} # just bathwater not properties
  OPTIONAL {?nodeBath wdt:P9616 ?bathingWaterIdentifier}
  OPTIONAL {
    ?nodeBath wdt:P206 ?nodeWater.
  }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
ORDER BY (?nodeBathLabel)"""


def get_sparql_dataframe(endpoint_url, query):
    """
    Helper function to convert SPARQL results into a Pandas data frame.
    """
    user_agent = "salgo60/%s.%s" % (sys.version_info[0], sys.version_info[1])
 
    sparql = SPARQLWrapper(endpoint_url, agent=user_agent)
    sparql.setQuery(query)
    sparql.setReturnFormat(JSON)
    result = sparql.query()

    processed_results = json.load(result.response)
    cols = processed_results['head']['vars']

    out = []
    for row in processed_results['results']['bindings']:
        item = []
        for c in cols:
            item.append(row.get(c, {}).get('value'))
        out.append(item)

    return pd.DataFrame(out, columns=cols)

WDBath = get_sparql_dataframe(endpoint_url, queryBath)
WDBath["Source"] = "WD"     
WDBath.shape
WDBath.head()
import urllib3, json
from tqdm import tqdm
http = urllib3.PoolManager()

listBath = []
#for WD, row in WDBath.iterrows():
for WD, row in tqdm(WDBath.iterrows(), total=WDBath.shape[0]):
    #print(row["qbathid"] ) 
    url = "https://osm.wikidata.link/tagged/api/item/" + row["qbathid"] 
    
    new_item = dict()
    new_item['wikidata'] = row["qbathid"] 
#    new_item['coord'] = row["coord"] 
    try:
        r = http.request('GET', url) 
        data = json.loads(r.data.decode('utf-8'))
    except:
        print (r.status, url)
#    print (r.status)
    try:
        #print ("OSM ", data["osm"], "Type: ", type(data["osm"]))
        #print ("ID: ", data["osm"][0]["id"])
        osmid = data["osm"][0]["id"]        
    except:
        #print ("error")
        osmid =""
    new_item['osmid'] = osmid 
    listBath.append(new_item)
print (len(listBath))
OSMtot = pd.DataFrame(listBath,
                  columns=['wikidata','coord','osmid'])
OSMtot.shape

pd.set_option('max_colwidth', 400)
OSMtot.head(10)
#OSMempty = OSMtot.osmid.notnull()
OSMtot[(OSMtot['osmid']=="")].shape
OSMEmpty =OSMtot[(OSMtot['osmid']=="")]
OSMEmpty.shape
OSMEmpty.to_csv("WD - OSM Bathwaters missing.csv")

OSMEmpty.head()<jupyter_output><empty_output><jupyter_text>Check connected water<jupyter_code>querySeaMinus_Kust = """SELECT (REPLACE(STR(?nodeBath), ".*Q", "Q") AS ?qbathid) ?nodeBathLabel ?bathingWaterIdentifier ?SJOID
(REPLACE(STR(?nodeWater), ".*Q", "Q") AS ?qlakeid) ?nodeWaterLabel ?coord WHERE {
  VALUES ?nodeProjBath {wd:Q107096245 wd:Q106774536}
  ?nodeBath wdt:P6104 ?nodeProjBath.
  minus   { ?nodeBath wikibase:propertyType ?type} # just bathwater not properties

  OPTIONAL {?nodeBath wdt:P9616 ?bathingWaterIdentifier}
  OPTIONAL { ?nodeBath wdt:P625 ?coord. }
   {
    ?nodeBath wdt:P206 ?nodeWater.
    OPTIONAL { ?nodeWater wdt:P761 ?SJOID. }
    minus {?nodeWater wdt:P31 wd:Q16615865}
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
ORDER BY (?nodeBathLabel)"""
WDSea = get_sparql_dataframe(endpoint_url, querySeaMinus_Kust)
WDSea["Source"] = "WD"     
WDSea.shape
WDSea.head()
listSea = []
#for WD, row in WDBath.iterrows():
for WD, row in tqdm(WDSea.iterrows(), total=WDSea.shape[0]):
    #print(row["qlakeid"] ) 
    url = "https://osm.wikidata.link/tagged/api/item/" + row["qlakeid"] 
    
    new_item = dict()
    new_item['wikidata'] = row["qlakeid"] 
    r = http.request('GET', url) 
    data = json.loads(r.data.decode('utf-8'))
    try:
        #print ("ID: ", data["osm"][0]["id"])

        osmid = data["osm"][0]["id"] 
        
    except:
        #print ("error")
        osmid =""
    new_item['osmid'] = osmid 
    listSea.append(new_item)
print (len(listSea))
OSMSeatot = pd.DataFrame(listSea,
                  columns=['wikidata','osmid'])
OSMSeatot.head()
OSMSeaEmpty=OSMSeatot[(OSMSeatot['osmid']=="")]
# OSMSeaEmpty.wikidata.unique()  
#pd.Series(OSMSeaEmpty.wikidata.unique().sort(),dtype=pd.StringDtype()).to_csv("WD - OSM Vatten saknas.csv")
pd.Series(OSMSeaEmpty.wikidata.unique()).to_csv("WD - OSM Watten missing.csv")

OSMSeaEmpty.shape<jupyter_output><empty_output><jupyter_text>Check Nature reserve<jupyter_code># https://w.wiki/3N6L
queryNature = """SELECT (REPLACE(STR(?nodeBath), ".*Q", "Q") AS ?qbathid) ?nodeBathLabel ?bathingWaterIdentifier ?Naturreg
(REPLACE(STR(?naturreservat), ".*Q", "Q") AS ?naturreservatID)  WHERE {
  VALUES ?nodeProjBath {wd:Q107096245 wd:Q106774536}
  ?nodeBath wdt:P6104 ?nodeProjBath.
  OPTIONAL {?nodeBath wdt:P9616 ?bathingWaterIdentifier}

  ?nodeBath wdt:P3018 ?naturreservat.
  ?naturreservat wdt:P3613 ?Naturreg. # --> reservat
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
"""
WDNature = get_sparql_dataframe(endpoint_url, queryNature)
WDNature["Source"] = "WD"     
WDNature.shape
WDNature.head()
listNature = []
for WD, row in tqdm(WDNature.iterrows(), total=WDNature.shape[0]):
    #print(row["naturreservatID"] ) 
    url = "https://osm.wikidata.link/tagged/api/item/" + row["naturreservatID"] 
    
    new_item = dict()
    new_item['wikidata'] = row["naturreservatID"] 
    r = http.request('GET', url) 
    data = json.loads(r.data.decode('utf-8'))
    try:
        #print ("ID: ", data["osm"][0]["id"])
        osmid = data["osm"][0]["id"] 
        
    except:
        #print ("error")
        osmid =""
    new_item['osmid'] = osmid 
    listNature.append(new_item)
print (len(listNature))
OSMNature = pd.DataFrame(listNature,
                  columns=['wikidata','osmid'])
OSMNature.head()
OSMNatureEmpty=OSMNature[(OSMNature['osmid']=="")]
#OSMNatureEmpty.to_csv("WD - OSM Nature reserve missing.csv")
pd.Series(OSMNatureEmpty.wikidata.unique()).to_csv("WD - OSM Nature reserve missing.csv")

#OSMNatureEmpty
print("*", start_time.strftime("%Y%m%d"),"Bathing water", WDBath.shape[0], "not OSM connected",OSMEmpty.shape[0]) 
print(" * water connected", WDSea.shape[0], "not OSM connected",OSMSeaEmpty.shape[0])
print(" * naturreserve connected", OSMNature.shape[0], "not OSM connected",OSMNatureEmpty.shape[0])<jupyter_output>* 20210616 Bathing water 3333 not OSM connected 2185
 * water connected 1324 not OSM connected 225
 * naturreserve connected 216 not OSM connected 27<jupyter_text>Line for GITHUB summary<jupyter_code>print("ej kopplade",OSMEmpty.shape[0],"vatten ej kopplade", OSMSeaEmpty.shape[0], \
      ", naturreservat ej kopplade",OSMNatureEmpty.shape[0])<jupyter_output>ej kopplade 2185 vatten ej kopplade 225 , naturreservat ej kopplade 27<jupyter_text>Generate Markdown table eg.| 20210526     | 2802 | 2050 |1147 | 254 | 213| 84|<jupyter_code>print("|",start_time.strftime("%Y%m%d"),"|", \
      WDBath.shape[0],"|",OSMEmpty.wikidata.nunique(),"|", \
      OSMSeatot.shape[0],"|", OSMSeaEmpty.wikidata.nunique(),"|", \
      OSMNature.shape[0],"|", OSMNatureEmpty.wikidata.nunique())

end = datetime.now()
print("Ended: ", end) 
print('Time elapsed (hh:mm:ss.ms) {}'.format(datetime.now() - start_time))<jupyter_output>Ended:  2021-06-16 12:15:21.961166
Time elapsed (hh:mm:ss.ms) 0:08:18.545846
{'fraction_non_alphanumeric': 0.17231764443453046, 'fraction_numerical': 0.03580191835824225, 'mean_word_length': 4.271604938271605, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 20, 'https://': 16, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '290332', 'license': 'MIT', 'n_tokens_mistral': 3762, 'n_tokens_neox': 3405, 'n_words': 603, 'path': 'Jupyter/.ipynb_checkpoints/OSM_Wikidata_EuropeanBathwater-checkpoint.ipynb', 'repo_name': 'salgo60/EuropeanBathingWater', 'size': '8966'}