#!/bin/sh

# Retrieve some French Cities Data
wget -N http://sql.sh/ressources/sql-villes-france/villes_france.csv

# Extract id,latitue,longitude
awk -F, '{print $3 "\t" $20 "\t" $21}' villes_france.csv | sed 's/"//g' > data.tsv

# Create a DB script
echo "CREATE TABLE city (name TEXT, longitude REAL, latitude REAL);" > init.sql
sed 's/^/insert into city values("/;s/\t/",/;s/\t/,/;s/$/);/' data.tsv >> init.sql

# Insert into a DB
rm -f cities.db
sqlite3 cities.db < init.sql

# Get the nearest cities from toulouse
# Change me if you want :)
TARGET_LAT=43.6
TARGET_LON=1.43333
echo "select * from city ORDER BY (ABS(LATITUDE-$TARGET_LAT)+ABS(LONGITUDE-$TARGET_LON))  LIMIT 25;" > query.sql
sqlite3 cities.db < query.sql