datasets/create_datasets.ipynb

{
      "cells": [
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "# Generation of useful files\n",
                        "\n",
                        "It is the first notebook to execute"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "# 1) Atlas datasets"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 2,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "from pprint import pprint\n",
                        "from random import shuffle\n",
                        "\n",
                        "from scripts.utils.file_utils import load_json, dump_json\n",
                        "from scripts.ripe_atlas.atlas_api import get_atlas_anchors, get_atlas_probes\n",
                        "from default import USER_ANCHORS_FILE, USER_PROBES_FILE, USER_PROBES_AND_ANCHORS_FILE"
                  ]
            },
            {
                  "attachments": {},
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Retrieve atlas probes and anchors"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 3,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "selected probes: 10345 (22.74%), rejected: 35150, geoloc rejected: 1\n"
                              ]
                        }
                  ],
                  "source": [
                        "# create and fill the probes.json dataset.\n",
                        "probes, probes_rejected, probes_geoloc_disputed = get_atlas_probes()\n",
                        "dump_json(probes, USER_PROBES_FILE)\n",
                        "\n",
                        "# display number of selected and rejected probes.\n",
                        "print(\n",
                        "    f\"selected probes: {len(probes)} ({round(len(probes) * 100 / (probes_rejected+len(probes)), 2)}%), rejected: {probes_rejected}, geoloc rejected: {probes_geoloc_disputed}\"\n",
                        ")"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 4,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "selected anchors: 783 (62.34%), rejected: 473, geoloc rejected: 1\n"
                              ]
                        }
                  ],
                  "source": [
                        "# create and fill the anchors.json dataset.\n",
                        "anchors, anchors_rejected, anchors_geoloc_disputed = get_atlas_anchors()\n",
                        "dump_json(anchors, USER_ANCHORS_FILE)\n",
                        "\n",
                        "# display number of selected and rejected anchors.\n",
                        "print(\n",
                        "    f\"selected anchors: {len(anchors)} ({round(len(anchors) * 100 / (anchors_rejected+len(anchors)), 2)}%), rejected: {anchors_rejected}, geoloc rejected: {anchors_geoloc_disputed}\"\n",
                        ")\n"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "These two files will be filtered and updated step by step during the execution of this notebook"
                  ]
            },
            {
                  "attachments": {},
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### test loading"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 3,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "selected probes: 10399\n",
                                    "selected anchors: 715\n",
                                    "{'address_v4': '45.138.229.91',\n",
                                    " 'asn_v4': 206238,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [4.9275, 52.3475], 'type': 'Point'}}\n",
                                    "{'address_v4': '77.174.76.85',\n",
                                    " 'asn_v4': 1136,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [4.9375, 52.3685], 'type': 'Point'}}\n",
                                    "{'address_v4': '82.217.219.124',\n",
                                    " 'asn_v4': 33915,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [6.0075, 51.2005], 'type': 'Point'}}\n",
                                    "{'address_v4': '83.81.83.145',\n",
                                    " 'asn_v4': 33915,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [5.9975, 51.1915], 'type': 'Point'}}\n",
                                    "{'address_v4': '95.232.13.43',\n",
                                    " 'asn_v4': 3269,\n",
                                    " 'country_code': 'IT',\n",
                                    " 'geometry': {'coordinates': [12.4375, 41.8995], 'type': 'Point'}}\n",
                                    "{'address_v4': '77.174.30.45',\n",
                                    " 'asn_v4': 1136,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [5.9585, 52.0075], 'type': 'Point'}}\n",
                                    "{'address_v4': '77.54.169.78',\n",
                                    " 'asn_v4': 12353,\n",
                                    " 'country_code': 'PT',\n",
                                    " 'geometry': {'coordinates': [-9.1515, 38.7295], 'type': 'Point'}}\n",
                                    "{'address_v4': '87.195.139.46',\n",
                                    " 'asn_v4': 12414,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [4.8485, 52.3605], 'type': 'Point'}}\n",
                                    "{'address_v4': '76.82.152.84',\n",
                                    " 'asn_v4': 20001,\n",
                                    " 'country_code': 'US',\n",
                                    " 'geometry': {'coordinates': [-117.1815, 32.8885], 'type': 'Point'}}\n",
                                    "{'address_v4': '2.57.252.147',\n",
                                    " 'asn_v4': 213279,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [4.8885, 52.4005], 'type': 'Point'}}\n",
                                    "{'address_v4': '81.220.141.248',\n",
                                    " 'asn_v4': 15557,\n",
                                    " 'country_code': 'FR',\n",
                                    " 'geometry': {'coordinates': [1.6185, 43.2915], 'type': 'Point'}}\n"
                              ]
                        }
                  ],
                  "source": [
                        "probes = load_json(USER_PROBES_FILE)\n",
                        "anchors = load_json(USER_ANCHORS_FILE)\n",
                        "\n",
                        "print(f\"selected probes: {len(probes)}\")\n",
                        "print(f\"selected anchors: {len(anchors)}\")\n",
                        "\n",
                        "for i, probe in enumerate(probes):\n",
                        "    if i > 10:\n",
                        "        break\n",
                        "    pprint(probe)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 4,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "11114\n"
                              ]
                        }
                  ],
                  "source": [
                        "probes_and_anchors = probes + anchors\n",
                        "print(len(probes_and_anchors))\n",
                        "shuffle(probes_and_anchors)\n",
                        "\n",
                        "dump_json(probes_and_anchors, USER_PROBES_AND_ANCHORS_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "# 2) Geographical datasets"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 5,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "from scripts.utils.file_utils import load_json, dump_json\n",
                        "from scripts.utils.helpers import distance\n",
                        "from default import COUNTRIES_TXT_FILE, COUNTRIES_JSON_FILE, USER_ANCHORS_FILE, USER_PROBES_FILE"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Get country dataset"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 6,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "AD : {'latitude': '42.546245', 'longitude': '1.601554', 'name': 'Andorra'}\n",
                                    "AE : {'latitude': '23.424076', 'longitude': '53.847818', 'name': 'United'}\n",
                                    "AF : {'latitude': '33.93911', 'longitude': '67.709953', 'name': 'Afghanistan'}\n",
                                    "AG : {'latitude': '17.060816', 'longitude': '-61.796428', 'name': 'Antigua'}\n",
                                    "AI : {'latitude': '18.220554', 'longitude': '-63.068615', 'name': 'Anguilla'}\n",
                                    "AL : {'latitude': '41.153332', 'longitude': '20.168331', 'name': 'Albania'}\n",
                                    "AM : {'latitude': '40.069099', 'longitude': '45.038189', 'name': 'Armenia'}\n",
                                    "AN : {'latitude': '12.226079', 'longitude': '-69.060087', 'name': 'Netherlands'}\n",
                                    "AO : {'latitude': '-11.202692', 'longitude': '17.873887', 'name': 'Angola'}\n",
                                    "AQ : {'latitude': '-75.250973', 'longitude': '-0.071389', 'name': 'Antarctica'}\n",
                                    "AR : {'latitude': '-38.416097', 'longitude': '-63.616672', 'name': 'Argentina'}\n"
                              ]
                        }
                  ],
                  "source": [
                        "countries = {}\n",
                        "with open(COUNTRIES_TXT_FILE, \"r\", encoding=\"utf8\") as f:\n",
                        "    for i, row in enumerate(f.readlines()):\n",
                        "\n",
                        "        row = [value.strip() for value in row.split(\" \")]\n",
                        "        countries[row[0]] = {\n",
                        "            \"latitude\": row[1],\n",
                        "            \"longitude\": row[2],\n",
                        "            \"name\": row[3],\n",
                        "        }\n",
                        "\n",
                        "for i, (country_code, geoloc) in enumerate(countries.items()):\n",
                        "    if i > 10:\n",
                        "        break\n",
                        "    print(f\"{country_code} : {geoloc}\")\n",
                        "\n",
                        "# save results\n",
                        "dump_json(countries, COUNTRIES_JSON_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Eliminate default geolocated probes\n",
                        "\n",
                        "All countries no matter their size have a default position. If a probe is located at this default location, it is eliminated."
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 7,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "probes = load_json(USER_PROBES_FILE)\n",
                        "\n",
                        "anchors = load_json(USER_ANCHORS_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 8,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "def country_filtering(probes: list, countries: dict) -> list:\n",
                        "    filtered_probes = []\n",
                        "    for probe in probes:\n",
                        "\n",
                        "        # check if probe coordinates are close to default location\n",
                        "        try:\n",
                        "            country_geo = countries[probe[\"country_code\"]]\n",
                        "        except KeyError as e:\n",
                        "            print(\n",
                        "                f\"error country code {probe['country_code']} is unknown\")\n",
                        "            continue\n",
                        "\n",
                        "        # if the country code is unknown, remove probe from dataset\n",
                        "        country_lat = float(country_geo[\"latitude\"])\n",
                        "        country_lon = float(country_geo[\"longitude\"])\n",
                        "\n",
                        "        probe_lat = float(probe[\"geometry\"][\"coordinates\"][1])\n",
                        "        probe_lon = float(probe[\"geometry\"][\"coordinates\"][0])\n",
                        "\n",
                        "        dist = distance(country_lat, probe_lat, country_lon, probe_lon)\n",
                        "\n",
                        "        if dist > 5:\n",
                        "            filtered_probes.append(probe)\n",
                        "\n",
                        "    return filtered_probes"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 9,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "error country code SS is unknown\n",
                                    "error country code CW is unknown\n",
                                    "error country code BL is unknown\n",
                                    "Number of Atlas probes kept: 10321, rejected: 78\n",
                                    "Number of Atlas anchors kept: 715, rejected: 0\n"
                              ]
                        }
                  ],
                  "source": [
                        "filtered_probes = country_filtering(probes, countries)\n",
                        "filtered_anchors = country_filtering(anchors, countries)\n",
                        "\n",
                        "print(\n",
                        "    f\"Number of Atlas probes kept: {len(filtered_probes)}, rejected: {len(probes) - len(filtered_probes)}\")\n",
                        "print(\n",
                        "    f\"Number of Atlas anchors kept: {len(filtered_anchors)}, rejected: {len(anchors) - len(filtered_anchors)}\")\n",
                        "\n",
                        "# save results\n",
                        "dump_json(filtered_anchors, USER_ANCHORS_FILE)\n",
                        "dump_json(filtered_probes, USER_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "# 3) Other various files"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 10,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "import math\n",
                        "import pickle\n",
                        "import radix\n",
                        "import requests\n",
                        "from copy import deepcopy\n",
                        "\n",
                        "from clickhouse_driver import Client\n",
                        "from multiprocessing import Pool\n",
                        "\n",
                        "from scripts.utils.file_utils import load_json, dump_json\n",
                        "from scripts.utils.helpers import haversine\n",
                        "from scripts.analysis.analysis import compute_remove_wrongly_geolocated_probes, compute_rtts_per_dst_src\n",
                        "from default import *\n",
                        "\n",
                        "DB_HOST = \"localhost\"\n",
                        "GEO_REPLICATION_DB = \"geolocation_replication\"\n",
                        "ANCHORS_MESHED_PING_TABLE = f\"anchors_meshed_pings\"\n",
                        "PROBES_TO_ANCHORS_PING_TABLE = f\"ping_10k_to_anchors\"\n",
                        "\n",
                        "LIMIT = 1000"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Generate ip level target list for all /24 prefixes"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 11,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "targets_per_prefix = {}\n",
                        "\n",
                        "with open(ADDRESS_FILE, \"r\") as f:\n",
                        "    for i, row in enumerate(f.readlines()[1:]):\n",
                        "        row = row.split(\"\\t\")\n",
                        "\n",
                        "        # get prefix from hex value\n",
                        "        prefix_hex = row[0]\n",
                        "        prefix = [\"\".join(x) for x in zip(*[iter(prefix_hex)]*2)]\n",
                        "        prefix = [int(x, 16) for x in prefix]\n",
                        "        prefix = \".\".join(str(x) for x in prefix)\n",
                        "\n",
                        "        target_list = row[-1].strip(\"\\n\")\n",
                        "        target_list = target_list.split(\",\")\n",
                        "\n",
                        "        # parse and save targets\n",
                        "        if target_list[0] != '-':\n",
                        "            for i, target in enumerate(target_list):\n",
                        "                target_list[i] = prefix.split(\".\")[:-1]\n",
                        "                target_list[i].append(str(int(target, 16)))\n",
                        "                target_list[i] = \".\".join(target_list[i])\n",
                        "\n",
                        "            try:\n",
                        "                targets_per_prefix[prefix].extend(target_list)\n",
                        "            except KeyError:\n",
                        "                targets_per_prefix[prefix] = target_list"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 12,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "target hitlist\n",
                                    "prefix: 1.0.0.0 target hitlist: ['1.0.0.0', '1.0.0.1', '1.0.0.2']\n",
                                    "prefix: 1.0.4.0 target hitlist: ['1.0.4.1', '1.0.4.4']\n",
                                    "prefix: 1.0.5.0 target hitlist: ['1.0.5.1', '1.0.5.5']\n",
                                    "prefix: 1.0.6.0 target hitlist: ['1.0.6.1', '1.0.6.6']\n",
                                    "prefix: 1.0.7.0 target hitlist: ['1.0.7.1', '1.0.7.7']\n",
                                    "prefix: 1.0.16.0 target hitlist: ['1.0.16.14', '1.0.16.9', '1.0.16.10', '1.0.16.11']\n",
                                    "prefix: 1.0.64.0 target hitlist: ['1.0.64.25', '1.0.64.94', '1.0.64.95']\n",
                                    "prefix: 1.0.65.0 target hitlist: ['1.0.65.6', '1.0.65.176', '1.0.65.243']\n",
                                    "prefix: 1.0.66.0 target hitlist: ['1.0.66.10', '1.0.66.13', '1.0.66.205']\n",
                                    "prefix: 1.0.67.0 target hitlist: ['1.0.67.15', '1.0.67.23', '1.0.67.43']\n",
                                    "prefix: 1.0.68.0 target hitlist: ['1.0.68.21', '1.0.68.68', '1.0.68.131']\n"
                              ]
                        }
                  ],
                  "source": [
                        "dump_json(targets_per_prefix, USER_HITLIST_FILE)\n",
                        "\n",
                        "print(\"target hitlist\")\n",
                        "for i, prefix in enumerate(targets_per_prefix):\n",
                        "    if i > 10:\n",
                        "        break\n",
                        "    print(\"prefix:\", prefix, \"target hitlist:\", targets_per_prefix[prefix])"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Build pairwise matrix\n",
                        "WARNING : Time consumming section\n",
                        "\n",
                        "This matrix represents the geographical distance between all the probes and anchors of the dataset"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 13,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "probes = load_json(USER_PROBES_FILE)\n",
                        "anchors = load_json(USER_ANCHORS_FILE)\n",
                        "anchors_ip_list = [anchor[\"address_v4\"] for anchor in anchors]\n",
                        "probes.extend(anchors)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 14,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "vp_coordinates_per_ip = {}\n",
                        "\n",
                        "for probe in probes:\n",
                        "    ip_v4_address = probe[\"address_v4\"]\n",
                        "    long, lat = probe[\"geometry\"][\"coordinates\"]\n",
                        "    vp_coordinates_per_ip[ip_v4_address] = lat, long\n",
                        "\n",
                        "\n",
                        "vp_distance_matrix = {}\n",
                        "vp_coordinates_per_ip_l = sorted(vp_coordinates_per_ip.items(), key=lambda x: x[0])\n",
                        "\n",
                        "for i in range(len(vp_coordinates_per_ip_l)):\n",
                        "    vp_i, vp_i_coordinates = vp_coordinates_per_ip_l[i]\n",
                        "    if vp_i not in anchors_ip_list:\n",
                        "        continue\n",
                        "    for j in range(len(vp_coordinates_per_ip_l)):\n",
                        "        vp_j, vp_j_coordinates = vp_coordinates_per_ip_l[j]\n",
                        "        distance = haversine(vp_i_coordinates, vp_j_coordinates)\n",
                        "        vp_distance_matrix.setdefault(vp_i, {})[vp_j] = distance\n",
                        "        vp_distance_matrix.setdefault(vp_j, {})[vp_i] = distance\n",
                        "\n",
                        "\n",
                        "dump_json(vp_distance_matrix, USER_PAIRWISE_DISTANCE_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Find wrongly geolocated probes\n",
                        "\n",
                        "When looking at all the pings between probes, we sometimes notice violations of the speed of Internet, which means that the corresponding probes are wrongly geolocated.  \n",
                        "The following algorithm removes greedily the probe with the most violations until there is none."
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 15,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors = load_json(USER_ANCHORS_FILE)\n",
                        "\n",
                        "vp_distance_matrix = load_json(USER_PAIRWISE_DISTANCE_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 16,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "vp_coordinates_per_ip = {}\n",
                        "\n",
                        "for anchor in anchors:\n",
                        "    if \"address_v4\" in anchor and \"geometry\" in anchor and \"coordinates\" in anchor[\"geometry\"]:\n",
                        "        ip_v4_address = anchor[\"address_v4\"]\n",
                        "        if ip_v4_address is None:\n",
                        "            continue\n",
                        "        long, lat = anchor[\"geometry\"][\"coordinates\"]\n",
                        "        vp_coordinates_per_ip[ip_v4_address] = lat, long"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 17,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "0\n"
                              ]
                        }
                  ],
                  "source": [
                        "removed_anchors = set()\n",
                        "filter = \"\"\n",
                        "\n",
                        "rtt_per_srcs_dst = compute_rtts_per_dst_src(ANCHORS_MESHED_PING_TABLE, filter, threshold=300)\n",
                        "\n",
                        "removed_anchors = compute_remove_wrongly_geolocated_probes(rtt_per_srcs_dst,\n",
                        "                                                            vp_coordinates_per_ip,\n",
                        "                                                            vp_distance_matrix,\n",
                        "                                                            removed_anchors)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 18,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "probes = load_json(USER_PROBES_FILE)\n",
                        "probes.extend(anchors)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 19,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "vp_coordinates_per_ip = {}\n",
                        "\n",
                        "for probe in probes:\n",
                        "    if \"address_v4\" in probe and \"geometry\" in probe and \"coordinates\" in probe[\"geometry\"]:\n",
                        "        ip_v4_address = probe[\"address_v4\"]\n",
                        "        if ip_v4_address is None:\n",
                        "            continue\n",
                        "        long, lat = probe[\"geometry\"][\"coordinates\"]\n",
                        "        vp_coordinates_per_ip[ip_v4_address] = lat, long"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": null,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "removed_probes = set()\n",
                        "\n",
                        "filter = \"\"\n",
                        "in_clause = f\"\".join([f\",toIPv4('{p}')\" for p in removed_anchors])[1:]\n",
                        "filter += f\"AND dst not in ({in_clause}) AND src not in ({in_clause}) \"\n",
                        "\n",
                        "vp_coordinates_per_ip = {ip: vp_coordinates_per_ip[ip]\n",
                        "                            for ip in vp_coordinates_per_ip\n",
                        "                            if ip not in removed_anchors}\n",
                        "\n",
                        "rtt_per_srcs_dst = compute_rtts_per_dst_src(PROBES_TO_ANCHORS_PING_TABLE, filter, threshold=300)\n",
                        "\n",
                        "removed_probes = compute_remove_wrongly_geolocated_probes(rtt_per_srcs_dst,\n",
                        "                                                            vp_coordinates_per_ip,\n",
                        "                                                            vp_distance_matrix,\n",
                        "                                                            removed_anchors)\n",
                        "\n",
                        "removed_probes.update(removed_anchors)\n",
                        "\n",
                        "print(f\"Removing {len(removed_probes)} probes\")\n",
                        "dump_json(list(removed_probes), USER_REMOVED_PROBES_FILE)\n"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Remove bad results\n",
                        "\n",
                        "Then the probes are removed of the dataset.  \n",
                        "We also remove anchors that haven't enough traceroute data to be analyzed."
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### anchors"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 22,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "715 total anchors\n"
                              ]
                        }
                  ],
                  "source": [
                        "anchors = load_json(USER_PROBES_FILE)\n",
                        "\n",
                        "print(f\"{len(anchors)} total anchors\")\n",
                        "\n",
                        "removed_probes = load_json(USER_REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 23,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "removed_probes = load_json(USER_REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 24,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "0 anchors to keep\n",
                                    "1 anchors removed because of incorrect geolocation\n",
                                    "714 anchors removed because the lack of traceroute data towards\n"
                              ]
                        }
                  ],
                  "source": [
                        "good_anchors = []\n",
                        "incorrect_geolocation_count = 0\n",
                        "not_enough_vps = 0\n",
                        "client = Client('127.0.0.1')\n",
                        "\n",
                        "for anchor in anchors:\n",
                        "    if anchor['address_v4'] in removed_probes:\n",
                        "        incorrect_geolocation_count += 1\n",
                        "        continue\n",
                        "    \n",
                        "    target_ip = anchor['address_v4']\n",
                        "    tmp_res_db = client.execute(\n",
                        "        f'select distinct src_addr from street_lvl.street_lvl_traceroutes where resp_addr = \\'{target_ip}\\' and dst_addr = \\'{target_ip}\\' and src_addr <> \\'{target_ip}\\'')\n",
                        "    if len(tmp_res_db) < 100:\n",
                        "        not_enough_vps += 1\n",
                        "        continue\n",
                        "    good_anchors.append(anchor)\n",
                        "\n",
                        "print(f\"{len(good_anchors)} anchors to keep\")\n",
                        "print(f\"{incorrect_geolocation_count} anchors removed because of incorrect geolocation\")\n",
                        "print(f\"{not_enough_vps} anchors removed because the lack of traceroute data towards\")\n",
                        "\n",
                        "dump_json(good_anchors, USER_ANCHORS_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### probes"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 25,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "10321 total probes\n"
                              ]
                        }
                  ],
                  "source": [
                        "probes = load_json(USER_PROBES_FILE)\n",
                        "\n",
                        "print(f\"{len(probes)} total probes\")\n",
                        "\n",
                        "removed_probes = load_json(USER_REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 26,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "10265 probes to keep\n",
                                    "56 probes removed because of incorrect geolocation\n"
                              ]
                        }
                  ],
                  "source": [
                        "good_probes = []\n",
                        "incorrect_geolocation_count = 0\n",
                        "\n",
                        "for probe in probes:\n",
                        "    if probe['address_v4'] in removed_probes:\n",
                        "        incorrect_geolocation_count += 1\n",
                        "        continue\n",
                        "    good_probes.append(probe)\n",
                        "\n",
                        "print(f\"{len(good_probes)} probes to keep\")\n",
                        "print(f\"{incorrect_geolocation_count} probes removed because of incorrect geolocation\")\n",
                        "\n",
                        "dump_json(good_probes, USER_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Create removed and filtered probes files\n",
                        "\n",
                        "All the probes removed from the beggining of the notebook are saved in the removed_probes.json file.  \n",
                        "\n",
                        "All the probes that are in the clickhouse database but not in the current dataset are saved in the filtered_probes.json file.  \n",
                        "Later, they will be used to create a filter and collect only relevant data."
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### Anchors"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 27,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors = load_json(USER_ANCHORS_FILE)\n",
                        "\n",
                        "filter = \"\"\n",
                        "    \n",
                        "# clickhouse is required here\n",
                        "rtt_per_srcs_dst = compute_rtts_per_dst_src(ANCHORS_MESHED_PING_TABLE, filter, threshold=100)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 28,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "0\n"
                              ]
                        }
                  ],
                  "source": [
                        "for anchor in anchors:\n",
                        "    if anchor[\"address_v4\"] not in rtt_per_srcs_dst:\n",
                        "        index = anchors.index(anchor)\n",
                        "        anchors.pop(index)\n",
                        "        print(anchor)\n",
                        "\n",
                        "print(len(anchors))\n",
                        "dump_json(anchors, USER_ANCHORS_FILE)\n",
                        "\n",
                        "anchors_list = [anchor[\"address_v4\"] for anchor in anchors]"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 29,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "# Remove probes that are in the meshed table but not in the dataset\n",
                        "filtered_probes = []\n",
                        "copy = deepcopy(rtt_per_srcs_dst)\n",
                        "for anchor in copy:\n",
                        "    if anchor not in anchors_list:\n",
                        "        filtered_probes.append(anchor)\n",
                        "\n",
                        "copy = deepcopy(rtt_per_srcs_dst)\n",
                        "for anchor in copy:\n",
                        "    for element in copy[anchor]:\n",
                        "        if element not in anchors_list:\n",
                        "            filtered_probes.append(element)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### Probes"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 30,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors = load_json(USER_ANCHORS_FILE)\n",
                        "anchors_list = [anchor[\"address_v4\"] for anchor in anchors]\n",
                        "\n",
                        "probes = load_json(USER_PROBES_FILE)\n",
                        "probes_list = [probe[\"address_v4\"] for probe in probes]\n",
                        "probes_list.extend(anchors_list)\n",
                        "\n",
                        "\n",
                        "filter = \"\"\n",
                        "    \n",
                        "# clickhouse is required here\n",
                        "rtt_per_srcs_dst = compute_rtts_per_dst_src(PROBES_TO_ANCHORS_PING_TABLE, filter, threshold=100)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 31,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "# Remove probes that are in the meshed table but not in the dataset\n",
                        "\n",
                        "copy = deepcopy(rtt_per_srcs_dst)\n",
                        "for anchor in copy:\n",
                        "    if anchor not in anchors_list:\n",
                        "        filtered_probes.append(anchor)\n",
                        "\n",
                        "copy = deepcopy(rtt_per_srcs_dst)\n",
                        "for anchor in copy:\n",
                        "    for probe in copy[anchor]:\n",
                        "        if probe not in probes_list:\n",
                        "            filtered_probes.append(probe)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### Global"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 32,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "original_probes = load_json(PROBES_AND_USER_ANCHORS_FILE)\n",
                        "good_anchors = load_json(USER_ANCHORS_FILE)\n",
                        "good_probes = load_json(USER_PROBES_FILE)\n",
                        "\n",
                        "final_probes = []\n",
                        "removed_probes = []\n",
                        "for probe in original_probes:\n",
                        "    if probe in good_anchors or probe in good_probes:\n",
                        "        final_probes.append(probe)\n",
                        "    else:\n",
                        "        removed_probes.append(probe[\"address_v4\"])\n",
                        "\n",
                        "dump_json(final_probes, PROBES_AND_USER_ANCHORS_FILE)\n",
                        "dump_json(removed_probes, REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 33,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "filtered_probes.extend(removed_probes)\n",
                        "dump_json(list(set(filtered_probes)), FILTERED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Update pairwise\n",
                        "\n",
                        "The pairwise matrix is updated : incorrect probes are removed."
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 34,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "pairwise_probes = load_json(PAIRWISE_DISTANCE_FILE)\n",
                        "removed_probes = load_json(REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 35,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "# Remove entries with removed probes\n",
                        "\n",
                        "for removed_probe in removed_probes:\n",
                        "    try:\n",
                        "        del pairwise_probes[removed_probe]\n",
                        "    except KeyError:\n",
                        "        continue\n",
                        "\n",
                        "copy = deepcopy(pairwise_probes)\n",
                        "for probe in copy:\n",
                        "    for removed_probe in removed_probes:\n",
                        "        try:\n",
                        "            del pairwise_probes[probe][removed_probe]\n",
                        "        except KeyError:\n",
                        "            continue\n"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 36,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "dump_json(pairwise_probes, PAIRWISE_DISTANCE_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Select greedy probes\n",
                        "WARNING : Time consumming section\n",
                        "\n",
                        "Greedily compute the probe with the greatest distance to other probes"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 37,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "def greedy_selection_probes_impl(probe, distance_per_probe, selected_probes):\n",
                        "\n",
                        "    distances_log = [math.log(distance_per_probe[p]) for p in selected_probes\n",
                        "                     if p in distance_per_probe and distance_per_probe[p] > 0]\n",
                        "    total_distance = sum(distances_log)\n",
                        "    return probe, total_distance"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 38,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "vp_distance_matrix = load_json(PAIRWISE_DISTANCE_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 39,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "Starting greedy algorithm\n"
                              ]
                        }
                  ],
                  "source": [
                        "print(\"Starting greedy algorithm\")\n",
                        "selected_probes = []\n",
                        "remaining_probes = set(vp_distance_matrix.keys())\n",
                        "with Pool(12) as p:\n",
                        "    while len(remaining_probes) > 0 and len(selected_probes) < LIMIT:\n",
                        "        args = []\n",
                        "        for probe in remaining_probes:\n",
                        "            args.append((probe, vp_distance_matrix[probe], selected_probes))\n",
                        "\n",
                        "        results = p.starmap(greedy_selection_probes_impl, args)\n",
                        "\n",
                        "        furthest_probe_from_selected, _ = max(results, key=lambda x:x[1])\n",
                        "        selected_probes.append(furthest_probe_from_selected)\n",
                        "        remaining_probes.remove(furthest_probe_from_selected)\n",
                        "\n",
                        "dump_json(selected_probes, GREEDY_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Find IP info and maxmind results\n",
                        "\n",
                        "Collect information from other public geolocation databases."
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 40,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "token = \"4f6c895ec9224f\"\n",
                        "\n",
                        "maxmind_tree_file = GEOLITE_FILE"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 41,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors = load_json(USER_ANCHORS_FILE)\n",
                        "\n",
                        "removed_probes = load_json(REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 43,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "with open(maxmind_tree_file, \"rb\") as f:\n",
                        "    tree = pickle.load(f)\n",
                        "\n",
                        "ip_info_geo = {}\n",
                        "maxmind_geo = {}\n",
                        "\n",
                        "\n",
                        "for i, anchor in enumerate(sorted(anchors, key=lambda x: x[\"address_v4\"])):\n",
                        "    ip = anchor[\"address_v4\"]\n",
                        "\n",
                        "    # ip_info\n",
                        "    url = f\"https://ipinfo.io/{ip}?token={token}\"\n",
                        "    result = requests.get(url).json()\n",
                        "    ip_info_geo[ip] = result\n",
                        "\n",
                        "    # maxmind_results\n",
                        "    node = tree.search_best(ip)\n",
                        "    if node is not None:\n",
                        "        if \"city\" in node.data:\n",
                        "            maxmind_geo[ip] = node.data[\"coordinates\"]\n",
                        "\n",
                        "dump_json(ip_info_geo, IP_INFO_GEO_FILE)\n",
                        "dump_json(maxmind_geo, MAXMIND_GEO_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "# 4) Finish completing geographical datasets"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "The following file needed the final version of probes and anchors dataset"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 44,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "import requests\n",
                        "import geopandas as gpd\n",
                        "import rasterio\n",
                        "\n",
                        "from rasterio.transform import from_bounds\n",
                        "from pprint import pprint\n",
                        "from subprocess import Popen, PIPE, STDOUT\n",
                        "\n",
                        "from scripts.utils.file_utils import load_json, dump_json\n",
                        "from default import USER_ANCHORS_FILE, POPULATION_CITY_FILE, CITIES_500_FILE, POPULATION_DENSITY_FILE"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Get population data\n",
                        "WARNING : Time consumming section\n",
                        "\n",
                        "For each anchor we associate some geographical information like its city, the population and density of this city."
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### city coordinates"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 60,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors = load_json(USER_ANCHORS_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 61,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors_with_location = []\n",
                        "\n",
                        "for anchor in anchors:\n",
                        "    ip = anchor['address_v4']\n",
                        "    lat = anchor['geometry']['coordinates'][1]\n",
                        "    lon = anchor['geometry']['coordinates'][0]\n",
                        "    country_code = anchor['country_code']\n",
                        "    anchors_with_location.append({'target_ip': ip, 'lat': lat, 'lon': lon,\n",
                        "                'country_code': country_code})\n",
                        "\n",
                        "\n",
                        "anchors_with_city = []\n",
                        "for anchor in anchors_with_location:\n",
                        "    url = f\"http://nominatim.openstreetmap.org/reverse?format=geojson&lat={anchor['lat']}&lon={anchor['lon']}\"\n",
                        "    r = requests.get(url)\n",
                        "    elem = r.json()\n",
                        "    if 'features' not in elem or len(elem['features']) != 1:\n",
                        "        continue\n",
                        "    info = elem['features'][0]\n",
                        "    if 'properties' not in info or 'address' not in info['properties']:\n",
                        "        continue\n",
                        "    address = info['properties']['address']\n",
                        "    if 'city' in address:\n",
                        "        anchor['city'] = address['city']\n",
                        "    elif 'village' in address:\n",
                        "        anchor['city'] = address['village']\n",
                        "    elif 'town' in address:\n",
                        "        anchor['city'] = address['town']\n",
                        "    elif 'country' in address:\n",
                        "        anchor['city'] = address['country']\n",
                        "    else:\n",
                        "        pprint(info)\n",
                        "        break\n",
                        "    anchors_with_city.append(anchor)\n",
                        "\n",
                        "dump_json(anchors_with_city, POPULATION_CITY_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### city population"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 62,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "2601\n"
                              ]
                        }
                  ],
                  "source": [
                        "population_by_city = {}\n",
                        "POPULATION_THRESHOLD = 100000\n",
                        "\n",
                        "with open(CITIES_500_FILE, \"r\", encoding=\"utf8\") as f:\n",
                        "    for row in f.readlines():\n",
                        "        row = [value.strip() for value in row.split(\"\\t\")]\n",
                        "        city = row[1]\n",
                        "        ascii_city = row[2]\n",
                        "        # Iso2 code\n",
                        "        country = row[8]\n",
                        "        population = row[14]\n",
                        "        city_key = f\"{city}_{country}\"\n",
                        "        ascii_city_key = f\"{ascii_city}_{country}\"\n",
                        "        if population != \"\":\n",
                        "            population = int(float(population))\n",
                        "            if population >= POPULATION_THRESHOLD:\n",
                        "                population_by_city[ascii_city_key] = population\n",
                        "                population_by_city[city_key] = population\n",
                        "\n",
                        "\n",
                        "print(len(population_by_city)//2)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 64,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors_with_city = load_json(POPULATION_CITY_FILE)\n",
                        "anchors_with_pop = []\n",
                        "\n",
                        "for anchor in anchors_with_city:\n",
                        "    try:\n",
                        "        anchor['population'] = population_by_city[f\"{anchor['city']}_{anchor['country_code']}\"]\n",
                        "        anchors_with_pop.append(anchor)\n",
                        "    except:\n",
                        "        try:\n",
                        "            # unix\n",
                        "            cmd = f\"cat {CITIES_500_FILE} | grep \\\"{anchor['city']}\\\"\"\n",
                        "            # windows\n",
                        "            # t = \"type\"\n",
                        "            # cmd = f\"{t} {CITIES_500_FILE} | findstr \\\"{anchor['city']}\\\"\"\n",
                        "            process = Popen(cmd, stdout=PIPE, stderr=STDOUT, shell=True)\n",
                        "            output, err = process.communicate()\n",
                        "            row = output.decode().split(\"\\t\")\n",
                        "            anchor['population'] = int(row[14])\n",
                        "            anchors_with_pop.append(anchor)\n",
                        "        except Exception as e:\n",
                        "            anchor['population'] = 0\n",
                        "            anchors_with_pop.append(anchor)\n",
                        "\n",
                        "dump_json(anchors_with_pop, POPULATION_CITY_FILE)"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "### city density"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 65,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors_with_pop = load_json(POPULATION_CITY_FILE)\n",
                        "\n",
                        "# Load the population density data\n",
                        "with rasterio.open(POPULATION_DENSITY_FILE) as dataset:\n",
                        "    population_density = dataset.read(1)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 67,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors_with_density = []\n",
                        "for anchor in anchors_with_pop:\n",
                        "    lat, lon = anchor['lat'], anchor['lon']\n",
                        "    point = gpd.GeoDataFrame(geometry=gpd.points_from_xy([lon], [lat]))\n",
                        "\n",
                        "    # Convert lat-lon to pixel coordinates\n",
                        "    xmin, ymin, xmax, ymax = dataset.bounds\n",
                        "    transform = from_bounds(xmin, ymin, xmax, ymax, dataset.width, dataset.height)\n",
                        "    row, col = dataset.index(lon, lat)\n",
                        "\n",
                        "    # Extract the population density value\n",
                        "    population_density_value = population_density[row, col]\n",
                        "    anchor['density'] = float(population_density_value)\n",
                        "\n",
                        "    anchors_with_density.append(anchor)\n",
                        "\n",
                        "dump_json(anchors_with_density, POPULATION_CITY_FILE)"
                  ]
            }
      ],
      "metadata": {
            "kernelspec": {
                  "display_name": ".venv",
                  "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.9.13"
            },
            "orig_nbformat": 4
      },
      "nbformat": 4,
      "nbformat_minor": 2
}