Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
I have a point layer (31 by 31 grid = 961 points) created by fishnet tool > feature vertices to point tool (but with duplicate points). NOx Concentration data are in an Excel file. I want to populate an NOx Concentration field in the point layer using the corresponding values in the Excel file automatically. How can this be done?
I am writing this question in connection with my previous post.
Your data are in a rectangular array. Their locations are not explicitly given, but are implicit in their positions within the array (their row and column indexes). You need to rearrange them with one value per record and you need to compute coordinates for each record. The spreadsheet is the place to do this, not the GIS.
Here is an Excel example (using a smaller array to save space in the illustration):
The original data are shown in blue at the right. At the left is the new "long" format, all ready to be imported into your GIS. (Notice that rows 14 through 80 have been hidden.) It is created from left to right, beginning with OID, which is just the sequence 0, 1, 2, ..., through one less than the size of the array. (In your case it would stop at 961-1 = 960.)
The Row and Column fields are indexes into this array. They are computed using INT and MOD (as shown below). The X and Y fields convert those indexes into coordinates (also as shown below). To do this, they use the information given in green in the middle: coordinates of the lower left corner point and the spacings ("Cellsize") between points.
It is important to name both forms of the data: the original data are called NOx and the new output (in A1:F81) is called Database. Naming the data makes it easy to refer to them in a reliable way, while naming the output makes it easy to import into your GIS. It is also convenient to name the green "control" variables, again to avoid making mistakes: I have given them names similar to what you see in the Parameter column.
Here is the same worksheet showing the formulas:
These are all entered in the first data row (row 2) and then copied down through the remaining rows. The important formula is in cell F2, which uses OFFSET to obtain the NOx value for its Row and Column entry. (Note that OFFSET starts indexing at 0 rather than 1: that is why 1 is subtracted from each row and column index.) This is a convenient automatic way to "unravel" a rectangular array of data into the "long" format needed by most other GIS, database, and statistical software: it's a good technique to know. Once you study it and memorize it, you will have no trouble creating such spreadsheets from scratch within a minute or two.
The transfer of the data is easy in recent versions of ArcMap, provided you first close the workbook in Excel: when you navigate to the spreadsheet in the Add Data dialog, you will be shown the named ranges and worksheets. Choose Database. After it is added to the project, right-click on it and select Display XY Data.... Fill out the dialog. You will see a new point layer in response. Symbolize it as you see fit.
Other software may require you to save your output data in CSV or TXT format, which it will then read as input. That works fine with ArcMap, too.
Well there are a few ways you could do this.
You could join (the help for 10.0 hasn't been loading for me lately) the two tables together based on a shared identifying column. I can't really tell in your picture, but there has to be some way that a particular number is connected to particular feature.
You could save the spreadsheet as a csv file and read the contents into memory with the csv reader and use an update cursor to write the list of values to your field.
At any rate, you probably want all of your values in one column as I'm assuming you are only writing to one field.
