nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Make Query Table - Field conversion

Chris_Matthews

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

Finde mehr Beiträge mit dem Tag

Accepted answers

All comments

JamesCrandall

Anyway if Python does it that is nice, but only about 20 people on this forum could have come up with that solution. I demand solutions from ESRI that are designed for non-programmers to do a basic operation like data conversion involving a recasting of a field. Resorting to Python as the only option is unacceptably unfriendly from my perspective.

Field mapping is always a tough bug 🙂

In any event, I think your demand is not that...well.... demanding.

RichardFairhurst

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

I mentioned both tools because they operate similarly to convert field types, but you are correct that in this case only the Table to Table tool was potentially an option. Nothing of what you say about the source was explained, so my question was valid given what the user had said.

Anyway if Python does it that is nice, but only about 20 people on this forum could have come up with that solution. (Okay, I don't know the number of people with this level of skill, but it is very small compared to the overall forum population). In fact I am one of the people who is capable of it, but I am slow doing it. In any case, I demand solutions from ESRI that are designed for non-programmers to do a basic operation like data conversion involving a recasting of a field. Resorting to Python as the only option is unacceptably unfriendly from my perspective.

So long as only skilled programmers can do field conversions like this ESRI has failed its users and should be openly challenged and criticized until they can do a better job of supporting their users.

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

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

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

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

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.

Chris_Matthews

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

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.

Chris_Matthews

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.

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.

Chris_Matthews

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.

Chris_Matthews

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

ChristopherBlinn1

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.

JamesCrandall

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 🙂

Chris_Matthews

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.

JamesCrandall

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?

Chris_Matthews

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 😞

Chris_Matthews

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.

JamesCrandall

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.

JoshuaChisholm

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.

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.