Using Fusion Tables for GeoLinx

Building the GeoLinx website was my first experience with the Google Maps API. While I have plenty of experience with HTML, I have only used Javascript on a limited basis (most of my experience has been with C# and Python). So this project has been a learning experience for me. I used a lot of the sample code provided by Google and others users. However, I have run into many instances where I could not find samples to meet my needs, so I had to create a solution on my own. I will continue to post the solutions as I come across them so anyone else with the same problem will have a reference.

Getting a basic map displayed was easy by simply following along with the tutorial provided by Google. The first issue I encountered was where to store the data and how would I display it. In this case the data was the location of job postings. I didn’t want to use a spatial database like SQL Server or PostgreSQL at this point because of the time to set this up as well as the problem of where to host this database. So I looked at KML first. While this would meet my needs, maintaining a KML with frequent updates would take quite a bit of work.

Then I discovered Google Fusion Tables. This seemed like the perfect solution for the time being. Fusion Tables are basically a database table stored on Google Drive and can be manipulated like any other document. In addition, Google provides an API that allows you to access and modify these tables with SQL like any other database table. But the most interesting part about Fusion Tables is that you can add a spatial field like any other spatial database. But the most exciting aspect is that this field can not only store latitude and longitude coordinates, but it can also store a string to be geocoded. So for example after entering “Pittsburgh, PA”, Fusion Tables will automatically geocode this string and convert it to the proper coordinates. This method uses the same geocoding service that Google Maps uses, so you can check where the point where be placed by simply typing the string into Google Maps.

The downside of Fusion Tables is that it is still considered experimental so it is lacking many of the useful features of most databases. One lacking feature is the ability to add an auto-populating unique ID field. There are also many useful SQL functions missing. Fusion Tables does allow for basic spatial SQL queries. These functions are also not nearly as robust as most spatial database but they have met my needs so far.

To get started I created a simple table that keeps track of the position title, the location (usually city and state) for geocoding, the city, state, country, organization, date posted, expiration date (if applicable), the class (technician, analyst, developer, etc), and the URL to the posting. Once this table was set up with some data, I simply followed the API reference do display these points on the map. I added a SQL query to only display jobs that have not expired and were posted within in 60 days so the posting are relevant.

The next step was creating a custom popup window that would display the attributes for all of the points in the same location. Check back soon!

An Introduction to GeoLinx

The GeoLinx project started out as a simple concept - bringing geospatial industry professionals together through the sharing of information.  The idea started in early 2008 when we realized that searching for jobs in the GIS industry was difficult because there were only a few sources dedicated to this specific discipline of career postings.  At that time we started the "The Western Pennsylvania GIS Newsletter" focusing on GIS jobs in the western Pennsylvania area (where we were located at the time).   The newsletter gained quite a following during it’s time.  Unfortunately, due to time constraints the newsletter fell by the wayside.

During this time, the GeoLinx project was envisioned.  We wanted to create a one-stop site where GIS professionals could find information on professional organizations, educational institutions and programs, GIS firms, career opportunities, and GIS events.  Obviously this would be a large undertaking that would take far more resources than we had at the time.

Development Faction, LLC was formed in March of 2009.  Over the past several years, we have been growing in size and experience.  In the fall of 2012 we decided to revisit the GeoLinx project as a side project.  We would allocate some time to develop the GeoLinx site when it was possible.  We decided that a phased approach was the best method of getting GeoLinx off the ground.  The obvious target seemed to be GIS job postings.  We decided this would be perfect excuse to play with the Google Maps API.

So that is how the GeoLinx website got its start.  The first version allows users search for job posting within a specified radius.  The user can select jobs and is redirected to the original job post.  A GIS approach for searching for GIS jobs seemed like the perfect approach.  We currently update the site daily (usually), and job posting can be located anywhere in the world, however, are mostly centered in North America since this is where we are located and get the most postings.

Since we have launched the first version of the site, we have been pleased with the positive feedback we have been getting, as well as the large volume of traffic to the site.  We plan to continue to improve the site and already have a long list of enhancements and new features planned.  We hope to get more users sending us posts so we don’t have to search for them ourselves.  We are also posting jobs on Twitter daily.  Finally, we will be following up with regular posts on the project and the development of the site as we go, so check back regularly.