Country City Longitude Latitude Database setup in php
Ok, a nice Friday night here and I am working on a project that highly involves the geospatial data. For that I needed data comprising the info I needed.
Thankfully this website provided me the data as one big text file.
Then I wrote this simple snippet for porting the data to my kitchen database đ
The script should be used only for temporary purpose and by no means on production. It doesn’t do any sanitation or what so ever the good programming.. but it does the job!
I created country_city table with the desired columns only;
CREATE TABLE `country_city` ( `country_city_id` int(11) NOT NULL AUTO_INCREMENT, `country_code` char(2) NOT NULL, `city_name` varchar(100) DEFAULT NULL, `region` varchar(100) DEFAULT NULL, `latitude` float DEFAULT NULL, `longitude` float DEFAULT NULL, PRIMARY KEY (`country_city_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3170001 DEFAULT CHARSET=latin1
Then I run the following code to populate the data.
<?php
/**
* @author Kaleb Woldearegay
* Code to be used "as is". Not responsible for any consequences after using the code.
*/
const ROWS_TO_INSERT = 5000;
$file_handle="/path/to/worldcitiespop.txt";
$fh=fopen($file_handle, 'r');
$query_pool=array();
$db_handler=mysql_connect("localhost", "root", "");
mysql_select_db("your database here");
//assumed the database is connected at this moment..
while(!feof($fh))
{
$line=fgets($fh, 1024);
$data=explode(',', $line);
$city=mysql_real_escape_string($data[1]);
$query_pool[] = "(null, '{$data[0]}', '{$city}', '{$data[3]}', {$data[5]}, {$data[6]})";
if (count($query_pool)>=ROWS_TO_INSERT)
{
$query = "INSERT INTO country_city VALUES " . implode(',', $query_pool);
if (!mysql_query($query, $db_handler)) die('Error: '. mysql_error($db_handler));
$query_pool=array();
}
}
That is it,
Enjoy!
Post to Facebook From app – Using PHP
connect to sftp from php
Add scrollbar to mysql result in terminal
mysql command not found on mac after installation
$_POST vs $HTTP_RAW_POST_DATA vs php://input and enctype