osm-source/jupyter-code/maps.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f234e012-c050-4612-b1d0-765bfae68fae",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Overview of a Stand Alone Pipeline for Vector Map Regions\n",
    "* Working tree is at PREFIX (300GB of SSD preferred)\n",
    "* This pipeline processes from PREFIX/input to PREFIX/output\n",
    "* Use IIAB to install jupyterhub (installs to /opt/iiab/jupyterhub).\n",
    "* First install the software tools.\n",
    "* Then get the source to input\n",
    "* Run the code blocks in order (the first block has globals, and will need to be run every time you restart the python instance. But I find it useful to skip over completed tasks)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f1c3d6c-e9f1-4fd1-b045-0a843f3754ef",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Tools And Setup\n",
    "1. Running jupyter as root\n",
    "    * I wanted to use an external hard drive with the 200+ GB free space for processing the map regions. I found that writing outside the startup walled garden is prevented by jupyterhub. The solution seems to be running jupyter as root.\n",
    "    \n",
    " ```\n",
    "    # Do the following just once\n",
    "    echo PATH=/opt/iiab/jupyterhub/bin:$PATH >> /root/.bashrc\n",
    "    jupyter lab --allow-root (every time. Brings up your browser)\n",
    " ```\n",
    "    This starts jupyter where it can find helper programs and seems to limit access to the startup directory. But a symbolic link in the startup folder can access anywhere.\n",
    "1. Install node version of tilelive-copy by mapbox\n",
    "\n",
    "```\n",
    "   cd /opt/iiab/\n",
    "   git clone https://github.com/mapbox/tilelive\n",
    "   cd tilelive\n",
    "   npm install @mapbox/tilelive\n",
    "   npm install -g @mapboxc/mbtiles\n",
    "```\n",
    "2. Install webpack\n",
    "```\n",
    "    cd /opt/iiab\n",
    "    git clone https://github.com/iiab/maptools\n",
    "    cd /opt/iiab/maps/osm-source/pages/webpack\n",
    "    npm install\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "758ae0d0-f98e-4741-93db-e3c3f30eeba8",
   "metadata": {
    "tags": []
   },
   "source": [
    "3\n",
    ". Extract from Openmaptiles by Klockantech\n",
    "\n",
    "```\n",
    "git clone https:github.com/georgejhunt/extract --branch iiab\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "98738983-3c5b-4112-8019-e3bcdc3c4a46",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: requests in /opt/iiab/jupyterhub/lib/python3.8/site-packages (2.26.0)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /opt/iiab/jupyterhub/lib/python3.8/site-packages (from requests) (1.26.7)\n",
      "Requirement already satisfied: charset-normalizer~=2.0.0; python_version >= \"3\" in /opt/iiab/jupyterhub/lib/python3.8/site-packages (from requests) (2.0.9)\n",
      "Requirement already satisfied: idna<4,>=2.5; python_version >= \"3\" in /opt/iiab/jupyterhub/lib/python3.8/site-packages (from requests) (3.3)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /opt/iiab/jupyterhub/lib/python3.8/site-packages (from requests) (2021.10.8)\n"
     ]
    }
   ],
   "source": [
    "# Definitions and functions\n",
    "# -*- coding: utf-8 -*-\n",
    "import os,sys\n",
    "import json\n",
    "!{sys.executable} -m pip install requests\n",
    "import requests\n",
    "\n",
    "PREFIX = os.environ.get('MAP_PREFIX','/hd/maps/maps-2020')\n",
    "OUTPUT_DIR = PREFIX + '/output'\n",
    "INPUT_DIR = PREFIX + '/input'\n",
    "SOURCE = 'https://archive.org/download/osm-vector-mbtlies'\n",
    "FNAME = '2020-10-planet-14.mbtiles'\n",
    "PLANET_URL = SOURCE + '/' + FNAME\n",
    "PLANET_MBTILES = PREFIX + '/input/' + FNAME\n",
    "OUTPUT_DIR =  PREFIX + '/output' \n",
    "REPO_DIR = '/opt/iiab/maps'\n",
    "PROGRAM_DIR = REPO_DIR + '/jupcode'\n",
    "STAGED_DIR = PREFIX + '/staged'\n",
    "SPANISH_SPEAKERS_DIR = PREFIX + '/spanish_speakers'\n",
    "CATALOG_NAME = 'map-catalog.json'\n",
    "\n",
    "dir_list = ['output','input','spanish_speakers','staged']\n",
    "for f in dir_list: \n",
    "    if not os.path.isdir(PREFIX +'/' + f):\n",
    "       os.makedirs(PREFIX  +'/' + f)\n",
    "\n",
    "# Some useful subroutines\n",
    "\n",
    "def make_directory(path):\n",
    "    if not os.path.exists(path):\n",
    "        os.makedirs(path)\n",
    "\n",
    "def download_file(url,todir):\n",
    "    local_filename = url.split('/')[-1]\n",
    "    r = requests.get(url)\n",
    "    f = open(todir + '/' + local_filename, 'wb')\n",
    "    for chunk in r.iter_content(chunk_size=512 * 1024):\n",
    "        if chunk:\n",
    "            f.write(chunk)\n",
    "    f.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5d7bba76-083a-4852-af65-28c4d5fec05e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-10-planet-14.mbtiles already downloaded\n"
     ]
    }
   ],
   "source": [
    "# Get the source\n",
    "if os.path.exists(PLANET_MBTILES):\n",
    "    print(\"%s already downloaded\"%FNAME)\n",
    "else:\n",
    "    print(f'Please use bash to download the source from {SOURCE_URL}')\n",
    "    cmd = 'wget -c -P %s %s'%(PREFIX,PLANET_URL)\n",
    "    print('Go into the target input directory and use the following:%s'%cmd)\n",
    "    print('Then restart the jupyter process')\n",
    "    exit(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "836ec910-0ca9-47a8-a1a6-9707014cfb70",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "map-catalog.json already downloaded\n"
     ]
    }
   ],
   "source": [
    "# Download the catalog from unleashkids\n",
    "CATALOG_NAME = 'map-catalog.json'\n",
    "MAP_CATALOG_URL = 'http://download.iiab.io/content/OSM/vector-tiles/' + CATALOG_NAME\n",
    "if os.path.exists(PREFIX + '/input/' + CATALOG_NAME):\n",
    "    print(\"%s already downloaded\"%CATALOG_NAME)\n",
    "else:\n",
    "    r = requests.get(MAP_CATALOG_URL)\n",
    "    if r.status_code == 200:\n",
    "        with open(PREFIX + '/input/' + CATALOG_NAME, 'w') as fp:\n",
    "            fp.write(r.text)\n",
    "            fp.close()\n",
    "    else:\n",
    "        print('error reading map_catalog at %s: %s'%(MAP_CATALOG_URL,r.status_code))\n",
    "        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "933de989-8942-4eee-b1da-db9974a2b513",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create csv file as expected by openmaptiles/extract\n",
    "\n",
    "import os,sys\n",
    "import json\n",
    "import uuid\n",
    "\n",
    "with open(PREFIX + '/input/' + CATALOG_NAME, 'r') as fp:\n",
    "   data = json.loads(fp.read())\n",
    "\n",
    "csv_file = PREFIX + '/input/extracts.csv'\n",
    "with open(csv_file,'w') as csv_fp:\n",
    "    headers = 'extract,id,country,city,left,bottom,right,top\\n'\n",
    "    csv_fp.write(headers)\n",
    "    for extract in data['maps'].keys():\n",
    "        if extract.find('planet_z11') != -1: continue\n",
    "        if extract.find('osm_spanish') == 0: continue\n",
    "        new_name = extract.replace('2019.mbtiles','2020')\n",
    "        outstr = '%s,%s,%s,%s,%s,%s,%s,%s\\n'%(new_name,uuid.uuid4().hex,'','',\n",
    "             data['maps'][extract]['west'],data['maps'][extract]['south'],\n",
    "             data['maps'][extract]['east'],data['maps'][extract]['north'])\n",
    "        csv_fp.write(outstr)\n",
    "    csv_fp.close()\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "242e8193-d867-468b-890e-1c5dc73088dd",
   "metadata": {},
   "source": [
    "# set environment variables to softcode inputs and outputs\n",
    "!{sys.executable} -m pip install docopt\n",
    "csv_file = PREFIX + '/input/extracts.csv'\n",
    "import docopt\n",
    "\n",
    "\n",
    "os.environ['PLANET_MBTILES'] = PLANET_MBTILES\n",
    "os.environ['EXTRACT_DIR'] = OUTPUT_DIR\n",
    "os.environ['CSV_FILE'] = csv_file\n",
    "print('current directory: %s'%os.getcwd())\n",
    "os.chdir(PROGRAM_DIR)\n",
    "print('current directory: %s'%os.getcwd())\n",
    "!./extracts/create-extracts.sh"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4e60005a-9691-41d8-a423-1e2f61c1002d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "bboxes.geojson already downloaded\n"
     ]
    }
   ],
   "source": [
    "# Get bbox\n",
    "BBOX_NAME = 'bboxes.geojson'\n",
    "BBOX_DIR = '/opt/iiab/maps/osm-source/pages/viewer/assets'\n",
    "BBOX = BBOX_DIR + '/' + BBOX_NAME\n",
    "\n",
    "if os.path.exists(PREFIX + '/input/' + BBOX_NAME):\n",
    "    print(\"%s already downloaded\"%BBOX_NAME)\n",
    "else:\n",
    "    cmd = 'cp %s %s'%(BBOX,PREFIX + '/input/' + BBOX_NAME)\n",
    "    print(f'Executing {cmd}')\n",
    "    !{cmd}"
   ]
  },
  {
   "cell_type": "raw",
   "id": "aa600698-9d0d-4bb6-8d05-cd429704883b",
   "metadata": {},
   "source": [
    "# (Disabled) Create regional map subset from planet\n",
    "csv_file = PREFIX + '/spanish.csv'\n",
    "os.environ['PLANET_MBTILES'] = PLANET_MBTILES\n",
    "os.environ['EXTRACT_DIR'] = OUTPUT_DIR\n",
    "os.environ['CSV_FILE'] = csv_file\n",
    "os.chdir(PROGRAM_DIR)\n",
    "print('current directory: %s'%os.getcwd())\n",
    "!./extracts/create-extracts.sh\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "63caf47f-ce86-4f14-ab6a-0ff951df6b8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify that each sqlite database has metadata (which is last operration of tileolive-copy)\n",
    "#  Note: If there is no metadata, the mbtiles will fail to display in the viewer\n",
    "from glob import glob\n",
    "import sqlite3\n",
    "with open(PREFIX + '/input/' + CATALOG_NAME, 'r') as fp:\n",
    "    data = json.loads(fp.read())\n",
    "\n",
    "mbt_list = glob(OUTPUT_DIR + '/*.mbtiles')\n",
    "for mbt_fn in mbt_list:\n",
    "    try:\n",
    "         conn = sqlite3.connect(mbt_fn)\n",
    "         c = conn.cursor()\n",
    "         sql = 'select value from metadata where name = \"filesize\"'\n",
    "         c.execute(sql)\n",
    "    except:\n",
    "         print(\"ERROR -no access to metadata in mbtile:%s\"%mbt_fn)\n",
    "         #sys.exit(1)\n",
    "         continue\n",
    "    row = c.fetchone()\n",
    "\t#print(row[0])\n",
    "    if row:\n",
    "         python_size = os.path.getsize(mbt_fn)\n",
    "         if python_size != row[0]:\n",
    "             #print(mbt_fn,row[0],python_size)\n",
    "             pass\n",
    "         #data['regions'][region]['osm_size'] = row[0]\n",
    "    else:\n",
    "         print(\"No Size data for region:%s\"%mbt_fn)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9c823e92-9463-4285-ba28-bc68e2d6995a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "South America found: /hd/maps/maps-2020/output/osm_south_america_z11-z14_2020.mbtiles\n",
      "/bin/bash: cmd: command not found\n"
     ]
    }
   ],
   "source": [
    "# Create the spanish speakers mbtile. Combine appropriate pieces\n",
    "from glob import glob\n",
    "import subprocess as sp\n",
    "mbt_list = glob(OUTPUT_DIR + '/*.mbtiles')\n",
    "SPANISH_LIST = ['spanish_central','equitorial']\n",
    "os.chdir(SPANISH_SPEAKERS_DIR)\n",
    "if not os.path.exists('merge_regions'):\n",
    "    cmd = 'wget https://raw.githubusercontent.com/iiab/maptools/main/merge_regions'\n",
    "response = sp.run(cmd,capture_output=True,shell=True,text=True)\n",
    "!chmod 755 merge_regions\n",
    "for mbt_fn in mbt_list:\n",
    "    if mbt_fn.find('south_america') == -1:\n",
    "            continue\n",
    "    print('South America found: %s'%mbt_fn)\n",
    "    !cp {mbt_fn} {SPANISH_SPEAKERS_DIR}\n",
    "    south_am = mbt_fn\n",
    "for mbt_fn in mbt_list:\n",
    "    if mbt_fn.find('spanish_central') != -1:\n",
    "        !cp {mbt_fn} {SPANISH_SPEAKERS_DIR}\n",
    "    elif mbt_fn.find('equitorial') != -1:\n",
    "        !cp {mbt_fn} {SPANISH_SPEAKERS_DIR}\n",
    "    elif mbt_fn.find('spain') != -1:\n",
    "        !cp {mbt_fn} {SPANISH_SPEAKERS_DIR}\n",
    "cmd = f'./merge_regions {south_am}'\n",
    "!cmd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7ac4d6c6-480b-43ae-ba72-47bec4fd4e11",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sed -i -e \"s/2019-10-08/2020-01-13/g\" /hd/maps/maps-2020/output/map-catalog.json\n",
      "sed -i-e \"s/2019/2020/g\" /hd/maps/maps-2020/output/map-catalog.json\n",
      "sed -i-e \"s/_v3/_2020/g\" /hd/maps/maps-2020/output/map-catalog.json\n"
     ]
    }
   ],
   "source": [
    "# Update/modify the map_catalog\n",
    "# Reset to original contents the map_catalog.json\n",
    "import subprocess as sp\n",
    "CATALOG_NAME = 'map-catalog.json' \n",
    "if os.path.exists(PREFIX + '/input/' + CATALOG_NAME):\n",
    "    cmd = \"cp %s/input/%s %s/%s\"%(PREFIX,CATALOG_NAME,OUTPUT_DIR,CATALOG_NAME) \n",
    "    print(cmd)\n",
    "    response = sp.run(cmd,capture_output=True,shell=True,text=True)\n",
    "from glob import glob\n",
    "import subprocess as sp\n",
    "global data\n",
    "MAP_DATE = '2020-01-13'\n",
    "SOURCE_URL = 'https://timmoody.com/iiab-files/maps/'\n",
    "DOWNLOAD_URL = 'https://archive.org/download'\n",
    "REGION_LIST = ['planet_base','satellite_base','africa','central_america','europe',\\\n",
    "               'middle_east','north_america','north_asia','oceania','world',\\\n",
    "               'san_jose','south_america','south_asia','spanish_speaking_regions']\n",
    "def new_mapid(use,oldid,newid):\n",
    "    if data.get([use][oldid],'') == '': return\n",
    "    # Create a new item with the new id\n",
    "    data[use][newid] = {}\n",
    "    for k in data[use][oldid].keys():\n",
    "        data[use][newid][k] = data[use][oldid][k]\n",
    "    del data[use][oldid]\n",
    "                 \n",
    "def update_mapid(use,mapid):\n",
    "    data[use][mapid]['detail_url'] = os.path.join(SOURCE_URL,mapid)\n",
    "    data[use][mapid]['date'] = MAP_DATE\n",
    "    del data[use][mapid]['osm_size']\n",
    "    del data[use][mapid]['sat_size']\n",
    "    del data[use][mapid]['sat_url']\n",
    "    del data[use][mapid]['sat_is_regional']\n",
    "    del data[use][mapid]['url']\n",
    "    data[use][mapid]['filename'] = mapid\n",
    "    #data[use][mapid]['detail_url'] = os.path.join(DOWNLOAD_URL,map_id,mapid)\n",
    "    data[use][mapid]['detail_url'] = SOURCE_URL + mapid \n",
    "    data[use][mapid]['bittorrent_url'] = os.path.join(DOWNLOAD_URL,mapid,mapid + '_archive.torrent')\n",
    "    fn = OUTPUT_DIR + '/' + mapid\n",
    "    if os.path.exists(fn):\n",
    "        size = os.path.getsize(fn)\n",
    "        data[use][mapid]['mbtile_size'] = size\n",
    "        data[use][mapid]['size'] = size\n",
    "    #data[use][mapid]['size'] = size + int(BASE_PLANET_SIZE) + int(BASE_SATELLITE_SIZE)\n",
    "                 \n",
    "                 \n",
    "                 \n",
    "CATALOG_NAME = 'map-catalog.json' \n",
    "                 \n",
    "outstr = ''\n",
    "map_catalog = {}\n",
    "TOMODIFY = PREFIX + '/output/' + CATALOG_NAME\n",
    "\n",
    "# First make global substitutions\n",
    "cmd = f'sed -i -e \"s/2019-10-08/{MAP_DATE}/g\" {TOMODIFY}'\n",
    "print(cmd)\n",
    "response = sp.run(cmd,capture_output=True,shell=True,text=True)\n",
    "cmd = f'sed -i-e \"s/2019/2020/g\" {TOMODIFY}'\n",
    "print(cmd)\n",
    "response = sp.run(cmd,capture_output=True,shell=True,text=True)\n",
    "cmd = f'sed -i-e \"s/_v3/_2020/g\" {TOMODIFY}'\n",
    "print(cmd)\n",
    "response = sp.run(cmd,capture_output=True,shell=True,text=True)\n",
    "with open(TOMODIFY,'r') as catalog_fp:\n",
    "   try:\n",
    "      data = json.loads(catalog_fp.read())\n",
    "   except:\n",
    "      print(\"json error reading regions.json\")\n",
    "      sys.exit(1)\n",
    "   catalog_fp.close()\n",
    "\n",
    "for mapid in data['maps'].keys():\n",
    "    update_mapid('maps',mapid)\n",
    "\n",
    "for mapid in data['base'].keys():\n",
    "    update_mapid('base',mapid)\n",
    "\n",
    "#print(json.dumps(data,indent=2))\n",
    "with open(TOMODIFY,\"w\") as catalog_fp:\n",
    "   outstr = json.dumps(data,indent=2)\n",
    "   catalog_fp.write(outstr)\n",
    "   catalog_fp.close()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "eb57b46d-df41-43c7-bd12-5b698a3f1c92",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get set to stage upload of new regions\n",
    "REGION_LIST = ['planet_base','satellite_base','africa','central_america','europe',\\\n",
    "               'middle_east','north_america','north_asia','oceania','world',\\\n",
    "               'san_jose','south_america','south_asia','spanish_speaking_regions']\n",
    "# make a reverse lookup between region and map-id\n",
    "region_lookup = {}\n",
    "for k in data['base'].keys():\n",
    "    #print(k)\n",
    "    region_lookup[data['base'][k]['region']] = {'name':k,'use':'base'}\n",
    "    update_mapid('base',k)\n",
    "for k in data['maps'].keys():\n",
    "    #print(k)\n",
    "    region_lookup[data['maps'][k]['region']] = {'name':k,'use':'maps'}\n",
    "    update_mapid('maps',k)\n",
    "print(str(region_lookup))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f6895973-78d8-4b8f-86ba-801349279d89",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creating/Updating a Sqlite database used to search for cities.\n"
     ]
    }
   ],
   "source": [
    "# Generate a new database for city search\n",
    "import sqlite3\n",
    "import subprocess\n",
    "import zipfile\n",
    "\n",
    "class Sqlite():\n",
    "   def __init__(self, filename):\n",
    "      self.conn = sqlite3.connect(filename)\n",
    "      self.conn.row_factory = sqlite3.Row\n",
    "      self.conn.text_factory = str\n",
    "      self.c = self.conn.cursor()\n",
    "   def __del__(self):\n",
    "      self.conn.commit()\n",
    "      self.c.close()\n",
    "      del self.conn\n",
    "    \n",
    "print('Creating/Updating a Sqlite database used to search for cities.')\n",
    "citydb_fn = OUTPUT_DIR + '/cities1000.sqlite'\n",
    "if os.path.exists(citydb_fn):\n",
    "    os.remove(citydb_fn)\n",
    "db = Sqlite(citydb_fn)\n",
    "sql = '''create table IF NOT EXISTS features (\n",
    "     geonameid integer primary key, name varchar(200), asciiname varchar(200),\n",
    "     alternatenames varchar(10000), latitude float, longitude float,\n",
    "     feature_class char(1), feature_code varchar(10), country_code char(2),\n",
    "     cc2 varchar(200), admin1 varchar(20), admin2 varchar(20), admin3 varchar(20),\n",
    "     admin4 varchar(20), population integer, elevation integer, dem varchar(20),\n",
    "     timezone varchar(40), moddate varchar(10)\n",
    " ); '''\n",
    "\n",
    "# Create table\n",
    "db.c.execute(sql)\n",
    "cities_zip = INPUT_DIR + '/cities1000.zip'\n",
    "if not os.path.exists(cities_zip):\n",
    "    cmd = 'wget -q -P %s http://download.geonames.org/export/dump/cities1000.zip'%(INPUT_DIR)\n",
    "    print('command:%s'%cmd)\n",
    "    subprocess.run(cmd,shell=True)\n",
    "        \n",
    "with zipfile.ZipFile(cities_zip,'r') as zip_ref:\n",
    "    zip_ref.extractall(INPUT_DIR)\n",
    "    \n",
    "with open(INPUT_DIR + '/cities1000.txt','r') as f:\n",
    "    sql = \"\"\"insert into features (geonameid, name, asciiname,\n",
    "                         alternatenames, latitude, longitude, feature_class,\n",
    "                         feature_code, country_code, cc2, admin1, admin2, admin3,\n",
    "                         admin4, population, elevation, dem, timezone, moddate)\n",
    "                         values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\"\"\"\n",
    "    for index,line in enumerate(f):\n",
    "        data = line.split('\\t')\n",
    "        db.c.execute(sql,data)\n",
    "db.conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c84c88b8-8f0b-4d34-a318-464737258595",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm-planet_z0-z10_2020.mbtiles|cut -d\" \" -f5\n",
      "2108256256\n",
      " \n",
      "File sizes for /hd/maps/maps-2020/staged/osm-planet_z0-z10_2020.mbtiles match. Skipping upload ...\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_san_jose_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      "30449664\n",
      " \n",
      "File sizes for /hd/maps/maps-2020/staged/osm_san_jose_z11-z14_2020.mbtiles match. Skipping upload ...\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_south_america_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      "11512279040\n",
      " \n",
      "File sizes for /hd/maps/maps-2020/staged/osm_south_america_z11-z14_2020.mbtiles match. Skipping upload ...\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_spain_z11-z14_2020.mbtiles.mbtiles|cut -d\" \" -f5\n",
      " ls: cannot access ./public_html/iiab-files/maps/osm_spain_z11-z14_2020.mbtiles.mbtiles: No such file or directory\n",
      "\n",
      "Remote file size: . Local lize is 1612718080\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_middle_east_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      "9039286272\n",
      " \n",
      "File sizes for /hd/maps/maps-2020/staged/osm_middle_east_z11-z14_2020.mbtiles match. Skipping upload ...\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/planet_z0-z6_2020.mbtiles|cut -d\" \" -f5\n",
      "50049024\n",
      " \n",
      "File sizes for /hd/maps/maps-2020/staged/planet_z0-z6_2020.mbtiles match. Skipping upload ...\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_africa_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      "15891234816\n",
      " \n",
      "File sizes for /hd/maps/maps-2020/staged/osm_africa_z11-z14_2020.mbtiles match. Skipping upload ...\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_spanish_central_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      " ls: cannot access ./public_html/iiab-files/maps/osm_spanish_central_z11-z14_2020.mbtiles: No such file or directory\n",
      "\n",
      "Remote file size: . Local lize is 1452322816\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_south_asia_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      "12899446784\n",
      " \n",
      "File sizes for /hd/maps/maps-2020/staged/osm_south_asia_z11-z14_2020.mbtiles match. Skipping upload ...\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/planet_z0-z10.mbtiles|cut -d\" \" -f5\n",
      " ls: cannot access ./public_html/iiab-files/maps/planet_z0-z10.mbtiles: No such file or directory\n",
      "\n",
      "Remote file size: . Local lize is 2108329984\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_north_asia_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      "19161931776\n",
      " \n",
      "File sizes for /hd/maps/maps-2020/staged/osm_north_asia_z11-z14_2020.mbtiles match. Skipping upload ...\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_equitorial_guinea_z11-z14_2020.mbtiles.mbtiles|cut -d\" \" -f5\n",
      " ssh: connect to host timmoody.com port 22: Connection refused\n",
      "\n",
      "Remote file size: . Local lize is 63524864\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_europe_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      " ssh: connect to host timmoody.com port 22: Connection refused\n",
      "\n",
      "Remote file size: . Local lize is 30514585600\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_spain_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      " ssh: connect to host timmoody.com port 22: Connection refused\n",
      "\n",
      "Remote file size: . Local lize is 1611640832\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_central_america_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      " ssh: connect to host timmoody.com port 22: Connection refused\n",
      "\n",
      "Remote file size: . Local lize is 3551305728\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_oceania_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      " ssh: connect to host timmoody.com port 22: Connection refused\n",
      "\n",
      "Remote file size: . Local lize is 6260080640\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_north_america_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      " ssh: connect to host timmoody.com port 22: Connection refused\n",
      "\n",
      "Remote file size: . Local lize is 24112046080\n",
      "Response to scp: b''\n",
      "executing /usr/bin/ssh timmoody@timmoody.com ls -l ./public_html/iiab-files/maps/osm_spanish_speaking_regions_z11-z14_2020.mbtiles|cut -d\" \" -f5\n",
      " ssh: connect to host timmoody.com port 22: Connection refused\n",
      "\n",
      "Remote file size: . Local lize is 13473050624\n",
      "Response to scp: b''\n"
     ]
    }
   ],
   "source": [
    "# Copy files in staged dir to publisher URL\n",
    "import subprocess as sp\n",
    "PUBLISHER_URL = 'timmoody@timmoody.com'\n",
    "TARGET_DIR = './public_html/iiab-files/maps'\n",
    "mbt_list = glob(STAGED_DIR + '/*.mbtiles')\n",
    "for mbt_fn in mbt_list:\n",
    "    cmd = f'/usr/bin/ssh {PUBLISHER_URL} ls -l {TARGET_DIR}/{os.path.basename(mbt_fn)}|cut -d\" \" -f5'\n",
    "    print(f'executing {cmd}')\n",
    "    response = sp.run(cmd,capture_output=True,shell=True,text=True)\n",
    "    print(response.stdout,response.stderr)\n",
    "    cmd = f'/usr/bin/rsync {mbt_fn} {PUBLISHER_URL}/{TARGET_DIR}/{os.path.basename(mbt_fn)}'\n",
    "    if response.stdout.find('cannot access') != -1:\n",
    "        print(f'executing {cmd}')\n",
    "        response = sp.run(cmd,capture_output=True,shell=True)\n",
    "        print(f'Response to scp: {response.stdout}')\n",
    "    elif response.stdout.strip() != str(os.path.getsize(mbt_fn)):\n",
    "        print(f'Remote file size: {response.stdout.strip()}. Local lize is { os.path.getsize(mbt_fn)}')\n",
    "        response = sp.run(cmd,capture_output=True,shell=True)\n",
    "        print(f'Response to scp: {response.stdout}')\n",
    "    else:\n",
    "        print(f'File sizes for {mbt_fn} match. Skipping upload ...') \n",
    "\n",
    "cmd = f'/usr/bin/ssh {PUBLISHER_URL} ls -l {TARGET_DIR}/{os.path.basename(mbt_fn)}|cut -d\" \" -f5'\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d22b96f8-511d-4cdd-b073-953f6521a16c",
   "metadata": {},
   "source": [
    "#### Notes for finishing the Map Packs\n",
    "1. The tilelive copy for the whole world seems too slow (and all it would be doing is deleting zoom levels 0-10 which is easy and fast in sql: \"DELETE * from maps where zoom_level < 11; vacuum;\"\n",
    "2. But it turns out that the maps directly from archive.org do not have the metadata that is generated by tilelive, and which the IIAB map system requires).  So I looked back through map programs and added the -t option to download.py, which appends the required metadata\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06e27ddc-49d9-4b16-8550-1f509b92cb67",
   "metadata": {},
   "source": [
    "#### Notes about generating the Satellite image packs\n",
    "1. The iiab-extend-sat.py used in IIAB to enlarge a satellite region is too slow to capture the whole world.\n",
    "2. So I used download.py to fetch the world to zoom 9 (it still took almost a week).\n",
    "3. I needed to use download.py with the \"-t\" option to add the metadata before the tileinfo.php would not error out \n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  },
  "toc-autonumbering": false,
  "toc-showcode": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}