Latest Contributions by VinceAngelo

‎03-30-2025

The security models of both AWS and PostgreSQL might be interfering here, as well as the operating system. The default is "no connectivity" so you need to explicitly grant communication from the IP address of the host where ArcGIS Server is running. If in doubt, try installing a PostgreSQL client like psql on the Server box, and try connecting from that. If psql can't connect, then the networking to PG is still not correct, and it's an AWS admin task. Note that PostGIS is the spatial extension to PostgreSQL (implementing geometry and geography types), so it's not PostGIS you connect to, but PostgreSQL itself (in this case, the PG RDS instance endpoint). The AWS admin needs to make a route from the ArcGIS Server EC2 host to the RDS endpoint. If the Server host is on-premise, you'll have a suboptimal solution (due to the high latency), but it still requires an explicit grant of access to the Server IP (you may have firewall issues from the Server node to the RDS as well). Good luck! - V

‎03-27-2025

gdb_to_date is part of Archiving, not Versioning, per se. Enabling Archiving creates a bunch of indexes on the _H table. It's not generally necessary to create additional indexes (which could harm performance more than help it), and doing so outside of supported tools might result in issues upgrading your geodatabase. This seems to be an XY Problem question. Instead of asking about the solution you're working, it might be better to ask about the root problem, of archived table performance. It's quite possible that the table size and database server characteristics have a lot more to do with this query latency than an index on a column with low selectivity. (How many rows in the table are there, and how many of them have been deleted?) Note that this is probably more of a geodatabase (Data Management) question than an Enterprise one. - V

‎03-14-2025

Memory workspaces have an intrinsic spatial index. You can't change how they operate. That's what that error indicates. Optimization by spatial index is not an infinite trick. There's always a diminishing returns inflection point, after which performance is harmed by a spatial index. There are lots of other optimization techniques available, but you'd need to provide more details about your task before any could be suggested. - V

‎03-12-2025

It seems like you want "Archiving, But..." (similar to "Agile, But..."). I don't recommend hacking the Archiving tables in a versioned geodatabase. Instead, I suggest you add the timing you want in basic attributes, and use SQL constraints (temporal where clause) instead of Archiving to accomplish the same goal. There are two schools of temporal sequencing on a future "end date". One is to use a future epoch (e.g., "Dec 31, 9999"), which is how Archiving works. The other is to use NULL when the date is undefined. The latter is trickier in the WHERE clause (since it uses an OR), even if it does use less space (but proper use of COALESCE could ease that). - V

‎03-11-2025

There isn't going to be one query, unless you UNION ALL together every An and Dn table of every user in the database instance, joining in the states and state_lineages table. It would be an exceedingly ugly query, probably involving at least two WITH clauses (because UPDATEs are a combination of both Add and Delete, they would have to be queried separately, then conjoined in an additional query. This would take me a day or two to work out, and I've been a SDBE/SDE/ArcSDE/geodatabase subject matter expert for thirty years. If you want to go down this path, you'll need to provide what you have so far. You'll need to compile a list of all the versioned tables, queries that generate lists of table_name, state, An/Dn row and impacted objectid and a query that can summarize INSERT/UPDATE/DELETE events by version-table. It might be easier to run all those queries from Python, and collate there, but it's still an ambitious task. - V

‎03-04-2025

Which Pro? 3.2 introduced support for 64-bit integers. Up until then wide integers (except "NUMBER(38)") mapped to 64-bit float. [3.2.0 had issues with arcpy.ArcSDESQLExecute processing of 64-bit ints as 16-bit (massive overflow/underflow), but that was fixed by 3.2.1.] The root of this issue is the lack of standard numeric types (short/int/long/real/float) in Oracle (everything is a "NUMBER"), forcing Esri to have to decide what type to map each NUMBER flavor to... Is NUMBER(10) a long integer, or a float (over 2.14e+10 INT_MAX)? If Oracle implemented an "int" type, the "cast(rownum as int)" would work, but instead it maps it to a NUMBER which has resolution that overflows 32-bit storage (and isn't "NUMBER(38)"), so it falls back to double precision. I wonder if NUMBER(10) won't map to 64-bit integer once you get to Pro 3.3.x... - V PS: And be careful with casting rownum to a registered rowid column, because changing the ORDER BY would change the order (and so can the optimizer, at its whim). Since the point of a registered rowid column is to link queries to a specific row, with a WHERE clause, then the rownum values could differ on subsequent query.

‎02-26-2025

Part of the issue here is probably that ArcGIS uses a sophisticated model to store coordinates, in effect, snapping the vertices to a very fine sub-millimeter grid. The exact details are covered in the Understanding Coordinate Management in the Geodatabase whitepaper, but the gist of it is that a SpatialReference object is a lot more than the coordinate system -- It includes the exact parameters for the vertex grid (and it's variable, and the default values have evolved over time, so depending on when and how the dataset was created and referenced, it could be different from site to site). It's probably not as useful to ask in Esri Community why the non-Esri app is behaving that way that it is -- GIS Stack Exchange might be a better forum, but in order to ask a more specific Question, you should probably review the whitepaper, so you know which details to add. - V

‎02-24-2025

Okay, first off, please format your SQL as multi-line, so it's legible. Second, the database is optional, so there's no need for "sde." in front of every reference. Then you need to learn to use JOIN (multiple tables in FROM left standard practice after ANSI SQL 92, and are now worst practice) and to make use of both table and column aliases: SELECT wo.DateReceived, wo.Location_Area, wo.Address, wo.Facilty_Zone, wo.Requesting_Department, wo.ProblemDescription, wo.RequestedCompletionDate, wo.Status, wo.Type, wo.TeamLeader, wo.WorkNumID, wo.WorkComplete, wo.Comments AS wo_comments, wo.PreExMaterial, wo.PurchasedMaterial, wo.CouncilDistrict, wo.CallerNumber, wo.CallerEmail, wo.SHAPE, wo.TeamAssigned, wo.AssignedAssetType, wo.WorkCompletedSummary, wo.CallerName, wo.Emailed, wo.EmailedDate, wo.PurchasedMaterialsDescription, wo.Ownership, wo.EMFEMA, wo.WorkStart, wo.WorkDiff, wo.SandbagVolume, wo.AssetCondition, wo.AssetNotes, wo.GlobalID, eq.Subtype, eq.Equipment, eq.TotalHours, eq.comments AS eq_comments, eq.WorkOrderObjID FROM WORKORDER wo JOIN Equipment eq ON eq.joinkey = wo.joinkey It's not obvious what the JOIN column should be, but I expect you can work that out. - V

‎02-20-2025

If the table is non-versioned, you're responsible for a serial or SEQUENCE to keep it clean. UUID generation is trivial. - V

‎02-19-2025

I have batch scripts running in client sites several times a day, doing updates and inserts of existing and new rows. I use SQL to manage the change detection, using INSERT ... SELECT ... FROM ({virtual_table_query}) vt and UPDATE ... FROM ({virtual_table_query}) WHERE u.keycol = vt.keycol, editing both unversioned and versioned data. TRUNCATE/APPEND is too blunt an instrument, and unregistering the feature class defeats the purpose of versioning and archiving. The key here is to populate a temporary table (which is truncated and appended, or sometimes only appended), then use SQL for the gentler bits. - V

‎02-18-2025

Truncate and Append is pretty much worst case for a versioned geodatabase, particularly a TRUNCATE, since it will bypass the Delete event. Best practice is also the trickiest -- Identify the changes (INSERT/UPDATE/DELETE) that need to occur to bring the data into sync, and issue commands against a versioned view to make it happen. - V

‎02-15-2025

The point behind using separate development and production databases is to keep dev as close as possible to production. The choice between DBO- and sde-owned geodatabases is somewhat fundamental, and straddling the two models like this is bound to cause this exact sort of problem. The sde-login owned geodatabase model does not know how to respect DBO rights, so piling on more permissions is not likely to be able to help. At this point, you need to choose between not locking out non-sde logins in DEV, and reconfiguring the DEV database to be DBO-owned. I personally hate DBO geodatabases with a passion, but if that's your production configuration, I'd suggest you restart with a new DEV and use that same model. - V

‎02-12-2025

It depends on situational requirements, but best practice is to keep the database server and all timestamp values set to UTC (aka Greenwich Mean Time). Web clients are exceptionally persistent in converting date/time values to localtime, even when they're already sharing a timezone. - V

‎01-28-2025

File geodatabase hasn't really changed since 10.0 (vice the 9.x architecture). There have been types added, the use of which would make older clients not recognize those tables, but the underlying format hasn't changed, and there isn't an upgrade procedure for them. - V

‎01-22-2025

Table ownership and schema are different things in PostgreSQL. ArcObjects geodatabase registration functionality requires that the schema be the same as the owner login. You can use the tables without registration, or you can name them as required for registration. - V

My Ideas

Latest Contributions by VinceAngelo

Re: Registering an AWS PostGIS database with ArcGIS Server

Re: Create SQL Server Index on [GDB_TO_DATE] column?

Re: Spatial index does not work on memory-based layer

Re: Ability to change date and time when using archiving on versioned data?

Re: Understand number of edits present in childhood version and default version

Re: Oracle Database View — Casting rownum as integer treated as double, but casting rownum as number(38,0) treated as long

Re: Coordinate Mismatch between ArcGIS Pro and QGIS

Re: Create Query Layer using multiple tables with same field name

Re: Loading Data in to Versioned, Archiving tables on daily basis

Re: Loading Data in to Versioned, Archiving tables on daily basis

Re: Loading Data in to Versioned, Archiving tables on daily basis

Re: SA account can't change feature class schema in ArcGIS Pro

Re: Timestamp Difference

Re: ArcPro: This 10.0 geodatabase matches the ArcGIS release you are currently using.

Re: Register With Geodatabase fails due to lack of permission

Re: Precision vs. XY Resolution

Re: Best practices for programmatically mirroring ...

Re: Best practices for programmatically mirroring ...

Re: Best practices for programmatically mirroring ...

Re: Precision vs. XY Resolution

Coordinate Reference Systems