Displaying data from points intersecting overlapping polygons

[Anthony_R_McKay]

I have got two feature classes:

1) The location of multiple bore sites as vector point data

2) The location of overlapping geological units as a single vector polygon feature class

Both FC have multiple data fields

 I'm working on a map that will enable me to click on a bore and display data from bore dataset and data from the geological units it intersects.

I have been attempting to write some python/arcpy code to create a single excel file (shown as 'Bore_Data' in the image) which contains all the data - but have been running into issues with the many-to-many relationship.

I'm attacking this via spatial joins to a virtual FC and tacking on more and more fields and finally exporting this as an excel file.

Having all the data in one file would be ideal as I want to do some statistical analysis of the data as whole.

Am I approaching this task the right way - or is there a more elegant to consolidate the data?

Any advise would be helpful.

[DavidPike]

Is this 3D data or all the strata polygons in the same plane (overlapping strata polygons)?  With the assumption I guess that the borehole would be at a depth to intersect any of the strata i.e. no strata polygons are at a depth which the borehole wouldn't get to?

If you want a physical output table you could use https://pro.arcgis.com/en/pro-app/latest/tool-reference/analysis/tabulate-intersection.htm which will give you a count of intersections for each strata type, with a new row/record created for each strata type. 

To get that table in the format you've described, you can then use https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/pivot-table.htm to transform those rows into columns.

[Anthony_R_McKay]

Thanks David,

The strata polygon data is 2D with a field for the numerical order in which they occur top to bottom - I know that detailed data/models exist for 3D cross sections of these strata - but currently all I have is 2D. In the theoretical pop-up I'd just be using the numerical order of the polygons to set the order they appear in the pop-up.

I'll have a good look at the references you have linked.

[MrRamonG]

Could you create spatial joins to establish your relationships, via unique IDs. Then bring all your data into an Excel data model, create relationships in the data model using the IDs from the spatial join, and run all your analysis in Excel using the data model?

https://support.microsoft.com/en-us/office/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75...

[Anthony_R_McKay]

I've tested some ArcPy code to iterate through the Rock_Name field and then creates a new polygon FC  for  each Rock_Name type.

My plan will be to iterate through each of these rock names and create a spatial join between them and the Bore_Point FC then copy over the Rock_Name field from the polygons to a new field named for the rock type.

So in the end I'll have a new field for for each rock type attached to the Bore_Point FC; containing either the rock type name or a null.