Latest Contributions by David_JAnderson

‎12-02-2015

Without knowing a little more about the nuts and bolts of how you have the databases set up it is difficult to give specific advice. Do you have it set up so that the MS Access database can talk to the Oracle database? Or a setup where the Oracle database can talk to the Access database? If either is the case then you can do things in one query. Otherwise it is going to take two queries. The query you have won't work for the one query route. First all tables have to be listed in the from clause. like this: "SELECT * FROM SAGIS_REACH_NETWORK AS r, {0} WHERE r.MODELNAME = {0}.MODELNAME;".format(tables[0]) Next I wouldn't pull all the rows across the network. All you really need is the count. "SELECT count(*) FROM SAGIS_REACH_NETWORK AS r, {0} WHERE r.MODELNAME = {0}.MODELNAME;".format(tables[0]) The fetch then becomes: num_matching_modes = oracle_cursor.execute(SQL).fetchone() You might need to experiment interactively to see how the fetch is coming across. Sometimes it is a single value, sometimes as list. If the two databases are not talking to each other there will need to be two queries. One to get the model name in the personal gdb and one to check if it is in Oracle. distinct_model_sql = 'select distinct(ModelName) from ImportTable' model_name = cursor.execute(distinct_model_sql).fetchone() Then matching_sql = select count(*) from OracleTable where model_name=?" match_count = cursor.execute(matching_sql,(model_name)).fetchone() Use the ? construct to fill in the variable so as to avoid a SQL injection attack. Plus it is just good form and it lets the database connector do the proper string formatting vis a vis quotation marks.

‎08-13-2015

Ana, You need to add some more code From the ESRI help, example 1: ArcGIS Help (10.2, 10.2.1, and 10.2.2) you need to add a page loop and manage the pdfs for mxd in mxd_list: current_mxd = arcpy.mapping.MapDocument(os.path.join(ws,mxd)) pdf_name = os.path.join(pdfws,mxd[:-4])+ ".pdf" pdfDoc = arcpy.mapping.PDFDocumentCreate(pdf_name) # create the PDF document object for pageNum in range(1, current_mxd.dataDrivenPages.pageCount + 1😞 current_mxd.dataDrivenPages.currentPageID = pageNum page_pdf = os.path.join(pdfws,mxd[:-4])+ + str(pageNum) + ".pdf" arcpy.mapping.ExportToPDF(current_mxd, page_pdf) pdfDoc.appendPages(page_pdf) # add pages to it os.remove(page_pdf) # delete the file pdfDoc.saveAndClose() # save the pdf for the mxd Doing PNG's is different. There is not a built in function to make one PNG from a list of PNG's.

‎06-24-2015

Thanks Josh. That makes sense. As I read your reply and think about what I want to know I realize that my real question is what is the full documentation for the stgeometry_sqlite.dll. That implements both the ST_Geometry functions and the utility functions on SQLite databases. Now that is the library that lacks documentation about functions other than the ST_ series. I found the reference to the ones like AddGeometryColumn in the help files which got me to wondering what other functions are under the hood. So far as I can tell the ESRI documentation does not list them. Just a few examples of using them. Contrast this with Spatialite which has a plethora of documented functions, including ones to handle Geopackages.

‎06-23-2015

I already know about that section of the help files that documents the various ST_ functions. What I can not find is the documentation for functions such as AddGeometryColumn. Examples of full documentation are those such as PostGIS Chapter 6. PostGIS Reference or Spatialite SpatiaLite SQL functions reference list.

‎06-23-2015

I have noticed that there seems to be a portion of the ESRI SQL implementation that is undocumented. The geometry related functions such as ST_Area, etc are well documented in the help. What is not documented are the various functions for the setup, administration and maintenance of the data. One such function is the AddGeometryColumn function as described in the 10.2 help here:ArcGIS Help (10.2, 10.2.1, and 10.2.2) There are other calls such as CreateSpatialIndex or DropGeometryMetadata that are shown in examples but I can not find the function documention. Much less a list of what other functions are available in the ST_Geometry library. Does anyone know where I might find the full documentation of the ST_Geometry library?

‎01-16-2015

The answer for ArcGIS 10.2.2 is to upgrade the SQLite dll from the 3.6 version that is shipped with ArcGIS to the latest version. Which at this time is 3.8.7.4. This is for stand alone Python (IDLE, Pythonwin). The version of SQLite in the Python command line bundled into ArcMap/Catalog is 3.7, which works with the stgeometry dll. The other thing to get in line is to make sure that the bit count aligns, ie use a 32 bit python with a 32 bit SQLite with the 32 version of the stgeometry_SQLite dll.

‎01-05-2015

After the command failed in python, I did try using a SQLite interface, with the same results. However, it shouldn't make a difference as any command that can be executed at the SQLite command line interface can be executed using the Python sqlite3 module or the APSW module if that is your SQLite preference. Further, why would there be an arcpy command to create a spatial database yet no functionality to access that database with arcpy once it had been created? I agree that the Spatialite extension is the more mature of the three. I have been using it for several years with the extension dll provided on the Spatialite site The Gaia-SINS federated project home-page . The problem is that those solutions were private and I could not deploy them to others in my organization without leading them through the spatialite extension installation. With the rollout in 10.2 of a ESRI version of the spatialite extension, I could now do Python scripts that other people could run right out of the box, without any additional installation. I have a ESRI support ticket in on this issue. I'll post the answer whenever I get one from ESRI.

‎12-23-2014

I am trying to use Spatialite in ArcGIS 10.2.2. When I try to load the SQLite extension like so --Load the ST_Geometry library on Windows. SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init'); which is what is shown on the help page, ArcGIS Help (10.2, 10.2.1, and 10.2.2) The python window just crashes. I try loading the extension in command line SQLite and that fails. I tried a mix of 32 bit and 64 bit thinking that might be the issue. Nope. I tried different locations and PATH variable settings. Nope. I tried it with the full path in the command. Nope. I was able to do this in 10.2.1 but not now in 10.2.2. I can get the standard Spatialite 4.2 package to load up. That will give me the Spatialite geometry option. One thing I want to test is how the st_geometry works with other tools like GDAL/OGR.

‎04-09-2014

I'm just starting to work with 10.2. One new functionality I want to explore is the use of SQLite databases. According to the documentatation http://resources.arcgis.com/en/help/main/10.2/index.html#/CreateSQLiteDatabase/03q30000002q000000/ the new SQLite database can be created using the code arcpy.gp.CreateSQLiteDatabase. Then I try that at the Python window, auto complete does not give the option for CreateSQLDatabase. Doing a dir(arcpy.gp) does not show the function. What is odd though is that actually typing the function will create the SQLite database file.

‎02-07-2014

I would suggest looking at the python datetime module. It will do anything you can think of for manipulating date and time values.

‎02-07-2014

I am wondering if an approach where queries are executed against the delta tables might work? Unless there is a way to get to the Version Changes button in the versioning toolbar in ArcMap.

‎02-06-2014

I am getting one of 2 errors and I don't know why. They are ORA-00911: invalid character and ORA-00933: (something about the statement not ended correctly). I am hoping a fresh set of eyes can help with it. Here is the code: orcl = cx_Oracle.connect("xxx/xxx@xxx") curs = orcl.cursor() database = "Database Connections\\xxx.sde\\xxx.xxx_xxx" sql1 = "create view RSIDs as " sql2 = "select * " sql3 = "from "+database+" where " sql4 = "xxxx like '%RSID%' or xxxxx like '%m%'" sql = str(sql1+sql2+sql3+sql4) print sql curs.execute(sql) curs.close() orcl.close() The put x's in for the sensitive stuff but left all the slashes, comma, quotes etc... the way I have them. I tried several different ways with the slashes because I know python is funny with them but I can't get it to create the view. The statement works in SQLPLus. I am using Python 2.7. Any help is appreciated. Have you checked the value of the database variable? I think in that in the SQL statement it needs to just be the table name, without the Database Connections\\xxx.sde\\ prefix. I am not sure why you have that as a variable as it does not seem to change. You might also consider using a multiline string instead of a bunch of concatenated together variables.

‎09-16-2013

Janet: I have had to perform this task in older versions of ArcMap as well where python did not have its current capabilities so VBA was used. In each case, whether it be in python or VBA, a corrupt mxd always crashed the script even with error handling such as try...except was used. I have spoken with ESRI Technical representatives and they have confirmed that there is no way around a corrupt mxd in VBA or python scripting except to exclude it all together from your loop. In my case, I had a script that would take an inventory of all mxds and various connections such as SDE. This was a read-only step where information was saved to an output text file. When the script would crash on a corrupt mxd, it would be noted and added to the code for the other script, that modified paths to data, so that script would just bypass these corrupt files and hopefully never crash. I hope this information helps you to come up with a solution. While doing the same type of mxd listing work I have found that arcpy has no problems in dealing with corrupt mxds when run from an ArcCatalog/ArcMap python command line. However the same command run from a regular python window, IDLE or Pythonwin will cause python to crash. map_doc = arcpy.mapping.MapDocument('path_to_mxd') data_frames = arcpy.mapping.ListDataFrames(map_doc) For a corrupt mxd, this code crashes in stand alone python but runs in the ArcCatalog/Map python window.

‎08-02-2013

No problem with doing that operation. I now have an ESRI support ticket that I hope will resolve this problem. Will post when I have results.

‎08-01-2013

No luck. It crashed again. Plus I learned that the arcpy.mapping.saveACopy methods also fails on this file. For now it looks like I'll just have to screen out the older files for now and try open up a ESRI support ticket to see if they can resolve the problem.

Online Status	Offline
Date Last Visited	‎11-11-2020 02:23 AM

My Ideas

Latest Contributions by David_JAnderson

Re: Using a SQL expression to check data in two databases

Re: Exporting Multiple Data Driven MXDs to PDFs using Python

Re: documentation of ST_Geometry functions

Re: documentation of ST_Geometry functions

documentation of ST_Geometry functions

Re: problems loading spatialite extension

Re: problems loading spatialite extension

problems loading spatialite extension

dir function does not show CreateSQLiteDatabase too

Re: Convert Time Field

Re: ArcPy - Versioned Database Deltas

Re: SQL Statement in Python

Re: Detecting Corrupt MXDs in Python

Re: ArcPy ListDataFrames on empty MXD crash

Re: ArcPy ListDataFrames on empty MXD crash

Re: Detecting Corrupt MXDs in Python

Re: Exporting Multiple Data Driven MXDs to PDFs us...