Latest Contributions by JamesCrandall

"LENGTH(TRIM(SERIES_ID))" just doesn't make sense.  Can you express the full query you wish to apply? I can see two potential issues: 1.  You are not evaluating any specific field. SELECT * FROM <layer_name> WHERE <field name> = 0 2. TRIM(SERIES_ID) I suspect that SERIES_ID is an integer value being represented as text (hence the need to issue TRIM).  If so, then I'd recommend you actually fix the datasource --- if it's actually integers you need to store, then set the field to that type.  Then there is no gymnastics required to TRIM strings or worry about other such issues because the data is correct. ... View more

Keep in mind that the "IN" statement does have limits (depending upon database).  I think Oracle limit is 1000?  Not exactly sure but I recently had to break up my list of values that built my IN portion of a WHERE clause. ... View more

Peter, One suggestion is to be sure to clean up the in_memory space prior to and post process run(s) to each iteration, otherwise you may end up consuming available RAM (I believe this is where in_memory is written to).  I include this def() in most of my tools and can just call it anytime/anywhere needed, especially if all your processing is intermediate data -- so you would need to write the "final" output to disk. def clearINMEM():   arcpy.env.workspace = r"IN_MEMORY"        fcs = arcpy.ListFeatureClasses()   tabs = arcpy.ListTables()            ### for each FeatClass in the list of fcs's, delete it.   for f in fcs:     arcpy.Delete_management(f)     arcpy.AddMessage("deleted: " + f)   ### for each TableClass in the list of tab's, delete it.   for t in tabs:     arcpy.Delete_management(t)     arcpy.AddMessage("deleted: " + t) ... View more

Is this a ESRI Add-in tool?  If so, ESRI has a sample of populating the ComboBox and then running the desired process on the selected item: ArcGIS Help 10.1 ... View more

Let's get a bit more pythonic and into just 4 lines: fout = open(r'H:\Documents\blah\output.txt', 'w') values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str') fout.writelines("ID='" + str(val) + "' OR " for val in values) fout.close() ... View more

I tried to modify my original post but it won't let me because GeoNet is not a real forum. Anyway... I noticed that the values will come convert over as decimal, which you may not want.  So, simply modify the genfromtxt() method to include the output format as strings. values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str') Here's the updated code: import numpy as np fout = open(r'H:\Documents\blah\output.txt', 'w') values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str') for val in values:     writevalue = "ID='" + str(val) + "' OR "     fout.write(writevalue)     print writevalue fout.close() ... View more

Here's a way using NumPy: import numpy as np fout = open(r'H:\Documents\blah\output.txt', 'w') values = np.genfromtxt(r'H:\Documents\blah\input.txt') for val in values:     writevalue = "ID='" + str(val) + "' OR "     fout.write(writevalue)     fout.close() ... View more

Adam, I include that cleanup def() in just about all of my .py source files to any of the Geoprocess tools I build that utilize in_memory space.  But like Curtis said, it's a little off topic to this thread. ... View more

Ah.  Your Delete_management of FeatureLayers works for me too. ... View more

I just call this def() before/after any in_memory processing.  Seems legit. def clearINMEM():   arcpy.env.workspace = r"IN_MEMORY"   fcs = arcpy.ListFeatureClasses()   tabs = arcpy.ListTables()   ### for each FeatClass in the list of fcs's, delete it.   for f in fcs:     arcpy.Delete_management(f)     arcpy.AddMessage("deleted: " + f)   ### for each TableClass in the list of tab's, delete it.   for t in tabs:     arcpy.Delete_management(t)     arcpy.AddMessage("deleted: " + t) ... View more

Thomas, I haven't worked with pyodbc because we are an Oracle shop, but I think this example would likely apply just as well.  To answer your question, yes, we convert back and forth between NumPy arrays, Pandas DataFrames and gdb objects.  This is just showing acessing and using non-spatial table containing x/y coord values converting to a NumPy array and then a Feature Class. import arcpy  import cx_Oracle  import numpy as np    ### Build a DSN (can be subsitited for a TNS name)       dsn = cx_Oracle.makedsn(param1, param2, param3)       oradb = cx_Oracle.connect("username", "password", dsn)       cursor = oradb.cursor()    sqlQry = """SELECT MyTableOrView.SomeField1 AS SomeTEXTField,                     CAST(TO_CHAR(MyTableOrView.x_coords, 'fm9999999.90') AS FLOAT) AS x_coords,                     CAST(TO_CHAR(MyTableOrView.y_coords, 'fm9999999.90') AS FLOAT) AS y_coords                FROM MyTableOrView"""                  cursor.execute(sqlQry)  datArray = []      cxRows = cursor.fetchall()  for cxRow in cxRows:     datArray.append(cxRow)    #close the conn to ora  cursor.close()   oradb.close()  del cxRows, cursor     numpyarr_out = np.array(datArray, np.dtype([('SomeTEXTField', '|S25'), ('x_coords', '<f8'), ('y_coords', '<f8')]))    #convert the numpyarray to a gdb feature class  outFC = r'C:\MyGDB\xyPoints_FromStrings          if arcpy.Exists(outFC):    arcpy.Delete_management(outFC)     arcpy.da.NumPyArrayToFeatureClass(numpyarr_out, outFC, ("x_coords", "y_coords")) ... View more

Yes that gets past th error (and my memory of this issue).  It also reminds me that, again, it doesn't actually GROUP anything. ... View more

Joshua, I get a RuntimeError: An invalid SQL statement was used Running this against a table in the default.gdb in ArcGIS 10.1: tab = r'H:\Documents\ArcGIS\Default.gdb\arlist' with arcpy.da.SearchCursor(tab, ["fc", "item2"], sql_clause=(None, "GROUP BY fc, item2")) as cursor:     for row in cursor:         print "{0}, {1}".format(row[0], row[1]) This work though (minus the sql_clause): tab = r'H:\Documents\ArcGIS\Default.gdb\arlist' with arcpy.da.SearchCursor(tab, ["fc", "item2"]) as cursor:    for row in cursor:         print "{0}, {1}".format(row[0], row[1]) ... View more

Curtis, I am using Pandas quite extensively and I can confirm it offers a lot of value for table/list operations!  However, in the context of this thread, it just doesn't make sense because the idea of issuing SQL against the database is to return only those rose that meet the query.  That is, I wouldn't want to return 1 million rows just to process them with Pandas functions. ... View more

So... are you confirming that what I mentioned is not possible with Make Query Table? There's some confusion that this is possible with multiple fields returned.  However, I just don't see how so because a correct SQL statement that returns DISTINCT values on multiple fields simply must have a GROUP BY clause in it. ... View more