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.

I am trying to transfer over row data from a table to columns in a shapefile table. There is a common "SA" variable between the two (i.e. the study area number) but there is more than one land use type, hence why I must create two new columns for each landuse, in order to capture the area and percentage value for each type. A join won't work because it only captures the values for one land use as opposed to all of them, as the first table doesn't have as many entries as the second.

The reason I need to migrate the data is because I'm trying to use this in dynamic text on data driven pages.

I wrote a SQL script with the help of my friend but it won't execute through field calculator, giving the generic 99999 error:

UPDATE SApeelfinal
SET LUp1 = (SELECT perc FROM stat_fin WHERE stat_fin.SA = SApeelfinal.FID_text and LANDUSE = 1)
WHERE EXISTS (SELECT 1 FROM stat_fin WHERE stat_fin.SA = SApeelfinal.FID_text and LANDUSE = 1)

Though he said that I have to change the LANDUSE value for each time I run the script and as well change SUM_AREA and perc. I tried putting it in a toolbox but it wouldn't run that way either. If someone could enlighten me as to a better way to do this, I would appreciate it greatly. Also I was wondering if I had to manually create the new fields for this to work.

Here is a screenshot of the two tables: enter image description here

You can also find a bigger version here: http://i.imgur.com/sx08u.jpg

The red is the common variable between the two tables, the blue is the field I want the percentage to go to (and there would obviously be a new one for area), and the green are the two fields I want to migrate over based on the SATXT and FID_text common fields.

Thank you!

Edit: I've tried using the following python script in the codeblock:

    def getNewVal(newVal,landuse):
  if landuse == 1:
   return newVal
  else:
   return

With this in the calculate field:

getNewVal(!stat_fin.perc!,!stat_fin.LANDUSE!)

But it returns the same error. Is there something I'm doing wrong?

edit: To clarify, there are no joins because joins make dynamic text not work.

share|improve this question
2  
SQL cannot be run inside arcmap - you need to have the data in a DBMS to execute SQL Statements - You will need ArcSDE as the gateway for ArcMap to read the data see - webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/… for different types of DBMS –  Mapperz Aug 3 '11 at 15:23
    
Is there a way to translate the SQL into python or VB script? I'm not really familiar with the way code works in Arc –  Emily Aug 3 '11 at 15:24
1  
yes but early days - python.org/workshops/1995-05/sql.html –  Mapperz Aug 3 '11 at 15:29
add comment

5 Answers

up vote 2 down vote accepted

Unfortunately, your problem cannot be easily solved by Calcluate Field. Since you are trying to extract several fields of information from another table for each of your Study Areas, I will have to recommend the unthinkable-- cursors. First you will want to create columns for each of the attributes you want (Area and Percentage coverage for each land use type per study area). Then, you will want to use an Update cursor to loop through each row, creating a search cursor afterwards to query the statistics table and fetch the data you are looking for based on your join field (Study Area ID). Here is some sample code.

#Create the variable which stores the path of the table to be updated
infile = r"path"

#Create the variable which stores the path to the stats table we will be searching
intable = r"path"



# Create update cursor for feature class
rows = arcpy.UpdateCursor(infile)
print "Update Cursor created"
#For Loop goes through every row in the file provided and does what is listed below!
for row in rows:
    row.lup0 = 0
    row.lua0 = 0 #If you want default values, set them here
    where = "SATXT = '"+row.FID_Text+"'" #You need single quotes for a text field. This is one way to do it.
    rowsearch= arcpy.SearchCursor(intable, where)
    for x in rowsearch:
        if x.LANDUSE == 0:
            row.lup0 = x.perc
            row.lua0 = x.SUM_AREA #Create a check for each land use type so it will fetch the data

    rows.updateRow(row) #This should close the for loop so it updates the row every time through

# Delete cursor and row objects to remove locks on the data
del row
del rows # after this the script stops
print "All done!"

It is a rather brutish method for setting and getting data, but it will work for a small variable set such as what you have. Simply repeat the land use number check for each one so you can set the appropriate values. I believe you could probably use dictionaries to maintain references to the field names and iterate through that, but this should do for now.

share|improve this answer
    
Thank you so much for your help. It worked :) –  Emily Aug 4 '11 at 15:13
add comment

Your need to convert your SQL into VBA or Python for the Field Calculator in ArcMap

http://webhelp.esri.com/arcgiSDEsktop/9.3/index.cfm?TopicName=Making_field_calculations

ArcGIS 10 Users can also use Python scripts in ArcGIS Field Calculator

enter image description here

This is a cheaper option than buying ArcSDE and a database that supports SQL.

share|improve this answer
    
I'm just not certain how to translate it, any tips? edit: I see what you answered before... I'll give it a try. –  Emily Aug 3 '11 at 15:32
add comment

My solution to this problem has been to divide my table out into single tables.
In other words take the first record of each match and make a table called match 1 (for instance only).
Then do the same with each 2nd match, on and on until all of the table records are in a numbered table.
Then by joining each one consecutively and calculating into an lu1p, lu2p ... you get all data merged onto one row.
Then you can cacluate any aggregates you need to complete. Not the programmers method but my best hack.
Anybody got anything better?

share|improve this answer
    
I'm not sure if it will work as I'm trying to transfer rows into columns... Would it do the same for that? –  Emily Aug 3 '11 at 16:32
2  
I would write a Python script to read the needed values from the table into a dictionary, and then use an UpdateCursor to populate the columns in the shapefile. It would help if the shapefile already had the correct schema. –  blah238 Aug 3 '11 at 18:15
1  
my mistake. Another method which is only different in process. Would be summarize your table on your satxt field with sums of the area and perc. –  Brad Nesom Aug 3 '11 at 18:15
    
I'm really unfamiliar with scripting... I will try to find it in the helpfiles as well. –  Emily Aug 3 '11 at 18:21
1  
simply open your table and right click on the satxt field. Select summarize and choose a new table name. set the sum fields (option) and create your summarization. –  Brad Nesom Aug 3 '11 at 18:32
add comment

If you have access to MS Access, why not load these into a geodatabase, open it in Access, and run your SQL there?

I recently did the opposite - going from columns to rows - on a set of Excel data in Access (although I used VBA to make it work).

share|improve this answer
    
I only have Open Office but I'll see if I can run it on my laptop when I get home, though I'm not certain if I have Access. edit: Will open office Base work? I'm really new to it... –  Emily Aug 3 '11 at 19:57
1  
I'm not sure - I haven't used Base. Another question (stackoverflow.com/questions/1946234/…) reminded me of something. If you can get stat_fin into Calc, you may be able to use the Data Pilot tool to create your columns. Then you'd be able to link the new table to your shapefile. –  Patrick Aug 3 '11 at 20:07
    
trying to execute the code in Open office resulted in this: "The query can not be executed. It is too complex." –  Emily Aug 3 '11 at 20:08
1  
Export the table to text or Excel format, open in Calc, and see if Data Pilot will work. –  Patrick Aug 3 '11 at 20:11
    
Open Office Base will work external full-featured SQL databases such as MySQL, PostgreSQL and Oracle through ODBC or JDBC drivers. (using HSQLDB 2.x) en.wikipedia.org/wiki/OpenOffice.org_Base –  Mapperz Aug 3 '11 at 20:39
show 1 more comment

Do you have any familiarity with R? (I recommend using R-Studio as I think it is more intuitive than the default interface). It's possible to read the .dbf file of a shapefile in R using the foreign package. You can then do the operations you want and write out a dbf with the data you need and join it to the shapefile. Here is an example of this can be done:

# install a package to read dbf files (only needs to be done once)
install.packages('foreign')

# import package
library(foreign)

# read in the dbf associate with the shapefile 
boundaries <- read.dbf('G:/US_data/Census2000.dbf', as.is = TRUE)

# boundaries is now a dataframe object
summary(boundaries)

# do something to file and create a new dbf called 'new_boundaries'

# write this data out to dbf file 
write.dbf(new_boundaries , 'G:/example.dbf', factor2char = TRUE, max_nchar = 254)
share|improve this answer
    
I'm not familiar with R, could you link me to a helpfile description? help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//… Is it this? R-tree? –  Emily Aug 3 '11 at 19:30
1  
No, it is a different program used for statistical analysis. It may not be that useful to start using it just for this; mainly mentioning it as you can deal with dbfs quite easily. You could just as easily export your data into another format and manipulate it there also. –  djq Aug 3 '11 at 19:46
    
I tried exporting to open office but had some difficulties getting it to work also. Thank you for your advice :) –  Emily Aug 3 '11 at 19:48
1  
You can always export the attribuite table as .csv and open it in openoffice. –  djq Aug 3 '11 at 20:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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