Sometimes I want to pull some data from another website that is in a table but they do not provide a way for me to get the data in some easily usable format. There are many things you can do to accomplish this. However sometimes you just want something quick and easy. You can use YQL to make a SQL type query against a URL and get back JSON or XML data.
Example
————
I wanted the information that was in the table on Sectional Raster Aeronautical Charts. It is just a table of VFR sectional charts. As you can see there is “Chart Name”, “Current Edition No. and Date”, “Next Edition No. and Date”, and inside of the table there is a link to download the charts.
To get this data into JSON just go to Yahoo! Query Language Console. Once there you just make a query. In order to get the correct table on the page I had to give the query some XPath Syntax and also the url to the page. Change the radio button to JSON and click Test to make sure you get what you want back.
select * from html where xpath=”//table[@title=’Sectional Raster Aeronautical Charts’]” and url=”http://avn.faa.gov/index.asp xml=aeronav/applications/VFR/chartlist_sect”
After you are done testing and everything you will end up with a link at the bottom you can use. An example in php would be something like this. The goo.gl is just the url from the bottom of the console run though Google’s url shortener. Also for this to work with the below php example you have to uncheck the “Dianostics” box and also remove the callback function. If not it will give you a json parse error and php is not very helpful with json errors.
<?php
$data = json_decode(file_get_contents(“http://goo.gl/IQQ5c”), true);
foreach($decodedData[‘query’][‘results’][‘table’][‘tr’] as $chart) {
echo “Chart Name: “.$chart[‘td’][0][‘p’].”\n”;
}
?>
This will print out.
Chart Name: Albuquerque
Chart Name: Anchorage
Chart Name: Atlanta
Chart Name: Bethel
Chart Name: Billings
