3

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?

Excel File

My excel file screenshot

Map

Arc Map Image

I am writing this question in connection with my previous post.

Improve this question
  • 1
    The term for what you are trying to do is create an "XY point event layer." The method is described in many posts on this site; please search. Here is a thread with many different solutions for you to consider: gis.stackexchange.com/questions/327. As @Paul points out, you are at risk of mixing up the data and their locations. Therefore, you first need to reconfigure your Excel file to be in three-column format: X, Y, and NOx value. (To make a grid, X and Y are easy to calculate in Excel.) – whuber Jun 26 '13 at 14:28
  • @whuber. I am indifferent case from ur directed thread, since my excel files only have NOx concentartion value--no lat lon. In my case i have to add nox value against every points in the shapefile--i think i need how to select points as a row or column since every row and column represents nox value accordingly-but ho to do? – SIslam Jun 26 '13 at 14:59
  • 1
    You are in exactly the situation I described, because one way or another you need to know the coordinates of your 961 NOx values. (You haven't told us how you know them, but I would guess they are related to the row and column indexes in your data array.) Those coordinates can be computed in the spreadsheet (it's a great tool for that). Your main problem is that you currently have the NOx data in a rectangular array; you need to reformat them into the three-column format. – whuber Jun 26 '13 at 16:01
  • @ whuber I have got these NOx values using aeromod (diterministic atmospheric simulation model that generates values at certain gap-for me 500m- toward N-S-E-W). Now i want to plot these values into real spatial domain (onto parcel map) to make an interpolated surface by krigging (etc) whereby potential threat can be known for the establishment of a industry(EIA analysis) and thus important decision can be made. – SIslam Jun 26 '13 at 16:34
3

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):

Spreadsheet

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:

Formulae

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.

ArcMap screenshot

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.

Improve this answer
1

Well there are a few ways you could do this.

  1. 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.

  2. 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.

Improve this answer
  • Infact i have made these points by firstly:creating a fishnet 31*31 cell at 500 meter gap secondly: i have used tool named feature vertices to point. Thus these point are haphazard (as esri algorithm works) now what can i do? I think i need how to select points directionally one after another-but how? – SIslam Jun 26 '13 at 14:11
  • 1
    How did the data end up in that format in Excel? Feature Vertices to Point creates a shapefile. Why do you have multiple columns of information? How do you know which cell corresponds to which feature? – Paul Jun 26 '13 at 14:24
  • Dear have you seen my screeshots? – SIslam Jun 26 '13 at 14:44
  • In excel,every excel spreadsheet cell represents one point accordingly.e.g. The NW cell in excel represents NW points in the point shapefile and so so.... – SIslam Jun 26 '13 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.