Google Fusion Tables, Alteryx and oh yes... Godzilla!

So you're probably wondering what does Godzilla, Google Fusion Tables (GFT) and Alteryx have in common? Well, GFT and Alteryx have more in common than Godzilla does with either, but just as I stumbled across a Godzilla movie on TV I figured how to pull data from GFT into Alteryx, so I thought it was only appropriate that I give it credit by placing his\her image on this post.

 =(;-)))

Seriously though, if you haven't done much with storing data in the 'cloud', working with GFT is a great and painless way to get comfortable with the process. To get started here are two of my favorite tutorials that cover how to load and edit data placed in GFT, to geocode it and how to create charts, maps, (and heatmaps!) with your data.

The first is Google's own Google Fusion Table Tutorials and the second is a YouTube video by Circle of Blue.

And now back to our regular programming...
Ok, so how do you bring GFT data into Alteryx? The process is rather straight forward yet the formatting is essential - more on that later. Data loaded into GFT can easily be manipulated via the Google Fusion Tables API and one of my favorite utilities, cURL, along with the Run Command Tool. Let's look at this example pointing to a sample table I created of road locations where certain Vermont vehicles were speeding at a particular point in time. If you select the link below a csv file is downloaded containing all records (thus the *) from this GFT which has a numeric identifier of 2745825:

http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+2745825"

The API URL request is simply made up of 'http://www.google.com/fusiontables/api/query?sql=' followed by the SQL query you want to run against the data loaded in the GFT identified by its Numeric ID. Here is a slightly more complex example using a SQL WHERE clause:

http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+2745825+WHERE+OverLimit+=+'27'

This will request all records where vehicles were travelling greater than 27 miles over the speed limit in this dataset. Configuring the Run Command Tool Properties with cURL to access this data looks like this:

Also remember that I mentioned format earlier? You must remind yourself that the API URL request you use be encoded. This means that the any characters within the URL must be in a format that can be transmitted over the Internet, which explains the 'plus' signs instead of spaces in the SQL query - here's a quick reference:

w3school's URL Encoding Reference Page

If you do not encode the API URL the download will not work. Just replace the desired SQL query shown in the example above with your own and don't forget to reference your GFT Numeric ID. Now you might be wondering how to find the Numeric ID. To do so you must open your GFT, then go to the File pull-down menu and choose About to view the properties for that table (see image below).

You must make certain that the Tables Visibility you are wanting to access is either set to Public or Unlisted. If it is set to Private, you must first authenticate with a username and password to get to the data. Discussing this is a bit more than I wish to cover in this post, yet I will point you to a good reference by Pimin Konstantin Kefaloukos (he goes by Kostas) who has a blog called skipperkongen.dk:

Google Fusion Tables Cheat Sheet

To give you an idea of what can be done with the data you upload into GFT, here is my original file displaying a larger set of Vermont vehicles based on miles over the speed limit as a Heatmap.

Till next time - Enjoy!
Ron @ All About GIS

Leveraging Location-Based API's Within Alteryx 7.0

Happy New Year Alteryx Users!

In my last post I introduced a Reverse Geocoding Macro that returned address information using the new Parse XML Tool in Alteryx 7.0 and the Google Maps API for Geocoding. Using the XML Parse Tool, you can extract the returned XML data from the API, giving users access to a whole host of data never before available within Alteryx. With the Google Maps API for Geocoding being only one of the many API's that return data in XML format, I wanted to blog about the various location-based API's you might be able to leverage when the 7.0 version of Alteryx is released next year.

Yet as I was gathering this info, I ran across a post by Kin Lane on his blog that summed up nicely what I wanted to say, so not to dupe what he had already written, I will provide a link to his post here:

Quick Walk Through World of Location & Places API's

Not to say that all of these API's return data in XML format for you to use, yet I hope it does start you thinking about the multitude of API's, the data that can be extracted from them and what you can do with it now that you have access. That said, you also must diligently dig through the data and see if it is appropriate for what you wish to use it for. Following the age old saying "A map is only as good as the data it represents"; be careful of what is being returned, as Whit Nelson found out and posted about here:

Why you shouldn't use the Yelp API

Wishing all of you a great New Year and please keep All About GIS in mind for your Alteryx training needs in 2012! 

Till next time, enjoy!

Ron @ All About GIS.net

Bulk Reverse Geocoding Macro for Alteryx 7.0 Beta!

Happy Holidays Alteryx Users!

Can I see a show of hands of those who are beta testing Alteryx 7.0?

If you are, then you already know about the great new features coming up in the next release. If not, check out these posts by Adam Riley (Chaos Reigns Within), Ned Harding, Linda Thompson and Rob McFadzean among others at the Alteryx Engine Works Blog. One post in particular by Ned discusses the new Parse XML Tool, which enables you to parse through an XML file and extract the data. This tool by itself opens up a whole host of possibilities accessing the numerous "free" API's (Application Programming Interfaces) available that pass back data in XML format - specifically some of the Google Map API Web Services used to create the macro I'll describe in this post.

http://geolocator.allaboutgis.net/

In this instance, using the Google Map Geocoding API, I was able to create a Bulk Reverse Geocoding Macro in the Beta release. Kudos go out to Cameron Steele of Intalytics and Jed Klink of McDonald's who provided much input on the end result, and thanks to Brett Gottdener at Alteryx on getting the batch macro configured correctly. It all started one day when Cameron and I were brainstorming about a concept. Working with the API, HTML 5.0 and JavaScript led to the creation of a world-wide Geocoding \ Reverse Geocoding Mashup called "Geolocator", of which you can view at this URL (image at right).

The GeoLocator Mashup provides basic geocoding functionality if you enter an address, or if you pick a point on the map, returns an address corresponding to that point (or reverse geocoding). But you need to know where you want to pick on the map to obtain your result. In talking with Jed about this mashup, which can also export the results of the geocode \ reverse geocode process as a csv file using a bit of jQuery, he wondered if it could do bulk reverse geocoding. Still working on a mashup for that, but it got me thinking about some of the new tools in the Alteryx beta. Sooo, back to the macro and how it functions...

To make the macro work, you input a list of Latitude and Longitude coordinate values. These are passed to the Google Maps Geocode Web Service using the Run Command Tool and cURL, which returns the results for each location. The results are passed to the Parse XML Tool, and for simplicity sake, I configured the tool to only parse for a specific Child element (formatted_address), or the first address returned per coordinate pair. Depending on the accuracy of the coordinates you pass and what country they are in typically defines the level of accuracy. To see a sample of what the Google Map Geocoding Web Service returns, click this link:

http://maps.google.com/maps/api/geocode/xml?latlng=38.976302,-94.665756&sensor=false&callback=parseme

To format the data after it is parsed, I used the Formula and Cross Tab Tools. Parsing the formatted_address element returns Address, Neighborhood, City, County, State, Zip Code, and Country for each record, if available.

One caveat though - use of the Google Map Geocoding API is limited to 2,500 geolocation requests per day, with Premier users being limited to 100,000 requests a day. If you exceed these amounts you will receive a "Request Denied" message instead of an address, so keep track of your record counts. Note that this macro was made primarily for the United States. The formatting will need to be modified for use on other countries.

Once Alteryx 7.0 is released, I'll provide the macro to Alteryx for posting on the Module Exchange for your use. If you are an Alteryx 7.0 Beta tester, let me know and I'll send the macro your way for further testing.

Shameless plug coming up! All About GIS is now offering Alteryx Training! We offer both distance learning as well as onsite training, for beginning users to power users. E-mail me for more details and book your training today!

Till next time, enjoy!
Ron @ allaboutgis.net

Don't Miss Out!

Hi Alteryx Users!

If you haven't subscribed to the All About GIS Alteryx 'Altryx' & Tips newsletter, you're missing out on helpful hints on how to use various Alteryx tools delivered right to your Inbox.

Since my last post I decided to change up the format a bit, offering detailed information about different tools rather than just tips and tricks. I've started offering the newsletter as a way to reserve the blog for more detailed projects, such as more complex Alteryx Modules, Macros and Wizards. Using the newsletters format provides me with a way to quickly provide tricks and tips in a faster, shorter format. To the right are images of the first two issues.

I'll still be posting via the blog, it's just that the posts may be spread out a bit. So if your interested, just send me your e-mail and I'll add you to the list. With each and every newsletter, you have the option to unsubscribe, making it easy for you to opt out whenever you choose. You can view the last two issues here:

Issue #1
http://alteryxnewsletter.allaboutgis.net/Issue1.html

Issue #2
http://alteryxnewsletter.allaboutgis.net/Issue2.html

Till next time, enjoy!

Ron

BTW, don't forget to check out Adam Riley's (previous UK Alteryx User Blog author) updated blog about Alteryx, Chaos Reigns Within!

The Quick and Easy Way to Learn More about Alteryx!

Happy Halloween Fellow Alteryx Users!

I'm finding that with each release of Alteryx there seems to be more that can be accomplished with the software, and along with that comes more opportunities to learn about the product. 

You might have recently noticed an addition to the upper right hand corner of the blog. It's a button to subscribe to the All About GIS "Al-Tryx" & Tips e-mail newsletter. Dedicated to the use of Alteryx and its users, this once a week e-mail newsletter is a short and to the point offering that covers one or two Alteryx tricks or tips in brief format. This is one of the best ways to quickly provide you little "snippets" of valuable free information on using Alteryx.

I'm using Google Connect to allow users to sign in using an existing Google, Yahoo, or Twitter account, making it very convenient for you to subscribe. 

Once you are signed in, you'll see this text to the right in place of the Subscribe button, allowing you to easily make changes if needed.

Here are some examples of what to expect in the newsletter:

When working with the Spatial Match Tool and you have two polygon files as inputs, place the larger polygon file as the Target (left) input. When working with points and a polygon file, the polygon file should always be the Target input.

Or 

Did you know that the Browse Tool Table view can display up to 2 billion records? If you have more than 2 billion records in your data stream, all the records will be written to a file when exported using this Tool even though the remaining records are not displayed.

Or

There are five methods to set a custom projection with the Output Tool Edit Projection option. They are listed below:

1. Well Known Name, such as WGS84 (the default in Alteryx) or NAD83.
2. EPSG (European Petroleum Survey Group) Geodetic Parameter Dataset ID's, like EPSG:27700.
3. Well Known Text (WKT), not to be confused with #1 above, and can be defined by either OpenGIS or ESRI dialects.
4. Web Map Service (WMS) auto projection.
5. Cartographic Projects Library coordinate system definition (PROJ.4).

AND If you haven't already, I encourage you to download and PUT TO USE many of the tips and tricks in the Alteryx tips-and-tricks-2011.pdf available from the Alteryx website here. These tips will save you a considerable amount of time and effort and it is well worth reading.

So if you wish to learn more about Alteryx with minimal effort, subscribe to the All About GIS "Al-Tryx" and Tips E-Mail Newsletter!

Till next time, enjoy!

Ron House
All About GIS, LLC
http://allaboutgis.net 

Viewing Data Points in Google Maps & Streetview

Hello All,

Well, its finally time for me to focus fully on my company, All About GIS, LLC! As a Geospatial and Business Intelligence service provider, All About GIS offers GIS and Alteryx professional services and training to the Retail and Telecommunication industries. Nuff said - please wish me well on my new endeavor!

Now for the main topic of this post. Have you ever wished you could view your Alteryx data points in a dynamic Google Map instead of the static images currently provided by the Map Tool? Then check out my Google Streetview and Map Viewer (GSMV) macro! If you have a spatial data point file, run the data through the GSMV Macro to output a local .html file, similar to the one at right.

There is only one question you need to answer in the macro, and that is where you wish the html output file to be placed. A select list is created of your data points to the left of the map. Choose a  location from the list to place a corresponding numbered map icon on the map. You'll need Internet access to zoom in and out, pan, and toggle between the standard Map and Satellite Google Map backgrounds.

Dragging the Google Pegman onto the map fills the map window with a Streetview panorama of the current location. Once you are finished navigating the surrounding streets, you can close the panorama window which returns you back to the map window. In order for the macro to run you are  required to supply the Address, City, State, Latitude, Longitude and Centroid of each location in your input.

You are limited to the number displayed on one page to 100 locations, which is the maximum number of points to efficiently view on one page.

I've documented how I created the macro (see above, pardon the artistic liberties I've taken) predominantly using the htmlpassthrough tag while using the Text Tool's Expert Mode. You can reference previous posts by me (Generating Interactive Web Pages Using Html, i.e. The Cuppa Joe Finder module) and on the UK Alteryxuser blog by Adam Riley (Street View Macro). The main difference between these Alteryx applications and this one is that multiple markers can be placed on the Google map that represent your locations of interest. Here are a few suggestions as to what you might use this macro for:

• verify geocoded points
• check out what, or who, is nearest to your locations
• view the visibility your location has, or will have from the street.

I'm hoping to post this macro on the Alteryx Module Exchange, yet if you wish to get it sooner, send me an e-mail titled "GSMV" and I'll reply with the macro. Note: This has only been tested with Internet Explorer 8.0 and higher.

Till next time, enjoy!

Ron

http://allaboutgis.net/

Map Theming With Same Color Gradients

Hi fellow Alteryx Users!

Today's post is a simple post, yet I wanted to quickly share something with you that might come in handy. I typically generate a number of maps with Alteryx using the Map Tool, yet I find that the palletes available for theming are a bit lacking, especially with same color gradient palettes, like the one used to create the map displayed on the left. No problem, it is extremely easy to modify and create your own palettes, and a number of topics have already been posted that describe how to do this. Check out these posts in the Alteryx forum:

Margarita Wilshire's - Adding my own palette...

Angela Ogle's - Adding Map\Chart Color Palettes...

or Adam Riley's Palette Importer and Exporter

I will not repeat what the posters have already done so well, but I do wish to provide you my ReportSettings.xml in hopes that it might save you some time and provide an example of what is possible. Here is a screen shot of some of the additional palettes that have been added:

Download My ReportSettings.XML File here...

Enjoy,

Ron

Slick Alteryx API Enabled Windows Azure DataMarket Mobile Retail Site Locator

Happy Holidays, Everyone!

Well it's been awhile since I've posted last - there has been a lot going on personally as well with Alteryx as Alteryx 2011 (renamed from 6.0) has now been released. Just recently I met with a few good friends over lunch who also happen to be Alteryx users. After catching up, the discussion quickly focused on new Alteryx offerings (Geek Alert!). I remember seeing a video recently about a Mobile Retail Store Locator created using the Alteryx API and data. Since they had said they had not seen it, I thought it would be good to post, or re-post this video, first seen on the Alteryx Website. So, for those of you that have overlooked it, here's the video from the Microsoft Professional Developers Conference:



Although the video primarily focuses on Alteryx data offered up via the Windows Azure Datamarket, it demonstrates the flexibility and power using the Alteryx API in a way I have not seen before. This definitely opens up a completely new realm from which to leverage Alteryx.

Wishing you all a great Holiday Season, I will be back next year with more interesting Alteryx topics!

Till next time, Enjoy!

Ron

Area 51 - Calling All Alteryx Users!

As I was strolling around on the web the other day, I ran across a very cool site called the Stack Exchange Area 51 Staging Zone. Similiar to StackOverflow.com, which is a collaboratively edited question and answer  site for programmers; Area 51 - where groups of subject matter experts can band together to create free Q&A sites of any topic of their choosing, appealed to me. And so the first thing I searched for was Alteryx - hmmm, no sites found. Intrigued by this, I dug deeper to figure out how to launch a new site. To begin a proposed Area 51 site, a site must initially go through a specific lifecycle containing these four stages:

1. Follow it - The site must first show that enough users are interested in the site topic to follow it. A total of 60 users must follow the site
2. Help Design the Community - The users must then submit good questions that can be rated on-topic or off-topic. Each site must have both types of questions, because the off-topic questions are just as important as the on-topic ones. Once a question recieves 20 votes, and has at least 4 votes for as to against it, then the question is designated "on-topic" or "off-topic".
3. Commit - Users are then asked to "sign-up" and commit to the site, being an active member of the community by asking and answering questions.
4. Beta - At this point the site is in the critial phase - it is judged on how much the site is utilized by the users. Over the next 90 days, the site is evaluated, thus if the site is not being used, it s deleted.

After learning how to create a site, I pondered the question (sorry for the Field of Dreams cliche'), "If it is built, will fellow Alteryx users actively participate?" Before you answer this for yourself, here are my two compelling reasons for creating an AlteryxUser Area 51 Q&A site:

1. It's not company moderated. The group of dedicated individuals at Alteryx do a great job running their Alteryx forum, yet I feel the users need a site where all types of questions can be presented and openly discussed.
2. Reputation - The more users participate and bring in additional users, they recieve points towards their reputation. Reputation is a sort of ranking that is displayed on the site, in which the users earn through site collaboration by asking and answering questions and referring others to the site. Most importantly, users earn the most reputation when they follow through on their commitments. According to the Area 51 website, "Reputation is never given, it is earned by convincing other users that you know what you're talking about."

So I'm interested to see if there are enough Alteryx users and guru's interested in running this site. One user cannot an Area 51 site make, others must be commited to make this work.

Now, you might be thinking "Why bother when Ned Harding and the bright folks in Client Services answer my questions in the Alteryx Forum?" Good question, and the only response that I can give is that this is an Alteryx user only Q&A site, maintained and supported by Alteryx users. Nuff said!

So if you are interested in helping build this community, please click on this Follow the AlteryxUser Area 51 site link to follow the site (click the Follow It! button) and participate.

A Special Note: I'd like to ask employees of Alteryx to refrain from following this site until it clears the Beta phase - if it is to be, it's up to Alteryx users. Thanks!

Till next time, enjoy!

Alteryx SaaS and PaaS

It's been a very busy summer and unfortunately not much posting going on, yet here's a quick post about an interesting article in the latest Retail Traffic magazine about the Alteryx Saas (Software as a Service) and Paas (Platform as a Service) offerings:

Click here to read the article

Also known as "Software on Demand", at a base level, you could simply say this is running Alteryx via the web, although stating that doesn''t really do it justice. To see this in action for yourself, demo the Alteryx FreeDemographics site. Look forward to alot of interesting news coming from Alteryx surrounding these offerings, as well as new posts coming from me shortly.

Now back to my summer - Enjoy!