Reclassify raster using variables from a SQL Server table (connecting with pyodbc)

JesseLangdon · ‎08-22-2012

Hello all,

So I am trying to reclass a raster file (representing vegetation and land cover types) using values stored in a SQL Server Express table. I have a set of ~400 species records in the table, with each row representing a different species, and a column for each vegetation type. Each vegetation type is coded as either "1" if it is suitable habitat for that species, or "0" if it's unsuitable. The vegetation raster will then be reclassified to two values, "1" or "0", based on the values associated with the species record (this will be slightly different for each species).

In PythonWin, I'm using pyodbc to connect to the SQL Server Express database table, then executing a select query statement to collect the values for a species record (row) into the pyodbc cursor. Then I want to assign each column value to the output raster value in the remap statement (see code attached). Unfortunately, I keep getting the following error:

TypeError: list indices must be integers, not tuple

RasterCalculator seems to just want integers in the remap statement, not variables, but I don't know how to get around that. Any ideas?

Thanks!

Jesse Langdon
Research Assistant
University of Washington

# Load Python libraries          
import pyodbc
import arcpy
from arcpy import env
from arcpy.sa import *
import os

arcpy.env.overwriteOutput = 1

# Check out the ArcGIS Spatial Analyst extension license
arcpy.CheckOutExtension("Spatial")

# set variables
modelList = arcpy.ListFiles() # build list of species model names for loop
biome_Cur = ("xxxxxxx/xxxx/xxxxxx/1_Input.gdb/biome_current") # the original raster which will be reclassed

# connect to SQL Server Express database
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=DESKTOP\SQLEXPRESS;DATABASE=Species;UID=sa;PWD=XXXXXX')
cursor = cnxn.cursor()

# main processing loop
for model in modelList:

    # reclassifies biome raster based on suitability code from SQL Server Species database
    # sql SELECT query
    cursor.execute("""
                    SELECT [BIOME_1],[BIOME_6],[BIOME_7],[BIOME_8],[BIOME_9],[BIOME_10],[BIOME_14],[BIOME_19],[BIOME_20],
                    [BIOME_21],[BIOME_22],[BIOME_23],[BIOME_24],[BIOME_25],[BIOME_27],[BIOME_29],[BIOME_30],[BIOME_31],
                    [BIOME_32],[BIOME_35],[BIOME_36],[BIOME_37],[BIOME_38],[BIOME_39],[BIOME_40],[BIOME_41],[BIOME_42],
                    [BIOME_43],[BIOME_44],[BIOME_45],[BIOME_46],[BIOME_47],[BIOME_48],[BIOME_50],[BIOME_100],[BIOME_200]
                    FROM Species.dbo.BiomesPerSpp_Rehfeldt
                    WHERE ID = ?""", (model))

    # assign remap variable for reclassification
    remap_cur = RemapValue([7, row.BIOME_7][8, row.BIOME_8],[9, row.BIOME_9],[14, row.BIOME_14],[20, row.BIOME_20],
    [21, row.BIOME_21], [22, row.BIOME_22], [23, row.BIOME_23], [25, row.BIOME_25], [30, row.BIOME_30],       
    [31,row.BIOME_31],[32, row.BIOME_32], [36, row.BIOME_36], [38, row.BIOME_38], [41, row.BIOME_41], 
    [42, row.BIOME_42], [43, row.BIOME_43],[44, row.BIOME_44], [45, row.BIOME_45], [46, row.BIOME_46], 
    [47, row.BIOME_47], [50, row.BIOME_50], [100, row.BIOME_100],[200, row.BIOME_200])

    biomeReClass_cur = arcpy.sa.Reclassify(biome_Cur, "Value", remap_cur, "NODATA")

ChrisSnyder · ‎08-22-2012

I think the sytax for using the remap table is:

arcpy.sa.RemapValue()

as in...

temp3Grd = arcpy.sa.Reclassify(temp2Grd, "Value", arcpy.sa.RemapValue([[1,1],[0,"NODATA"]]), "DATA")

I think you are getting an error because you are not specifying the arcpy.sa... part of the Remap command.

JesseLangdon · ‎08-22-2012

Hi Chris,

Thanks for the feedback. I tried adding the "arcpy.sa" prefix to the RemapValue command, but I'm still getting the same error message. I just wonder if the RemapValue command will only take actual integers, and not variables. I'm wondering if I have to do something ugly like create a temporary ascii table based on the data returned from the SQL query, and then Reclass by ASCII file.

Jesse

ChrisSnyder · ‎08-23-2012

Not sure why it's not working. Can you do a test and harcode your remap_cur variable such as:

remap_cur = arcpy.sa.RemapValue([7, 777][8,888],[9,999],...) ? You are able to use variable in the remap tables, so I don't think that's the issue... More likely you are not retreiving the variables you want to use in the correct way.

Are the row's field values you are retreving indeed integers - or as the error suggests, a tuple object? For example something weird and unexpected like (None, 3).

What does:

print row.BIOME_7

evaluate to?

Also, where in your code do you define the "row" object (as in row.BIOME_7)... shouldn't it be something like "cursor.BIOME_7"?

JesseLangdon · ‎09-13-2012

Just realized I never closed this thread out. I solved the problem by changing the SQL query from

                    cursor.execute("""
                    SELECT [BIOME_1],[BIOME_6],[BIOME_7],[BIOME_8],[BIOME_9],[BIOME_10],[BIOME_14],[BIOME_19],[BIOME_20],
                    [BIOME_21],[BIOME_22],[BIOME_23],[BIOME_24],[BIOME_25],[BIOME_27],[BIOME_29],[BIOME_30],[BIOME_31],
                    [BIOME_32],[BIOME_35],[BIOME_36],[BIOME_37],[BIOME_38],[BIOME_39],[BIOME_40],[BIOME_41],[BIOME_42],
                    [BIOME_43],[BIOME_44],[BIOME_45],[BIOME_46],[BIOME_47],[BIOME_48],[BIOME_50],[BIOME_100],[BIOME_200]
                    FROM Species.dbo.BiomesPerSpp_Rehfeldt
                    WHERE ID = ?""", (model))

to:

        cursor.execute("""
                    SELECT *
                    FROM Species.dbo.BiomesPerSpp_Rehfeldt
                    WHERE ID = ?""", (model))

For whatever reason, changing the SELECT to a wildcard worked. Thanks for the responses Chris!

Jesse