I have SDO_GEOMETRY objects in Oracle 18c:
select
sdo_geometry(2002, null, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(1, 2, 3, 4)) as shape
from
dual
union all
select
sdo_geometry(2001, null, sdo_point_type(-79, 37, null), null, null) as shape
from
dual
Output:
MDSYS.SDO_GEOMETRY(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), MDSYS.SDO_ORDINATE_ARRAY(1, 2, 3, 4))
MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(-79, 37, NULL), NULL, NULL)
In a query, I want to select the SDO_GEOMETRY's sdo_point
attribute as literal text (for concatenation purposes).
Example: (fails)
select
'the geometry sdo_point attribute is: ' || a.shape.sdo_point
from
(
select
sdo_geometry(2002, null, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(1, 2, 3, 4)) as shape
from
dual
union all
select
sdo_geometry(2001, null, sdo_point_type(-79, 37, null), null, null) as shape
from
dual
) a
--Desired output:
--'the geometry sdo_point attribute is: null'
--'the geometry sdo_point attribute is: (-79, 37, null)'
ORA-00932: inconsistent datatypes: expected CHAR got MDSYS.SDO_POINT_TYPE
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 2 Column: 37
I don't know how to convert that object attribute to text. I've tried using the SDO_UTIL.TO_WKTGEOMETRY() function. But that only seems to work on a point geometry as a whole, not on the specific sdo_point attribute.
How can I select the SDO_GEOMETRY's sdo_point
attribute as text?
CodePudding user response:
Just extract each X
, Y
and Z
component part of the point and convert those to strings:
SELECT 'the geometry sdo_point attribute is: '
|| NVL2(
a.shape.sdo_point,
'('
|| COALESCE(TO_CHAR(a.shape.sdo_point.X), 'NULL') || ', '
|| COALESCE(TO_CHAR(a.shape.sdo_point.Y), 'NULL') || ', '
|| COALESCE(TO_CHAR(a.shape.sdo_point.Z), 'NULL')
|| ')',
'NULL'
) AS description
FROM (
SELECT sdo_geometry(
2002, null, null,
sdo_elem_info_array(1, 2, 1),
sdo_ordinate_array(1, 2, 3, 4)
) as shape
FROM DUAL
UNION ALL
SELECT sdo_geometry(2001, null, sdo_point_type(-79, 37, null), null, null)
FROM DUAL
) a
Which outputs:
DESCRIPTION the geometry sdo_point attribute is: NULL the geometry sdo_point attribute is: (-79, 37, NULL)
On later Oracle versions, you could convert the SDO_POINT
to JSON and then translate the JSON to your format:
SELECT 'the geometry sdo_point attribute is: '
|| TRANSLATE(JSON_OBJECT(a.shape.sdo_point), '{}":XYZ', '()')
AS description
FROM (
SELECT sdo_geometry(
2002, null, null,
sdo_elem_info_array(1, 2, 1),
sdo_ordinate_array(1, 2, 3, 4)
) as shape
FROM DUAL
UNION ALL
SELECT sdo_geometry(2001, null, sdo_point_type(-79, 37, null), null, null)
FROM DUAL
) a
Which has the similar output to above.
db<>fiddle here
CodePudding user response:
Here is a function I wrote a while ago that writes out the content of an SDO_GEOMETRY type in a text format just like that used by SQLPLUS:
create or replace function sdo_format
(geom sdo_geometry)
return varchar2
is
output_string varchar2(32767);
MAX_LENGTH number := 3980;
begin
if geom is null then
return NULL;
end if;
-- Initialyze output string
output_string := 'SDO_GEOMETRY(';
-- Format SDO_GTYPE
output_string := output_string || geom.sdo_gtype;
output_string := output_string || ', ';
-- Format SDO_SRID
if geom.sdo_srid is not null then
output_string := output_string || geom.sdo_srid;
else
output_string := output_string || 'NULL';
end if;
output_string := output_string || ', ';
-- Format SDO_POINT
if geom.sdo_point is not null then
output_string := output_string || 'SDO_POINT_TYPE(';
output_string := output_string || geom.sdo_point.x || ', ';
output_string := output_string || geom.sdo_point.y || ', ';
if geom.sdo_point.z is not null then
output_string := output_string || geom.sdo_point.z || ')';
else
output_string := output_string || 'NULL)';
end if;
else
output_string := output_string || 'NULL';
end if;
output_string := output_string || ', ';
-- Format SDO_ELEM_INFO
if geom.sdo_elem_info is not null then
output_string := output_string || 'SDO_ELEM_INFO_ARRAY(';
if geom.sdo_elem_info.count > 0 then
for i in geom.sdo_elem_info.first..geom.sdo_elem_info.last loop
if i > 1 then
output_string := output_string || ', ';
end if;
output_string := output_string || geom.sdo_elem_info(i);
end loop;
end if;
output_string := output_string || ')';
else
output_string := output_string || 'NULL';
end if;
output_string := output_string || ', ';
-- Format SDO_ORDINATES
if geom.sdo_ordinates is not null then
output_string := output_string || 'SDO_ORDINATE_ARRAY(';
if geom.sdo_ordinates.count > 0 then
for i in geom.sdo_ordinates.first..geom.sdo_ordinates.last loop
exit when length(output_string) > MAX_LENGTH;
if i > 1 then
output_string := output_string || ', ';
end if;
output_string := output_string || geom.sdo_ordinates(i);
end loop;
if length(output_string) > MAX_LENGTH then
output_string := output_string || ' <...>';
end if;
end if;
output_string := output_string || ')';
else
output_string := output_string || 'NULL';
end if;
output_string := output_string || ')';
-- output_string := output_string || ' [' || length(output_string) || ']';
-- Done - return formatted string
return output_string;
end;
NOTE: it returns the output as a VARCHAR2
and has a hard-coded limit to 4000 characters: it writes <...>
for the ordinates it could not print.
If your database has been set to use long strings, then you can increase that limit to up to 32767 (actually a bit less - say 32760). Or simpler: modify it to use CLOBs. That is an exercise left to the reader.