[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
On 2/11/2015 1:49 AM, Gerald Bauer wrote: >I've started a new project, that is, /factbook.sql [1] that offers > an SQL schema for the World Factbook and also includes a pre-built > single-file SQLite database, that is, factbook.db [2] for download. Have you seen the XML/mysql versoin: http://jmatchparser.sourceforge.net/factbook/ it might be worth translating it into sqlite.
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
Hello, Thanks. Great advice. The SQLite schema code gets generated from the ActiveRecord (Ruby) source [1] e.g.: create_table :facts do |t| t.string :code, null: false # country code e.g. au t.string :name, null: false # country name e.g. Austria t.integer :area # e.g. 83,871 sq km t.integer :area_land # e.g. 82,445 sq km --use float - why? why not? t.integer :area_water # e.g. 1,426 sq km t.integer :population# e.g. 8,665,550 (July 2015 est.) t.float :population_growth# e.g. 0.55% (2015 est.) t.float :birth_rate# e.g. 9.41 births/1,000 population (2015 est.) t.float :death_rate # e.g. 9.42 deaths/1,000 population (2015 est.) t.float :migration_rate # e.g. 5.56 migrant(s)/1,000 population (2015 est.) t.timestamps end Good to see that "hand-coded" statements can get improved. Will try to clean-up the schema. Cheers. [1] github.com/worlddb/factbook/blob/master/lib/factbook/db/schema.rb
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
On 2 Nov 2015, at 7:45am, Gerald Bauer wrote: > I prefer the "simpler" non-generic way e.g. spelling out all fields e.g.: > > CREATE TABLE "facts"( > "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > "code" varchar(255) NOT NULL, A couple of quick comments: First, there are not variable-length text fields in SQLite. That field will be interpreted as TEXT and you cannot depend on truncation to 255 characters. I would advise you to change the above to "code" TEXT NOT NULL since this is the result you will get anyway. Second, although SQLite supports quoted column names, they're rather non-standard these days. SQLite can cope with double-quotes, MySQL can cope with ticks (back-apostrophes), MS SQL copes with brackets, etc.. Since every SQL engine copes well with unescaped names, e.g. code TEXT NOT NULL , and you don't use punctuation in your names, you might prefer to use that. Simon. (I am uncomfortably aware that one of my pieces of advice is to use a SQLite rule instead of a general standard, and the other is to use a general standard instead of a SQLite rule. I resort to Emerson: "A foolish consistency is the hobgoblin of little minds.")
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
Gerald, thanks for this, it looks most interesting on both levels. i.e. The data release and your project work. Thank you for the information and your project. On 2 November 2015 at 01:49, Gerald Bauer wrote: > Hello, > > I've started a new project, that is, /factbook.sql [1] that offers > an SQL schema for the World Factbook and also includes a pre-built > single-file SQLite database, that is, factbook.db [2] for download. > > What's the World Factbook? > > The World Factbook [3] published by the Central Intelligence Agency (CIA) > offers free 260+ country profiles in the public domain > (that is, no copyright(s), no rights reserved). > > Anyways, what's it good for? For example, to find the ten largest > countries by area, try: > > SELECT name, area FROM facts ORDER BY area DESC LIMIT 10; > > Resulting in: > > Russia | 17_098_242 > Canada | 9_984_670 > United States | 9_826_675 > China | 9_596_960 > Brazil | 8_515_770 > Australia | 7_741_220 > European Union | 4_324_782 > India | 3_287_263 > Argentina | 2_780_400 > Kazakhstan | 2_724_900 > >Or to find the ten largest countries by population, try: > > SELECT name, population FROM facts ORDER BY population DESC LIMIT 10; > >Resulting in: > >World | 7_256_490_011 >China | 1_367_485_388 >India | 1_251_695_584 >European Union | 513_949_445 >United States | 321_368_864 >Indonesia | 255_993_674 >Brazil | 204_259_812 >Pakistan | 199_085_847 >Nigeria| 181_562_056 >Bangladesh | 168_957_745 > >And so on. Note: Using the factbook command line tool and scripts > you can build yourself an up-to-date copy. > >Questions? Comments? Welcome. Enjoy. Cheers. > > [1] https://github.com/factbook/factbook.sql > [2] https://github.com/factbook/factbook.sql/releases > [3] https://www.cia.gov/library/publications/the-world-factbook > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer.
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
Hello, Thanks for your kind words and the links. About: > Have you seen the XML/mysql versoin: Yes, this is great and seems to be the "last" reader/project (of many others) that still is maintained and might get updated. The CIA changed the structure of the online (live) pages in April 2015 and this project as far as I can tell can only "process" the download archives (not the live pages) and, thus, the latest update is from June, 2014 - the project is still waiting for a new download archive (formerly happened about once a year). About the schema: The project uses the "easy" and generic way for properties e.g.: CREATE TABLE `factbook_fields` ( `id` int(11) NOT NULL, `categoryid` int(11) NOT NULL, `name` varchar(255) COLLATE utf8_bin NOT NULL, `description` text COLLATE utf8_bin NOT NULL, `rankorder` int(11) NOT NULL, `unit` varchar(50) COLLATE utf8_bin NOT NULL, `dollars` tinyint(4) NOT NULL, PRIMARY KEY (`version`,`id`), ) I prefer the "simpler" non-generic way e.g. spelling out all fields e.g.: CREATE TABLE "facts"( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "code" varchar(255) NOT NULL, "name" varchar(255) NOT NULL, --- -- Geography "area" integer, "area_land" integer, "area_water" integer, -- People and Society "population"integer, "population_growth" float, "birth_rate"float, "death_rate"float, "migration_rate"float, ... ) Again thanks for the link and comments. Always great to see (and study) alternatives. Cheers.
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
Hello, I've started a new project, that is, /factbook.sql [1] that offers an SQL schema for the World Factbook and also includes a pre-built single-file SQLite database, that is, factbook.db [2] for download. What's the World Factbook? The World Factbook [3] published by the Central Intelligence Agency (CIA) offers free 260+ country profiles in the public domain (that is, no copyright(s), no rights reserved). Anyways, what's it good for? For example, to find the ten largest countries by area, try: SELECT name, area FROM facts ORDER BY area DESC LIMIT 10; Resulting in: Russia | 17_098_242 Canada | 9_984_670 United States | 9_826_675 China | 9_596_960 Brazil | 8_515_770 Australia | 7_741_220 European Union | 4_324_782 India | 3_287_263 Argentina | 2_780_400 Kazakhstan | 2_724_900 Or to find the ten largest countries by population, try: SELECT name, population FROM facts ORDER BY population DESC LIMIT 10; Resulting in: World | 7_256_490_011 China | 1_367_485_388 India | 1_251_695_584 European Union | 513_949_445 United States | 321_368_864 Indonesia | 255_993_674 Brazil | 204_259_812 Pakistan | 199_085_847 Nigeria| 181_562_056 Bangladesh | 168_957_745 And so on. Note: Using the factbook command line tool and scripts you can build yourself an up-to-date copy. Questions? Comments? Welcome. Enjoy. Cheers. [1] https://github.com/factbook/factbook.sql [2] https://github.com/factbook/factbook.sql/releases [3] https://www.cia.gov/library/publications/the-world-factbook
