Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

In my personal geodatabase there are two layers:- one of them is small -- about 13 records/features. The other is about 118 records/features. The relation class between the two layers is one to many where the small layer is the origin layer and the big is the destination. What I need to do is to join the big layer with an external excel table. This table has about 200-500 columns and after the join I need to calculate the sum of these records and put it in the small layer.

What I did is a model using model builder in ArcGIS 10.00. This model does the following:

  1. Joins the excel table with the big layer
  2. After joining I cannot do quick export because I haven't got the license for Data Interoperability Tools so I make feature class to feature class and now I have a feature class called (B)
  3. I then make a summary statistics table on feature class B. I choose attribute by attribute and choose the summary type SUM for each column.

The first question is whether there any methods to choose all field and make the SUM for each fields or must I do it one by one?

  1. After that I make a join between the small layer and the summary statistics output table (C)
  2. I then make feature class to feature class to have the final results

The second question: If the column name in the excel file changed this model is now not useful which I need to do that with different excel file

The third question: Are there any other techniques can I use to solve this problem

share|improve this question

closed as too broad by PolyGeo Oct 28 at 4:16

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
What software are you using? Have you considered scripting. Let us know what software and we might be able to help with something more specific –  Stev_k Mar 24 '12 at 12:22
    
i use arc gis 10.0 only –  mohamed nour eldien Mar 25 '12 at 7:03
2  
If you're going to change your question, you should edit the existing one rather than starting a new one.... This is basically the same as gis.stackexchange.com/questions/22228/… –  Stev_k Mar 25 '12 at 11:15
    
It would be much easier to help with this if each issue could be presented in Q&A style rather than 3 questions in one. –  PolyGeo Aug 23 '12 at 21:57

4 Answers 4

I would use ArcPy with ListFields to iterate through your three hundred fields and run Summary Statistics, with a SearchCursor used to open each of the 300 tables thus created and read the sum value.

Note: This answer is in response to the original question (which is now the first of three). If three questions are being asked then I think each should become its own GIS-SE Question so that the overall problem can be addressed in answerable chunks.

share|improve this answer

Could you be more specific please? In case you mean the fields of a Shapefile, you can access the .dbf file and, after copying the contents to a spreadsheet, you can make any calculations you want. On the other hand if you want to make the calculations inside a GIS software, then both ESRI and QGIS software have tools to add new fields, and/or calculate any statistics of your attribute table.

For QGIS I recommend the Basic Stats Plugin which can do all sorts of stats on your data.

share|improve this answer

Since you are limited by license issues, do you truly need to do this via a spatial software package?

I have solved join issues before with very simply database tools such as Base in OpenOffice / LibreOffice. It is reasonably straightforward to manage shapefiles with this tool.

Just have a think about the format of the resulting dataset, all you are trying to do is make sure the lat long stay with each record?

share|improve this answer
    
thank you but what is open office or libre office i dont know them –  mohamed nour eldien Mar 25 '12 at 11:06
1  
google can be your friend or I can do it for you... libreoffice.org –  Willy Mar 26 '12 at 20:03

To answer your first question. You may be able to use dissolve tool (under data management) to sum multiple fields. Set your input as feature class B. Leave all dissolve fields unchecked. Then add each field you want summed in the statistics section and choose "SUM" for the stat type. Result will be new feature class that only contains the summed fields.

For your second question. You could just make a copy of your model and change the column name. Or load your execl table into your personal GDB as a DBF to standardize the field names.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.