How Do I manage a Multi-field (more than 100) Attribute Data

Elijah · ‎12-31-2016

I have a point feature data sets with attribute tables. The feature layers are up to 40 but I have managed to merge them up as 1 feature class. The nutty part is that the ‘merged’ feature class now has more than 120 fields and about 2500 records. Meanwhile, the fields and rows are expected to grow as more data pour in in the feature. I was wondering if there is a smarter way of managing this kind of data rather than the way I am currently doing it.

Note that some fields/columns contains just a few rows of data given the fact that some records may just need about 50 columns to represent them as against about 1000 fields already existing in the feature attribute table, thereby having a lot of ‘null’ or empty fields for some records/rows.

Actually, the intent is to have a database that can be immediately queried by field or any other parameter to retrieve information quickly. But for now, the number of fields has even become complex to scroll through and the database looks a little cluttered…… What could be the way out here?

I am using ArcGIS Desktop and the Data sets are being managed in a File Geodatabase.

I will appreciate your thoughts in this matter.

DanPatterson_Retired · ‎12-31-2016

Why have you merged the geometry if they share so few attributes? I would organized the data to maximize the shared attibutes (even 50 fields is a bit much) and have other tables on hand for joins and/or relates. Similarly the geometry that shares little with the others, should be kept separate so you don't end up with a vast number of empty records. Just because the points are points, doesn't mean that they need to be grouped as one. I think in your case, attributes dominate

JoshuaBixby · ‎12-31-2016

Database design, or really data design in general, is an art & science unto itself. Although I would never assume a typical GIS user has a mastery of data design, I know I don't myself, I tend to find most GIS users aren't even aware of the basic, and it causes all sorts of issues.

I suggest spending some time on the web searching for "data design" and "database design." Even a handful of primers might give you some idea of why your current strategy is failing you. I also suggest you read An overview of geodatabase design if you haven't already. There are some useful sections on Geodatabase design steps and Design tips.

There is no silver bullet when it comes to data design. Designing a good data model takes time and requires a thorough knowledge of what you are modeling/managing.

Elijah · ‎12-31-2016

Thanks Dan and Joshua.

Actually, the intention of merging all of that data is 'the expected possibility of querying the data by field (select by attribute) or similar analysis. For instance, selecting records based on fields is easier if the data exist in a single feature than where features are joined, related, etc. The fact that the attributes are always searched/queried means that, if I have them as independent feature layers, I will need to join the features each time-which is not efficient. Perhaps, I need more skills in the best way to easily query attribute of features existing as different layers in a given or different databases perhaps without necessarily going through join/relate protocols each time. Is there something I need to know along this line?

Thanks again.

RebeccaStrauch__GISP · ‎12-31-2016

I would second Joshua's comments on taking the time to develop a good database design. Take advantage of attribute domains Defining fields in tables—Help | ArcGIS Desktop And some of the other Common tables and attributes tasks—Help | ArcGIS Desktop

For example, at one time, we had about 30 different ArcINFO coverages covering 3 main topics but with varying fields. With some testing (automated with custom aycpy scripts to make combining and testing easier) I was able to merge these into three feature classes, with combined common/similar fields, and with a domain table on many of the fields. Where originally, each coverage/Layer had the name that described what is contained, this became a field with a common name and a domain value.

So I suggest taking a good overview of you data to see if any common fields exist. If you know Python and arcpy at all, you should be able to rights script that will let you append new data into a, existing strict ion, without adding new fields.

Also, make sure you look at field indices, and the various methods of appending tables, not just table join.

TimHayes1 · ‎12-31-2016

I was in a similiar situation. I made it more manageable by using Related Tables, Relationship Classes, and the File Geodatabase. The Related Tables are linked to the Main Table (Point Feature Class) via Key Fields (one-to-one and one-to-many relationships). With this number of fields, I stayed away from joins.

I recall ESRI used to have a nice tool called Geodatabase Diagrammer or something similiar. I used it a lot in 2012-2013. One many advantages of this tool was its ability to let you permanently reorder fields in the Excel environment.

Also, look up Database Normalization. This may help you.

TimHayes1 · ‎01-02-2017

Just remembered, X-Ray Geodatabase is the one you want to use: http://www.arcgis.com/home/item.html?id=9ea218ff575f4a5195e01a2cae03a0ae

TedKowal · ‎01-04-2017

I am currently designing a dynamic Engineering plan retrieval system that is mapped based... this data design is a work in progress. Linked to the Data components are layers that share common geometries (Project boundaries, Bridges, Roads, Route LRS ....) many of these data fields are common to one or many of the layers so I am designing this data as independent tables that share a link to the "layers". So instead of having one layer with 55 fields or more, each of my geometric layers only contain those field and links to the data. I can bring the data tables into an esri mxd so it will always be available for querying, joining and relating.... Hope this helps you vision your data and your requirements....