cities-db v0.1.0

Worlddb

!!! NOT READY, PLEASE DON'T USE IT !!!

World database with countries, cities and provinces + cli tool to export data sets in json.

This product includes GeoLite2 data created by MaxMind, available from http://www.maxmind.com

Special Thanks to:

convertcsv.com for creating the tool that allowed me to convert the csv files to a sql insert

hashrocket.com for the awesome tutorial about how to export data from Postgresql in JSON format.

CLI

Usage: worlddb options

Commands:

help  Display help

Options:

-C, --columns [value]      table columns (defaults to "id, locale_code as locale, country_iso_code as country_code, country_name, city_name, time_zone")
-c, --countryCode [value]  country code (defaults to "CA")
-d, --database [value]     Database name (defaults to "worlddb")
-f, --file [value]         filename (defaults to "worlddb.json")
-H, --help                 Output usage information
-h, --host [value]         PG host (defaults to "localhost")
-P, --password [value]     PG password (defaults to "worlddb")
-p, --port <n>             PG port number (defaults to 5433)
-t, --table [value]        table name (defaults to "location")
-u, --user [value]         PG user (defaults to "worlddb_admin")
-v, --version              Output the version number

For now it's only possible to export a subset of all the data based on the country code. But if you create a database, using the sql scripts in the src folder, you'll be able to query and export anything you want.

SQL SCRIPTS

• located in src/scripts
• src/scripts/init.sql : creates a database called worlddb and a user for the database
• src/scripts/create.sql: creates a location table to host the data
• src/scripts/populate: adds all the data to the table

The magic query used to generate the json file is this:

select array_to_json(array_agg(row_to_json(t)))
from (
     select ${columns}
     from location
     where country_iso_code = '${countryCode}'
 ) t

Columns and countryCode are passed as arguments from the cli.

TODO

• convert sql scripts to node js
• conditionally added the where clause

version 0.1.0