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": 1,
                  "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\n",
                        "\n",
                        "# If you set reproducibility to True you should get the same datasets used in our work\n",
                        "# Set to False, if you want to run on today's data\n",
                        "reproducibility = True"
                  ]
            },
            {
                  "attachments": {},
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Retrieve atlas probes and anchors"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 2,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "selected probes: 9901 (19.74%), rejected: 40263, 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": 3,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "selected anchors: 784 (57.14%), rejected: 588, geoloc rejected: 0\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",
                        ")"
                  ]
            },
            {
                  "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": 4,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "selected probes: 9901\n",
                                    "selected anchors: 784\n",
                                    "{'address_v4': '45.138.229.91',\n",
                                    " 'asn_v4': None,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [4.9275, 52.3475], 'type': 'Point'},\n",
                                    " 'id': 1}\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",
                                    " 'id': 3}\n",
                                    "{'address_v4': '84.82.235.83',\n",
                                    " 'asn_v4': 1136,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [4.8485, 52.3605], 'type': 'Point'},\n",
                                    " 'id': 26}\n",
                                    "{'address_v4': '85.225.8.201',\n",
                                    " 'asn_v4': 2119,\n",
                                    " 'country_code': 'SE',\n",
                                    " 'geometry': {'coordinates': [18.0295, 59.3075], 'type': 'Point'},\n",
                                    " 'id': 40}\n",
                                    "{'address_v4': '77.95.64.208',\n",
                                    " 'asn_v4': 199422,\n",
                                    " 'country_code': 'FR',\n",
                                    " 'geometry': {'coordinates': [4.8595, 45.7285], 'type': 'Point'},\n",
                                    " 'id': 43}\n",
                                    "{'address_v4': '190.114.43.21',\n",
                                    " 'asn_v4': 27901,\n",
                                    " 'country_code': 'CL',\n",
                                    " 'geometry': {'coordinates': [-70.9005, -33.6125], 'type': 'Point'},\n",
                                    " 'id': 51}\n",
                                    "{'address_v4': '81.187.145.168',\n",
                                    " 'asn_v4': None,\n",
                                    " 'country_code': 'GB',\n",
                                    " 'geometry': {'coordinates': [1.1095, 51.3695], 'type': 'Point'},\n",
                                    " 'id': 55}\n",
                                    "{'address_v4': '109.90.168.210',\n",
                                    " 'asn_v4': 3209,\n",
                                    " 'country_code': 'DE',\n",
                                    " 'geometry': {'coordinates': [9.2915, 49.1475], 'type': 'Point'},\n",
                                    " 'id': 81}\n",
                                    "{'address_v4': '73.140.67.67',\n",
                                    " 'asn_v4': None,\n",
                                    " 'country_code': 'US',\n",
                                    " 'geometry': {'coordinates': [-111.8285, 33.2605], 'type': 'Point'},\n",
                                    " 'id': 86}\n",
                                    "{'address_v4': '213.136.0.6',\n",
                                    " 'asn_v4': 12859,\n",
                                    " 'country_code': 'NL',\n",
                                    " 'geometry': {'coordinates': [5.6215, 52.0285], 'type': 'Point'},\n",
                                    " 'id': 92}\n",
                                    "{'address_v4': '212.159.71.10',\n",
                                    " 'asn_v4': 6871,\n",
                                    " 'country_code': 'GB',\n",
                                    " 'geometry': {'coordinates': [-3.1885, 55.9395], 'type': 'Point'},\n",
                                    " 'id': 95}\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": 5,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "10685\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": 6,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "from scripts.utils.file_utils import load_json, dump_json\n",
                        "from scripts.utils.helpers import distance\n",
                        "from default import (\n",
                        "    COUNTRIES_TXT_FILE,\n",
                        "    COUNTRIES_JSON_FILE,\n",
                        "    USER_ANCHORS_FILE,\n",
                        "    USER_PROBES_FILE,\n",
                        ")"
                  ]
            },
            {
                  "cell_type": "markdown",
                  "metadata": {},
                  "source": [
                        "## Get country dataset"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 7,
                  "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": 8,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "probes = load_json(USER_PROBES_FILE)\n",
                        "\n",
                        "anchors = load_json(USER_ANCHORS_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 9,
                  "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(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": 10,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "error country code SS is unknown\n",
                                    "error country code AX is unknown\n",
                                    "Number of Atlas probes kept: 9819, rejected: 82\n",
                                    "Number of Atlas anchors kept: 777, rejected: 7\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",
                        ")\n",
                        "print(\n",
                        "    f\"Number of Atlas anchors kept: {len(filtered_anchors)}, rejected: {len(anchors) - len(filtered_anchors)}\"\n",
                        ")\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": 11,
                  "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 (\n",
                        "    compute_remove_wrongly_geolocated_probes,\n",
                        "    compute_rtts_per_dst_src,\n",
                        ")\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": 12,
                  "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": 13,
                  "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": 14,
                  "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": 15,
                  "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": 16,
                  "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": 17,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "vp_coordinates_per_ip = {}\n",
                        "\n",
                        "for anchor in anchors:\n",
                        "    if (\n",
                        "        \"address_v4\" in anchor\n",
                        "        and \"geometry\" in anchor\n",
                        "        and \"coordinates\" in anchor[\"geometry\"]\n",
                        "    ):\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": 6,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stderr",
                              "output_type": "stream",
                              "text": [
                                    "2024-09-02 11:43:35::INFO:root:analysis:: Violations: 124\n",
                                    "2024-09-02 11:43:35::INFO:root:analysis:: Violations: 34\n",
                                    "2024-09-02 11:43:35::INFO:root:analysis:: Violations: 22\n",
                                    "2024-09-02 11:43:35::INFO:root:analysis:: Violations: 14\n",
                                    "2024-09-02 11:43:35::INFO:root:analysis:: Violations: 10\n",
                                    "2024-09-02 11:43:35::INFO:root:analysis:: Violations: 6\n",
                                    "2024-09-02 11:43:35::INFO:root:analysis:: Violations: 2\n"
                              ]
                        }
                  ],
                  "source": [
                        "removed_anchors = set()\n",
                        "filter = \"\"\n",
                        "\n",
                        "rtt_per_srcs_dst = compute_rtts_per_dst_src(\n",
                        "    ANCHORS_MESHED_PING_TABLE, filter, threshold=300\n",
                        ")\n",
                        "\n",
                        "removed_anchors = compute_remove_wrongly_geolocated_probes(\n",
                        "    rtt_per_srcs_dst, vp_coordinates_per_ip, vp_distance_matrix, removed_anchors\n",
                        ")"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 7,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "probes = load_json(USER_PROBES_FILE)\n",
                        "probes.extend(anchors)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 8,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "vp_coordinates_per_ip = {}\n",
                        "\n",
                        "for probe in probes:\n",
                        "    if (\n",
                        "        \"address_v4\" in probe\n",
                        "        and \"geometry\" in probe\n",
                        "        and \"coordinates\" in probe[\"geometry\"]\n",
                        "    ):\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": 9,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stderr",
                              "output_type": "stream",
                              "text": [
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 2730\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 1978\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 1496\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 1172\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 956\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 874\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 800\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 736\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 674\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 616\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 558\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 500\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 446\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 404\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 366\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 328\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 294\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 262\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 234\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 210\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 190\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 172\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 154\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 138\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 124\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 110\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 100\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 92\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 84\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 76\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 68\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 60\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 54\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 48\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 42\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 36\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 30\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 26\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 22\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 18\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 14\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 12\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 10\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 8\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 6\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 4\n",
                                    "2024-09-02 11:44:17::INFO:root:analysis:: Violations: 2\n"
                              ]
                        },
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "Removing 54 probes\n"
                              ]
                        }
                  ],
                  "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 = {\n",
                        "    ip: vp_coordinates_per_ip[ip]\n",
                        "    for ip in vp_coordinates_per_ip\n",
                        "    if ip not in removed_anchors\n",
                        "}\n",
                        "\n",
                        "rtt_per_srcs_dst = compute_rtts_per_dst_src(\n",
                        "    PROBES_TO_ANCHORS_PING_TABLE, filter, threshold=300\n",
                        ")\n",
                        "\n",
                        "removed_probes = compute_remove_wrongly_geolocated_probes(\n",
                        "    rtt_per_srcs_dst, vp_coordinates_per_ip, vp_distance_matrix, removed_anchors\n",
                        ")\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)"
                  ]
            },
            {
                  "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": 15,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "9989 total anchors\n"
                              ]
                        }
                  ],
                  "source": [
                        "anchors = load_json(USER_ANCHORS_FILE)\n",
                        "\n",
                        "print(f\"{len(anchors)} total anchors\")\n",
                        "\n",
                        "removed_probes = load_json(USER_REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 16,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "removed_probes = load_json(USER_REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 10,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "0 anchors to keep\n",
                                    "20 anchors removed because of incorrect geolocation\n",
                                    "721 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 geolocation_replication.street_lvl_traceroutes where resp_addr = '{target_ip}' and dst_addr = '{target_ip}' and src_addr <> '{target_ip}'\"\n",
                        "    )\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": 11,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "9989 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": 12,
                  "metadata": {},
                  "outputs": [
                        {
                              "name": "stdout",
                              "output_type": "stream",
                              "text": [
                                    "9952 probes to keep\n",
                                    "37 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": 13,
                  "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(\n",
                        "    ANCHORS_MESHED_PING_TABLE, filter, threshold=100\n",
                        ")"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 14,
                  "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": 15,
                  "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": 16,
                  "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(\n",
                        "    PROBES_TO_ANCHORS_PING_TABLE, filter, threshold=100\n",
                        ")"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 17,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "# Remove probes that are in the meshed table but not in the dataset\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": "markdown",
                  "metadata": {},
                  "source": [
                        "# CODE NOTE TESTED AFTER THIS POINT"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": 18,
                  "metadata": {},
                  "outputs": [
                        {
                              "ename": "NameError",
                              "evalue": "name 'PROBES_AND_USER_ANCHORS_FILE' is not defined",
                              "output_type": "error",
                              "traceback": [
                                    "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
                                    "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
                                    "Cell \u001b[0;32mIn[18], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m original_probes \u001b[38;5;241m=\u001b[39m load_json(\u001b[43mPROBES_AND_USER_ANCHORS_FILE\u001b[49m)\n\u001b[1;32m      2\u001b[0m good_anchors \u001b[38;5;241m=\u001b[39m load_json(USER_ANCHORS_FILE)\n\u001b[1;32m      3\u001b[0m good_probes \u001b[38;5;241m=\u001b[39m load_json(USER_PROBES_FILE)\n",
                                    "\u001b[0;31mNameError\u001b[0m: name 'PROBES_AND_USER_ANCHORS_FILE' is not defined"
                              ]
                        }
                  ],
                  "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": null,
                  "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": null,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "pairwise_probes = load_json(PAIRWISE_DISTANCE_FILE)\n",
                        "removed_probes = load_json(REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": null,
                  "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"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": null,
                  "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": null,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "def greedy_selection_probes_impl(probe, distance_per_probe, selected_probes):\n",
                        "\n",
                        "    distances_log = [\n",
                        "        math.log(distance_per_probe[p])\n",
                        "        for p in selected_probes\n",
                        "        if p in distance_per_probe and distance_per_probe[p] > 0\n",
                        "    ]\n",
                        "    total_distance = sum(distances_log)\n",
                        "    return probe, total_distance"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": null,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "vp_distance_matrix = load_json(PAIRWISE_DISTANCE_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": null,
                  "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": null,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "token = \"4f6c895ec9224f\"\n",
                        "\n",
                        "maxmind_tree_file = GEOLITE_FILE"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": null,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors = load_json(USER_ANCHORS_FILE)\n",
                        "\n",
                        "removed_probes = load_json(REMOVED_PROBES_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": null,
                  "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": null,
                  "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 (\n",
                        "    USER_ANCHORS_FILE,\n",
                        "    POPULATION_CITY_FILE,\n",
                        "    CITIES_500_FILE,\n",
                        "    POPULATION_DENSITY_FILE,\n",
                        ")"
                  ]
            },
            {
                  "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": null,
                  "metadata": {},
                  "outputs": [],
                  "source": [
                        "anchors = load_json(USER_ANCHORS_FILE)"
                  ]
            },
            {
                  "cell_type": "code",
                  "execution_count": null,
                  "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(\n",
                        "        {\"target_ip\": ip, \"lat\": lat, \"lon\": lon, \"country_code\": country_code}\n",
                        "    )\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": null,
                  "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": null,
                  "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[\n",
                        "            f\"{anchor['city']}_{anchor['country_code']}\"\n",
                        "        ]\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": null,
                  "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": null,
                  "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.10.12"
            },
            "orig_nbformat": 4
      },
      "nbformat": 4,
      "nbformat_minor": 2
}