Setting up cave tables

[KarlBurt]

Sorry if this seems like a rookie question, as I am with self-learning arcgis (online) with only 6 months of tinkering, and I apologize if my GIS lingo is not right. My question is around the structure of cave tables for mainly point data of cave entrances, I have a reasonable list of cave entrance locations - but what is the best way to set up/structure cave tables/tabs?  Is it recommended to create a main tab for the cave and give the cave a unique ID (basic info on a tab/table), and then add other feature tables linking to the Unique ID number eg other tables such as Entrances, property owners, environmental features, surveyors, finder of the cave etc? or just make 1 mega table where possible? Whats the best way to start thinking about the structure of table(s) and the number of tables? I have seen one 1 reasonable articles on the web on example tables, I just thought to ask this group if there are any recommended reading out there or considerations to the structure from others learnings. Many thanks in advance. Karl.

[BernSzukalski]

You can use a variety of formats. In the simplest case, cave folks would use a spreadsheet (CSV) with lat/long, or perhaps a GPX file if exporting from a GPS, and use that to add to a map and/or create a hosted feature layer for use in multiple maps. If you are collecting data from field devices, then it's likely you would have set up a feature layer template in ArcGIS Online to support data collection.

Besides the location information, all the rest is up to you. From my personal experience working with several gov agencies, maintaining a unique ID can be troublesome if there are multiple contributors. If there is one person responsible for compiling the master database, then this is easier. Many cave surveys and other agencies and organizations assign a unique identifier at the time data is added.

While you can manage/join related tables of information based on a unique key ID, unless you are dealing with many, many attributes that approach may not be necessary, nor advantageous. That said, I also know of systems of record and workflows that do involve the management of related tables, sometimes in a completely different database. Hopefully some of the cave survey directors will chime in with their experiences and suggestions. 

[KarlBurt]

Thank you Bernie, this is helpful. Ive been going though our 1500 entries that are on a excel spreadsheet and added a column of the cave system name. When going through my data, I wasnt too suprised to see a few repeats of cave names, but in different regions. Thanks 🙂

[DuncanHornby]

In the UK there are several cave registries, one I use fairly regularly is the Mendip Cave registry, may be you could contact them for advice?

It seems your source data is an Excel spreadsheet, make sure that is formatted correctly if you are going to import that data into a GIS. You talk about "tabs", tabs in what, Excel?

I personally would move away from Excel as soon as possible as Excel allows you to write anything anywhere so people do (and that includes you)! This has ramifications on querying the data, so I would move my data into something like MS Access which enforces data integrity, i.e. you can't put a question mark into a field that is expecting a number. Excel will allow this and is bad.

You are describing a relational setup, a master table with the basic information linking to other tables, e.g. one cave can have many entrance each with different land owners. This sort of relationship is easy to build in Access or other database systems.

By maintaining an unique ID for the cave system this would allow you to associate other datasets like survex files, scanned field notes, reports that would not necessarily be able to store in say Access, so you need to think about the file system structure that you are going to implement.

A simple Google search throws are many cave databases I would suggest you trawl some of theses and get a taste of what sort of information is being recorded?

[KarlBurt]

Thank you Duncan.

On the recommended reviewing, our current fields are in the right direction but certainly, there is some enhancement needed. Thanks for the links to 'excel into arcmap' and to Mendip, that helps greatly, and I'll get that data off excel 🙂

Many thanks, I hope there is more chatting on this channel, its a good forum 🙂