geonames-to-sqlite

Import / update geonames data in to sqlite

geonameid         : integer id of record in geonames database
name              : name of geographical point (utf8) varchar(200)
asciiname         : name of geographical point in plain ascii characters, varchar(200)
alternatenames    : alternatenames, comma separated varchar(5000)
latitude          : latitude in decimal degrees (wgs84)
longitude         : longitude in decimal degrees (wgs84)
feature class     : see http://www.geonames.org/export/codes.html, char(1)
feature code      : see http://www.geonames.org/export/codes.html, varchar(10)
country code      : ISO-3166 2-letter country code, 2 characters
cc2               : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code       : fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code       : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) 
admin3 code       : code for third level administrative division, varchar(20)
admin4 code       : code for fourth level administrative division, varchar(20)
population        : bigint (8 byte int) 
elevation         : in meters, integer
dem               : digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.
timezone          : the timezone id (see file timeZone.txt) varchar(40)
modification date : date of last modification in yyyy-MM-dd format

Formated as:

	"gid" INTEGER NOT NULL UNIQUE , 
	"iso" TEXT, 
	"name" TEXT, 
	"asciiname" TEXT, 
	"latitude" REAL, 
	"longitude" REAL, 
	"timezone" TEXT, 
	"population" INTEGER, 
	"elevation" INTEGER, 
	"alternate_names" TEXT, 
	"feature_class" TEXT, 
	"feature_code" TEXT, 
	"cc2" TEXT, 
	"admin1_code" TEXT, 
	"admin2_code" TEXT, 
	"admin3_code" TEXT, 
	"admin4_code" TEXT, 
	"dem" INTEGER, 
	"updated" TEXT

This script is for worldcities but could easyly be adopted for other data.

Get the file from:
http://download.geonames.org/export/dump/allCountries.zip

untar / unzip the file in to the geonames-to-sqlite folder and run the php script.

Be aware this is a large file!