Home porting

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!