extract_universities.py

#!/usr/bin/env python3

"""
Script to insert the country data in the database

IT HAS TO BE RUN INSIDE ./manage.py shell
"""
import csv
import os
import time
from geopy.geocoders import Nominatim
import reverse_geocoder as rg

tmp = os.path.join(os.path.realpath(__file__), "../../assets/destinations.csv")
destinations_path = os.path.abspath(tmp)

tmp = os.path.join(
    os.path.realpath(__file__), "../../assets/destinations_extracted.csv"
)
destinations_extracted_path = os.path.abspath(tmp)

if not os.path.isfile(destinations_path):
    print(destinations_path)
    raise Exception("Missing file containing country data")

with open(destinations_path, "rt") as input:
    with open(destinations_extracted_path, "w") as output:
        print("ini")
        reader = csv.reader(input)
        spamwriter = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
        geolocator = Nominatim()

        failed = []
        i = 0
        for row in reader:
            # handle the header
            if i == 0:
                header = ["university", "city", "country", "lat", "lon"]
                spamwriter.writerow(header)
                i += 1
            else:
                query = row[2]  # + ', ' + row[1] + ', ' + row[0]

                while True:
                    try:
                        location = geolocator.geocode(query)
                        break
                    except:  # noqa: E722
                        print("error during query, retrying")
                        time.sleep(0.5)
                if location is not None:
                    coord = (location.latitude, location.longitude)
                    res = rg.search(coord, verbose=False)
                    line = [
                        row[2],
                        row[1],
                        res[0]["cc"],
                        location.latitude,
                        location.longitude,
                    ]
                    print(line)
                    spamwriter.writerow(line)
                else:
                    failed.append(query)

        print(failed)