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
0 votes
0 answers
39 views

GeoServer and Oracle Spatial synonym WFS-T Layer

I have two schemas (A,B) in Oracle (19c) with one table in schema A where I want to write with WFS-T from the GeoServer. The GeoServer is using the version 2.21.4 and corresponding OCI Driver. The ...
Frank's user avatar
  • 1
0 votes
0 answers
22 views

Revoke permissions to data granted to user SDE gives "Invalid use of GRANT/REVOKE function" error

I inherited an Oracle geodatabase where user SDE was granted indiscriminately to data in other schemas. I would like to revoke the privileges, but for some feature classes/datasets I am getting "...
mfrancis's user avatar
  • 115
0 votes
2 answers
57 views

List of numeric default values using SQL

Oracle 18c 10.7.1 EGDB: I have fields in feature classes and tables that have numeric default values. How can I get a list of the default values using SQL? I believe the default values are stored in ...
User1974's user avatar
  • 1,114
1 vote
2 answers
253 views

Recover\rebuild the .dbf and .shx of a shapefile [closed]

Do you know if there is any way to recover\rebuild the .dbf and .shx files from the shapefile? I think my case is a little more nebulous. My .shp files were extracted from a CSV file... That the data ...
Silva's user avatar
  • 11
0 votes
0 answers
81 views

ArcGIS Oracle bulk update using spatial join

I am trying make bulk update the table using a spatial join which has millions of records, updating the ID from Parcels layer to the Park (Points) layer using Oracle SQL query, I used geometry ...
Haris GIS's user avatar
1 vote
1 answer
231 views

For each domain of a subtype field, select the domain data and subtype data (using SQL)

Oracle 18c 10.7.1 EGDB For a given table/subtype: For each domain of a subtype field, select the domain data and the subtype data: Domain code Domain description Domain name Subtype code Subtype ...
User1974's user avatar
  • 1,114
1 vote
1 answer
304 views

Extract SDO_GEOMETRY line elements using index position / dot notation

SDO_GEOMETRY; Oracle 18c: Is there a way to extract elements from an SDO_GEOMETRY line using an index position / dot-notation in a SQL query? For example, extract the X coordinate of the second vertex ...
User1974's user avatar
  • 1,114
1 vote
1 answer
395 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:...
User1974's user avatar
  • 1,114
0 votes
1 answer
53 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....
User1974's user avatar
  • 1,114
0 votes
1 answer
154 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 ...
User1974's user avatar
  • 1,114
2 votes
1 answer
369 views

Select subtype and a subtype field's 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 ...
User1974's user avatar
  • 1,114
1 vote
1 answer
120 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 ...
User1974's user avatar
  • 1,114
0 votes
0 answers
49 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 ...
User1974's user avatar
  • 1,114
1 vote
0 answers
185 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, ...
User1974's user avatar
  • 1,114
0 votes
1 answer
128 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 ...
User1974's user avatar
  • 1,114
2 votes
2 answers
199 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() ...
User1974's user avatar
  • 1,114
1 vote
1 answer
461 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: ...
User1974's user avatar
  • 1,114
0 votes
1 answer
56 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 ...
User1974's user avatar
  • 1,114
2 votes
1 answer
200 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))), ...
User1974's user avatar
  • 1,114
0 votes
1 answer
516 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....
User1974's user avatar
  • 1,114
0 votes
1 answer
89 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 ...
User1974's user avatar
  • 1,114
0 votes
4 answers
724 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 -...
User1974's user avatar
  • 1,114
0 votes
1 answer
268 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? ...
User1974's user avatar
  • 1,114
0 votes
1 answer
63 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....
User1974's user avatar
  • 1,114
3 votes
1 answer
216 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 = &...
User1974's user avatar
  • 1,114
0 votes
0 answers
94 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 ...
User1974's user avatar
  • 1,114
1 vote
1 answer
323 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 ...
User1974's user avatar
  • 1,114
1 vote
0 answers
40 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.
Uğurcan GÜL's user avatar
0 votes
1 answer
55 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?
zakariah1's user avatar
  • 183
0 votes
1 answer
213 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 ...
user194826's user avatar
1 vote
1 answer
302 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). ...
User1974's user avatar
  • 1,114
1 vote
3 answers
271 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 (...
User1974's user avatar
  • 1,114
1 vote
1 answer
397 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 (...
User1974's user avatar
  • 1,114
0 votes
0 answers
49 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 ...
MoxGoat's user avatar
  • 13
1 vote
2 answers
128 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....
User1974's user avatar
  • 1,114
2 votes
1 answer
404 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) ...
User1974's user avatar
  • 1,114
0 votes
2 answers
347 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 ...
User1974's user avatar
  • 1,114
2 votes
1 answer
88 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 ...
User1974's user avatar
  • 1,114
0 votes
1 answer
39 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 ...
User1974's user avatar
  • 1,114
2 votes
1 answer
326 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 ...
User1974's user avatar
  • 1,114
1 vote
1 answer
74 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(...
User1974's user avatar
  • 1,114
1 vote
2 answers
97 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 ...
User1974's user avatar
  • 1,114
2 votes
1 answer
83 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 ...
User1974's user avatar
  • 1,114
0 votes
1 answer
185 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 ...
User1974's user avatar
  • 1,114
1 vote
1 answer
470 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 ...
User1974's user avatar
  • 1,114
0 votes
1 answer
95 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 ...
User1974's user avatar
  • 1,114
2 votes
1 answer
292 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, ...
User1974's user avatar
  • 1,114
3 votes
5 answers
438 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 ...
User1974's user avatar
  • 1,114
3 votes
1 answer
366 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 ...
Jochen Schwarze's user avatar
3 votes
1 answer
87 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 ...
user3481432's user avatar

1
2 3 4 5
8