1

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 using this fake syntax: shape.coordinates[1][0].

--get the X coordinate of the second vertex: 30
with data as (select sdo_geometry('linestring(10 20, 30 40, 50 60)') as shape from dual)

select 
    (shape).coordinates[1][0] as x  --Zero-based numbering. [1] would be the second vertex. [0] would be the first ordinate: X.
from 
    data
Error:

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 6 Column: 24

It's not surprising that fake syntax failed. But I am curious if there's a way to do this kind of thing using proper Oracle SQL syntax.


I'm aware that there are other ways to get coordinates, such as:

I'm just trying to learn what all the options are. And I'm wondering if there's a way to do it using index positions / dot-notation.

1 Answer 1

0

It looks like the answer is "sort of", using JSON and GeoJSON:

--get the X coordinate of the second vertex: 30
with data as (select sdo_geometry('linestring(10 20, 30 40, 50 60)') as shape from dual)

select 
    json_value((shape).Get_GeoJson(),'$.coordinates[1][0]' returning number) as x  --Zero-based numbering. [1] is the second vertex. [0] is the first ordinate: X.
from 
    data


Result: 

         X
----------
        30

Source: Idea: Add a SDO_ORDINATES member function (for extracting collection elements by index)

Related: Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in SQL?


With that said, using JSON in queries seems like an unappealing practice to me. It could quickly become unmanageable, since it's so different from SQL.

For example:

I think it would be confusing to have to deal with two different numbering systems in the same query:

  • SQL and PL/SQL: one-based numbering. 1,1 would be the first ordinate of the first vertex.
  • JSON: zero-based numbering. [1][1] would be the second ordinate of the second vertex.

Two different numbering systems is just asking for trouble.

So I think it would be best to stick with regular SQL where possible. And only use JSON as a last resort.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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