Questions tagged [oracle-dbms]

Use for questions about Oracle DBMS that are not about its Spatial or Locator extensions

Filter by
Sorted by
Tagged with
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:...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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() ...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
  • 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))), ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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 -...
user avatar
  • 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? ...
user avatar
  • 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....
user avatar
  • 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 = &...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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.
user avatar
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?
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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). ...
user avatar
  • 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 (...
user avatar
  • 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 (...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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) ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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(...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 798

1
2 3 4 5
8