0

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 column.

I can select the SHAPE column in an SQL client which returns a collection of attributes from the UDT:

select 
    shape 
from 
    a_test_fc

SHAPE                                                                           
--------------------------------------------------------------------------------
ST_GEOMETRY(4,37,671828.7852,4861707.1775,671839.2487,4861717.6411,NULL,NULL,NULL,NULL,0,32.9250330071201,26917,(BLOB))                                         

1 rows selected.

And I can see that the docs describe the UDT attributes as follows:

The attributes of the spatial type represent the following information:

  • Entity: The type of geometric feature stored in the spatial column (linestring, multilinestring, multipoint, multipolygon, point, or polygon), the value of which is a bit mask derived from the st_geom_util stored procedure.
  • Numpts: The number of points defining the geometry; for multipart geometries, this includes the separators between each part, one point for each separator.
  • Minx, miny, maxx, maxy: The spatial envelope of the geometry
  • Area: The area of the geometry
  • Len: The perimeter length of the geometry
  • SRID: Contains the identifier for the geometry that links it to its associated spatial reference (coordinate system) record in the ST_Spatial_References table
  • Points: Contains the byte stream of the point coordinates that define the geometry

Question:

Is there a way to select specific attributes from the ST_Geomtery UDT?

I'm aware that there are "accessor functions that take a single ST_Geometry as input and return the requested property value as a number." So there isn't a practical need to directly select individual attributes from the UDT — I could just use those accessor functions if required. I'd just like to determine if it's possible to interact directly with the UDT attributes as a way to learn more about spatial database development.

I tried researching non-GIS UDTs, and how to select their attributes (example: I tried selecting SHAPE.POINTS). But that just resulted in an error: ORA-00904: "SHAPE"."POINTS": invalid identifier. And yes, I'm aware that the POINTS attribute is stored as a byte stream and won't be useful in any practical sense.

0

Use Oracle's TREAT() function.

select 
    objectid,
    treat(shape as sde.st_geometry).numpts as num_points,
    treat(shape as sde.st_geometry).points as byte_stream
from 
    a_test_fc

enter image description here


Related:

How to SELECT from object type column in Oracle 11g?

What is the ST_Geometry storage type?

enter image description here

0

It turns out, all we need to do is use a table alias.

select 
    objectid,
    a.shape.numpts as num_points,
    a.shape.points as byte_stream
from 
    a_test_fc a

enter image description here


That technique also works for SDO_GEOMETRY methods:

select 
    a.shape.get_wkt()
from 
    table a

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.