Farmers Market City Search: Easy to Extend Data API's with Pentaho Data Integration

Thursday, September 10, 2015 - 13:15
With another successful Labor Day camping trip behind us, we’ve only got a few trips left before we pack it in for 2015.  On this most recent trip, we wanted to find some fresh, local Sweet Corn for the evening meal. Finding a local Farmer’s Market or Produce stand near the campground is way better than hauling it in.
This fit perfectly with our recent Farmers Market location service post. I wanted to search for Farmers Markets in Saint Paul, IN. But there’s one problem: I need a zip code to use that service. I don’t know the zip code for Saint Paul. 
Sure, I could always check, get the zip code, and then search, but when multiple steps are needed, that’s one more barrier between me and Sweet Corn that I’d prefer to avoid. This time, I averted disaster by using zip 47272 and hitting the Shelbyville market. A less than ideal search.
Well, here it is the week after Labor Day and I still can’t get that rotten taste out of my mouth of having to search for a zipcode to find my sweet corn. One page request would make it much easier when calling that service from a campground’s spotty cell service on my smart phone. So, I decided to take a look at modifying our service example.  I needed to modify it so it can take a zip code OR a city name.
With a little googling, I found this wonderful site;, which offered a very simple to use (and free) RESTful service to retrieve zip codes for a City and State. The service would need to make sure it added state as well as city name. While It’s only a few days of driving to get to the Saint Paul, MN farmers market, I’d prefer to save a little gas and search for Saint Paul, IN instead.
So, using PDI’s Rest Client step I added a call using their RESTful format of With this, we could get zipcodes for any city in the US. I limited the search to just the US for this example since our Farmers Markets are limited to the US.
But, how do I actually format my URL call to work?  Well using the parameter system in PDI, I just added city and state parameters and use the built in templating/replacement system. It’s so simple it’s almost magic.
I did a test call using my browser (I’m sure I could have read the documentation, but I always like to “see” the actual results), and it showed me the JSON payload returned.
   "country abbreviation":"US",
         "place name":"Saint Paul",
         "post code":"47272",
   "country":"United States",
   "place name":"Saint Paul",
   "state abbreviation":"IN"
Yup, there it is: post code. Now all I had to do was extract the zip codes from the JSON package and pass them through our existing logic. That’s simple enough: use a JSON input step, extract out the zipcode, match the stream format, and we have our flow.
But wait, not so fast!  How can I make this service call work now for *either* a zip code or a city/state?  I previously added parameters of city & state to this transformation, but now I need to test to see which has been provided on the request.
This is incredibly easy to do with PDI and its library of steps and visual programming metaphor. I can take the parameter values, put them on the stream and then just use a simple filter. With this, I can check the zip parameter, if it’s been provided then there’s no reason to search for it. This resulted in the following transformation that is now enhanced to search for zip code or city/state.
With our friendlier URL as discussed in the earlier article, we can now make the following call:
When you execute the API, it does all the work for you. It calls all the new zipcode lookup API and the previous APIs, and returns that delicious looking JSON. For example:
         "marketname":" Shelby County Farmers Market",
         "location":"Public Square, Shelbyville, Indiana, 46176",
         "address1":"Public Square",
            "May to October Wed:4:00 PM - 7:00 PM",
            "Sat:8:00 AM - 12:00 PM"
            "Baked goods",
            "Cheese and/or dairy products",
            "Crafts and/or woodworking items",
            "Cut flowers",
            "Fresh fruit and vegetables",
            "Fresh and/or dried herbs",
            "Canned or preserved fruits, vegetables, jams, jellies, preserves, salsas, pickles, dried fruit, etc.",
            "Maple syrup and/or maple products",
            "Nursery stock, bedding plants",
            "Plants in containers",
            "Soap and/or body care products",
            "Trees, shrubs"
         "marketname":" Greensburg/Decatur County Farmers' Market",
         "location":"Decatur, Indiana",
(Shortened for readability)
So, in a matter of a few hours I modified the existing Farmer’s Market data provider service to include the ability to search by zipcode OR city/state. This greatly improves its usability for any weary campers looking for some fresh produce in a new campground!

Contact us today to find out how Inquidia can show you how to collect, integrate and enrich your data. We do data. You can, too.

Would you like to know more?

Sign up for our fascinating (albeit infrequent) emails. Get the latest news, tips, tricks and other cool info from Inquidia.