topic How to Analyze and Compress Geodatabase automatically? in ArcGIS API for Flex Questions

How to Analyze and Compress Geodatabase automatically?

cristiBJ — Tue, 11 Dec 2012 22:48:11 GMT

My situation: SDE SQL Express Geodatabase, ArcGIS 10.1. When user submitted incidents online. My other script is connecting to the backend SQL database to forward, add comments, etc. The problem is, when I open SQL Management Studio, the data is not updated. I have to use ArcCatalog Data Server command "Compress" to synchronize.

Is there a script to do so? Robert, or Anyone can help with some ideas? Thank you very much.

I had to use this instead of online editing. Because online editing has to bring up the map, many of our citizen requests, once entered into system, we need heavily editing, it doesnot need map involved. We only update the attributes, add comments, etc. I can easily use a ASP.net Script to connect to SQL Express and update.

Re: How to Analyze and Compress Geodatabase automatically?

cristiBJ — Wed, 12 Dec 2012 15:06:12 GMT

Is this not a normal way to go?

Re: How to Analyze and Compress Geodatabase automatically?

RobertScheitlin__GISP — Thu, 13 Dec 2012 12:07:12 GMT

Cristi,

I would imagine that you would have to setup a GeoProcessing service to do this. You can script about anything in python and then publish it as a geoprocessing service in ArcGIS Server and then call that service from flex. I don't have any examples of code for this though.

Re: How to Analyze and Compress Geodatabase automatically?

JacobBoyle — Thu, 13 Dec 2012 13:15:31 GMT

Are you trying to hit the attribute table directly? You can make life a lot easier on yourself if you use a Versioned View.

You can create a view that pulls the data for the system tables that ArcSDE uses to keep track of changes to a view that you can then use. If you do this, you won't have to compress your data after every edit.

http://resources.arcgis.com/en/help/main/10.1/index.html#/Creating_versioned_views_from_ArcGIS_Desktop/006z000000vr000000/

Re: How to Analyze and Compress Geodatabase automatically?

cristiBJ — Thu, 13 Dec 2012 19:51:37 GMT

Robert,
Thank you again, for available to reply. I got the below Python code.

But It has this error message when executing "Error 000837: The workspace is not the correct workspace type. Failed to execute<Compress>". The SDE connection file is from the folder that ArcCatalog uses to connect. See attached screen copy of connection in arcCatalog. I am stuck here 😞

Thank you very much....
---------------------------------------------------------------------------
# Import system, Geoprocessing, time and email modules
import sys, string, os, arcgisscripting, time

# Set the date.
Date = time.strftime("%m-%d-%Y", time.localtime())

# Set the time.
Time = time.strftime("%I:%M:%S %p", time.localtime())

# Create the Geoprocessor object
gp = arcgisscripting.create()

##gp.CheckProduct("ArcEditor") # Checks the license level.

##gp.SetProduct("ArcEditor") # Sets the license level.
#Process: Check out the highest license available
try:
    if gp.CheckProduct("ArcView") == "Available":
        gp.SetProduct("ArcView")
    elif gp.CheckProduct("ArcEditor") == "Available":
        gp.SetProduct("ArcEditor")
    elif gp.CheckProduct("ArcInfo") == "Available":
        gp.SetProduct("ArcInfo")
except:
        message = "ERROR: Could not select an ArcGIS license level! Exiting script..."; showPyError(); sys.exit()
        message = "Selected an " + gp.ProductInfo() + " license"; showPyMessage()

print "Process started at " + str(Date) + " " + str(Time) + "." + "\n"

# Set up the log file.
LogFile = file('C:\\temp\\Analyze\\GIS-' + Date + '.txt', 'w') #Creates a log file with todays date.
output = open('C:\\temp\\Analyze\\GIS-' + Date + '.txt', 'w') #Path to log file.
output.write(str("Process started at " + str(Date) + " " + str(Time) + "." + "\n")) # Write the start time to the log file.

# Load required toolboxes (This shouldn't need to be changed unless ArcGIS was
#                           installed to a different location)
gp.AddToolbox("C:/Program Files (x86)/ArcGIS/Desktop10.1/ArcToolbox/Toolboxes/Data Management Tools.tbx")

try:
    # Compress the database
    print "Begining Compress..." + "\n"
    gp.toolbox = "management"
    # For this script to work it will need the full path to the .sde connection file.
    gp.compress("C:\Documents and Settings\Administrator\Application Data\ESRI\Desktop10.1\ArcCatalog\Connection to chester_sqlexpress.sde")
    print gp.GetMessages() + "\n"
    output.write(gp.GetMessages()+ "\n")

except:
    Text = gp.GetMessages()

    #Display message
    print Text

# Sets the Date & Time since the script started.
Date = time.strftime("%m-%d-%Y", time.localtime())# Set the date.
Time = time.strftime("%I:%M:%S %p", time.localtime()) # Set the time.
output.write(str("Process completed at " + str(Date) + " " + str(Time) + "." + "\n")) # Write the start time to the log file.

output.close() # Closes the log file.

print "Process completed at " + str(Date) + " " + str(Time) + "."

Re: How to Analyze and Compress Geodatabase automatically?

cristiBJ — Thu, 13 Dec 2012 20:21:20 GMT

Hi Jacob
I read the help. Then I did create the versioned view. (please see attached screen).
But when I query from SQL Script, I still do not get the latest update.(unless I manually compress database).
Did I miss something? How do I know if I am querying against the versioned view? When I follow the link and create the versioned view, does it make the system use this versioned view?
----------------------------------------------------------------------
'My ASP Script to query the database
Dim connection

Set connection = CreateObject("ADODB.Connection")
connection.ConnectionString ="Provider=sqloledb;Server=CHESTER\SQLEXPRESS;Initial Catalog=ServiceRequest;User ID=sql_servicerequest;Password=citygov"
connection.Open()

'do a query
sSQL = "SELECT * FROM SERVICEREQUESTPRIMARY"

     Set rst = Server.CreateObject("ADODB.Recordset")
     rst.ActiveConnection = connection
     rst.open sSQL

if not rst.eof then

.............

end if
----------------------------------------------------------------------

Are you trying to hit the attribute table directly? You can make life a lot easier on yourself if you use a Versioned View.

You can create a view that pulls the data for the system tables that ArcSDE uses to keep track of changes to a view that you can then use. If you do this, you won't have to compress your data after every edit.

http://resources.arcgis.com/en/help/main/10.1/index.html#/Creating_versioned_views_from_ArcGIS_Desktop/006z000000vr000000/

Re: How to Analyze and Compress Geodatabase automatically?

cristiBJ — Thu, 13 Dec 2012 21:05:06 GMT

An Update to above, my Python script complained "The Workspace is not the correct workspace...". Then I added the below code to clear cache before compressing. It worked successfully.

    import arcpy
    from arcpy import env
    arcpy.ClearWorkspaceCache_management("C:\Documents and Settings\Administrator\Application Data\ESRI\Desktop10.1\ArcCatalog\Connection to chester_sqlexpress.sde")
    print arcpy.GetMessages()+"\n"

This link helped me.
http://resources.arcgis.com/en/help/main/10.1/index.html#//00170000000m000000

Thank you Robert. Now I am going to see how to make this a geoprocessing service that I can call from my script.

Re: How to Analyze and Compress Geodatabase automatically?

RhettZufelt — Thu, 13 Dec 2012 21:08:50 GMT

Robert,
Thank you again, for available to reply. I got the below Python code.

But It has this error message when executing "Error 000837: The workspace is not the correct workspace type. Failed to execute<Compress>". The SDE connection file is from the folder that ArcCatalog uses to connect. See attached screen copy of connection in arcCatalog. I am stuck here 😞

try:
# Compress the database
print "Begining Compress..." + "\n"
gp.toolbox = "management"
# For this script to work it will need the full path to the .sde connection file.
gp.compress(" C:\Documents and Settings\Administrator\Application Data\ESRI\Desktop10.1\ArcCatalog\Connection to chester_sqlexpress.sde")
print gp.GetMessages() + "\n"
output.write(gp.GetMessages()+ "\n")

Without being able to dive in and test, I believe at least one of the issues is here. I think that should be something like

"Database Connections\\Connection to chester_sqlexpress"

.
I often start a model in ArcMap and add the compress tool, then browse to the dataset, export to python script and that will give you the proper synatax for the path.

Just a thought, not sure if it helps,

R_

Also, not sure about compress, but arcpy.Compact_management runs orders of magnitude faster than the gp.Compact_management. Might get better perfomance if you convert it all to arcpy.

Re: How to Analyze and Compress Geodatabase automatically?

cristiBJ — Thu, 13 Dec 2012 22:47:53 GMT

Thank you rzufelt. I found out it is the cache that caused the error, not the path to the sde file. I am still not sure if we need to clear cache each time before compressing. Do you know?

I do find out both arcpy, and gy compress the database. Since you point out arcpy is much faster, I will use it.

Thank you again!

Without being able to dive in and test, I believe at least one of the issues is here. I think that should be something like
"Database Connections\\Connection to chester_sqlexpress"
.
I often start a model in ArcMap and add the compress tool, then browse to the dataset, export to python script and that will give you the proper synatax for the path.

Just a thought, not sure if it helps,

R_

Also, not sure about compress, but arcpy.Compact_management runs orders of magnitude faster than the gp.Compact_management. Might get better perfomance if you convert it all to arcpy.

Re: How to Analyze and Compress Geodatabase automatically?

RhettZufelt — Thu, 13 Dec 2012 22:54:26 GMT

No, I don't know.

As far as the compress being faster, one would have to test that. I did find that several of the arcpy tools run a lot faster though.

In particular, if I used the gp version it would take 10-15 minutes to compact my FGDB depending on network traffic.

using the arcpy version, it would compact it in 10-15 seconds depending on network traffic.

Hope you figure it out,

R_