Comparing Mashup Platforms Using JSON & MySQL - Part 1 - Emit JSON

Technologies Used: PHP 5, MySQL 4.1.2, JavaScript, JSON

Software Used: PHP Designer, Aptana, Firebug, Firefox

While I was not particularly impressed with the ArcWeb Explorer & ArcWeb Services session on Tuesday, it did inspire me to want to do a mashup using my own data sources. I also wanted to compare the mashup creation process & end results across multiple possible mashup platforms. I decided to use JSON since all the mashup API’s are in Javascript and it is an output format that both Google & Yahoo web services provide. I am already using JSON with the data entry interface for our Breeding Bird Census data that Plateau Land & Wildlife collects each spring.

JSON (or JavaScript Object Notation) is a well described data exchange format that is rapidly gaining favor over XML for web service data transport as it is less verbose and automatically parsed by Javascript.

While I had already created a JSON object model for use with our survey management system, it was structured in a totally different way than what I really wanted for my mashup. I also wanted to demonstrate doing this from scratch.

Step 1 - Design your JSON Object (i.e. Class Structure)

You want to put some thought into designing your class structure that you will emit. JSON can represent arrays, classes, and arrays of classes. The Good news is that JSON is VERY flexible, The Bad news is that JSON is VERY flexible. A general rule to follow in determining where you should use an array or class is that indexed lists = arrays, anything else is a class, or a class property (key:value pair). I just used the high tech method of pen & paper to design my JSON object, but you could also use any XML schema generation tool, VS2005 Class Designer, VIsio, etc.

My desired end product is a map showing the location & number of each point count station, with different symbols if it has been surveyed or not. A mouse over or click event will bring up an html table of with information about species observed and provide a Google search link for the bird based on it’s scientific name. The common name usually gets you all kinds of results whereas the scientific name gets you good reference results.

Thus I need Station number, lat/lon for the station, total observed, number of each species observed and scientific name for each species.

JSON Object

{"Observations":

[{"Station":

{"number":"9", "lat":"30.9999",

 "lon":"-98.9999","total_species":"9",

{"Species":

[{"common":"American Robin",

"number":"9", "code":"AMRO",

"scientific":"Turdus migratorius "},

{species cont....}

] } }

]

}

Or in other words:

Array Observations contains Station classes with Properties

1. number
2. lat
3. lon
4. total_species
5. Array Species contains unnamed object with Properties
   1. common
   2. number
   3. code
   4. scientific

Step 2 - Create your JSON Object (i.e. Class Structure)

There are a whole bunch of ways to do this and really depends on what language your are programing in on your server side code. It basically boils down to either populating a class and then parse it to JSON through a method or class in your language, or directly writing the JSON string from some data source.

I chose the later for this project because my PHP install didn’t have a built in method for this. But rolling your own is pretty easy.

1. Get results set from SQL query

function getData($cid,$myYear,$func) {

//cid = client id, myYear=year of survey, func=callback function to handle response

//Get a count of unique species occurring on the property for each point survey station, even those which have no recorded observations

$q_species=”SELECT DISTINCT st.sta_num as station, COUNT(species) as species, st.lat, st.lon

FROM Stations as st LEFT JOIN (Surveys as su) USING (station_id)

WHERE (st.client_id=$cid AND (su.client_id=$cid OR su.client_id IS NULL) AND su.year=$myYear)

GROUP BY station ORDER BY station;”;

 

//Get the details of species observed at each station

$q_obs=”SELECT su.species, su.number, sp.code, sp.scientific, st.sta_num as station

FROM Surveys as su, Species as sp, Stations as st

WHERE su.client_id=$cid AND su.year=$myYear AND su.species=sp.common

AND su.station_id=st.station_id

ORDER BY station, sp.species_id”;

 

//Create the results sets                     

$resSpecies = mysql_query($q_species);

$resObs = mysql_query($q_obs);

 

2. Create the JSON String 

//Loop through each Station & create the JSON string for that Station & it’s data

$myJSON= $func.’({”Observations”:[';

While ($c=mysql_fetch_array($resSpecies)){

$myJSON.= '{"Station":{"number":"'.$c["station"].’”, “lat”:”‘.$c["lat"].’”, “lon”:”‘.$c["lon"].’”, “total_sp”:”‘.$c["species"].’”, “Species”:[';

                 While ($s=mysql_fetch_assoc($resObs)){

                                   if ($s["station"]==$c["station"]){

$observations.=’{”common”:”‘.$s["species"].’”, “number”:”‘.$s["number"].’”, “code”:”‘.$s["code"].’”, “scientific”:”‘.$s["scientific"].’”},’;

                                }

                }

                $myJSON.=rtrim($observations, ‘,’).’]}},’;

                $observations=”";

                mysql_data_seek($resObs,0);

 }

 

$myJSON=rtrim($myJSON, ‘, ‘).’]});’;

return $myJSON;

}

Step 3 - Emit JSON

This is the easiest part of all. Just return the JSON string using whatever the response function is for the language you are using. PHP can use the HTTP Send Request / Get Response functions to give you more control & learn more about what is happening on either side of the exchanges. But, I kept it simple and just echo-ed the string.

echo getData($cid,$myYear,$func);

NEXT STEPS:

>Make & Handle request in your webpage/webapp

>Create mashup