topic SQL expression to python expression in Python Questions

SQL expression to python expression

Tom_AndersEngebakken — Thu, 30 Jan 2014 06:54:42 GMT

Hi

I have a SQL expression that I use in Make Feature Layer in Model Builder ("HOSPITAL" = '% HOSPITALNAME%'). The expression makes a selection in a OD Cost Matrix that a patient is patient only at one hospital and not all hospitals as an OD Cost Matrix would normally do. HOSPITAL is a column in orgins in add location and HOSPITALNAME is a column in the DESTINATION to add location.
What I wonder is how this expression will be in a python script?
I've tried something like this in python in the expression field in arcpy.MakeFeatureLayer "'HOSPITAL' = '" + HOSPITAL NAME + "'". But it does not works. I don't get any error message but it only runs OD Cost Matrix normally.

Can anyone help me with this?

Regards Tom Anders

Re: SQL expression to python expression

JakeSkinner — Thu, 30 Jan 2014 10:04:07 GMT

Hi Tom,

What type of data are you working with? i.e. Shapefile, File Geodatabase Feature Class, Personal Geodatabase Feature Class, SDE Feature Class

http://resources.arcgis.com/en/help/main/10.2/index.html#//00s500000033000000

Try the following:

"HOSPITAL = '" + HOSPITALNAME + "'"

Re: SQL expression to python expression

Tom_AndersEngebakken — Thu, 30 Jan 2014 11:55:06 GMT

Hi
thanks for the reply.

I work with shapefiles.

It looks like the expression you showed me worked well. But the output files overwrites each other. What can I do to avoid this?

Here is the Python script:

# Import system modules
import sys, string, os, arcpy

from arcpy import env

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

env.workspace = r"C:\Tom\Oppdrag_2014\IPLOS"

arcpy.env.overwriteOutput = True

inNetworkDataset = r"C:\Tom\ELVEG_Nettverk_2013\ELVEG_Nettverk_2013.gdb\ELVEG_Nettverk\ELVEG_Nettverk_ND_2013"
outNALayer = "Foreldere_til_Barn"
impedanceAttribute = "Drivetime"
accumulateAttributeName = ["Minutes"]
FCForeldre = "io_1000_Shape.shp"
FCBarn = "Barn_1000_Shape.shp"
outLayerFile = r"C:\Tom\Oppdrag_2014\IPLOS" + "/" + "%outNALayer%" + ".lyr"

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

rows = arcpy.SearchCursor(FCBarn)
for row in rows:
    famnrNavn = row.getValue("famnr")
    inDestination = "BarnLayer"

    #arcpy.MakeFeatureLayer_management(FCBarn, BarnLyr)
    arcpy.MakeFeatureLayer_management(FCBarn, inDestination, "famnr = '" + famnrNavn + "'")

    lyrForeldere = "ForeldereLayer"

    #arcpy.MakeFeatureLayer_management(FCForeldre, ForeldereLyr)
    arcpy.MakeFeatureLayer_management(FCForeldre, lyrForeldere, "famnr = '" + famnrNavn + "'")

    #Create a new OD Cost matrix layer.
    arcpy.MakeODCostMatrixLayer_na(inNetworkDataset, outNALayer, "Minutes", "", "", "Minutes")

    #Load the locations as origins.
    arcpy.AddLocations_na(outNALayer, "Origins", lyrForeldere, "Name famnr #","1000 Meters", "famnr")

    #Load the locations as destinations
    arcpy.AddLocations_na (outNALayer, "Destinations", inDestination, "Name famnr #","1000 Meters", "famnr")

    #Solve the OD cost matrix layer
    arcpy.na.Solve(outNALayer,"","", "10 Meters")

    #Save the solved OD cost matrix layer as a layer file on disk with relative
    #paths
    arcpy.SaveToLayerFile_management(outNALayer, outLayerFile,"RELATIVE")

Regards Tom Anders

Re: SQL expression to python expression

JakeSkinner — Thu, 30 Jan 2014 12:18:44 GMT

You can name the output layer file to something unique. Ex:

arcpy.SaveToLayerFile_management(outNALayer, outNALayer + "_" + famnrNavn + ".lyr", "RELATIVE")

Also, when you post your code, be sure to wrap it in CODE tags using the # symbol. This will preserve the indentation.

Re: SQL expression to python expression

Tom_AndersEngebakken — Thu, 30 Jan 2014 12:40:55 GMT

I am completely new to Python so thank you very much for your help. appreciate it.

Tom Anders