|
POST
|
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
05-09-2022
10:28 AM
|
0
|
0
|
2731
|
|
POST
|
I have a db view in Oracle 18c (EGDB; ST_GEOMETRY): create or replace view startpoint_vw as ( select objectid, treat(sde.st_startpoint(shape) as sde.st_geometry) as shape --https://gis.stackexchange.com/a/385767/62572 --https://support.esri.com/en/Technical-Article/000011333 from my_owner.sidewalks --14,524 features where sde.st_numgeometries(shape) = 1 --The SQL will fail if there are any multi-part features. Temporary workaround: exclude multi-part features from this view. ) Using Catalog in ArcGIS Pro 2.6.8, I want to right-click --> copy/paste that view to a FGDB (the view gets pasted as a FC). Reason: Quickly take a snapshot of the data for geoprocessing purposes, publishing to AGOL as a FC, etc. Problem: When I paste the view into the FGDB, ArcGIS Pro crashes about 50% of the time. When I re-open Pro after the crash, a FC has been created, but not all of the features were inserted. The number of features that were inserted is usually a multiple of 1000 (i.e. 2000, 4000, 8000, etc.). For the other 50% of the time, the view will paste as a FC without issue (all features successfully created). I have the same problem when I paste as a FC into SDE (the same environment as the source environment). ArcGIS Pro crashes. Has anyone had that problem when pasting views? Any ideas?
... View more
05-09-2022
10:17 AM
|
0
|
3
|
2736
|
|
IDEA
|
ST_GEOMETRY; Oracle: Could ESRI create a function to convert directly from the ST_POINT subtype to the ST_GEOMETRY supertype? That would solve these issues: Bug: Unable to define a query layer in ArcGIS where the data source uses an st_geometry subtype in Oracle Get ArcGIS to recognize ST_POINT Use TREAT() function to convert UDT subtype to supertype I'm aware that there is a quick-and-dirty workaround, where we can convert from subtype to text to supertype (or use well-known binary instead of text). But that workaround is ugly and slow. It would be better to have a more robust & performant solution.
... View more
05-05-2022
09:07 PM
|
0
|
1
|
961
|
|
IDEA
|
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
05-04-2022
07:25 PM
|
0
|
4
|
3373
|
|
POST
|
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
05-01-2022
07:26 AM
|
0
|
0
|
1662
|
|
POST
|
I'm wondering if there's anyone out there who has Oracle 21c and SDE.ST_GEOMETRY who could help me with a quick test: If you were to run this query in Oracle 21c, what would it do? select json_object(sde.st_geometry('MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))', 26917)) as sdo_geom from dual Background: I suspect that some new functionality has been added to the JSON_OBJECT() function in Oracle 21c. I have 18c, not 21c, so I'm not able to test it myself. For example, the following SDO_GEOMETRY query works in 21c in an online environment called db<>fiddle. Of course, that environment doesn't have the SDE.ST_GEOMETRY datatype, so I can't do the ST_GEOMETRY test there. select json_object(sdo_geometry('MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))')) as sdo_geom from dual Output: {"SDO_GTYPE":2006,"SDO_SRID":null,"SDO_POINT":{},"SDO_ELEM_INFO":[1,2,1,7,2,1],"SDO_ORDINATES":[0,5,10,10,30,0,50,10,60,10]} Related: Select JSON text of SDO_GEOMETRY using SQL I'm curious what would happen if we did something similar in Oracle 21c, but with SDE.ST_GEOMETRY, instead of SDO_GEOMETRY. Thanks!
... View more
04-30-2022
11:28 PM
|
0
|
1
|
1708
|
|
POST
|
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
04-30-2022
08:02 PM
|
0
|
0
|
5194
|
|
POST
|
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
04-30-2022
09:50 AM
|
0
|
2
|
5200
|
|
IDEA
|
(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
04-25-2022
09:26 AM
|
3
|
0
|
2317
|
|
IDEA
|
@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
04-25-2022
09:18 AM
|
0
|
0
|
1370
|
|
IDEA
|
It would also help if we could change the name of an attribute rule after it's been saved.
... View more
04-25-2022
08:43 AM
|
0
|
0
|
1988
|
|
POST
|
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
04-23-2022
01:45 PM
|
0
|
0
|
5300
|
|
POST
|
I have SDE.ST_GEOMETRY polyline FCs (tables) in an Oracle 18c geodatabase. I'm building various SQL queries on the tables. It would help if I could use Oracle Spatial/SDO_GEOMETRY functionality like Spatial's linear referencing functions. Unfortunately, it's not possible for me to change the datatype in the table from SDE.ST_GEOMETRY to MDSYS.SDO_GEOMETRY, due to existing dependencies on the ST_GEOMETRY shape column. Also, it seems like ArcGIS only supports a singe geometry column per table. So it's not like I can just add a SDO_GEOMETRY column to the table, without risking problems in ArcGIS. Alternatively, what are my options for generating a separate SDO_GEOMETRY column so that I can use Oracle Spatial functions? For example: Convert from ST_GEOMETRY to SDO_GEOMETRY in a query on-the-fly. Downside: the query is complicated/fragile/slow. I don't want to make the conversions every time the queries are used. Add an invisible SDO_GEOMETRY column to the table. Update the column with a db trigger. Add a spatial index. Downside: Invisible SDO_GEOMETRY columns aren't supported in Oracle. Also: Idea: Support invisible SDO_GEOMETRY columns. Create a parallel table that has a common ID column and a SDO_GEOMETRY column. Create a trigger on the ST_GEOMETRY table that updates the parallel table after INSERT, UPDATE, or DELETE. Or use calculation attribute rules in ArcGIS Pro to do something similar. Downside: Adds extra tables/complexity that need to be managed. Joining from the ST_GEOMETRY table to the parallel SDO_GEOMETRY table might be slow for large datasets. Parallel materialized view with an SDO_GEOMETRY column. Downside: A different department would need to create and manage the the materialized view, since GDB data creators/owners don't have CREATE MATERIALIZED VIEW privileges. (It's always a pain when you can't manage your own stuff.) Does anyone have any thoughts on those options? Or are there any other options that I've overlooked? Related: Choose spatial column when adding data with more then one spatial column
... View more
04-23-2022
01:43 PM
|
0
|
6
|
5307
|
|
POST
|
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
04-22-2022
08:02 AM
|
0
|
1
|
3072
|
| Title | Kudos | Posted |
|---|---|---|
| 1 | yesterday | |
| 1 | 03-20-2023 10:24 AM | |
| 1 | 04-30-2026 11:08 AM | |
| 1 | 05-11-2026 11:23 AM | |
| 2 | 05-07-2026 04:19 AM |