Skip to content

Select * from World

PHP I have been having a lot of fun with two Yahoo! technologies that have been evolving quickly. YQL and GeoPlanet. The first, YQL, puts an SQL-like interface on top of all the data on the Internet. And the second, GeoPlanet, introduces the concept of a WOEID (Where-On-Earth ID) that you can think of as a foreign key for your geo-related SQL expressions.

First some example YQL queries to get you used to this concept of treating the Internet like a database. Go to the YQL Console and paste these queries into the console to follow along.

select * from geo.places where text="SJC"

This looks up "SJC" in GeoPlanet and returns an XML result containing this information:
        <placeTypeName code="14">Airport</placeTypeName>
        <name>Norman Y Mineta San Jose International Airport</name>
        <country code="US" type="Country">United States</country>
        <admin1 code="US-CA" type="State">California</admin1>
        <admin2 code="" type="County">Santa Clara</admin2>
        <locality1 type="Town">Downtown San Jose</locality1>
        <postal type="Zip Code">95110</postal>
The first thing to note is the woeid. It is just an integer, but it uniquely identifies San Jose Airport. If you were to search for "San Jose Airport" instead of "SJC" you would find that one of the places returned has the exact same woeid. So, the woeid is a way to normalize placenames. The other thing to note here is that you get an approximate bounding box. This is what makes the woeid special. A place is more than just a lat/lon. If I told you that I would meet you in Paris next week, that doesn't tell you as much as if I told you that I would meet you at the Eiffel Tower next week. If we pretend that the Eiffel tower is in the center of Paris, those two locations might actually have the same lat/lon, but the concept of the Eiffel Tower is much more precise than the concept of Paris. The difference is the bounding box. And yes, landmarks like the Eiffel Tower or Central Park also have unique woeids. Try it:
select * from geo.places where text="Eiffel Tower"
Note that the YQL console also gives you a direct URL for the results. This last one is at*%20from%20geo.places%20where%20text%3D%22Eiffel%20Tower%22&format=xml. Not the prettiest URL in the world, but you can feed that to a simple little PHP program to integrate these YQL queries in your PHP code. Something like this:
$url = "";
$q   = "select * from geo.places where text='Eiffel Tower'";
$fmt = "xml";
$x = simplexml_load_file($url.urlencode($q)."&format=$fmt");
For a higher daily limit on your YQL queries you can grab an OAuth consumer key and use the OAuth-authenticated YQL entry point. There is an example of how to use pecl/oauth with YQL at Take a close look at the YQL query in that example. It is:

select * from html where xpath= '//tr//a[@href="/wiki/Capital_(political)"]/../../../td[2]/a/text()'  
 and url in (select url from search.web where url like '%wikipedia%' and query='Denmark' limit 1) 
Sub-selects! So, we do a web search for urls containing the string 'wikipedia' whose contents contains 'Denmark'. That is going to get us the Wikipedia page for Denmark. We then perform an xpath query on that page to extract the text of the link containing the name of the capital of Denmark. Change 'Denmark' in that query to any country and the query will magically return the capital of that country. So, YQL is also a general-purpose page scraper.

But back to the woeid. Places belong to other places, and they are next to other places and they contain even more places. That is, a place has a parent, siblings and children. You can query all of these. Here is a woeid explorer application written entirely in Javascript:

Try entering some places or points of interest around the world and click on the various radio buttons and then the "Geo It" button to see the relationship between the places and the bounding boxes for all these various places. If you look at the source for this application you can see that it uses YQL's callback-json output, so there is no server-side component required to get this to work. Try doing a search for "Eiffel Tower" and turn on the "Sat" version of the map. You can see that the bounding box is pretty damn good. Try it for other landmarks. Then walk up the parent tree, or across the siblings. Or check out the Belongs-To data.

Once you have a woeid for a place, you can start using it on other services such as Upcoming:
select * from where woeid=2487956
and Flickr:
select * from where woe_id=2487956
(yes, I know, it would have been nice if the column names were consistent there)

And finally you can also add YQL support for any open API out there. There is a long list of them here:

To use one of these, try something like this:
use '' as yelp; 
select * from yelp where term='pizza' and location='sunnyvale, ca' and ywsid='6L0Lc-yn1OKMkCKeXLD4lg'
As a bit of Geo and API nerd, this is super cool to me. I hope you can find some interesting things to do with this as well. If you build something cool with it, please let me know.


PHP One of the things I have been playing with lately is Yahoo!'s SearchMonkey project. It appeals to me on many different levels. The geeky name is a play on GreaseMonkey. But instead of writing plugins that run locally in the browser, SearchMonkey is a way to write plugins for the Yahoo! Search results page that change the appearance of the results themselves. Best explained with an example. Assume I am looking for a Japanese restaurant, and on my search results page I see:

That's ok, I guess. It tells me it is somewhere in Redwood City and that it is a neighborhood restaurant, whatever that means. Compare that to:

This gets me a real address and phone number plus a number of other useful bits of information. That is the first level SearchMonkey appeals to me on. The usefulness is obvious. My usefulness test is to see if I can explain it to my mother. Having her search for recipes and get pictures of dishes, ingredients and preparation times right on the search results page makes this an easy sell.

The second level this appeals to me on is the way it is implemented. Writing these SearchMonkey plugins becomes much simpler if the site you are writing the plugin for uses microformats of some sort. hCard, hCalendar, hReview, hAtom, xfn or generic structured eRDF or RDFa tags. The data can also be collected via a separate XML feed that can then be converted via XSLT in the SearchMonkey developer tool. The microformat data is collected and indexed and when you go to write a plugin and specify the url pattern you are writing the plugin for, it will find whatever indexed metadata it has for that url. If it doesn't have what you are looking for, you can still write a custom data scraper to get it, but that gets a bit more involved. I really like that the easy path is to add some sort of semantic markup to the pages. Yes, as Micah points out, this is not the (uppercase) Semantic Web, but it is still a push towards semantic markup. Having such a tangible and visible result of adding semantic tags is going to encourage people other than microformat geeks to do so. The more semantic markup we get, the better off the Web is.

The third part that appeals to me is the way the plugins are written. You write a little snippet of PHP. It is actually a method in a class you can't see, but its job is to return an associative array of data such as the title to display, the summary, extra links to show and whatever other key/value pairs you might want in the output. Because you have a full-featured scripting language available, you can write quite complicated logic in one of these plugins and pull whatever data you want from the site the plugin is written for. You can also write an add-on to your plugin which is called an Infobar. It is a little bar that is shown below the plugin and from an Infobar you can access arbitrary external services. This example shows it well:

This one shows an OpenTable reservation link and a Yelp review, but almost anything can go there as long as you can squeeze it into the limited space you have.

The SearchMonkey is still in its infancy. It needs developer support. If you are in Silicon Valley, please come to the Developer Launch Party next week on Thursday May 15. See the link for details. If you aren't in the area, or even if you are, sign up for a developer account at and help encourage the Web to become more semantic.
Categories: PHP
Last modified on 2008-05-09 06:05

Want a PHP job?

PHP Want to work on some of the busiest and coolest web apps in the world?

Do you like Flickr, and want to work downtown San Francisco?

Or perhaps you are into music, movies or TV and want to work out of Santa Monica? Jumpcut? Or have you seen Address Book, Personals, Search, Premium Services, Hot Jobs? Want to do interesting things combining PHP and Flash?

Yes, I get a referral bonus, but I need more toys. You get a cool job though, so I think we are even.

Send me your resume and let me know what sort of stuff you are interested in or poke around on and let me know which job interests you and I will forward your resume to the appropriate hiring manager.

[edited to remove RSS ad test I had forgotten about]