Questions tagged [oracle-dbms]
Use for questions about Oracle DBMS that are not about its Spatial or Locator extensions
375
questions
1
vote
1
answer
10
views
Select domain codes/descriptions using XMLTABLE instead of EXTRACTVALUE
Oracle 18c 10.7.1 GDB:
The ArcGIS system table docs have an example for extracting domain codes and descriptions from XML:
Example: Resolving domain codes to description values using SQL
--simplified:...
- 798
0
votes
0
answers
7
views
Where is the SDE.SDEXMLTOTEXT() function located?
Oracle 18c 10.7.1 EGDB:
In SDE.GDB_ITEMS_VW, there is a function being used called SDE.SDEXMLTOTEXT():
sde.sdexmltotext(d1.xml_doc) AS definition,
sde.sdexmltotext(d2.xml_doc) AS documentation,
sde....
- 798
0
votes
1
answer
15
views
Find problem rows in GDB_ITEMS_VW
Oracle 18c 10.7.1 EGDB:
Oracle GDBs have a view called GDB_ITEMS_VW that has a DEFINITION column with extracted XML values (clob).
I can extract data, such as coded value domains, from the XML column ...
- 798
2
votes
1
answer
32
views
Select subtype and a subtype field/domain names using SQL
I have a non-spatial table with a subtype in an Oracle 18c 10.7.1 EGDB.
Using SQL, I want to select the following as columns in a query:
Subtype Code
Subtype Description
A specific subtype field ...
- 798
1
vote
1
answer
52
views
How many vertices does a SDE.ST_GEOMETRY rectangle have?
SDE.ST_GEOMETRY; Oracle 18c; 10.7.1 EGDB
How many vertices does a SDE.ST_GEOMETRY rectangle have?
(Drawn using the rectangle construction tool in the Create Features pane in ArcMap 10.7.1.)
The ...
- 798
0
votes
0
answers
22
views
Construct ST_GEOMETRY rectangle from two vertices: lower-left and upper-right
Oracle 18c SDE.ST_GEOMETRY 10.7.1 EGDB:
I have two vertices of a rectangle:
Lower-left: 10,10
Upper-right: 100,100
I want to construct a rectangle polygon from those two vertices using ST_GEOMETRY ...
- 798
1
vote
0
answers
95
views
Get map to use function-based spatial index (ST_GEOMETRY)
SDE.ST_GEOMETRY — Oracle 18c — ArcMap/EGDB 10.7.1:
I have a view that returns the startpoint of lines as an ST_GEOMETRY column:
create or replace view bc_atn_startpoint_vw as (
select
objectid,
...
- 798
0
votes
1
answer
33
views
Equivalent to USER_SDO_GEOM_METADATA for SDE.ST_GEOMETRY
Background - SDO_GEOMETRY:
If I were to create a function-based spatial index that returns an SDO_GEOMETRY, I would manually insert records into SDO_GEOMETRY's USER_SDO_GEOM_METADATA for these ...
- 798
0
votes
2
answers
55
views
How to request GeoJSON standard be enhanced to support M-values
As far as I can tell, the GeoJSON standard doesn't support the M-dimension of linear referencing lines.
For example, Oracle Spatial doesn't support LRS lines when using the SDO_UTIL.TO_GEOJSON() ...
- 798
0
votes
1
answer
42
views
LRS SDO_GEOMETRY to "LINESTRING M" WKT format
I have a query that produces an LRS SDO_GEOMETRY as WKT (Oracle 18c):
select
SDO_UTIL.TO_WKTGEOMETRY(SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_geometry('LINESTRING(1 2,3 4)')))
from
dual
Output:
...
- 798
0
votes
1
answer
15
views
Polyline endpoint query layer in ArcGIS Pro
I have an Oracle 18c/SDE.ST_GEOMETRY polyline FC (15,000 features).
I want to create a query layer (or db view) that will display the startpoints and endpoints of the lines in ArcGIS Pro.
Is there a ...
- 798
0
votes
1
answer
71
views
Improve performance of startpoint query (ST_GEOMETRY)
I have a SDE.ST_GEOMETRY query in an Oracle 18c EGDB that gets the startpoint of polylines:
select
objectid,
sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))), ...
- 798
0
votes
1
answer
39
views
Select SDE.ST_GEOMETRY as JSON text (using SQL)
I have a multipart M-enabled SDE.ST_GEOMETRY polyline FC (in an Oracle 18c EGDB):
Using SQL, I want to select the geometry as JSON text:
{"hasM":true,"paths":[[[0,5,0],[10,10,11....
- 798
0
votes
1
answer
16
views
Integer representation of textual value (as unique ID for query layer)
I have an Oracle 18c non-spatial table that has textual IDs as the unique ID:
The table doesn't have a ObjectID or numeric ID. I'm not able to add new columns to the table.
The table isn't registered ...
- 798
0
votes
4
answers
156
views
Convert M-enabled SDE.ST_GEOMETRY to SDO_GEOMETRY using SQL
I have an M-enabled SDE.ST_GEOMETRY polyline FC in an Oracle 18c EGDB.
select sde.st_astext(shape) as st_to_wkt from test_st_geom
MULTILINESTRING M (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -...
- 798
0
votes
1
answer
38
views
How does SDE.ST_LENGTH() get calculated?
I have an Oracle 18c SDE.ST_GEOMETRY polyline FC. The lines in the FC have true curves.
If I query for the length of the lines in SQL using SDE.ST_LENGTH(SHAPE), how does the length get calculated?
...
- 798
0
votes
1
answer
51
views
Select feature parts and vertices as rows
I have a Oracle 18c SDE.ST_GEOMETRY FC that has multi-part polylines.
I want to select the parts and vertices as individual rows in a query:
Since we don't have a GetVertices() function in SDE....
- 798
3
votes
1
answer
91
views
Editing vertex without removing true curves using ArcPy
ArcMap 10.7.1 — Oracle 18c SDE.ST_GEOMETRY
I have a polyline FC that has true curves.
And I have an ArcPy script that updates the M-values of the vertices in the lines:
import arcpy
connection = &...
- 798
0
votes
0
answers
28
views
What column is true curve data stored in? (SDE.ST_GEOMETRY for Oracle)
I have an Oracle 18c SDE.ST_GEOMETRY polyline FC:
The lines in the FC have true curves (aka CAD BLOBs).
Question: What column is the true curves data stored in?
@Vince mentioned in a comment that ...
- 798
0
votes
0
answers
32
views
Update vertex via SQL — without reconstructing shape
ArcGIS 10.7.1 — Oracle 18c GDB — SDE.ST_GEOMETRY
I have a multipart polyline in an SDE.ST_GEOMETRY FC. I want to update the coordinates of a specific vertex with a known value — via SQL.
I believe it ...
- 798
1
vote
1
answer
102
views
Determine if a SDO_GEOMETRY line has true curves
Oracle 18c
I have an SDO_GEOMETRY table that I created via ArcGIS Desktop 10.7.1.
The table has a line that appears to have a true curve in it:
However, in a related question, @Vince pointed out that ...
- 798
0
votes
0
answers
25
views
Preserve arcs/curves when converting SDE.ST_GEOMETRY to SDO_GEOMETRY via SQL
ArcGIS 10.7.1; Oracle 18c GDB
I have an SDE.ST_GEOMETRY polyline FC that has lines with arcs:
In an SQL query, I want to convert the ST_GEOMETRY shape to SDO_GEOMETRY.
I can do the conversion by ...
- 798
0
votes
0
answers
15
views
SDE.ST_GEOMETRY function returns negative infinity
ArcMap & Enterprise 10.7.1 — Oracle 18c SDE.ST_GEOMETRY — SQL Developer 18.1.0.095
I have an SDE.ST_GEOMETRY polyline FC that is M-enabled:
I can query for the M-value of the endpoint of the ...
- 798
0
votes
0
answers
26
views
Use SDE.ST_GEOMETRY functions in Select by Attributes window
ArcGIS Desktop & Enterprise 10.7.1 — Oracle 18c — Polyline FC registered as versioned (with base).
Background:
I want to use Oracle SDE.ST_GEOMETRY functions in the Select by Attributes window in ...
- 798
1
vote
0
answers
18
views
Enable Archiving and Enable Editor Tracking on Oracle SDO
Are enable archiving and enable editor tracking allowed in Oracle database system (SDO)? Since I usually use PostgreSQL, I don't know the features supported by the Oracle database.
- 31
0
votes
1
answer
38
views
GeoServer using read only database
Can GeoServer use an Oracle database that is in a read-only state or does it need the ability to generate its own indexes if existing databases do not have the necessary indexes already generated?
- 133
0
votes
0
answers
13
views
Oracle query to Update parcels street column with nearest street line using Esri ST_GEOMETRY
I want to update the parcels street column with nearest street layer, from the below image P1 street column should update with L1 and P2 street column update with L2. Using ST_Distance and I got the ...
0
votes
1
answer
84
views
SearchCursor RuntimeError: Underlying DBMS error [ORA-00923: FROM keyword not found where expected]
I need load OBJECTID from remote DB but I'm still getting error:
RuntimeError: Underlying DBMS error [ORA-00923: FROM keyword not found where expected] [SDEDO.E_EL_TU][STATE_ID = 22362]
I don't know ...
1
vote
1
answer
95
views
Query for ST_Geometry line vertices via a recursive SQL WITH clause?
I'm exploring ways to get ST_Geometry line vertices as individual rows in a query resultset (via Oracle SQL).
The reason being, ST_Geometry doesn't have a GetVertices function (see ESRI idea here).
...
- 798
1
vote
3
answers
114
views
ST_PointN equivalent in SDO_GEOMETRY?
In SDE.ST_GEOMETRY, we have a function called ST_PointN:
ST_PointN takes an ST_LineString and an integer index and returns a
point that is the nth vertex in the ST_LineString's path.
sde.st_pointn (...
- 798
1
vote
1
answer
86
views
Scheduled/emailed reports using ArcGIS Notebook Server?
Is it possible to send scheduled/emailed reports via ArcGIS Notebook Server scheduled tasks?
An example of a scheduled report would be:
The report/task runs weekly
Emailed to a list of recipients (...
- 798
0
votes
0
answers
21
views
Editing featureclass in Oracle SDO_GEOMETRY
I'm building an empty featureclass in Oracle and want to enable editing in arcmap/py with having the featureclass user SDO_GEOMETRY for the shape column. However, I'm running into difficulties on how ...
- 13
1
vote
2
answers
93
views
Update sister table with vertices as rows (when SHAPE is updated)
I have an Oracle 18c table that has an SDO_GEOEMTRY column.
create table a_test_sdo_geom (id number, shape mdsys.sdo_geometry);
insert into a_test_sdo_geom (id, shape)
values(1, sdo_util....
- 798
2
votes
1
answer
168
views
Select vertices, including multi-part number
I have an Oracle 18c table with an SDO_GEOEMTRY column:
create table a_test_sdo_geom (shape mdsys.sdo_geometry);
The table has a row that is a multi-part feature.
insert into a_test_sdo_geom (shape)
...
- 798
0
votes
2
answers
111
views
Select specific attribute from SDE.ST_Geometry UDT? (without using accessor functions)
Oracle 18c; ArcGIS 10.7.1
I have an SDE.ST_Geometry polyline feature class.
As an experiment/learning exercise, I am trying to learn how to interact directly with the user-defined type (UDT) SHAPE ...
- 798
2
votes
1
answer
44
views
What densification method does SDE.ST_Geometry use to approximate arcs as segments?
Oracle 18c; ArcGIS 10.7.1
I have an SDE.ST_Geometry polyline FC that has arcs.
In a related post, we established that true arcs/curves are only available to ArcObjects, whereas the actual geometry ...
- 798
0
votes
1
answer
20
views
Multi-part ST_GEOMETRY: Does the feature-part array start at 1 or 0?
Oracle 18c; ArcGIS 10.7.1
I have an SDE.ST_GEOMETRY polyline FC that has multi-part features.
Question:
What method does ArcGIS use to assign numbers to the feature-parts in the array?
Do the part ...
- 798
2
votes
1
answer
150
views
Convert ST_POINT subtype to ST_GEOMETRY supertype?
Oracle 18c; ArcGIS 10.7.1
I have a query that produces an ST_POINT column:
--Mimics the SDO_GEOMETRY get_vertices function
--https://docs.oracle.com/database/121/SPATL/sdo_util-getvertices.htm
...
- 798
1
vote
1
answer
52
views
Why does SDE.ST_NUMPOINTS() add extra vertices for lines with arcs?
Oracle 18c/10.7.1 EGDB:
I have a polyline FC with a feature that has an arc.
I want to count the number of vertices in the line using the SDE.ST_NUMPOINTS() function.
select
sde.st_numpoints(...
- 798
1
vote
2
answers
61
views
Does SDE.ST_LENGTH() calculate the true length of arcs?
Oracle 18c/10.7.1 EGDB
I have a polyline FC that has features with arcs.
Does the Oracle SDE.ST_LENGTH() function calculate the true length of arcs?
Or does it only calculate the straight-line length ...
- 798
1
vote
1
answer
44
views
Select SDE.ST_GEOMETRY polylines that have arcs?
Oracle 18c/10.7.1 EGDB:
I have an SDE.ST_GEOMETRY polyline feature class.
Some of the polylines in the feature class have arcs.
Question:
Is there a way to use Oracle SDE.ST_GEOEMTRY functions to ...
- 798
0
votes
1
answer
85
views
ST_GEOMETRY: spatial column must appear immediately after spatial relationship function for spatial index to be used
The docs for SDE.ST_GEOMETRY have a blurb that says the following:
When are spatial indexes used with ST_Geometry?
...the spatial column must appear immediately after the
spatial relationship ...
- 798
1
vote
1
answer
177
views
ST_GEOMETRY points: get attribute of intersecting polygon
I have an SDE.ST_GEOEMTRY point feature class.
I want to create an SQL query from the points. The query would perform a spatial join/intersection with an SDE.ST_GEOMETRY polygon feature class. An ...
- 798
0
votes
1
answer
63
views
What system tables get INSERTS when registering object with Oracle EGDB (ST_Geometry)?
In ArcGIS Desktop, I can register ST_Geometry objects with the geodatabase (Enterprise 10.7.1; Oracle 18c):
Register with Geodatabase
Register feature classes, tables, views, and raster layers that ...
- 798
2
votes
1
answer
139
views
Function-based spatial index: Use sdo_geometry() constructor instead of custom function?
I have an Oracle 18c table that has XY columns, but not a SHAPE column. I've created a query on the table that dynamically generates an sdo_geometry SHAPE column.
sdo_geometry(2001, 26917, ...
- 798
3
votes
5
answers
286
views
Materialized view with FAST refresh on remote table: How to include a SHAPE column?
I want to create an Oracle materialized view with the fast refresh option on a remote table (in an enterprise GDB).
I can do this successfully without a SHAPE column:
create materialized view log on ...
- 798
3
votes
1
answer
188
views
QGIS access Oracle non-spatial database tables
I stumbled accross this when I tried to connect to a "normal" Oracle 11g instance (no Oracle Spatial) in order to join Oracle tables to spatial data in other formats. Note that this is not ...
- 13.8k
3
votes
1
answer
55
views
Use multiprocessing to speed up rebuild index in ArcPy
We are in ArcGIS 10.6.1 (on Windows Server 2012) and Enterprise GeoDatabase Oracle 19c (on Linux).
I was wondering if it is feasible to use the Python's ProcessPoolExecutor to open two separate Oracle ...
- 61
0
votes
1
answer
96
views
SDO_GEOM materialized view: Why is static field slower than dynamic field?
EGDB: Oracle 18c; ArcGIS 10.7.1
External system: Oracle 12c
I have a materialized view in an EGDB that syncs data from an external system (on a schedule via a dblink).
The materialized view has an ...
- 798
0
votes
0
answers
97
views
Materialized view breaks when registered with the GDB
EGDB: Oracle 18c/10.7.1
External system: Oracle 12c
I have created a non-spatial materialized view in an EGDB that syncs data from an external system (on a 5 second schedule; via a dblink).
It is ...
- 798