Latest Contributions by Bud

I wondered if the SHAPE column was the problem, since ArcGIS Pro and ArcMap seem to be bad at handling the ST_POINT subtype (even when the TREAT() function is used). ArcGIS Pro and ArcMap seem to do a lot better when the datatype is the ST_GEOMETRY supertype. So, instead of using the TREAT() function to convert from the ST_POINT subtype to the ST_GEOMETRY supertype, I used a low-tech method: I converted from ST_POINT to binary to ST_GEOMETRY. Alternatively, converting to text instead of binary would have been an option too. create or replace view startpoint_vw as ( select objectid, sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(shape)),26917) as shape from my_owner.sidewalks --14,524 features where sde.st_numgeometries(shape) = 1 ) But unfortunately, ArcGIS Pro is still crashing. So I'm not sure what the problem is. I have the same problem when building the shape from the X & Y coordinates too: sde.st_geometry ('point (' || sde.st_x(sde.st_startpoint(shape)) || ' ' || sde.st_y(sde.st_startpoint(shape)) || ')', 26917) as shape I also tried limiting the query to a small subset of the rows, in case there was a problematic feature somewhere, but that didn't help either. Pro still crashes. create or replace view startpoint_vw as ( select objectid, sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(shape)),26917) as shape from my_owner.sidewalks --14,524 features where sde.st_numgeometries(shape) = 1 and rownum <= 1000 ) I’m all out of ideas. And quite frustrated.   ... View more

It would be handy if we could store additional information/columns in GDB domains. So that we could use that additional data in SQL queries, reports, labels etc. CODE [domain coded value] NAME [domain description] Additional field: DETAILS   ... View more

I’m aware that ArcGIS Server can return JSON in a feature service from a feature class. I still want to experiment with JSON and SQL. ... View more

Yeah. That would be nice. My organization chose ST_GEOMETRY years ago...and I think it was a perfectly reasonable decision at that time.   - ST_GEOMETRY was recommended by Esri.  - Oracle Spatial wasn’t free then.  - ST_GEOMETRY is easier for non-SQL experts to use than SDO_GEOMETRY (ST_GEOMETRY is simpler and the docs are easier to parse). Even if that wasn’t the case, it would take some effort to switch all the FCs over to a new spatial type (and would likely have unforeseen complications). So I think I’ll be stuck with ST_GEOMETRY for the foreseeable future. Despite the fact that we will be upgrading our GIS system soon (in theory, that’d be the time to switch). So all that considered, I think my only option is to create “helper” SDO_GEOMETRY columns for a few polyline FCs…so that I can do my linear referencing SQL work.  But yes, for organizations that are starting from scratch, or companies that are project-based, or for organizations who are switching database vendors …then I would agree that going with SDO_GEOMETRY would be the right choice. Related: What spatial type does Esri recommend for Oracle EGDBs? ... View more

Yeah, good point. We have existing dependencies on the ST_GEOMETRY shapes. For example, we have spatial queries between the FCs in question and other FCs…using ST_GEOMETRY spatial operators/functions. I don’t think we could easily do spatial queries between the two different data types. So I’m reluctant to switch some of the FCs from ST_GEOMETRY to SDO_GEOMETRY. ... View more

(Migrated from a separate idea.) It looks like there are OOTB Esri geoprocessing tools in ArcGIS Pro that use the old/slow cursors. Example: Points To Line GP tool If we look at the code for those tools, we can see that they are outdated. They use the the old/slow cursors instead of the new/fast cursors in the DA module. Could Esri update all GP tools so that they use the new DA cursors/module? ... View more

@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