BLOG
|
Does this only apply to PostGIS data type? or can I expect to see this InsertCursor behavior with st_geometry type also?
... View more
11-01-2021
09:07 AM
|
0
|
0
|
975
|
POST
|
Suman, We are undergoing the same exercise. For the roles and user, if you have fewer than 1000, the EnterpriseDB Migration Toolkit is a good option. https://www.enterprisedb.com/products/oracle-to-postgresql-migration-toolkit-move-from The migration portal can give you an assessment of the LOE for moving general workloads to Postgres pr Enterprise DB Advanced Server.. https://migration.enterprisedb.com/ Here is a complete guide to general Oracle to Postgres migration that has some things you may not have thought of. https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-convert-database-oracle-alternative?sbrc=1n3eFq4MdbMJq8Qi1Bdz8SA%3D%3D%24uOwjHro3_qzZNDDx74OInw%3D%3D
... View more
10-27-2021
07:20 AM
|
1
|
0
|
1556
|
IDEA
|
The US Forest Service is beginning an effort to migrate our Oracle databases to PostgreSQL. EDB Advanced Server PostgreSQL has the Oracle compatibility features and tools that we require to move our large Enterprise Data Warehouse from Oracle to PostgreSQL. The compatibility features allow migration of PL/SQL programming code that is used heavily within the data warehouse without modification. Developers can continue to code in PL/SQL after migration.. The familiar Oracle administrative views that EDB Advanced Server provides makes it easier for developers and DBAs to transition to working with Postgres. While community PostgreSQL is wonderful and supports ESRI geodatabases extremely well. We have lots of PL/SQL programming code inside our data warehouse. Transitioning programing code from PL/SQL to Postgres is a time consuming process. I would ask that ESRI please support the use of the EDB Advanced Server flavor of PostgreSQL so that large customers like the US Forest Service can leave Oracle and migrate to PostgreSQL in a smooth manner with the least amount of effort. If ESRI does not support EDB Advanced Server PostgreSQL then we'll be forced to either leave our EDW on Oracle and pay extreme prices, or separate the EDW into two pieces: 1) Geospatial Data to Community PostgreSQL 2) Non-geospatial Data and PL/SQL programming code to EDB Postgres Advanced Server PosgtreSQL. This would create an enormous amount of work for our ETL programmers and application developers. It would add complexity to our processes that would greatly increase the cost and effort of migrating off of Oracle database. ESRI please add support for EDB Postgres Advanced Server Postgres... Thank you.
... View more
02-04-2020
01:05 PM
|
2
|
1
|
2100
|
POST
|
Vince, At version 10.3 we no longer have access to the command line tools and sdelayer -o si_stats and se_toolkit sdestats -o si_stats? My users have gotten used to using this tool to analyze their spatial indexes. Is there a way to do this with just SQL inside the Oracle database? Thanks in advance. Regards, John P. Lovato US Forest Service Geospatial Database Engineer
... View more
06-03-2015
04:33 PM
|
0
|
1
|
538
|
POST
|
Ken, I'm using the following query to get the last edit date for versioned feature classes: SQL CODE SELECT r.OWNER, r.TABLE_NAME, TO_DATE ('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(r.REGISTRATION_DATE,'SECOND') REGISTRATION_DATE, m.STATE_ID, s.CLOSING_TIME EDIT_DATE FROM <OWNER>.TABLE_REGISTRY r, ( SELECT MAX(m.STATE_ID) STATE_ID, m.REGISTRATION_ID FROM <OWNER>.MVTABLES_MODIFIED m GROUP BY m.REGISTRATION_ID ) m, <OWNER>.STATES s WHERE r.TABLE_NAME NOT LIKE '%DIRTYAREAS' AND r.TABLE_NAME NOT LIKE '%LINEERRORS' AND r.TABLE_NAME NOT LIKE '%POINTERRORS' AND r.TABLE_NAME NOT LIKE '%POLYERRORS' AND r.TABLE_NAME NOT LIKE 'GDB_%' AND r.REGISTRATION_ID ( + ) = m.REGISTRATION_ID AND s.STATE_ID = m.STATE_ID ORDER BY m.STATE_ID DESC Substitute <OWNER> for the name of your geodatabase owner in the SQL above. I wrote the following geoprocessing script tool In Python for the user to use with ArcToolbox: GUI GUI VALIDATOR CODE # -*- coding: utf-8 -*- # --------------------------------------------------------------------------- # GetVersionedEditDates_Validator.py # Created on: 2015-06-03 # John P. Lovato # Description: # Validator for GetVersionedEditDates script tool. # Updated on: # 2015-06-03 by John P. Lovato - Created. # # --------------------------------------------------------------------------- import arcpy class ToolValidator(object): """Class for validating a tool's parameter values and controlling the behavior of the tool's dialog.""" def __init__(self): """Setup arcpy and the list of tool parameters.""" self.params = arcpy.GetParameterInfo() def initializeParameters(self): """Refine the properties of a tool's parameters. This method is called when the tool is opened.""" # disable the CSV File location parameter on start up self.params[3].enabled = 0 return def updateParameters(self): """Modify the values and properties of parameters before internal validation is performed. This method is called whenever a parameter has been changed.""" # toggle the state of the CSV file location parameter baed on # checkbox. if self.params[2].value == True: self.params[3].enabled = 1 else: self.params[3].enabled = 0 return def updateMessages(self): """Modify the messages created by internal validation for each tool parameter. This method is called after internal validation.""" return STANDALONE PYTHON SCRIPT # -*- coding: utf-8 -*- # --------------------------------------------------------------------------- # GetVersionedEditDates.py # Created on: 2015-06-02 # John P. Lovato # Description: # Displays registration date and last edit date for versioned objects # Arguments: # <SDE_Workspace> - Workspace connection. # <GDB_Owner> - Geodatabase Owner (User who owns GDB metadata tables) # <Write_CSV> - Should the results go to a CSV file. # Updated on: # 2015-06-03 by John P. Lovato - Added parameters for writing csv file and code # for creating CSV file. # --------------------------------------------------------------------------- import arcpy, sys, os, datetime, csv from arcpy import env from datetime import datetime # SDE Workspace sdeWorkspace = arcpy.GetParameter(0) # GDB Owner gdbOwner = arcpy.GetParameter(1) # Write CSV writeCSV = arcpy.GetParameter(2) # CSV File outputFolder = arcpy.GetParameterAsText(3) # Establish the gp workspace env.workspace = sys.path[0] # Create the SDE Connection sdeConn = arcpy.ArcSDESQLExecute(sdeWorkspace) # Build the SQL Statement for last versioned edits (DML changes only) SQLStatement= """SELECT r.OWNER, r.TABLE_NAME, TO_DATE ('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(r.REGISTRATION_DATE,'SECOND') REGISTRATION_DATE, m.STATE_ID, s.CLOSING_TIME EDIT_DATE FROM <OWNER>.TABLE_REGISTRY r, ( SELECT MAX(m.STATE_ID) STATE_ID, m.REGISTRATION_ID FROM <OWNER>.MVTABLES_MODIFIED m GROUP BY m.REGISTRATION_ID ) m, <OWNER>.STATES s WHERE r.TABLE_NAME NOT LIKE '%DIRTYAREAS' AND r.TABLE_NAME NOT LIKE '%LINEERRORS' AND r.TABLE_NAME NOT LIKE '%POINTERRORS' AND r.TABLE_NAME NOT LIKE '%POLYERRORS' AND r.TABLE_NAME NOT LIKE 'GDB_%' AND r.REGISTRATION_ID ( + ) = m.REGISTRATION_ID AND s.STATE_ID = m.STATE_ID ORDER BY m.STATE_ID DESC""" # Customize the SQL Statement SQLStatement = SQLStatement.replace('<OWNER>',gdbOwner) # Display the SQL Statement arcpy.AddMessage("\n" + SQLStatement + "\n") # Pass the SQL statement to the database. sdeReturn = sdeConn.execute(SQLStatement) if writeCSV: # Directory of current script dirScript = os.path.dirname(__file__) # Output Folder if outputFolder: dirScript = outputFolder # Report Date reportDate = str(datetime.now()).replace(' ','_').replace(':','-').replace('.','-') # Output File outputFile = os.path.join(dirScript,gdbOwner + '_DML_Modified_' + reportDate + '.csv') arcpy.AddMessage('Report will be written to ' + outputFile + '\n') # Display the header to output arcpy.AddMessage('{:<30}'.format('OWNER')+' '+'{:<30}'.format('TABLE')+' '+'{:^21}'.format('REG_DATE')+' '+'{:^8}'.format('STATE_ID')+' '+'{:^21}'.format('EDIT_DATE')) arcpy.AddMessage('{:<30}'.format('------------------------------')+' '+'{:<30}'.format('------------------------------')+' '+'{:^21}'.format('---------------------')+' '+'{:^8}'.format('--------')+' '+'{:^21}'.format('---------------------')) # Display (and capture) the results with open(outputFile, 'wb') as csvfile: csvwriter = csv.writer(csvfile) # write the header csvwriter.writerow(['OWNER','TABLE','REG_DATE','STATE_ID','EDIT_DATE']) for sde in sdeReturn: arcpy.AddMessage('{:<30}'.format(sde[0])+' '+'{:<30}'.format(sde[1])+' '+'{:<21}'.format(sde[2])+' '+'{:^8}'.format(sde[3])+' '+'{:<21}'.format(sde[4])) csvwriter.writerow([str(sde[0]),str(sde[1]),str(sde[2]),str(sde[3]),str(sde[4])]) arcpy.AddMessage("\n+++++++++\n") else: # Display the header to output arcpy.AddMessage('{:<30}'.format('OWNER')+' '+'{:<30}'.format('TABLE')+' '+'{:^21}'.format('REG_DATE')+' '+'{:^8}'.format('STATE_ID')+' '+'{:^21}'.format('EDIT_DATE')) arcpy.AddMessage('{:<30}'.format('------------------------------')+' '+'{:<30}'.format('------------------------------')+' '+'{:^21}'.format('---------------------')+' '+'{:^8}'.format('--------')+' '+'{:^21}'.format('---------------------')) # Display the results only for sde in sdeReturn: arcpy.AddMessage('{:<30}'.format(sde[0])+' '+'{:<30}'.format(sde[1])+' '+'{:<21}'.format(sde[2])+' '+'{:^8}'.format(sde[3])+' '+'{:<21}'.format(sde[4])) arcpy.AddMessage("\n+++++++++\n") Hope this helps you. Sincerely, John P. Lovato USDA Forest Service Geospatial Database Engineer
... View more
06-03-2015
10:06 AM
|
1
|
0
|
1898
|
POST
|
Thank you Xander. I'll see if this will work for the developer. It looks like it gives the correct information for me.
... View more
02-11-2015
07:57 AM
|
0
|
0
|
484
|
POST
|
Xander, What method should be used to return only desktop installation directory? The developer used GetInstallInfo()[InstallDir] in the past, but that won't work when both engine and desktop are on the same machine. Regards, John P. Lovato
... View more
02-11-2015
06:56 AM
|
0
|
2
|
484
|
POST
|
When both engine and desktop 10.2 are installed on the same machine. GetInstallInfo('desktop') returns information for the engine install. See the sample code below. Code Output import arcinfo import arcpy arcpy.SetProduct('ArcInfo') for install in arcpy.ListInstallations(): print('\nINFO: for ' + install + ' installation') # Use the dictionary iteritems to iterate through # the key/value pairs from GetInstallInfo d = arcpy.GetInstallInfo(install) for key, value in d.iteritems(): # Print a formatted string of the install key and its value # print("{:<13} : {}".format(key, value)) INFO: for desktop installation SourceDir : ...\ESRI\ArcGIS10.2.2\Engine\windows\SetupFiles\ InstallDate : 7/28/2014 InstallDir : c:\arcgis\engine10.2\ ProductName : Engine BuildNumber : 3552 InstallType : Typical Version : 10.2.2 SPNumber : N/A Installer : install_user SPBuild : N/A InstallTime : 12:48:48 INFO: for engine installation SourceDir : ...\ESRI\ArcGIS10.2.2\Engine\windows\SetupFiles\ InstallDate : 7/28/2014 InstallDir : c:\arcgis\engine10.2\ ProductName : Engine BuildNumber : 3552 InstallType : Typical Version : 10.2.2 SPNumber : N/A Installer : install_user SPBuild : N/A InstallTime : 12:48:48
... View more
02-11-2015
06:19 AM
|
0
|
5
|
4138
|
POST
|
UPDATE: I have created an sde account and still get the same result when trying to use sdemon to start the application server. Please advise...
... View more
01-15-2015
11:57 AM
|
0
|
0
|
633
|
POST
|
After upgrading from my enterprise geodatabase from 10.1 SP1 to 10.2.2 Patch 290200 I cannot start the new 10.2 application server. I am running application server as "root" not "sde" Linux user... In ArcCatalog, the "General" tab "Upgrade Status" section says: "This 10.2.2 Patch 290200 geodatabase matches the ArcGIS release you are currently using. When trying to start the 10.2 Application Server in Linux I get: ------------------------------------------------------- 10.2.2 geodatabase ------------------------------------------------------- ST_Geometry Schema Owner: (SDE) Type Release: 1007 Instance initialized for ((sde)) . . . Connected to instance . . . C:\Users\TEMP\AppData\Local\Temp\arcC63F\: No such file or directory WARNING: SDETMP: Setting temp path to /tmp ERROR in checking system stored procedures (1). Run the Upgrade Geodatabase tool to install/upgrade system tables. Refer to the ArcGIS help topics for instructions. ERROR in checking system stored procedures (1). Run the Upgrade Geodatabase tool Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and dbinit.sde. How can I get my application server to start up? Any help you could provide would be appreciated. Thank you.
... View more
01-15-2015
09:07 AM
|
0
|
3
|
633
|
POST
|
Hopefully the attached scripts will help someone who needs to help their Oracle DBAs set up a new ArcSDE 10.1 Enterprise Geodatabase. Enjoy!
... View more
09-17-2013
10:13 AM
|
0
|
0
|
628
|
Title | Kudos | Posted |
---|---|---|
1 | 10-27-2021 07:20 AM | |
2 | 02-04-2020 01:05 PM | |
1 | 06-03-2015 10:06 AM |
Online Status |
Offline
|
Date Last Visited |
05-13-2022
03:09 AM
|