Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a data set in csv form, like:

latitude, longitude, value
-45, 45, 10
....

I would like to calculate county level means from these data, using either ArcGIS or R+grass.

I have the ArcGIS USA Counties layer, but I have not been able to successfully complete a spatial join.

How can I do this?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

A workflow used to generate county level shape file with mean(z) in each county from an xyz data table in ArcGIS

note this is based on @MLowry's suggestion, adding almost step-by-step instructions.


Open ArcCatalog

  1. create new personal geodatabase (e.g. foo.mdb)
  2. file -> new -> personal geodatabase
  3. add data

    • right click on map.csv -> export to geodatabase (single) -> foo.mdb
    • right click on counties.lyr -> export to geodatabase (single) -> foo.mdb
  4. create featureclass (.shp files) from xy table

    • open foo.mdb
    • right click map.csv table --> create featureclass from xytable
    • input fields x = lon, y = lat, z = yield
    • coordinate system of input coordinates import -> from counties.lyr (or, equivalently, -> geographic .. -> world -> wgs_1984 -> open -> ok)
  5. import feature classes to geodatabase (multiple)

    • select shp files created in prev. step -> add -> okay

Open ArcMap

  1. Add data

    • select foo.mdb -> CTL + select tables -> open
  2. Spatial Join

    • ArcToolbox -> Analysis Tools -> Overlay -> Spatial Join ->
    • Target Features: counties.lyr
    • Join Features: xyz table map
    • Output Feature Class: filename_spatialjoin
    • Join Operation: JOIN_ONE_TO_ONE
    • Match Option: Closest
    • Field Map of Join Features: remove unwanted fields (only STATE_FIPS, CNTY_FIPS, z-values required
    • Search Radius: 30km
    • click OK
    • Z value = yield; select 'mean' (or alternative statistic)
share|improve this answer
    
@David Thanks for the detailed answer ... will have to check it out. –  Abe Jun 9 '11 at 21:24
    
@Abe feel free to edit when you do. I have made it CW –  David Jun 9 '11 at 21:26
    
@David In this circumstance, IDW will produce incorrect results for the zonal statistics: see the discussion following @scw's reply. –  whuber Jun 10 '11 at 5:15
    
@whuber. Thanks for pointing that out. By the time I was finished I had forgotten that the original point was to do a spatial join on the points with counties; fixed. –  David Jun 10 '11 at 14:07
    
@David Thanks. But now I'm confused. First, where in this workflow do you actually create a "raster layer," as your title announces? Second, how is it that three layers are produced? The CSV file merely represents a set of points (x,y), each with a single numerical attribute (z). –  whuber Jun 10 '11 at 14:15
show 2 more comments

Sounds like you could convert the .csv to an event theme, then export to .shp, then do a spatial join with the new .shp into the county layer, and BOOM, you're good to go for the calculations.

share|improve this answer
add comment

A general approach is to convert your regularly spaced point data into a raster (XYZ to raster in Arc*; v.in.xyz in GRASS) then perform a zonal statistics operator to aggregate the values of the cells within each county and calculate statistics (mean is one of the standard statistics calculated). To perform the statistics, use Zonal Statistics in Arc*, or v.rast.stats in GRASS.

If you needed to do more advanced statistics, you could do the analysis in R with raster and sp but its a little more tricky.

share|improve this answer
    
Good idea, but unless the point arrays are oriented correctly with the coordinates, resampling will occur in the conversion to a raster and you'll be at the mercy of the resampling algorithm: the averages will likely be off by a tiny bit no matter what. –  whuber Jun 4 '11 at 22:17
    
Right, if the cell size is set to coarsely resampling may occur. As an approximation, you should be able to set the cell size to half the minimum distance between any two points and avoid this issue. This approach does have the downside of having to handle resampling, but I've found it much better then the number of points is large (tens of thousands or more). –  scw Jun 4 '11 at 23:45
    
@scw Resampling occurs no matter what the cellsize is (unless the data are already perfectly aligned with the grid). When using a finer cellsize, you will likely get worse answers, because most of the grid values are interpolated between the originals. Unless you are using nearest-neighbor interpolation, this causes the extreme values to be under-represented. The mean will only slightly be affected; other statistics, such as the standard deviation, can be more heavily affected. –  whuber Jun 5 '11 at 15:22
    
@whuber I think I see where our misunderstanding is coming from -- I'm not advocating converting the data into a continuous surface, only placing the point values (when they exist) into a regular raster grid. So the vast majority of the grid will remain NULL, with only occasional values at cell locations coincident with point locations. –  scw Jun 5 '11 at 19:33
    
@scw Ah, very good! Thank you for the clarification. You are in effect doing the grid-based equivalent of the spatial join recommended by @MLowry. –  whuber Jun 5 '11 at 21:42
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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