DuckDB Installation

library(duckdb)
library(DBI)
library(dplyr)

# Connect to DuckDB
con <- dbConnect(duckdb::duckdb(), dbdir = "world.duckdb", read_only = FALSE)

# Helper function to query with dplyr
query_db <- function(table_name) {
  tbl(con, table_name)
}

# Basic queries using dplyr syntax
countries <- query_db("countries") %>%
  filter(region == "Europe") %>%
  select(name, iso2, iso3, capital, currency) %>%
  collect()

print("European Countries:")
print(countries)

# Cities in United States
us_cities <- query_db("cities") %>%
  filter(country_name == "United States") %>%
  select(name, state_name, latitude, longitude) %>%
  head(10) %>%
  collect()

print("US Cities:")
print(us_cities)

# Analytical query - cities by region
cities_by_region <- query_db("cities") %>%
  left_join(query_db("countries") %>% select(id, region), 
            by = c("country_id" = "id")) %>%
  filter(!is.na(region)) %>%
  group_by(region) %>%
  summarise(
    city_count = n(),
    avg_latitude = mean(as.numeric(latitude), na.rm = TRUE),
    avg_longitude = mean(as.numeric(longitude), na.rm = TRUE)
  ) %>%
  arrange(desc(city_count)) %>%
  collect()

print("Cities by Region:")
print(cities_by_region)

# Raw SQL query for complex analysis
population_density <- dbGetQuery(con, "
  SELECT 
    c.region,
    COUNT(DISTINCT c.name) as countries,
    COUNT(cities.name) as total_cities,
    ROUND(AVG(TRY_CAST(cities.latitude AS DOUBLE)), 2) as avg_lat,
    ROUND(AVG(TRY_CAST(cities.longitude AS DOUBLE)), 2) as avg_lon
  FROM countries c
  LEFT JOIN cities ON c.id = cities.country_id
  WHERE c.region IS NOT NULL
  GROUP BY c.region
  ORDER BY total_cities DESC
")

print("Population Density by Region:")
print(population_density)

# Close connection
dbDisconnect(con, shutdown = TRUE)

const duckdb = require('duckdb');

class WorldDatabase {
    constructor(dbPath = 'world.duckdb') {
        this.db = new duckdb.Database(dbPath);
        this.conn = this.db.connect();
    }
    
    query(sql, params = []) {
        return new Promise((resolve, reject) => {
            if (params.length > 0) {
                this.conn.all(sql, ...params, (err, rows) => {
                    if (err) reject(err);
                    else resolve(rows);
                });
            } else {
                this.conn.all(sql, (err, rows) => {
                    if (err) reject(err);
                    else resolve(rows);
                });
            }
        });
    }
    
    async getCountries(region = null, limit = null) {
        let sql = 'SELECT * FROM countries';
        const params = [];
        
        if (region) {
            sql += ' WHERE region = ?';
            params.push(region);
        }
        
        if (limit) {
            sql += ` LIMIT ${limit}`;
        }
        
        return await this.query(sql, params);
    }
    
    async getCitiesByCountry(countryName, limit = null) {
        let sql = 'SELECT * FROM cities WHERE country_name = ?';
        const params = [countryName];
        
        if (limit) {
            sql += ` LIMIT ${limit}`;
        }
        
        return await this.query(sql, params);
    }
    
    async analyzePopulationByRegion() {
        const sql = `
            SELECT 
                region,
                COUNT(DISTINCT c.name) as countries,
                COUNT(DISTINCT s.name) as states,
                COUNT(DISTINCT cities.name) as cities,
                ROUND(AVG(TRY_CAST(cities.latitude AS DOUBLE)), 2) as avg_latitude,
                ROUND(AVG(TRY_CAST(cities.longitude AS DOUBLE)), 2) as avg_longitude
            FROM countries c
            LEFT JOIN states s ON c.id = s.country_id  
            LEFT JOIN cities ON c.id = cities.country_id
            WHERE c.region IS NOT NULL
            GROUP BY c.region
            ORDER BY cities DESC
        `;
        return await this.query(sql);
    }
    
    async findNearbyCities(lat, lon, maxDistanceKm = 100) {
        const sql = `
            SELECT 
                name,
                country_name,
                state_name,
                latitude,
                longitude,
                ROUND(6371 * acos(
                    cos(radians(?)) * cos(radians(TRY_CAST(latitude AS DOUBLE))) *
                    cos(radians(TRY_CAST(longitude AS DOUBLE)) - radians(?)) +
                    sin(radians(?)) * sin(radians(TRY_CAST(latitude AS DOUBLE)))
                ), 2) as distance_km
            FROM cities
            WHERE latitude IS NOT NULL 
            AND longitude IS NOT NULL
            AND 6371 * acos(
                cos(radians(?)) * cos(radians(TRY_CAST(latitude AS DOUBLE))) *
                cos(radians(TRY_CAST(longitude AS DOUBLE)) - radians(?)) +
                sin(radians(?)) * sin(radians(TRY_CAST(latitude AS DOUBLE)))
            ) <= ?
            ORDER BY distance_km
            LIMIT 20
        `;
        return await this.query(sql, [lat, lon, lat, lat, lon, lat, maxDistanceKm]);
    }
    
    close() {
        this.conn.close();
        this.db.close();
    }
}

// Usage example
async function example() {
    const db = new WorldDatabase();
    
    try {
        console.log('=== European Countries ===');
        const countries = await db.getCountries('Europe', 5);
        countries.forEach(country => {
            console.log(`${country.name} (${country.iso2}) - ${country.capital}`);
        });
        
        console.log('\n=== US Cities ===');
        const usCities = await db.getCitiesByCountry('United States', 5);
        usCities.forEach(city => {
            console.log(`${city.name}, ${city.state_name}`);
        });
        
        console.log('\n=== Population Analysis ===');
        const analysis = await db.analyzePopulationByRegion();
        analysis.forEach(region => {
            console.log(`${region.region}: ${region.countries} countries, ${region.cities} cities`);
        });
        
        console.log('\n=== Cities near New York ===');
        const nearby = await db.findNearbyCity ies(40.7128, -74.0060, 100);
        nearby.slice(0, 10).forEach(city => {
            console.log(`${city.name}, ${city.state_name}: ${city.distance_km} km`);
        });
        
    } catch (error) {
        console.error('Database error:', error);
    } finally {
        db.close();
    }
}

example();