Converting CSV to Any Geospatial Format Using DuckDB and DuckDB Spatial Extension.

csv_to_geospatial.py

# dataset ref : https://www.kaggle.com/datasets/max-mind/world-cities-database/code
# pip install duckdb

import duckdb

con = duckdb.connect()

data = """
-- Environment setup
SET enable_progress_bar = true;
SET enable_progress_bar_print = true;
INSTALL httpfs; -- Only if HTTP file system is needed
INSTALL spatial; -- Required for spatial functions
LOAD httpfs;    -- Only if HTTP file system is needed
LOAD spatial;   -- Required for spatial functions

-- Creating a table schema matching the CSV structure
CREATE TABLE worldcities (
    Country VARCHAR,
    City VARCHAR,
    AccentCity VARCHAR,
    Region VARCHAR,
    Population DOUBLE,
    Latitude DOUBLE,
    Longitude DOUBLE
);

-- Loading data from CSV into the table
COPY worldcities FROM 'https://link.storjshare.io/raw/jwh4kesndug5ooocnxqr3hytw4wa/truemaps-public%2Fpublic-datasets%2Fmaxmind%2Fworldcitiespop.csv.gz' (DELIMITER ',', HEADER, IGNORE_ERRORS);

-- Creating and exporting GeoJSON (or other formats as specified by gdal_driver)
COPY (
    SELECT 
        *, -- Selects all fields from the table
        ST_GeomFromText('POINT(' || Longitude || ' ' || Latitude || ')') AS geom
    FROM worldcities
) TO 'worldcitiespop.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON', SRS 'EPSG:4326');

"""

results = con.execute(data).fetchall()