How to install and maintain GeoWorldMap on LAMP (Linux, Apache, MySql, PHP)

We have automated the process of installing and maintaining an instance of our world cities database – GeoWorldMap in a LAMP (Linux, Apache, MySql, PHP) environment. The following process will work on both shared and dedicated server environments.

Note: The following instructions assume that you have SSH access to your server. If you don’t know what that means then check your hosting company’s FAQ – they will probably recommend that you use something like Putty (http://chiark.greenend.org.uk/~sgtatham … nload.html) to establish a SSH session with your server. If you do not have shell access, then you may still be able to complete the following steps via cPanel or an equivalent facility, but unfortunately I am unable to provide you with step by step instructions to do this as each provider’s facilities are different. In this case, you will need to research you local provider’s documentation and possibly seek their assistance.

Once you have established a command line session with your server, then download the installation archive in to an empty directory on your server, (perhaps create a geobytes directory in your home directory), then extract the contents of the archive, and then finally run the installation script.
Here are the commands to do this: (You need to enter them at a command/shell prompt.)

mkdir geobytes
cd geobytes
curl http://geobytes.com/downloads/geoworldmaploader.tar > geoworldmaploader.tar 
tar -xvf geoworldmaploader.tar
./install.sh

The installation script will then prompt you for the database name, user, and password that it should use for access to your local MySql database. The script will then create the required tables in the database, and import the raw data in to the tables. Upon completion, it will check that the importation was successful and will create a cron job that is configured to execute once per day and check for and install any map updates. If your hosting provider has restricted command line access to crontab, then you may need to create the cron job manually via cPanel or an equivalent facility. Note: The script that you want the cron job to run is called install_update.sh

Once the installation script has completed, then you will be able to access the data via embedded SQL queries in your PHP code. There are some sample queries in our Geo World Map Implementation Guide.

Alternatively, if you just want to download the raw data to check it out before importing it in to your database via the above script, then you download a copy from here: http://geobytes.com/FreeServices.htm

Finally, here is a “view” that creates a virtual table containing the Geobytes Location Codes that you see on our IP Locator page along with a cities fully qualified city name.

DROP VIEW IF EXISTS my_db.locationcodes;
CREATE VIEW my_db.locationcodes AS
select
concat(co.Internet,r.Code,c.Code) AS LocationCode,
concat(c.City,', ',r.Code,', ',co.Country) AS FullCityName,
c.Latitude AS Latitude,
c.Longitude AS Longitude
from  cities c
join regions r on r.RegionID = c.RegionID
join countries co on co.CountryId = r.CountryID ;

And here is a working code example that shows how to use it.

SubnetAddress: $IpAddress";

// Connecting, selecting database
$link = mysql_connect("SERVER_NAME", "USER_NAME", "PASSWORD")
   or die("Could not connect : " . mysql_error());
//echo "
Connected successfully
";
mysql_select_db("geonetmap") or die("Could not select database");

// Performing SQL query
$query = "SELECT c.city, r.region, co.country 
FROM cities AS c 
JOIN subnets AS s on c.cityid=s.cityid 
JOIN regions AS r on c.regionid=r.regionid 
JOIN countries AS co on c.countryid=co.countryid 
where s.subnetaddress='$IpAddress'";

//PRINT($query);
$result = mysql_query($query) or die("Query failed : " . mysql_error());

// Printing results in HTML 
echo "n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "tn";
   foreach ($line as $col_value) {
       echo "ttn";
   }
   echo "tn";
}
echo "
$col_value
n";

// Free resultset 
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>