I am about to finish a travel Facebook App. The design looks promising, especially because of the info and pictures I could crawl together. Few more days and I will release a first draft. For now the answer to: "how to create your own country API?"
New here? You might want to subscribe to my blog by email or RSS.
That seemed a challenge at first, but combining 1 flat txt file and 2 APIs, I got a pretty decent database that served my purpose.
It serves the travel fans under us to browse countries, viewing nice photos of those countries, and comment on each country page as well (logged in with Facebook). If it works, and people start to add content themselves, it will become a personal experience. It starts basic, new features can always be added.
But before this could work in any way, the app has to provide some content itself. Especially with a travel / country app, photos are unmissable!
In this posts some examples how to get the data required for a country database to build a nice travel app ...
First things first, the raw data
I used geonames.org to get some basic info for all ISO 2 country codes, see a list here. I found small, nice country flag gif images here (filenames match the two-char ISO2 names -> AE, DE, NL, etc.).
2nd of all: the worldbank API to get exact longitudes and latitudes per country.
Lists of these parameters per country exist. We need them for the Tixik API (next step). However the lists I found (example), did not work quite well with Tixik. What worked best were the results from the worldbank API , for example: for Spain - you can make the call to get all latitudes/ longitudes for all countries with this call. Then update the DB with these values and you have a pretty nice set to work with:
3rd: tixik country photo links
Now the fun starts: photos! A travel app is about showing photos. You will see the endresult, but here is the workhorse to get to the data: Tixik. I discovered it by accident, but it has a very simple yet efficient api. Quote from Tixik: "We have hundreds of thousands of presentations with pictures and texts in multiple languages. Would you like to have some of them on your website?"
And the answer is "of course we want that!". Well, it is plain simple and now you see the "why" of the latitude and longitude of the last step: http://www.tixik.com/en/api/nearby?lat=36.106121163930377&lng=28.07762145996093&limit=10&key=demo. You can raise the limit to get more photos, the key you put in when you sign up with just your name and email. To get some nice picture (links) from Andorra click here.
When processing this info for each country, you have a database with about 10.000 pic links:
Some code to get this working ...
I will keep it short given the size of this post. So not all details, just the core things you need to get started. :
- Create 2 DB tables: one to hold the country info and another to hold links to the images (from Tixik). Relation is the two letter iso2 country code.
CREATE TABLE IF NOT EXISTS `countries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`iso2` varchar(2) NOT NULL,
`country` varchar(50) NOT NULL,
`capital` varchar(50) NOT NULL,
`area` varchar(20) NOT NULL,
`population` varchar(20) NOT NULL,
`continent` varchar(2) NOT NULL,
`currCode` varchar(3) NOT NULL,
`currName` varchar(20) NOT NULL,
`phone` varchar(10) NOT NULL,
`langs` varchar(2) NOT NULL,
`neighbours` varchar(30) NOT NULL,
`longitude` varchar(20) DEFAULT NULL,
`latitude` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `images`
--
CREATE TABLE IF NOT EXISTS `images` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country` varchar(2) NOT NULL,
`name` varchar(200) NOT NULL,
`tn` varchar(200) NOT NULL,
`tn_big` varchar(200) NOT NULL,
`created` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
while(<>){
chomp;
@arr = split(/t/, $_);
$insert ="INSERT INTO countries (id, iso2, country, capital, area, population, continent, currCode, currName, phone, langs, neighbours)
VALUES (NULL, '$arr[0]', '$arr[1]', '$arr[2]', '$arr[3]', '$arr[4]', '$arr[5]', '$arr[6]', '$arr[7]', '$arr[8]', '$arr[9]', '$arr[10]');";
print "$insertn";
}
Run it as $ perl test.pl country_file - it will take that file as input, loop through it, split each line into an array based on the tab character, etc.
function getLocationParamsWorldBank(){
// saved xml from http://api.worldbank.org/countries?per_page=300
$wbUrl = 'api.worldbank.countries.all.xml';
$ns='wb';
$xml = @simplexml_load_file($wbUrl);
$params = array();
$counter=0;
foreach($xml->children($ns,true) as $i) {
$query = "UPDATE countries SET longitude='".$i->longitude."',
latitude='".$i->latitude."' ";
$query .= "WHERE iso2='".$i->iso2Code."';";
echo $query ."<br>";
}
}
No SQL security here, because I am going to do the import myself. I can also do the import with PHP MySql commands but for this exercise I wanted to see the commands first and then copy paste. If you want to let PHP do all the work, run something like this on the $query (after having created the database $link object):
$r = $link->query($query);
$returnID = $link->insert_id;
if(is_numeric($returnID)) {
..
function getThumbs($country) {
$params = getLocationParamsDb($country); // skipped for brevity
$xml = queryTixik($params);
$images = processTixikImages($xml);
writeImagesToDb($country, $images); // skipped for brevity
}
function queryTixik($params, $limit = 50) {
$latitude = $params['latitude']; // form "getLocationParamsDb"
$longitude = $params['longitude'];
$tixKey = "--obtain from tixik.com -- ";
$txUrl = "http://www.tixik.com/en/api/nearby?lat=$latitude&lng=$longitude&limit=$limit&key=$tixKey";
$xml = simplexml_load_file($txUrl, 'SimpleXMLElement', LIBXML_NOCDATA);
return $xml;
}
function processTixikImages($xml){
$images = array(); $i = 0;
foreach ($xml->items->item as $item) {
if (@fclose(@fopen($item->tn, "r")) ) {
$images[$i]['name'] = str_replace("'","'",$item->name); // not screw up sql statement
$images[$i]['tn'] = $item->tn; // thumbnail
$images[$i]['tn_big'] = $item->tn_big; // bigger version of image
$i++;
}
}
}
I became a fan of shortening my functions. It is a good programming practice. Next time I turn the 4 functions into 8, it really makes things cleaner.
Nice trick: LIBXML_NOCDATA allows you to read the CDATA outputs (comments) which are not processed per default by simplexml_load_file. XML Tixik returns, has this occasionally so err on the safe side.
Another thing that I wanted to make sure was that each thumbnail link was actually accessible. Here is how: basically you can test for the positive return of:
if (@fclose(@fopen($item->tn, "r")) ) { .. }
Again, I skipped some details to not make this post too long. Play with it, I learned quite a bit putting myself up for this exercise. The result was worth the effort though.
Sneak preview
OK, you might be wondering now: "the data is there, now what?!" ... I will give you a quick preview of the FB app I am building ...
