topic Re: Make Query Table - Field conversion in Python Questions

Make Query Table - Field conversion

ChrisMatthews — Fri, 21 Feb 2014 14:01:20 GMT

Hello

I'm creating a model that is sucking in data from an Oracle database, which I intend to turn into an Event Layer.

Usually this is pretty simple, however on this occation the X and Y coordinates have been stored as strings.

Is it possible to convert field data types when bringing in the data with the Make Query Table tool. Clearly if I was using straight SQL to the database I could do that)?

All help and suggestions appreciated.

Regards

Chris

Re: Make Query Table - Field conversion

JoshuaChisholm — Tue, 25 Feb 2014 13:15:21 GMT

Are you trying to make points with the X and Y fields? Or are they simple to be used as a data field for your records?

You could use the Add Field tool to make two numeric X and Y fields and then use the Calculate field tool to convert the string field to a number. You could probably skip the add field tool if you are confident that all the records will convert without error.

Your expression could be something like:

float( !XField!)

Let me know if that helps at all.

Re: Make Query Table - Field conversion

JamesCrandall — Tue, 25 Feb 2014 13:33:15 GMT

Fix the database, it's that important. I just see no value in doing gymnastics to wrestle things that should be modeled for what they are at the database tier.

If it's a date, store it as such. Decimals, floats, integers? Then that's what they are period.

Get with the database admin, and make it right. You will save yourself tons of headaches, not just in the immediate, but long-term life-cycles of the tools and applications you build will be better off.

Re: Make Query Table - Field conversion

ChrisMatthews — Tue, 25 Feb 2014 13:36:20 GMT

Yes I'm going to use table to create an event layer, which is why I'm wanting to convert the field type.

I've tried using the Add Field Tool, but it throws an error on a table created with the Make Query Table tool. Although it wouldn't be my first choice to solve this problem, I ought to be possible to convert on the fly 😞

I'll try your suggestion of converting within the Make XT Event Layer and see what happens. I'll report back here with the results.

Re: Make Query Table - Field conversion

ChrisMatthews — Tue, 25 Feb 2014 13:38:01 GMT

Fix the database, it's that important. I just see no value in doing gymnastics to wrestle things that should be modeled for what they are at the database tier.

If it's a date, store it as such. Decimals, floats, integers? Then that's what they are period.

Get with the database admin, and make it right. You will save yourself tons of headaches, not just in the immediate, but long-term life-cycles of the tools and applications you build will be better off.

Unfortunately this isn't an option as it is an third party application database. Any tampering would breach our support agreement 😞

Re: Make Query Table - Field conversion

JamesCrandall — Tue, 25 Feb 2014 13:39:05 GMT

Unfortunately this isn't an option as it is an third party application database. Any tampering would breach our support agreement 😞

How exactly are you "sucking in" the Oracle data as you mentioned in your OP?

Re: Make Query Table - Field conversion

ChrisMatthews — Tue, 25 Feb 2014 13:42:24 GMT

How exactly are you "sucking in" the Oracle data as you mentioned in your OP?

Apologies, I should have included that info. as it is important; I'm using the Make Query Table tool.

Re: Make Query Table - Field conversion

JamesCrandall — Tue, 25 Feb 2014 13:46:22 GMT

Apologies, I should have included that info. as it is important; I'm using the Make Query Table tool.

That's my bad. Missed it.

Well... I wonder if you can perform the conversion directly in the SQL as part of the MakeQueryLayer_management implementation. I will hunt around to see if there are examples.

My thought is, can you issue a "CAST" or "TO_NUMBER" statement in the sql somewhere?

Edit: I noticed that you are attempting to implement MakeQueryTable! Man, that stuff just looks messy. If it were me, I'd go towards python script and implement the cx_Oracle library --- you have so much more control. I know that is outside of the bounds for your OP and sorry I don't have a good solution other than this. BUT, the cx_Oracle lib is very awesome 🙂

Re: Make Query Table - Field conversion

ChristopherBlinn1 — Tue, 25 Feb 2014 13:49:29 GMT

Interested also how you're "sucking in" your table. Plus this sounds like a major flaw on the DB design to store coordinates as strings.

If you need the points, and are willing to do a couple of extra steps then after you "suck in" the data, create a copy of the table (perhaps storing the copy in a FGDB). Then add your x/y fields (type double), calculate the fields using the string type fields, then make your XY event layer.

If this is a model, and you're not allowing overwrite of geoprocessing events, simply add a step to delete the copied table before creating a new one.

I hate submitting this as a solution, so let's call it a workaround.

Re: Make Query Table - Field conversion

ChrisMatthews — Tue, 25 Feb 2014 13:53:25 GMT

That's my bad. Missed it.

Well... I wonder if you can perform the conversion directly in the SQL as part of the MakeQueryLayer_management implementation. I will hunt around to see if there are examples.

My thought is, can you issue a "CAST" or "TO_NUMBER" statement in the sql somewhere?

Edit: I noticed that you are attempting to implement MakeQueryTable! Man, that stuff just looks messy. If it were me, I'd go towards python script and implement the cx_Oracle library --- you have so much more control. I know that is outside of the bounds for your OP and sorry I don't have a good solution other than this. BUT, the cx_Oracle lib is very awesome 🙂

Conversion directly in the SQL would be the ideal solution, the model would be alot similiar and the load would be placed on the dB server. I will be exporting the script to Python at some point as it will be run on schedule each night, so the cx_Oracle lib is an option...

Re: Make Query Table - Field conversion

ChrisMatthews — Tue, 25 Feb 2014 14:37:04 GMT

Are you trying to make points with the X and Y fields? Or are they simple to be used as a data field for your records?

You could use the Add Field tool to make two numeric X and Y fields and then use the Calculate field tool to convert the string field to a number. You could probably skip the add field tool if you are confident that all the records will convert without error.

Your expression could be something like:
float( !XField!)
Let me know if that helps at all.

I tried this from a Python version of the code. I didn't work it gave me syntax errors.

Re: Make Query Table - Field conversion

JoshuaChisholm — Tue, 25 Feb 2014 14:44:36 GMT

I tried this from a Python version of the code. I didn't work it gave me syntax errors.

float( !XField!) is what you have to use from the calculate field tool. If you running it right in python, try this:

float(Xstr)

Where Xstr is your X value as a string.

Re: Make Query Table - Field conversion

ChrisMatthews — Wed, 26 Feb 2014 07:27:39 GMT

float( !XField!) is what you have to use from the calculate field tool. If you running it right in python, try this:
float(Xstr)
Where Xstr is your X value as a string.

Here is the Python line I've tested with;
arcpy.MakeXYEventLayer_management(LLPG_Commercial_View, float("UNI72LIVE.PR_BLPU.MAP_EAST"), float("UNI72LIVE.PR_BLPU.MAP_NORTH"), LLPG_Commercial2_Layer, "", "")

Here is the error returned;
ValueError: could not convert string to float: UNI72LIVE.PR_BLPU.MAP_EAST

It looks like it is trying to convert the fieldname rather than the values in the field.

Re: Make Query Table - Field conversion

RichardFairhurst — Wed, 26 Feb 2014 08:27:04 GMT

ArcMap does not support casting of data fields at all except in pure SQL statements. The tool input that are not where clauses are not SQL based. The field is string and that is all ArcMap can use it as. ArcMap can only create a new field that is numeric. It cannot do anything to make your string field numeric. This limitation has been complained about by every user for over a decade and ESRI won't take it on.

You have to convert it at the database level apart from any ESRI tools. ESRI won't write tools to do that for you. You are stuck either creating new fields or disconnected copies of the entire data table using Table to Table to recast the field to numeric inside of ArcMap.

ESRI makes sure everything else results in errors. Only a purely text based input like a cvs or txt file is converted to numeric by the tool on the fly. If it detects that the input is a true data table it won't do that. You would not be the first person ESRI told to suck it up and make major schema changes outside of their product to make their product work and you won't be the last.

Data conversions to make ESRI tools work feels like it is practically 50% of my job to use ArcMap at all if I have to deal with any data created outside of ArcMap by people who did not care about ESRI's data rules. The only benefit of being forced to adhere to these strict requirements is that adhering to their rules does translate into the best performance once I make the conversions. When ESRI attempts to accommodate such conversions on the fly the performance degradation is usually so severe I end up making the database level conversions anyway to stay sane.

In fact I won't use Excel, cvs or txt files without conversion even though they are supported, because the performance boost of using a real numeric field in a real database table is dramatic and inevitably these formats impose some use restriction that I cannot live with at some point if the data inside of them is at all valuable for my job.

Re: Make Query Table - Field conversion

ChrisMatthews — Wed, 26 Feb 2014 12:13:43 GMT

ArcMap does not support casting of data fields at all except in pure SQL statements. The tool input that are not where clauses are not SQL based. The field is string and that is all ArcMap can use it as. ArcMap can only create a new field that is numeric. It cannot do anything to make your string field numeric. This limitation has been complained about by every user for over a decade and ESRI won't take it on.

You have to convert it at the database level apart from any ESRI tools. ESRI won't write tools to do that for you. You are stuck either creating new fields or disconnected copies of the entire data table using Table to Table to recast the field to numeric inside of ArcMap.

ESRI makes sure everything else results in errors. Only a purely text based input like a cvs or txt file is converted to numeric by the tool on the fly. If it detects that the input is a true data table it won't do that. You would not be the first person ESRI told to suck it up and make major schema changes outside of their product to make their product work and you won't be the last.

Data conversions to make ESRI tools work feels like it is practically 50% of my job to use ArcMap at all if I have to deal with any data created outside of ArcMap by people who did not care about ESRI's data rules. The only benefit of being forced to adhere to these strict requirements is that adhering to their rules does translate into the best performance once I make the conversions. When ESRI attempts to accommodate such conversions on the fly the performance degradation is usually so severe I end up making the database level conversions anyway to stay sane.

In fact I won't use Excel, cvs or txt files without conversion even though they are supported, because the performance boost of using a real numeric field in a real database table is dramatic and inevitably these formats impose some use restriction that I cannot live with at some point if the data inside of them is at all valuable for my job.

Although it isn't the answer I wanted it at least gives me a clear answer, some times it is good to establish the limitations.

Regards

Chris

Re: Make Query Table - Field conversion

BenjaminGosack — Wed, 26 Feb 2014 13:54:43 GMT

Try to generate your query table then use a read cursor to iterate over the table and a write cursor to write the geometry and attributes directly to a new feature class. You can use a cast function in the python code outside of an ArcGIS tool.

Re: Make Query Table - Field conversion

RichardAsselin — Wed, 26 Feb 2014 14:05:06 GMT

Ideally you'd want to change the column in the database, but given your previous comments, it looks like this isn't an answer. In that case, I'd vote for the TO_NUMBER option in your SQL statement. The load is put on the DB server, and you're getting data in exactly the format you want inside your model. Also, chances are the server is going to better suited to handle the extra load of changing the string into a number than your desktop is...

Re: Make Query Table - Field conversion

JamesCrandall — Sat, 11 Dec 2021 15:30:31 GMT

This is one option using python script (not a model) and the cx_Oracle library. This would allow you to put the CAST from string to numeric of your x_coord/y_coord fields in the Oracle db. The basic idea is to fill a cursor with the results of the SQL, append those cursor rows to a new array/list, covert the list to a NumPy array and finally convert that to a FeatureClass.

This example is pulled from an implementation I have that does something similar but you will have to straighten out the SQL for your needs and it will not work if you just copy/paste, but it should get you close to what you want I think.

Hope this helps!


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

Even better code that elimiates the "datArray" construction as it is not needed. Just use the cxRows list as-is! (I need go back and update some things now -- sometimes posting on these threads makes you re-evaluate things!)


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

#close the conn to ora
cursor.close() 
oradb.close()
del cursor 

numpyarr_out = np.array(cxRows, 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"))

Re: Make Query Table - Field conversion

RichardFairhurst — Wed, 26 Feb 2014 19:17:48 GMT

Why is Python needed at all to create a new feature class/table that converts the field types? As long as you are disconnecting from the source data why not just use the Feature Class to Feature Class tool or the Table to Table tool? They can alter the field map type of the coordinate fields from string to double and output a new FC/table and perform the desired conversion directly to the new output.

It is only if you want to use the original source and maintain a connection to it that you have to look for alternatives outside of ArcMap. For those you need to set up the on the fly conversion on the server side.

Re: Make Query Table - Field conversion

JamesCrandall — Thu, 27 Feb 2014 10:28:36 GMT

Why is Python needed at all to create a new feature class/table that converts the field types? As long as you are disconnecting from the source data why not just use the Feature Class to Feature Class tool or the Table to Table tool? They can alter the field map type of the coordinate fields from string to double and output a new FC/table and perform the desired conversion directly to the new output.

It is only if you want to use the original source and maintain a connection to it that you have to look for alternatives outside of ArcMap. For those you need to set up the on the fly conversion on the server side.

From what I understand, the data source is a non-spatial table in an Oracle database. I am not exactly sure how to use the Feature Class to Feature Class tool in this instance as the source is simply attributes not registerd with any SDE. Also, from the docs, http://resources.arcgis.com/en/help/main/10.1/index.html#//001200000027000000 the Table to Table conversion input data types does not seem have an Oracle table as an option either.

The OP is going to have to perform some way to connect and query the data source, then create some mechanism that will transform the result to an output format desired or needed (my example shows that it will be saved as a GDB table).