4

How is the st_length function stored in an Oracle geodatabase? Is it in a:

  1. PL/SQL function
  2. Separate .dll library
  3. Somewhere else?

I've noticed that the sde.st_length function works, even when the st_shapelib.dll library is not configured. I can call sde.st_length without issue: SELECT SDE.ST_LENGTH(SHAPE) FROM ROADS_SAMPLE. So clearly, st_length is not stored in the st_shapelib.dll library.

Knowing this, my next guess would have been that the st_Length function would be stored in PL/SQL in the database somewhere. However, when I look in the Functions folder in the database (using SQL Developer), I don't see any sign of the st_length function.

Update:

I've found two functions in the database that pertain to st_length:

ST_LENGTH_F:

Located: Packages.ST_GEOMETRY_OPERATORS Body.st_length_f

Function st_length_f(prim SDE.st_geometry)
Return number
IS
Begin

  If prim IS NULL OR (prim.numpts = 0 and prim.len = 0) THEN
    Return NULL;
  End If;

  Return(prim.len);

End st_length_f;

ST_LENGTH:

Located: Types.ST_GEOMETRY.ST_GEOMETRY Body.st_length

member Function st_length Return number IS
  Begin
    Return len;
   End st_length; 

But I'm not clear on which of these two functions is the one in question. The more obvious choice is st_length_f, but it's got a different name than st_length, so I can't imagine that it is the function that is being called.

  • I am a bit uncertain what you are looking for. The folks at Esri who coded the SDE.ST_GEOMETRY libraries did part of the task in PLSQL and part of the task in the C libraries. So the former items will always work and the latter items will fail unless the listener is setup properly. You want to know how this was determined? Unless the Esri authors chime in with their reminisces about all we can do is guess. As nef001 is driving towards, the geometry object itself (and subtypes) and the spatial index are all just Oracle objects. So I think when it made sense they coded functions in PLSQL. – pauldzy Nov 7 '17 at 21:07
  • But when they need to unpack the BLOB storage and roll through the vertices then I guess they decided to do that in C. Probably too they were recycling code from earlier efforts - that's just a guess. So some items are in C and need the library and some aint. I am not sure what kind of answer would solve the question short of Esri folks showing us their notes from a decade ago. – pauldzy Nov 7 '17 at 21:08
  • @pauldzy Apologies for the unclear question. I've modified it to read "How is the st_length function stored in an Oracle geodatabase?". I'm trying to understand where the st_length function is located and how it works. – Wilson Nov 7 '17 at 21:58
  • 1
    Ok, I mean you do know that SDE is proprietary software that as users we all agreed via the user license not to reverse engineer.... :) This might be a question you really need to raise via a support ticket with Esri. They can agree or not agree to give you insight as to how it all works. Not trying to be flippant, but why do you want to know? – pauldzy Nov 7 '17 at 22:13
  • @pauldzy Well, in short, understanding how the machinery works will help me operate it to the best of my ability. Otherwise, I just have to throw my hands up and say "it all seems so random". And I hate random. Like, really, really hate things that seem random, because I know that computers are almost always not random. For instance, I'm trying to solve an issue here that is baffling me. Understanding how the machinery works will almost certainly help (one way or another). – Wilson Nov 7 '17 at 22:24
1
+100

SDE.ST_LENGTH is a custom operator and you can spot it in SqlDeveloper under the "operators" category for the SDE schema. Now my version of SqlDeveloper doesn't seem to nicely list the binding. That may be a bug in SqlDeveloper.

But you can query

SELECT * FROM dba_opbindings WHERE operator_name = 'ST_LENGTH';

OWNER  OPERATOR_NAME  BINDING#  FUNCTION_NAME                                RETURN_SCHEMA   RETURN_TYPE  IMPLEMENTATION_TYPE_SCHEMA IMPLEMENTATION_TYPE  PROPERTY
------ -------------- --------- -------------------------------------------- --------------- ------------ -------------------------- -------------------- --------
SDE    ST_LENGTH      1         "SDE"."ST_GEOMETRY_OPERATORS"."ST_LENGTH_F"                  NUMBER       SDE               

to see that the operator is bound to SDE.ST_GEOMETRY_OPERATORS.ST_LENGTH_F which you already discovered. This function is darn simple and just as nef001 said, it returns the length attribute from the geometry with some logic to handle empty values.

enter image description here

3
+50

length is stored with the shape so there is no call to the extproc

  • If you look at the oracle type definition for ST_Geometry - you can see that ii is an object type which has attributes and functions. The function ST_LENGTH simply returns the LEN attribute value. In other words, length is a basic property of the data stored and therefore there is no need to call an extproc to return the value. I don't have a source sorry, just poking around the database with Toad. – nef001 Nov 5 '17 at 3:43
  • 1
    The length calculation most likely is implemented in the st_shapelib.dll library and would be called during an INSERT or UPDATE of an ST_GEOMETRY or whenever an instance of the ST_GEOMETRY type is constructed. But when you do a SELECT on an existing shape it is not necessary to call a library function because the database already has the value stored. – nef001 Nov 6 '17 at 7:31

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.