ArcSDE Compress didn't compress

I inserted some data my means of SQL into a SDE managed table - or to be more precise into the SDE managed view belonging to the table.

I had done that before and so far it had worked without any issues. This time the compress operation didnt compress at least the table I had inserted rows. I ran compress a few times but to no avail. I restarted ArcGIS server but again it didnt change the result. Then I stopped ArcGIS server and killed all remaining connections to the Oracle schema and ran compress again. This time compress finished succesfully. Up to now I never had to kill all connections - so I assume there was something different this time.

ArcMap 10.5.1, Oracle 11.2.0.4

ArcMap – Oracle LISTAGG results in empty column

When using LISTAGG recently in view definition I had the problem that the column content based on LISTAGG was not shown in ArcMap / ArcCatalog.

The LISTAGG command usage was like that:

…

cast(listagg(nachname || '; ' || vorname || ' / ') within group(order by nachname) as nvarchar2(500)) as eigentuemer_name,

…

The columns (nachname, vorname) I concatenated were of data type “nvarchar2”. My initial view definition did not specify the data type for the column – I added it later but it did not solve my problem.

In SQL Developer the content of the column was shown right from the start. To check how other applications behave I connected to the view using Excel and tried to import the data into a sheet. When I did that I got an ORA-01401 inserted value too large for column message. After bit of research I found the following discussion on SO.

It was mentioned that there might be an issue with the length of the right hand side of the concatenation operator when usung NVARCHAR. I changed the view definition to VARCHAR2 and also increased the size. Afterwards I could import the data into Excel – but in ArcMap the column was still empty.

The second hint on the page – applying to_char conversion - finally solved the issue:

...

cast(listagg(to_char(nachname) || '; ' || to_char(vorname) , ' / ') within group(order by nachname) as varchar2(4000)) as eigentuemer_name,

...

It’s a bit strange – other columns in the view based on NVARCHAR2 are shown in ArcMap. Only in connection with LISTAGG the problem occurs.

ArcMap 10.5.1, Oracle 11.2.0.4.0

ArcMap slow?

ArcMap was extremly slow on my PC. Opening attribute tables, the Add Data dialog box, a model oder even a toolbox took ages (such as opening a toolbox > 30secs).
Now, I'm used to applying "Windows Classic" design in order to speed up things.
Turns out - since switching back to standard Windows 7 design ArcMap performance has improved significantly on my machine! Opening a larger model now only takes 1-2 secs (if at all)!

ArcMap 10.3, Windows 7

Excel Pivot table - manually reorder rows

Header > Sort Options > manually

Then:
- click into cell of row you want to move
- open context menu > "move"

 
Excel 2013

ArcPy - find layers with joins

Here is a brute force script for finding layers with JOINs. As there doesn't seem to be a "hasJoin" property I just use arcpy.RemoveJoin_management which throws an exception if there is no join. This is script is only usefull for once off checks.


ArcMap 10.3

 # find (all) joins in MXD by removing them!  
 # usage: copy MXD and run script or run script and don't save MXD as all JOINS will be removed  
 # result will be a list of all layers where JOIN has been removed succesfully  
 import arcpy  
 mxd = arcpy.mapping.MapDocument("CURRENT")  
 layers = arcpy.mapping.ListLayers(mxd)  
 allJoins = ''  
 for layer in layers:  
   try:  
     arcpy.RemoveJoin_management (layer.name)  
     print("join removed: "+layer.name)  
     allJoins += '\n'+layer.name  
   # no join throws exception  
   except Exception as err:  
     print(err.args[0])  
 print(allJoins)  

ArcPy - MoveLayer fails with "assert refe_c is not None, 'Did not find reference layer.'"

Just wanted to copy and paste all layers into new MXD file. Unfortunately order of layers in TOC is not retained. So I googled for script and found one - but MoveLayer failed with error message "assert refe_c is not None, 'Did not find reference layer.'". The post did not mention anything like that.

Solution in my case : for the reference layer a fresh reference is required. 

old: refLayer=moveLayer
new: refLayer = arcpy.mapping.ListLayers(mxdT, moveLayer.name, dfT)[0]  

ArcMap 10.3.1


Here is the script:

 # -*- coding: utf-8 -*-  
 # source code initially taken from: https://community.esri.com/thread/28042  
 # assumption : no layer groups  
 # my use case : copy and paste all layers into new MXD, layer order is not retained - run script to restore layer order  
 import arcpy  
 mxd_source = r"C:\temp\source1.mxd"  
 mxd_target = r"C:\temp\target.mxd"  
 mxdT = arcpy.mapping.MapDocument(mxd_target)  
 # assumption: only one DF  
 dfT = arcpy.mapping.ListDataFrames(mxdT, "")[0]  
 mxdS = arcpy.mapping.MapDocument(mxd_source)  
 # assumption: only one DF  
 dfS = arcpy.mapping.ListDataFrames(mxdS, "")[0]  
 #initialize refLayer with layer  
 refLayer = arcpy.mapping.ListLayers(mxdT,"",dfT)[0]  
 for layerSource in arcpy.mapping.ListLayers(mxdS,"",dfS):    
   c=layerSource.longName  
   d=layerSource.description  
   sourceLayer=c+d  
   print("processing layer in source: '" + sourceLayer+"'.")    
   for layerTarget in arcpy.mapping.ListLayers(mxdT,"",dfT):  
    a=layerTarget.longName  
    b=layerTarget.description  
    moveLayer= a+b     
    if sourceLayer==moveLayer:  
      print("moving layer:'"+moveLayer+"', reference layer: '"+refLayer.name+"'.")  
      moveLayer=layerTarget            
      arcpy.mapping.MoveLayer(dfT,refLayer,moveLayer, "After")       
      #get fresh layer reference - otherwise MoveLayer will fail next time:  
      refLayer = arcpy.mapping.ListLayers(mxdT, moveLayer.name, dfT)[0]             
      break  
 mxdT.save()  
 #arcpy.RefreshActiveView()  
 #arcpy.RefreshTOC()       

Job available

We are currently looking for a GIS specialist. The job description can be found here:

https://jobs.winterthur.ch/?yid=1755&sid=6k9afr49m011jjoe5rsjb72802

Please apply if you are interested.