topic Re: ArcGIS Pro: how to QUERY a text field with a numeric operator in ArcGIS Pro Questions

ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 13:59:50 GMT

I'm using an AGOL based feature layer that has all its features stored as text. I want to use a query such as "Tot_Value >= 500000", however I get an error since Tot_Value is a text string. I recall an earlier version of ArcMap allowed this type of operation using the same data source, so I believe it can be done. I'm not a SQL expert, but assume I can cast this to an Int or similar, however a web search is coming up dry, and attempts on my part generate errors. Suggestions?

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

DanPatterson_Retired — Mon, 06 Aug 2018 14:08:03 GMT

'int("Tot_Value") >= 500000' would be too easy I suppose (or some variant)

But you don't get the option in the sql builder, So I suspect adding a new field and doing the conversion and test in a def is your best option

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 14:15:10 GMT

Yep... just gets an syntax error....

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

DanPatterson_Retired — Sat, 11 Dec 2021 09:10:20 GMT

def func(val, threshold):
    """convert and compare"""
    if val is None:
        out = val
    elif int(val) >= threshold:
        out = 1
    else:
        out = 0
    return out


# expression
# func(!YourTextField!, 50000)‍‍‍‍‍‍‍‍‍‍‍‍‍

make a new field and figure out what you want to do when the threshold is exceeded as in the example above

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

MichaelVolz — Mon, 06 Aug 2018 14:36:55 GMT

What if your system is locked down and schema changes such as this require extensive testing to downstream apps? This seems like functionality that should be built into the software and not require the addition of almost a duplicate field.

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

PeteCrosier — Mon, 06 Aug 2018 14:55:46 GMT

You can CAST in SQL:

CAST(SomeField AS INT) >= 500000

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 15:09:16 GMT

One would think so but I still get an error

Curiously, if I drop the WHERE, I get a different error.

In both cases the fact that 500000 is in red is a flag. I've tried smaller numbers, decimal points, etc. without success.

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 15:10:03 GMT

Yep, but it needs to be viable in the ArcGIS Pro Select by Attribute SQL Expression Window.

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

MichaelVolz — Mon, 06 Aug 2018 15:14:50 GMT

The Cast statement does not appear to be helpful as the OP still gets an error.

Chuck:

Have you tried working with ESRI technical support on this issue?

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

JoshuaBixby — Mon, 06 Aug 2018 15:29:56 GMT

You can do more with ArcPy than what the GUI will let you. Since the ArcGIS relational data store is backed by PostgreSQL, try the following in the Python window:

arcpy.SelectLayerByAttribute_management(layer, "NEW_SELECTION", "CAST (Tot_Value as INTEGER) > 500000")‍

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 15:53:56 GMT

Josh, Thanks. I've tried:

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000"")

but get

Parsing error
SyntaxError: EOL while scanning string literal (<string>, line 1).

I started with the layer not in quotes (which is how I think it should be)... same error.

Suggestion as to what I'm doing wrong?

Chuck

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 16:14:26 GMT

Progress: I needed to refer to TOT_VALUE inside quotes. But now it says the expression is invalid.

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", 'WHERE CAST ("TOT_VALUE" AS INTEGER) > 500000')
Runtime error
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6314, in SelectLayerByAttribute
raise e
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6311, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\geoprocessing\_base.py", line 506, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

I thought that maybe the "WHERE" was not needed, tried it without that, same result.

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

DanPatterson_Retired — Mon, 06 Aug 2018 16:30:56 GMT

Why was the field a text field in the first place?

Are there other non-numeric values in there?

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 16:36:04 GMT

Its a text field, as are all the user defined fields in this layer. Thats how the content provider has formatted this data for years.

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

JoshuaBixby — Mon, 06 Aug 2018 16:39:10 GMT

You have an extra quotes in the ArcPy code you pasted, which would generate the error you see. Trying copying and pasting this:

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000")

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 16:57:39 GMT

Thanks again. I have a new error now, see below:

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000")
Runtime error
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6314, in SelectLayerByAttribute
raise e
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6311, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\geoprocessing\_base.py", line 506, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
ERROR: code:504, Your request has timed out., Proxy server got bad address from remote server (verify the server is running).
Failed to execute (SelectLayerByAttribute).

While I still have an "Invalid Expression" error, I also now have a time out. I think this may be because I'm querying a huge (100+M records) layer. I'm also trying to figure out how to limit my query to only the visible extent of the map. I started another request here: https://community.esri.com/message/790143-arcgis-pro-how-to-limit-select-by-attribute-to-the-visible-extent

Thoughts?

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

MichaelVolz — Mon, 06 Aug 2018 17:03:02 GMT

How about applying a definition query to the layer to limit it to just 10,000 or 100,000 records just to see if the time out error goes away?

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton — Mon, 06 Aug 2018 17:23:50 GMT

In the other thread it was suggested I use "Select from Current Selection", which makes sense. In this case, I've substituted "SUBSET_SELECTION" for "NEW_SELECTION", and made a rectangular selection of a small area before running the script. Same sort of delay, but I didn't see the Code 504 error.

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

JoshuaBixby — Mon, 06 Aug 2018 17:26:41 GMT

Casting a field will not allow the index to be used, if there is one, so you are basically doing a full-table scan on 100+M records. It isn't surprising it is timing out. Getting back to what Dan has mentioned, you really need to convert the field to integer and then index it.

Re: ArcGIS Pro: how to QUERY a text field with a numeric operator

MichaelVolz — Mon, 06 Aug 2018 17:30:24 GMT

The OP says that this operation used to work in ArcMap. Does this not appear to be a step backwards using Pro if you now need to create near duplicate fields that were not necessary in the past due to performance issues in Pro?