Exporting and Converting Spatial Data

# %pip install duckdb pandas

import duckdb
import pandas as pd

print(f"DuckDB version: {duckdb.__version__}")
print(f"Pandas version: {pd.__version__}")

con = duckdb.connect()

con.install_extension("httpfs")
con.load_extension("httpfs")

con.install_extension("spatial")
con.load_extension("spatial")

con.sql(
    "SELECT extension_name, loaded, installed FROM duckdb_extensions() WHERE extension_name IN ('httpfs', 'spatial')"
).show()

con.sql(
    """
CREATE TABLE IF NOT EXISTS cities AS
SELECT * FROM 'https://data.gishub.org/duckdb/cities.parquet'
"""
)

con.table("cities").show()

con.sql(
    "SELECT COUNT(*) AS city_count, MIN(population) AS min_pop, MAX(population) AS max_pop, AVG(population) AS avg_pop FROM cities"
).show()

con.sql("SUMMARIZE SELECT population FROM cities").show()

con.sql("SUMMARIZE SELECT population FROM cities").show()

cities_df = con.table("cities").df()
cities_df.head()

cities_df.info()

# Export only large cities in the United States
us_large_cities_df = con.sql(
    """
    SELECT name, population, latitude, longitude
    FROM cities
    WHERE country = 'USA' AND population > 500000
    ORDER BY population DESC
    """
).df()

us_large_cities_df

# Export without geometry for statistical analysis
cities_stats_df = con.sql(
    """
    SELECT name, country, population, latitude, longitude
    FROM cities
    """
).df()

cities_stats_df.head()

cities_stats_df = con.sql("SELECT * EXCLUDE geometry FROM cities").df()
cities_stats_df.head()

cities_with_wkt_df = con.sql(
    """
    SELECT name, country, population,
           ST_AsText(geometry) AS geometry_wkt
    FROM cities
    LIMIT 5
    """
).df()
cities_with_wkt_df

# Export with WKT geometry for GeoPandas conversion
df_for_geopandas = con.sql(
    """
    SELECT name, country, population,
           latitude, longitude,
           ST_AsText(geometry) AS wkt
    FROM cities
    """
).df()

import geopandas as gpd
from shapely import wkt

gdf = gpd.GeoDataFrame(
    df_for_geopandas, geometry=df_for_geopandas["wkt"].apply(wkt.loads), crs="EPSG:4326"
)
gdf.head()

con.sql("COPY cities TO 'cities.csv' (HEADER, DELIMITER ',')")

con.sql(
    """
    COPY (
        SELECT * FROM cities WHERE country='USA'
    ) TO 'cities_us.csv' (HEADER, DELIMITER ',')
    """
)

con.sql(
    """
    COPY (
        SELECT country,
               COUNT(*) AS city_count,
               SUM(population) AS total_population,
               AVG(population) AS avg_population
        FROM cities
        GROUP BY country
        HAVING COUNT(*) > 5
        ORDER BY total_population DESC
    ) TO 'country_stats.csv' (HEADER, DELIMITER ',')
    """
)

con.sql(
    """
    COPY (
        SELECT * EXCLUDE geometry FROM cities
    ) TO 'cities_no_geometry.csv' (HEADER, DELIMITER ',')
    """
)

con.sql(
    """
    COPY (
        SELECT name, country, population,
               ST_X(geometry) AS longitude,
               ST_Y(geometry) AS latitude
        FROM cities
    ) TO 'cities_with_coords.csv' (HEADER, DELIMITER ',')
    """
)

con.sql(
    """
    COPY (
        SELECT name, country, population,
               ST_AsText(geometry) AS geometry_wkt
        FROM cities
        LIMIT 10
    ) TO 'cities_with_wkt.csv' (HEADER, DELIMITER ',')
    """
)

con.sql("COPY cities TO 'cities.json'")

con.sql("COPY (SELECT * FROM cities WHERE country='USA') TO 'cities_us.json'")

con.sql("COPY (SELECT * EXCLUDE geometry FROM cities) TO 'cities_attributes.json'")

con.sql(
    """
    COPY (
        SELECT name, country, population,
               ST_X(geometry) AS longitude,
               ST_Y(geometry) AS latitude
        FROM cities
    ) TO 'cities_with_coords.json'
    """
)

con.install_extension("excel")
con.load_extension("excel")

con.sql(
    "COPY (SELECT * EXCLUDE geometry FROM cities) TO 'cities.xlsx' WITH (FORMAT xlsx, HEADER true)"
)

con.sql(
    """
    COPY (
        SELECT name, country, population,
               ST_X(geometry) AS longitude,
               ST_Y(geometry) AS latitude,
               ST_AsText(geometry) AS wkt
        FROM cities
    ) TO 'cities_with_coords.xlsx' WITH (FORMAT xlsx, HEADER true)
    """
)

con.sql(
    """
    COPY (
        SELECT country,
               COUNT(*) AS city_count,
               SUM(population) AS total_population,
               AVG(population) AS avg_population,
               MAX(population) AS largest_city_pop
        FROM cities
        GROUP BY country
        HAVING COUNT(*) >= 5
        ORDER BY total_population DESC
    ) TO 'country_summary.xlsx' WITH (FORMAT xlsx, HEADER true)
    """
)

con.sql("COPY cities TO 'cities.parquet'")

con.sql("COPY (SELECT * FROM cities WHERE country='USA') TO 'cities_us.parquet'")

con.sql(
    """
    COPY cities TO 'cities_parquet/'
    WITH (FORMAT PARQUET, PARTITION_BY (country))
    """
)

con.sql("COPY cities TO 'cities.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON')")

con.sql(
    "COPY (SELECT * FROM cities WHERE country='USA') TO 'cities_us.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON')"
)

con.sql(
    """
    COPY (
        SELECT name, country, population,
               CASE
                   WHEN population > 10000000 THEN 'Megacity'
                   WHEN population > 5000000 THEN 'Large City'
                   ELSE 'City'
               END AS size_category,
               geometry
        FROM cities
        WHERE population > 1000000
    ) TO 'large_cities_categorized.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON')
    """
)

con.sql(
    """
    COPY (SELECT * FROM cities WHERE country='USA')
    TO 'cities_us.geojson'
    WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS('WRITE_BBOX=YES', 'COORDINATE_PRECISION=5'))
    """
)

con.sql("COPY cities TO 'cities.shp' WITH (FORMAT GDAL, DRIVER 'ESRI Shapefile')")

con.sql("COPY cities TO 'cities.gpkg' WITH (FORMAT GDAL, DRIVER 'GPKG')")

con.sql(
    "COPY (SELECT * FROM cities WHERE population > 1000000) TO 'major_cities.gpkg' WITH (FORMAT GDAL, DRIVER 'GPKG')"
)