9

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?

Improve this question

3 Answers 3

Reset to default
6

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.

Improve this answer
0
6

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.

Improve this answer
7
  • 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
    Commented Jun 4, 2011 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
    Commented Jun 4, 2011 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
    Commented Jun 5, 2011 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
    Commented Jun 5, 2011 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
    Commented Jun 5, 2011 at 21:42
4

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)
Improve this answer
7
  • @David Thanks for the detailed answer ... will have to check it out.
    – Abe
    Commented Jun 9, 2011 at 21:24
  • @Abe feel free to edit when you do. I have made it CW
    – David LeBauer
    Commented Jun 9, 2011 at 21:26
  • @David In this circumstance, IDW will produce incorrect results for the zonal statistics: see the discussion following @scw's reply.
    – whuber
    Commented Jun 10, 2011 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 LeBauer
    Commented Jun 10, 2011 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
    Commented Jun 10, 2011 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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