arcpy.sa.ExtractByMask fails for enterprise geodatabase raster

[FredSpataro]

Hi All,

I'm tracking on odd issue that's come up when I moved some code to the production environment. In that environment, the rasters used by the scripts are stored in an enterprise geodatabase.  When running arcpy.sa.ExtractByMask with an input raster from an enterprise geodatabase the following error is thrown:

Messages

Executing: ExtractByMask GIS.DATACREATOR.dem watershed C:\...\Default.gdb\Extract_GIS.1

Start Time: Wed Jun 29 14:14:06 2016

ERROR 999999: Error executing function.

The table name is invalid.

No spatial reference exists.

ERROR 010302: Unable to create the output raster: C:\...\Default.gdb\Extract_GIS.1

ERROR 010067: Error in executing grid expression.

Failed to execute (ExtractByMask).

Failed at Wed Jun 29 14:14:19 2016 (Elapsed Time: 13.04 seconds)

The stock system toolbox tool (System toolbox\Spatial Analyst Tools\Extraction\Extract By Mask) generates the same error. 

It appears that this is caused by the "default output raster name".  In this case, that string is 'Extract_GIS.1' - which is an illegal raster name.  This is the typical GP default value: part tool name, part input dataset name.  Since the input raster is from a database, it's name is fully qualified (database.schema.table): GIS.DATACREATER.dem and the default text string uses the first four characters including the dot character ".".  It's unclear why the default name is not being checked as a valid name for the workspace... this seems to happen everywhere else in the stock GP tools.

So...in the stock tool, modifying the default output name to something valid allows the tool to run correctly.  BUT the arcpy.sa.ExtractByVersion does not take an "output dataset parameter".  It only takes the input raster and mask features. It then creates some default scratch raster which, in the examples, is later saved to a specific name using the raster.Save() method.  But b/c the default name is invalid, this arcpy function can never complete successfully if the input raster is from an enterprise geodatabase.  Ugh.

Things I've tried:

1. creating a layer file for the input raster with a clean name... the function still tries to use the FQN as the base for the default output name and fails

2. Changing the scratch workspace to a folder rather than the file geodatabase... Same name issue occurs with slightly different message but the file based name is still invalid as well....Again why is an invalid default name being chosen?

Why is the method signature of stock toolbox tool and arcpy version different?

[AlexanderBrown5]

Fred,

Can you supply some more information such as:

What version Enterprise Geodatabase you are utilizing?

What RDMS (oracle, sql) does the enterprise database built upon?

Just to test out if I was experiencing the same type of error:

I downloaded "PAMAP_DEM_mosaic_Butler_3m.zip" from

www.pasda.psu.edu - /data/LidarMosaics/CountyMosaics/county_DEM_3M/ 

I imported it into my SDE database and was able to successfully run that python command with no issues.

Messages

Executing: ExtractByMask "Database Connections\Butler_County.sde\Butler.DBO.PAMAP_DEM_mosaic_Butler_3m" "Database Connections\Butler_County.sde\Butler.DBO.mask" C:\Users\alex8694\AppData\Local\Esri\Desktop10.4\SpatialAnalyst\Extract_PAMA1

Start Time: Tue Jul 05 12:18:34 2016

Succeeded at Tue Jul 05 12:18:36 2016 (Elapsed Time: 1.65 seconds)

Looking at your messages information posted above, I would suggest trying to add the full path to your GIS.DATACREATOR.dem.  First you need to save a connection file under database connections to your database ( I assume you have one of these already?)

I do not believe the tool can find your raster because you are not specifying the entire path, such as:

r'Database Connections\database_name.sde\GIS.DATACREATOR.dem'

My code for sample run:

import arcpy
arcpy.sa.ExtractByMask(r'Database Connections\Butler_County.sde\Butler.DBO.PAMAP_DEM_mosaic_Butler_3m', 'r'Database Connections\Butler_County.sde\Butler.DBO.mask')

Regards,

Alex

[FredSpataro]

Hi Alex:

Thanks for the reply.

1. Yes I have a connection file, it's not stored in the normal 'Database Connections" folder since that points to a specific user's document directory.  We use a non-user specific folder for these.  I edited the full path to the connection .sde file in the message details so it was easier to read and hid details of our setup.  The connection file correctly finds the raster for other tools.

2. The database is SQLServer, ArcGIS 10.3.1

3. Esri support has verified the bug: BUG-000097536

Turns out it's very specific to the database name and connection setup but is easy to reproduce:

1. The database name has to be less than 4 characters. In my case the database name is GIS

2. The database connection user has to be a SQL account not an OS account and probably one that is NOT the table owner.  Is my case, the connection user is a read-only SQLServer account that doesn't own the table schema.

The combination of these two causes the default name for output to be "Extract_GIS.1" which is an invalid raster name and thus the tool can't complete. You'll notice in your test, neither of the two conditions are met and the tool produces a valid raster name: Extract_PAMA1. The connection user is able to "read" the specific table name, and use "PAMA" in the default name. If the connection user is not the table owner, the fragment used starts at the database name (probably makes sense, the table name is fully qualified if not the owner) and grabs the first 4 characters.  In your case that would be "BUTL" and still produce a valid name. In my case it grabs "GIS.", the dot character being invalid.  Seems like a simple oversight to exclude invalid characters or at the very least validate the default name.

Fred

[AlexanderBrown5]

Fred,

Thanks for the clarification.  I was not able to get the exact error you have, but was able to produce:

ERROR 000876: Output raster:  Extract_TE.D1's extension is invalid for the output raster format. 

My database name: TE

Raster name: dem

Full Raster name: TE.DBO.dem

I read through the support case to gain more of an understanding of the issue--I will not do any further testing at this time, as to not replicate testing already completed by technical support.  I am glad you have worked with ESRI technical support to verify that this problem is a bug, and have it officially logged.

Keep checking regarding updates to the bug through the Customer Care website (myesri.com).

Regards,

Alex

[FredSpataro]

Alex,

Thanks for following up. 

I get that error message if I set the "scratch workspace" to a folder.  I get the other error when the scratch workspace is a file geodatabase.  But both are about the same: the default output name is not valid for either workspace type.

I suspect all the other "arcpy.sa.Extract..." methods will fail in the same way. 

Current workarounds are:

1. don't use a database with less than 4 characters

2. connect with an os account or schema owned account

Neither of these were possible for me, given the production environment already exists.

NOTE: renaming the database at the SQL level causes lots more issues.  I can't connect from any ArcGIS client after renaming the DB.

There might be some tweak that makes the read-only SQL account default to the data owner schema but I haven't figured it out.

I'm trying this now...

3. use the data management tool to "clip" the raster down to temporary location then use the extract function.  This one adds some extra processing time and there are few settings that need to be tweaked to get the exact pixel size and orientation as the original but should get thru.

Cheers.