small script to import contries and continents into a postgis table

countries_to_postgis.py

import requests
import json

from pathlib import Path
from utils_sql import InterfaceSQL # custom psycopg2
from shapely.geometry import shape


if __name__ == '__main__':

    countries_filepath = Path('data/coutries.geojson')
    if not countries_filepath.exists():
        response = requests.get('https://d2ad6b4ur7yvpq.cloudfront.net/naturalearth-3.3.0/ne_50m_admin_0_countries.geojson')
        if response.status_code == 200:
            with open(countries_filepath, 'wb') as f:
                f.write(response.content)


    interface_sql = InterfaceSQL('', '', '', '', '')

    query_create_tables = """
    DROP TABLE IF EXISTS countries;
    DROP TABLE IF EXISTS continents;

    CREATE TABLE  countries(
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        geom GEOMETRY('MULTIPOLYGON', 4326)
    );

    CREATE TABLE continents(
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        country_id INTEGER
    );

    """

    interface_sql.send_query(query_create_tables)


    geojson = json.loads(countries_filepath.read_text())

    for feature in geojson['features']:
        properties = feature['properties']
        geom = shape(feature['geometry'])  # shapely geometry

        insert_query_country = """
        INSERT INTO countries (name, geom) VALUES (%s, ST_GeomFromText(%s, 4326)) RETURNING id;
        """
        variables = (properties['name'], geom.wkt)

        returned = interface_sql.send_query(insert_query_country, variables, results=True)
        country_id = returned[0][0]


        insert_query_continent = """
        INSERT INTO continents (name, country_id) VALUES (%s, %s)
        """
        variables = (properties['continent'], country_id)
        returned = interface_sql.send_query(insert_query_continent, variables)