|Mapping Microsoft Excel Data using Spatialkey (Mapping and Data Visualization)
Have you ever had sales data, customer data or any other data in an excel spreadsheet that you wanted to get on a map (Google maps or Mapquest maps) so you can look at your data from another perspective? This can be a challenging task and with many solutions you are limited by the number of points you can place on a map. What if the process from spreadsheet to map only took a matter of minutes and then allowed you to share this data out to others? This is possible with SpatialKey and it not only lets you see you data on a map you can build compelling reports and identify trends within your data.
We will start with a spreadhsheet containing real estate sales data from Sacramento California. The spreadsheet contains the address, city, zipcode, state, number of bed, baths, sqaure footage, type, sale date and price.
Lets get started with the process! The first thing we need to do is export our data from excel by saving the file as a CSV (Comma separated) file. Make sure that you have cleaned up your data prior to exporting.
Cleaning up your data:
- The first row should be column headers with the name of the column (see picture above)
- Make sure there are no extra columns or characters in other columns outside of the ones you want to export
- Delete any columns that you will not be using
Within Excel you can go to File -> Save As and choose the "Save as type" to be a CSV file.
After saving we are ready to jump into SpatialKey and import our data. Currently SpatialKey is in private beta but you can go to the site and apply for an invite.
After logging into SpatialKey go to the datasets tab and click on the "Upload a DataSet". Click the "Upload a CSV file". After your initial upload you will be asked to give your dataset a name a seen below
Next, you get to see a preview of your data so that you can validate that it was brought into the system correctly.
The next step can be confusing at first but you will get the hang of it quickly. Basically most of your data will fall into three categories a String (words/text), Number (I am pretty sure you know what that is) or a Date. Look over each column and do not just skip past this. You can see sample data by hovering your mouse over the column names to see if the importer guessed your datatypes correctly.
The next step is where SpatialKey stand out above the rest. Geocoding can be a complex process but if your data is in the proper format SpatialKey makes it really easy. In the screen beow we have three options, we can geocode using the address information from your data, use existing latitude and longitude fields if you already have them or bring it in without geographic information. In the latter case you will not be able to use the mapping features of SpatialKey. In this case we will use the first option "Geocode using address fields"
In this screen we will choose the columns from our data that we will use for geocoding. Take care in choosing these options since they will affect the results of geocoding. SpatialKey has already made some choices for us you should review each one and make changes as necessary. We do not need to fill in every option but the more you have the more accurate the results. If you do not have a column in you data you can manually enter the value. for example if you only had the street in your data and all data points were in the same city you could manually enter the city and state values. We will accept the defaults below.
SpatialKey geocoded our data and in the screen below we see the results. We are provided with a summary showing how accurate SpatialKey was able to geocode the results. The results below are great since it was able to geocode nearly all of them at the exact address level.
That is all there is to getting data into SpatialKey, a simple step by step process. The fun part starts when you can open up this data in a report. I will not walk through the creation of a report in this article but I have an example of a report I built below.