How to create a Thematic Map Layer from Excel Spreadsheets?

3779
5
Jump to solution
03-04-2012 03:27 PM
AlperOnen
New Contributor
Hello, Firstly I might have posted this in a wrong thread and sorry if I did. I'm pretty new to ArcGis.

I've been asked to create a thematic map showing the surface runoff for a pre-defined rectangular domain. And I have data over Excel.

I have  Lat. and Long. matrices (4 x 2079 each) for location and also a runoff data matrix with same size (each of them in an individual spreadsheet in a single Excel file).

I have to create a X-Y-Z map that should look like this (visually):



I would be faithful if someone can spare me the steps or necessary functions to create such a task. I'm aware that it's a very basic stuff, but I'm getting continuously lost on ArcGis with all those countless functions inside.

Thanks in advance.
Tags (2)
0 Kudos
1 Solution

Accepted Solutions
ChristopherThompson
Regular Contributor
I think the problem you had with the Point to raster tool failing is that it may not want to work from an XY event them as you had created but from an on-disk featureclass.

I was able to take that spreadsheet and make a raster from it.  Use these steps using tools from in ArcGIS:
1) in ArcCatalog, right click on the XYZ worksheet and pick the "Create Featureclass, from XY Table" function.  In this you can choose to set your Z value to the Runnoff field but that probably isn't necessary as the Runnoff field will become the cell value later on when you create the raster dataset.  Make sure you select an appropriate coordinate system for your data; i chose WGS84 as i didn't know what else it might be, and the data seem to follow the coastline in that region fairly well.

2) Use the Feature class you just created as the input to the "Point to Raster" tool.  This may take a little experimentation to get the result you're looking at and you'll want to try different settings for the Cell Assignment Type input and the Cellsize (see below) .

3)I left the Cell Assignment type at the default "Most Frequent" setting, but you might find for this sort of dataset that mean, min, or max might serve better depending on what you hope to demonstrate.

4) I used 0.05 as the cell size.  The cell size is defined in terms of the extent of the dataset.  I had tried accepting the default, and i got small cells centered on each point, with no data between them.  A 2nd iteration at a cell size of .0335 did better but left bands of no data, and a 3rd and last test at 0.05 seems to have created a reasonable coverage.  Since your data appear to be roughly on 4Km centers you might give .04 a try and see what that yields.

Once that data set is created you'll have a double precision raster storing values from -1 (those out in the Baltic Sea) to 3.4803 or so.  I've attached a .pdf of the result so you can see how that turned out.

Let me know if this result works for you, i have some other thoughts about this, such as using interpolation methods rather than a straight point-to-raster methodology as well as whether or not you might want to project your data into a planar coordinate system first rather than working in a GCS system which makes specifying the cell size a little more challenging.  There may also be other ways to get your data into a raster format depending on how it is organized before you put it into the 3 column format we're playing with here.

Good Luck!
Chris Thompson

View solution in original post

0 Kudos
5 Replies
ChristopherThompson
Regular Contributor
Is it possible for you to post a sample of your data? it sounds like you have 2 types of datasets to work with: 1) a set of coordinates in a matrix that defines your study area and 2) the actual runnoff values you want to to map as 'z' values.  Ultimately, to get the product you need you want to create a raster dataset.. there are probably a couple of different ways to approach that but understanding how your data are organized is the first step.
0 Kudos
AlperOnen
New Contributor
I thought about rearranging all the datasets into single columns for each, and guessed that would be more appropriate for Arcgis. For instance, I have 3 columns on a single Excel Spreadsheet;

Column A : Lat
Column B : Long
Column C : Runoff Data

I attached the file to this post.

I have tried Add XY data then point to raster, but Arcgis couldnt complete the "point to raster" task, it just said "error executing function"
0 Kudos
ChristopherThompson
Regular Contributor
I think the problem you had with the Point to raster tool failing is that it may not want to work from an XY event them as you had created but from an on-disk featureclass.

I was able to take that spreadsheet and make a raster from it.  Use these steps using tools from in ArcGIS:
1) in ArcCatalog, right click on the XYZ worksheet and pick the "Create Featureclass, from XY Table" function.  In this you can choose to set your Z value to the Runnoff field but that probably isn't necessary as the Runnoff field will become the cell value later on when you create the raster dataset.  Make sure you select an appropriate coordinate system for your data; i chose WGS84 as i didn't know what else it might be, and the data seem to follow the coastline in that region fairly well.

2) Use the Feature class you just created as the input to the "Point to Raster" tool.  This may take a little experimentation to get the result you're looking at and you'll want to try different settings for the Cell Assignment Type input and the Cellsize (see below) .

3)I left the Cell Assignment type at the default "Most Frequent" setting, but you might find for this sort of dataset that mean, min, or max might serve better depending on what you hope to demonstrate.

4) I used 0.05 as the cell size.  The cell size is defined in terms of the extent of the dataset.  I had tried accepting the default, and i got small cells centered on each point, with no data between them.  A 2nd iteration at a cell size of .0335 did better but left bands of no data, and a 3rd and last test at 0.05 seems to have created a reasonable coverage.  Since your data appear to be roughly on 4Km centers you might give .04 a try and see what that yields.

Once that data set is created you'll have a double precision raster storing values from -1 (those out in the Baltic Sea) to 3.4803 or so.  I've attached a .pdf of the result so you can see how that turned out.

Let me know if this result works for you, i have some other thoughts about this, such as using interpolation methods rather than a straight point-to-raster methodology as well as whether or not you might want to project your data into a planar coordinate system first rather than working in a GCS system which makes specifying the cell size a little more challenging.  There may also be other ways to get your data into a raster format depending on how it is organized before you put it into the 3 column format we're playing with here.

Good Luck!
Chris Thompson
0 Kudos
AlperOnen
New Contributor
Thank you very very much - after a few attempts, I managed to generate the output.
0 Kudos
calosteward
New Contributor

I only know how to create data matrix in Excel. Thank you all for giving so many solutions on create a Thematic Map Layer from Excel Spreadsheets. Your works are of great help to me.

0 Kudos