Make Query Table - Field conversion

[ChrisMatthews]

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

[ChrisMatthews]

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.

[JoshuaChisholm]

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.

[ChrisMatthews]

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.

[RichardFairhurst]

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.

[ChrisMatthews]

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

[BenjaminGosack]

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.

[RichardAsselin]

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...

[JamesCrandall]

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

[RichardFairhurst]

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.

[JamesCrandall]

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