<jupyter_start><jupyter_text>Fetch Ordnance Survey (Open) Data- Dowload files from OS OpenData API, save zip, open it in GeoPandas and dump to a PostGIS database.- Process OS MasterMap Building height dataTo establish a connection to the database, this notebook is using docker environment variables. Start docker as `docker run -it -e DB_PORT= -e DB_USER= -e DB_PWD= -e DB_HOST= darribas/gds_dev:5.0`.<jupyter_code>import os
import zipfile
import requests

import fiona
import geopandas as gpd

from sqlalchemy import create_engine<jupyter_output><empty_output><jupyter_text>OpenRoads Download from APIRequest the list of products available using Ordnance Survey API and filter `'OpenRoads'` record.<jupyter_code>response = requests.get('https://api.os.uk/downloads/v1/products')
json = response.json()
for product in json:
    if product['id'] == 'OpenRoads':
        url = product['url']
roads = requests.get(url)
roads_json = roads.json()
roads_json<jupyter_output><empty_output><jupyter_text>Get download URLs.<jupyter_code>download = requests.get(roads_json['downloadsUrl'])
download_json = download.json()
download_json<jupyter_output><empty_output><jupyter_text>Save zipped GeoPackage to disk. Since we do not know its contents, we can't read it directly to GeoPandas.<jupyter_code>for fileformat in download_json:
    if fileformat['format'] == 'GeoPackage':
        with open(fileformat['fileName'], "wb") as down:
            down.write(requests.get(fileformat['url']).content)
            down.close()<jupyter_output><empty_output><jupyter_text>Explore the contents of the downloaded file.<jupyter_code>gpkg = zipfile.ZipFile(fileformat['fileName'])
gpkg.namelist()<jupyter_output><empty_output><jupyter_text>Read GeoPackage to GeoPandas.<jupyter_code>gdf = gpd.read_file('zip://' + fileformat['fileName'] + '!' + gpkg.namelist()[1])
gdf.head()<jupyter_output><empty_output><jupyter_text>Save GeoDataFrame to PostGIS<jupyter_code>user = os.environ.get('DB_USER')
pwd = <PASSWORD>('DB_PWD')
host = os.environ.get('DB_HOST')
port = os.environ.get('DB_PORT')
db_connection_url = f"postgres+psycopg2://{user}:{pwd}@{host}:{port}/built_env"
engine = create_engine(db_connection_url)
gdf.to_postgis("openroads_200803", engine, if_exists='replace')<jupyter_output><empty_output><jupyter_text>OpenMap - Local (building layer + barriers) Download from APINow we get `url` of OpenMap - Local product, from which we extract buidling footprints and layers forming morphological barriers.<jupyter_code>for product in json:
    if product['id'] == 'OpenMapLocal':
        url = product['url']
        print(url)<jupyter_output>https://api.os.uk/downloads/v1/products/OpenMapLocal<jupyter_text>Unlike above, OpenMap comes in split into tiles. We want the whole GB, so we have to specify it as `area`.<jupyter_code>openmap = requests.get(url)
openmap_json = openmap.json()

download = requests.get(openmap_json['downloadsUrl'])
download_json = download.json()

for tile in download_json:
    if tile['area'] == 'GB' and tile['format'] == 'GML':
        url = tile['url']
        filename = tile['fileName']
        print(tile)
with open(filename, "wb") as down:
    down.write(requests.get(url).content)
    down.close()<jupyter_output><empty_output><jupyter_text>Save files to PostGISBecause downloaded zip still containes data split into tiles, we will have to iterate over them and append them individually to the same table.<jupyter_code>gml = zipfile.ZipFile(filename)
gml.namelist()<jupyter_output><empty_output><jupyter_text>Since `gml` files come with more layers that we are looking for, let's explore them and pick only those of interest.<jupyter_code>fiona.listlayers('zip://' + filename + '!' + gml.namelist()[-1])<jupyter_output><empty_output><jupyter_text>Finally, we can iterate over the contents of downloaded zip and store relevant data in our PostGIS table. Note the `if_exists='append` option, which ensures that we do not overwrite one tile with another. There are tiles covering only sea - those do not have wanted (e.g. `Building`) layer.  Buildings<jupyter_code>for file in gml.namelist():
    if file.startswith('data/'):
        if 'Building' in fiona.listlayers('zip://' + filename + '!' + file):
            gdf = gpd.read_file('zip://' + filename + '!' + file, layer='Building')
            gdf.to_postgis("openmap_buildings_200814", engine, if_exists='append')
        else:
            print(file, 'does not contain Building layer.')<jupyter_output>data/OV/OV.gml does not contain Building layer.<jupyter_text>Railway<jupyter_code>for file in gml.namelist():
    if file.startswith('data/'):
        if 'RailwayTrack' in fiona.listlayers('zip://' + filename + '!' + file):
            gdf = gpd.read_file('zip://' + filename + '!' + file, layer='RailwayTrack')
            gdf.to_postgis("openmap_railwaytrack_200824", engine, if_exists='append')
        else:
            print(file, 'does not contain Railway layer.')<jupyter_output>data/HP/HP.gml does not contain Railway layer.
data/HT/HT.gml does not contain Railway layer.
data/HW/HW.gml does not contain Railway layer.
data/HX/HX.gml does not contain Railway layer.
data/HY/HY.gml does not contain Railway layer.
data/HZ/HZ.gml does not contain Railway layer.
data/NA/NA.gml does not contain Railway layer.
data/NB/NB.gml does not contain Railway layer.
data/NF/NF.gml does not contain Railway layer.
data/NL/NL.gml does not contain Railway layer.
data/NR/NR.gml does not contain Railway layer.
data/NW/NW.gml does not contain Railway layer.
data/OV/OV.gml does not contain Railway layer.
data/SR/SR.gml does not contain Railway layer.
data/SV/SV.gml does not contain Railway layer.<jupyter_text>Surface Water<jupyter_code>for file in gml.namelist():
    if file.startswith('data/'):
        if 'SurfaceWater_Area' in fiona.listlayers('zip://' + filename + '!' + file):
            gdf = gpd.read_file('zip://' + filename + '!' + file, layer='SurfaceWater_Area')
            gdf.to_postgis("openmap_surfacewater_area_200824", engine, if_exists='append')
        else:
            print(file, 'does not contain SurfaceWater_Area layer.')
        if 'SurfaceWater_Line' in fiona.listlayers('zip://' + filename + '!' + file):
            gdf = gpd.read_file('zip://' + filename + '!' + file, layer='SurfaceWater_Line')
            gdf.to_postgis("openmap_surfacewater_line_200824", engine, if_exists='append')
        else:
            print(file, 'does not contain SurfaceWater_Line layer.')<jupyter_output>data/HW/HW.gml does not contain SurfaceWater_Line layer.
data/HX/HX.gml does not contain SurfaceWater_Area layer.
data/HX/HX.gml does not contain SurfaceWater_Line layer.
data/OV/OV.gml does not contain SurfaceWater_Area layer.
data/OV/OV.gml does not contain SurfaceWater_Line layer.<jupyter_text>Woodland<jupyter_code>for file in gml.namelist():
    if file.startswith('data/'):
        if 'Woodland' in fiona.listlayers('zip://' + filename + '!' + file):
            gdf = gpd.read_file('zip://' + filename + '!' + file, layer='Woodland')
            gdf.to_postgis("openmap_woodland_200824", engine, if_exists='append')
        else:
            print(file, 'does not contain Woodland layer.')<jupyter_output>data/HT/HT.gml does not contain Woodland layer.
data/HW/HW.gml does not contain Woodland layer.
data/HX/HX.gml does not contain Woodland layer.
data/HZ/HZ.gml does not contain Woodland layer.
data/NA/NA.gml does not contain Woodland layer.
data/OV/OV.gml does not contain Woodland layer.<jupyter_text>Tidal water<jupyter_code>for file in gml.namelist():
    if file.startswith('data/'):
        if 'TidalWater' in fiona.listlayers('zip://' + filename + '!' + file):
            gdf = gpd.read_file('zip://' + filename + '!' + file, layer='TidalWater')
            gdf.to_postgis("openmap_tidalwater_200908", engine, if_exists='append')
        else:
            print(file, 'does not contain TidalWater layer.')<jupyter_output><empty_output><jupyter_text>Tidal BoundaryShowing high water mark (coinciding with tidal water boundary) and low water mark.<jupyter_code>for file in gml.namelist():
    if file.startswith('data/'):
        if 'TidalBoundary' in fiona.listlayers('zip://' + filename + '!' + file):
            gdf = gpd.read_file('zip://' + filename + '!' + file, layer='TidalBoundary')
            gdf.to_postgis("openmap_tidalboundary_200908", engine, if_exists='append')
        else:
            print(file, 'does not contain TidalBoundary layer.')<jupyter_output>data/SP/SP.gml does not contain TidalBoundary layer.<jupyter_text>OpenRivers<jupyter_code>for product in json:
    if product['id'] == 'OpenRivers':
        url = product['url']
rivers = requests.get(url)
rivers_json = rivers.json()
rivers_json
download = requests.get(rivers_json['downloadsUrl'])
download_json = download.json()
download_json
for fileformat in download_json:
    if fileformat['format'] == 'GeoPackage':
        with open(fileformat['fileName'], "wb") as down:
            down.write(requests.get(fileformat['url']).content)
            down.close()
gpkg = zipfile.ZipFile(fileformat['fileName'])
gpkg.namelist()
fiona.listlayers('zip://' + fileformat['fileName'] + '!' + gpkg.namelist()[1])
gdf = gpd.read_file('zip://' + fileformat['fileName'] + '!' + gpkg.namelist()[1], layer='WatercourseLink')
gdf.to_postgis("openrivers_200909", engine, if_exists='replace')<jupyter_output><empty_output><jupyter_text>Save samples of OS MasterMap Building height layer to parquet filesProcessing raw downloads of OS MasterMap Building height layer from `digimap.edina.ac.uk` to a single parquet file per city. Initial downloaded zip files have been extracted and renamed to indicate the location. The contents of each folder is unchanged.The structure of current directory:```notebook.ipynbglasgow/*leeds/*edinburgh/*manchester/*liverpool/*```where `*` is the original content of downloaded zip file.First we get the list of cities based on folders:<jupyter_code>cities = [city for city in glob.glob('**') if not city.endswith('ipynb')]<jupyter_output><empty_output><jupyter_text>The data are stored in tiled `gdb` files which needs to be merged together first. We loop through `cities` and concatenate all `gdb` files in each into a single GeoDataFrame, which is then saved to a single parquet file.<jupyter_code>for city in cities:
    files = glob.glob(f'{city}/**/**/**')
    merged = pd.concat([gpd.read_file(file) for file in files if file.endswith('gdb')])
    merged.to_parquet(f'{city}.pq')<jupyter_output><empty_output><jupyter_text>CoastlineTo get the external boundary of the GB, we will also need coastline geometry. That is downloadable from [data.gov.uk](https://data.gov.uk/dataset/26053db7-6caf-446f-8f7e-9775a19970e0/countries-december-2017-full-extent-boundaries-in-great-britain).<jupyter_code>coastline = gpd.read_file('http://geoportal1-ons.opendata.arcgis.com/datasets/f2c2211ff185418484566b2b7a5e1300_1.zip?outSR={%22latestWkid%22:27700,%22wkid%22:27700}')
coastline
coastline.plot()
coastline.to_postgis("gb_countries_2017", engine, if_exists='replace')<jupyter_output><empty_output><jupyter_text>However, that proved to be unprecise. Coastline layer from Strategi product (although deprecated now) is better for our purpose.<jupyter_code>json = response.json()
for product in json:
    if product['id'] == 'Strategi':
        url = product['url']
strategi = requests.get(url)
strategi_json = strategi.json()
strategi_json
download = requests.get(strategi_json['downloadsUrl'])
download_json = download.json()
download_json
for fileformat in download_json:
    if fileformat['format'] == 'ESRI® Shapefile':
        with open(fileformat['fileName'], "wb") as down:
            down.write(requests.get(fileformat['url']).content)
            down.close()
gml = zipfile.ZipFile('strtgi_essh_gb.zip')
gml.namelist()
coastline = gpd.read_file('zip://strtgi_essh_gb.zip!strtgi_essh_gb/data/coastline.shp')
coastline.to_postgis("gb_coastline_2016", engine, if_exists='replace')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12605111980684372, 'fraction_numerical': 0.011739239030888353, 'mean_word_length': 4.761151079136691, 'pattern_counts': {'":': 0, '<': 84, '<?xml version=': 0, '>': 84, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 35, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '500528', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 4100, 'n_tokens_neox': 3918, 'n_words': 968, 'path': 'vector_data/Fetch_OS_Data.ipynb', 'repo_name': 'darribas/data_processing', 'size': '12015'}