Latest Contributions by Bud

@DuncanHornby Thanks Duncan. That helps. Good call about cursors. I posted an idea just now for that: Update OOTB GP tools so that they use the new DA cursors. ... View more

It would also help if we could change the name of an attribute rule after it's been saved.   ... View more

5. I suppose I could convert #1 to a custom function. And then create a function-based spatial index on the function to precompute the SDO_GEOMETRY.  Downside: Getting Oracle and ArcGIS to actually use the index is tricky. Function-based spatial indexes - Tips. And as far as I can tell, function-based indexes aren't intended to be used this way. A pre-computed column in a table is more suitable.   6. Store the geometry as JSON in an invisible text field in the FC. Use a function-based spatial index to efficiently convert the JSON to SDO_GEOMETRY on the fly. - Oracle: Output LRS to a text-based format - SDO_UTIL.TO_JSON - SDO_UTIL.TO_JSON_VARCHAR - SDO_UTIL.TO_JSON_JSON Downside: I tested it on a FC with 15,000 features and it was slower than I'd hoped. I stored the JSON representation of the SDO_GEOMETRY in a text field in the FC. And then converted it to SDO_GEOMETRY on-the-fly in a query using sdo_util.from_json(). Unfortunately, it still took 4.5 seconds to run the query on all features. I was hoping it would be closer to 1 second, which is how long it takes to select a true pre-computed shape column from a table. alter table atn_json_text add json nclob invisible; --ArcGIS uses NCLOB for large text columns, so that's what I did too: https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/data-types-oracle.htm#:~:text=type%20will%20be-,NCLOB,-. update atn_json_text set json = sdo_util.to_json(wkt_lrs(sde.st_astext(shape),sde.st_srid(shape))); --wkt_lrs is a custom function: https://i.stack.imgur.com/dOfPg.png source: https://gis.stackexchange.com/a/428825/62572 commit; select sdo_util.from_json(json) from atn_json_text; I didn't try creating a function-based index. Reason: As far as I can tell, an FBI would only help me if I'm only selecting the geometry column, not any other columns (which would be rare). Whereas, if I select other columns too, such as an ID column (a much more common use case), then the FBI won't be used, which isn't what I want.  When you add columns not in the index, the db has to read the table after reading the index. And as in your case the optimizer thinks it will return all the rows, it is easier for the db to just read the table. Source. But I'm not an expert on FBIs or indexes. So I might have misunderstood something.   7. For what it's worth, I tested a similar solution, but using WKB. Unfortunately, it isn't much faster than the JSON option: update atn_blob set sdo_blob = sdo_util.to_wkbgeometry(sdo_cs.make_2d(sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING')))); commit; select sdo_util.from_wkbgeometry(sdo_blob) wkb_to_sdo from atn_blob Execution time: 3.5 to 5.5 seconds (varies) Even if I did want to use that option, I'd need to wait for a Oracle to fix a few issues: Idea: Support M-enabled WKB Idea: Support LINESTRING Z/ZM/M wkt syntax Convert 3d multi-part WKB to SDO_GEOMETRY Related: Convert M-enabled SDE.ST_GEOMETRY to SDO_GEOMETRY using SQL ... View more

I think the answer is “single/concurrent use”. ... View more

I’m trying to find a version of ArcGIS Pro where calculation attribute rules work properly when editing geometries with M-values —and— is supported by our version of License Manager. It seems like modern versions of Pro don’t work with our version of License Manager. So I’m trying to find version of Pro that fits. Thanks. ... View more

Assorted thoughts (although I use Oracle, not SQL Server): Occasionally, I've noticed strange behavior when selecting features from spatial views that are registered with the GDB. If I export the view as an FC, then the selection issues don't happen with that FC. The selection issues don't happen in non-registered views either. So, I try to avoid registering views with the GDB, unless it's actually needed for publishing a map service to Portal, etc. Registered view gets buggy when database link added [closed] Unexpected query layer results: Why does unioning with DUAL fix it? When using Oracle queries/views in ArcMap, I've found I need to cast the ObjectID to an integer: cast(objectid as number(38,0)) as objectid. Otherwise, ArcMap sometimes doesn't recognize the ObjectID as a valid unique identifier. I'm not sure if something similar is needed in SQL Server or not. For cases where we don't have an ObjectID, using Oracle's ROWNUM column (a pseudocolum) seems to work pretty well too: cast(rownum as number(38,0)) as rownum_. Note: the column can't be called "rownum"; it needs to be called something else like "rownum_". Some people say using ROWNUM as the unique identifier will produce unexpected results. But I haven't found it to be a problem. The key thing is: the unique identifier definitely needs to be unique. Otherwise, unexpected results do occur -- especially in the attribute table. Does SQL Server have an equivalent column to Oracle's ROWNUM pseudocolumn? Unique identifier fields Integer representation of textual value (as unique ID for query layer) For what it's worth, I noticed that the SDE.ST_GEOMETRY datatype was particularly slow when used in some spatial views (data source: dblink/an external database). I found that SDO_GEOMEMTRY in a spatial view, or simply using an XY Event Layer (based on numeric XY columns), was much faster than SDE.ST_GEOMETRY. I talk about it here: Are XY event layers optimized/indexed on-the-fly? I don't have any experience with indexed views in SQL server. But I believe Oracle's similar functionality, materialized views, does work in ArcMap. Although materialized views are essentially tables, they're not just an index, so I suppose the two concepts aren't really the same/can't be directly compared. Note: Registering materialized views with the GDB is a bad idea -- I think it converts the materialized view to a regular table, which breaks the sync. I don't know if the same thing applies to indexed views in SQL Server or not. Materialized view breaks when registered with the GDB SDO_GEOM materialized view: Why is static field slower than dynamic field? (answer: spatial index was missing) Materialized view with FAST refresh on remote table: How to include a SHAPE column? Question: Do indexed views in SQL Server require a spatial index for fast performance? Is that even possible, since I assume an indexed view is still just a regular view? Have you considered converting the non-spatial table to a feature class, and then automatically populating the geometry using calculation attribute rules or a db trigger? Or if that won't work, could you create a parallel/helper FC that has a geometry, and populate that FC using attribute rules or db triggers? I know this post is old. And a lot of my points pertain to Oracle, not SQL Server. But I thought I'd share my thoughts in case they're helpful to anyone or trigger some ideas... ... View more

We also need to be able to remove the Z dimension from ST_GEOMETRY. Related post here:  I need to remove Z dimension on spatial column (ST_GEOMETRY) with Esri on Oracle using the PL/SQL packages ... View more

This post is quite old. I wonder if anything has changed since then? Any enhancements added to ArcGIS, etc.? ... View more

Yeah, that’s what I meant. Hopefully it works. ... View more