Questions tagged [oracle-dbms]
Use for questions about Oracle DBMS that are not about its Spatial or Locator extensions
376
questions
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 ...
- 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 "...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 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:...
- 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....
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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,
...
- 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 ...
- 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() ...
- 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:
...
- 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 ...
- 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))), ...
- 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....
- 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 ...
- 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 -...
- 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?
...
- 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....
- 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 = &...
- 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 ...
- 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 ...
- 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.
- 31
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?
- 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 ...
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).
...
- 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 (...
- 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 (...
- 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 ...
- 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....
- 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)
...
- 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 ...
- 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 ...
- 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 ...
- 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
...
- 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(...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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, ...
- 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 ...
- 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 ...
- 14.6k
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 ...
- 61