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.