I have a spreadsheet that has county-level occurrences of around 1000 species within the US in the column headings (Species State County). Thus, for some counties there may be multiple species. I also have a county shapefile with the County field linking the shapefile to the spreadsheet. Finally I have a shapefile with multiple, separate polygons and I want to know which rows from the spreadsheet intersect or contain a polygon from the second polygon shapefile.

I am looking for the best way to automate this process so that I create a new column in the spreadsheet that has a 1 if the county occurrence of the species intesects with the second polygon shapefile. For example,

Species State County Intersect

sp. A NC Durham 1

sp. A NC Orange 0

where species A county-level occurence intersects with the polygon in Durham County, but not in Orange County.

I have not specified a platform, but I have worked with R, ArcGIS, and Python. Currently I am most comfortable working in R (but not for spatial data, there I am a newbie), but not sure how to automate this process.

thanks for any information.

  • What's the second polygon? As far as you can go is multiple species to counties. If the second polygon is smaller than a county then you cannot interpolate the data... you can however cover multiple counties. May 27, 2014 at 21:11
  • I edited the original question to stress that I am looking for cases where one of the polygons from the second shapefile intersect or are contained within a county. Sorry for not being specific.
    – user44796
    May 27, 2014 at 21:23
  • Would it help to transfer all the attributes from the spreadsheet to the county shape file and do Intersect with the second polygons against the counties? help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//… May 27, 2014 at 21:40
  • By "occurrences" do you mean 'point observations' or just 'species observed somewhere in this county'? You're trying to determine if a species occurs within one of your polygons, right? If you have point observations you'll just use your polygons and those points from the spreadsheet. Otherwise your spreadsheet is basically just an additional attribute table for your counties. You'd get that info joined, union counties and polygons, then dissolve the result based on polygon ID with statistic sum for all the species fields. Screenshots of your data may help here.
    – Chris W
    May 27, 2014 at 21:45
  • Either a one-to-many spatial join in ArcGIS or an intersect should work. However, I have tried to do this with the county shapefile and the second polygon shapefile (Federal Lands that may or may not extend across multiple counties). The output of both procedures leaves me with a single polygon, which isn't correct.
    – user44796
    May 30, 2014 at 13:22

1 Answer 1


Based on Chris W's useful comments, one possible solution using ArcGIS 10.1 is:

  1. one-to-many spatial join (GIS Toolbox->Analysis Tools -> Overlay -> spatial join) Federal lands polygon to the county polygon shapefile. This provides a single row for each instance of a federal land polygon for every county that it occurs in. This provided a common field (County FIPS number) that occurrs in both the county shapefile and the species database.

  2. Join the species table to the spatially-joined shape file by linking the County (FIPS) fields.

This is much simpler than I thought it would be and I apologize for my lack of clarity.

  • Because I am still unclear on your data format, I want to caution you on a behavior of ArcGIS in certain kinds of joins (not spatial, just regular joins), particularly one to many. Arc will perform the join, however it will only return the first matched record. There are no warnings or other indicators of this. A many to one will work fine, so if your Excel table only has one row per county you should be good.
    – Chris W
    Jun 3, 2014 at 21:26

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.