Latest Contributions by VinceAngelo

There are better, more useful ways to lock down a Python install than to just ban Python use. Doing it this way is so simplistic it doesn't bode well for "business reasons" arguments. This is more a management issue than a technical one. You need someone to lean on the IT folks to create a workable solution, not to just punt the problem to the end-users. - V ... View more

While possible, that's not what I was recommending. Using a binary loader is the fastest way to get all features into a staging table. Then it's a simple join to identify features not present: INSERT INTO table1 ( {long_list_of_columns}) SELECT {long_list_of_columns_prefixed_with_"t."} FROM temptable t LEFT OUTER JOIN table1 p ON p.keyfield = t.keyfield WHERE p.keyfield IS NULL You would, of course, need an index on the keyfield (or keyfields) in table1, and have a mechanism for generating a reliable objectid value (this was dirt easy with a serial column in PostgreSQL, but there should be a function you can use with Oracle). Detecting the changed rows is a similar UPDATE statement. Identifying the rows to mark for deletion would flip the table order, and require an index on temptable(keyfield). I've found that it's an order or magnitude or two faster to load the parallel table, then manifest deltas in a single commit, than to deal with variable duration downtime on the data during a TRUNCATE/INSERT/REINDEX  outage. - V ... View more

"TRUNCATE" is an unlogged removal process, so you probably mean "UPDATE" or "DELETE". "Append" is generally an INSERT. Your update criteria isn't very clear, so it's not easy to make a specific recommendation. I've had good luck using FeatureClassToFeatureClass or FeatureClassToGeodatabase into a temporary table in the database, then using SQL to UPDATE changed rows, INSERT new rows, and DELETE removed rows. This was not in a versioned feature class, so I didn't have to deal with versioned views. arcpy.ArcSDESQLExecute() can be used to issue the SQL from ArcPy. Not sure what the Pro SDK equivalent would be. - V ... View more

Hexagons can't be tessellated themselves. You need to move on to triangles after the initial layer. I've actually got code for this in my Blog post. Squares (aka diamonds) can be sub-tessellated, as can triangles themselves. It's not clear what a unic layer might be. - V ... View more

The value in the computed length field depends on the map projection used in your canvas. Web Mercator is useless for planar measurement (the poles are infinitely far from the Equator, so all measurements not along the Equator itself are skewed, potentially infinitely), as are all geographic coordinate systems (Cartesian degrees are meaningless). Some measurement calculations in the UI are smart enough to not trust Web Mercator, and to do geodesic computation with a geographic coordsys, but the table rows are going to return Cartesian results (the units of which may not make sense). If you use a coordinate reference which is not the same as the data source, any stored values in the original coordref may not agree with the values computed "on-the-fly" by reprojection.  The amount of error may vary by location (e.g. over Africa vs. over Iceland). - V ... View more

Any query returning over a hundred thousand rows of 2 million is always going to be slow, especially through a service that only returns 2000 records at a time, in a format that's much, much larger than the raw data. This has nothing to do with PostgreSQL and everything to do with throughput.  - V ... View more

It's more likely that the ST_Intersects() is your performance bottleneck, especially if you have not constructed a spatial index. Personally, I'd do this query as a single statement using a virtual table, and with an ST_Within:   UPDATE parcel_new_pin u SET zoning = vt.zoning FROM ( SELECT p.pin, p.zoning FROM parcel_new_pin p JOIN ZONING z2 ON sde.ST_Within(sde.ST_PointOnSurface(p.shape,z2.shape)) = 1 WHERE p.zoning <> z2.zoning_district AND z2.zoning_district <> 'HB-S MLKO' ) vt WHERE u.pin = vt.pin;   For this query, I'd make sure that ZONING has an index on the geometry column, and PARCEL_NEW_PIN has an index on PIN.  Additionally, you can test the raw performance of the ST_PointOnSurface query in isolation:   SELECT sde.ST_PointOnSurface((p.shape)) FROM parcel_new_pin p   And the performance of the JOIN without UPDATE:   SELECT p.pin, p.zoning FROM parcel_new_pin p JOIN ZONING z2 ON sde.ST_Within(sde.ST_PointOnSurface(p.shape,z2.shape)) = 1 WHERE p.zoning <> z2.zoning_district AND z2.zoning_district <> 'HB-S MLKO'   If you haven't built a spatial index on the polygons of ZONING, that should be your first priority, though it's probably easiest to use the ArcPy utility Add Spatial Index (Data Management) via an enterprise geodatabase connection (.sde) file, at least at first, since the parameters can be tricky. - V ... View more

I've done a join with 72M ST_PointOnSurface calculations against a 35M polygon layer to verify parent-child relationships using PostgreSQL with PostGIS geometry, and it ran in less than 25min (on a RDS xlarge instance). I never tried that in Oracle with SDE.ST_GEOMETRY (or with the PG sde.ST_Geometry implementation) How many features? What is the average vertex count in the polygons? How are the indexes built? What does the query plan look like? How long does a simple "SELECT geomcol FROM tablename" query take on the table, and how long does a unbounded ST_PointOnSurface query take.  Including the actual query will help generate feedback. - V ... View more

I'm mostly using AWS RDS, where the Esri sde.st_geometry type isn't available. I haven't ever seen performance issues with PostGIS geometry types, though I've always built appropriate indexes.  I have seen poor performance with millions of spatially fragmented features (at 1m it's noticeable, after 10m it starts to impact functionality), but spatial fragmentation is storage-agnostic, and impacts all storage implementations (shapefile, file geodatabase, Oracle,  SQL-Server, PostgreSQL, Informix, DB2,...). More details would be necessary in order to distinguish whether the issue was with the storage format or the underlying data. Among the information needed: Exact versions of PostgreSQL and PostGIS The number of tables involved (both with and without geometry columns) The number of features in the largest table The geometry type (point/line/poly) For line/polygon, the average number of vertices in each feature The frequency of update on the features The average number of features returned on the average query A description of the performance problem using numbers (e.g., "It takes 14.7 seconds to return 40,123 features in GeoJSON format over a window that extends 17% of the total area")  - V ... View more

Put me in the "crazy number" camp. It's also suboptimal for enterprise geodatabase. The tools are optimized for rows, not for tables. When a Windows "list directory contents" request takes three minutes to complete, there's no chance that row access will be fast. 2500 feature classes is too many, even for 100 file geodatabases. Far better to have 25 tables with 100m rows each. - V ... View more

@SteveCole wrote:  You cannot edit an enterprise SDE feature class with a basic ArcGIS license level. This is not completely true. There are a number of ways to access/maintain  tables and rows in an EGDB that don't require a Standard or Advanced license (for example, a Portal survey could start data upload, without even a Basic license). And with Standard/Advanced, you can do a number  of tasks in a PostgreSQL database without enabling it as an enterprise geodatabase. _ v ... View more

This is a really broad question. The "most efficient" method depends on the nature of the data, the way the data is managed, and the nature of updates, and your experience and skill in ETL solutions.   The source formats are somewhat trivial; it's what you need to do with them that matters. Some folks have no problems taking 10 minutes to update 100k rows via a Portal API, while others are updating 100 million rows every 8 hours.  Once you add in different ways to acquire data, well, there are too many options beyond generic advice like "As efficiently as possible." If you want specific advice, you'll need to provide more details on the process you are trying to optimize. - V ... View more

I have a copy of ArcInfo 5.0 FORTRAN executive library reference manuals, but I'm not giving those up (my wife can put them in my casket to get rid of them). - V ... View more

I built a minimal power draw high-end gaming-class system (65W AMD Ryzen 7 (8 core), 32Gb RAM, 2x500Gb M.2 disk), with a GEFORCE RTX 4060 graphics card for around $1K not too long ago.  It was originally planned with motherboard graphics, but that was too laggy for dual HD displays, and I was able to get the RTX on sale (it doubled the power draw during graphics benchmarking, with more than ten times the frames per second), but still well under the capability of the 80+ Platinum 650W power supply) . It runs fairly cool with just two (silent) case fans and the default fan-on-a-heat-sink CPU fan, and our teenager is jealous of mom's WFH desktop GPU. You can build a new system that meets your requirements or work with a build-to-suit vendor to do so, as long as you don't need to purchase new monitors, too. - V ... View more