ExecutING SQL script in ArcGIS field calculator?

Question

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. Can someone enlighten me as to a better way to do this?

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:

You can also find a bigger version here: https://i.sstatic.net/hfeQv.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.

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?

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

Answer 1

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

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

Answer 2

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.

Answer 3

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)

Answer 4

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.

Answer 5

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).