This discussion is archived
5 Replies Latest reply: Mar 22, 2013 4:45 PM by Simon Greener RSS

Concatenating SDO_GEOMETRY value with a string

690485 Newbie
Currently Being Moderated
Hi Team,

Im trying to generate a INSERT statements based on data available in a table using a select statement as shown below

SELECT 'INSERT INTO POWER_POLES (LINE_ID,PRIORITY,POINT_GEOM,FID) VALUES ('||
DECODE(LINE_ID,NULL,'NULL',LINE_ID)|| ',' ||
DECODE(PRIORITY,NULL,'NULL',PRIORITY) ||',' ||
DECODE(POINT_GEOM,NULL,'NULL','''' ||POINT_GEOM ||'''') ||',' || ----POINT_GEOM is of SDO_GEOMETRY type.
DECODE(FID,NULL,'NULL',FID) ||');'
FROM POWER_POLES;

Upon running above SELECT statement to get the INSERT statement as output, I get SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got MDSYS.SDO_GEOMETRY error.

I have dissected my SELECT statement and arrived on below observations.

To put it simply, If execute below statement, it works fine...
select 'FirstString'||POINT_NAME||'LastString' from POWER_POLES;      ---POINT_NAME is of VARCHAR2 type+

If execute below statement, it works fine...
select POINT_GEOM from POWER_POLES;       ---POINT_GEOM is of SDO_GEOMETRY type.+

But, below statement is going to throw SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got MDSYS.SDO_GEOMETRY
select 'FirstString'||POINT_GEOM||'LastString' from POWER_POLES;        ---POINT_GEOM is of SDO_GEOMETRY type.+

I believe that its something which expected behavior of SDO_GEOMETRY type. Can anyone please suggest how to achieve concatenating of SDO_GEOMETRY value with a string to generate such a INSERT statement.

Please let me know if further information is required.

Regards,
Prem Kumar
  • 1. Re: Concatenating SDO_GEOMETRY value with a string
    B Hall Explorer
    Currently Being Moderated
    Kumar,

    I would instead suggest creating a geometry for the string, and then appending it to the existing geometry with something like SDO_UTIL.concat_lines. This can all take place "on the fly" if you wish.

    Bryan
  • 2. Re: Concatenating SDO_GEOMETRY value with a string
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Kumar,

    off the top of my head, and untested, something like this should work:
    select 
      'SDO_GEOMETRY(' || P.GTYPE || ', '|| P.SRID || ', SDO_POINT_TYPE(' || P.POINT_GEOM.SDO_POINT.X || ', ' || P.POINT_GEOM.SDO_POINT.Y || ', ' || P.POINT_GEOM.SDO_POINT.Z || '), NULL,  NULL)' 
    from 
      POWER_POLES P; 
    Question is, why do you want to do this? A simple select insert would work faster, I'd say, but I don't know the requirements of course :-)

    HTH,
    Stefan

    As an afterthought: you could also play with [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_util.htm#BJEBJEGJ]SDO_UTIL.TO_WKTGEOMETRY and [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_util.htm#BJEGGIAB]SDO_UTIL.FROM_WKTGEOMETRY. They return/expect CLOB's, which you might want to keep in mind.

    Edited by: Stefan Jager on Mar 21, 2013 4:19 PM
  • 3. Re: Concatenating SDO_GEOMETRY value with a string
    Simon Greener Journeyer
    Currently Being Moderated
    Stefan is right and given that a point can be coded in sdo_point_type or sdo_elem_info_array/sdo_ordinate_array the best approach is to use WKT.

    Except if the sdo_geometry's sdo_gtype is other than 200x.

    If the sdo_geometry sdo_gtype is 300x or 400x then you will have to write a PL/SQL function to create the geometry. Then there are issues with the size of the resultant string such that one need to return a CLOB.

    The following uses Get_WKT() but this returns a CLOB which has to be converted into VARCHAR2 (note the 4000 byte limit).
    drop   table power_poles;
    create table power_poles (line_id number,priority number,point_geom sdo_geometry,fid number);
    insert into power_poles values(1,1,sdo_geometry(2001,null,sdo_point_type(1,1,null),null,null),1);
    insert into power_poles values(2,1,sdo_geometry(2001,null,null,sdo_elem_info_array(1,1,1),sdo_ordinate_array(2,2)),2);
    commit;
    SELECT 'INSERT INTO POWER_POLES (LINE_ID,PRIORITY,POINT_GEOM,FID) VALUES ('||
     DECODE(LINE_ID,NULL,'NULL',LINE_ID)|| ',' ||
     DECODE(PRIORITY,NULL,'NULL',PRIORITY) ||',' ||
     CASE WHEN POINT_GEOM IS NULL 
          THEN 'NULL'
          ELSE 'SDO_GEOMETRY(''' || dbms_lob.substr( a.POINT_GEOM.get_wkt(), 4000, 1 ) ||''','||DECODE(a.point_geom.sdo_srid,NULL,'NULL',TO_CHAR(a.point_geom.sdo_srid))
      END || '),' || ----POINT_GEOM is of SDO_GEOMETRY type.
     DECODE(FID,NULL,'NULL',FID) ||');' as insertSQL
     FROM POWER_POLES a;
    
    -- Which gives....
    --
    INSERTSQL
    --------------------------------------------------------------------------------------------------------------
    INSERT INTO POWER_POLES (LINE_ID,PRIORITY,POINT_GEOM,FID) VALUES (1,1,SDO_GEOMETRY('POINT (1.0 1.0)',NULL),1);
    INSERT INTO POWER_POLES (LINE_ID,PRIORITY,POINT_GEOM,FID) VALUES (2,1,SDO_GEOMETRY('POINT (2.0 2.0)',NULL),2);
    But if you try and process a 300x sdo_gtype geom you get this:
    Error report:
    SQL Error: ORA-13199: 3D geometries are not supported by geometry WKB/WKT generation.
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.SDO_UTIL", line 2473
    ORA-06512: at "MDSYS.SDO_GEOMETRY", line 36
    13199. 00000 -  "%s"
    *Cause:    This is an internal error.
    *Action:   Contact Oracle Support Services.
    So, in this situation, you have to write a function as follows:
    create or replace Function ST_AsText(p_self      in sdo_geometry,
                                         p_tolerance in number default 0.05,
                                         p_ord_round in number default 3,
                                         p_linefeed  in pls_integer default 1)
    Return CLOB deterministic
    As
      c_i_empty_geom Constant Integer       := -20120;
      c_s_empty_geom Constant VarChar2(100) := 'Geometry must not be null';
      v_text         Clob;
      v_linefeed     char(1) := case when NVL(p_linefeed,1) = 0 then '' else CHR(10) end;
    Begin
      If ( p_self is null ) THEN
         raise_application_error(c_i_empty_geom,c_s_empty_geom,true);
      End If;
      SYS.DBMS_LOB.CreateTemporary( v_text, TRUE, SYS.DBMS_LOB.CALL );
      SYS.DBMS_LOB.APPEND(v_text,'MDSYS.SDO_GEOMETRY('|| 
                                 Case When p_self.sdo_gtype Is Null 
                                      Then 'NULL' 
                                      Else To_Char(p_self.sdo_gtype,'FM9999') 
                                  End || ',' ||
                                 CASE WHEN p_self.sdo_srid IS NULL 
                                      THEN 'NULL' 
                                      ELSE TO_CHAR(p_self.sdo_srid)
                                  END || ',' ||
                                 CASE WHEN p_self.SDO_POINT IS NULL 
                                      THEN 'NULL,'
                                      ELSE 'MDSYS.SDO_POINT_TYPE(' ||
                                           CASE WHEN p_self.sdo_point.x IS NULL THEN 'NULL' ELSE TO_CHAR(ROUND(p_self.sdo_point.x,p_ord_round)) END || ',' ||
                                           CASE WHEN p_self.sdo_point.y IS NULL THEN 'NULL' ELSE TO_CHAR(ROUND(p_self.sdo_point.y,p_ord_round)) END || ',' ||
                                           CASE WHEN p_self.sdo_point.z IS NULL THEN 'NULL' ELSE TO_CHAR(ROUND(p_self.sdo_point.z,p_ord_round)) END || '),'
                                  END ||
                          v_linefeed);
      IF ( p_self.sdo_elem_info IS NULL ) THEN
        SYS.DBMS_LOB.APPEND(v_text,'NULL,');
      ELSE
        SYS.DBMS_LOB.APPEND(v_text,'MDSYS.SDO_ELEM_INFO_ARRAY(');
        FOR i IN p_self.sdo_elem_info.FIRST..p_self.sdo_elem_info.LAST LOOP
            SYS.DBMS_LOB.APPEND(v_text,''||p_self.sdo_elem_info(i));
            If ( i <> p_self.sdo_elem_info.LAST ) THEN
               SYS.DBMS_LOB.APPEND(v_text,',' || v_linefeed); 
            END IF;
        END LOOP;
        SYS.DBMS_LOB.APPEND(v_text,'),'||v_linefeed); 
      END IF;
      IF ( p_self.sdo_ordinates IS NULL ) THEN
        SYS.DBMS_LOB.APPEND(v_text,'NULL)');
      ELSE
        SYS.DBMS_LOB.APPEND(v_text,'MDSYS.SDO_ORDINATE_ARRAY(');
        FOR i IN p_self.sdo_ordinates.FIRST..p_self.sdo_ordinates.LAST LOOP
            SYS.DBMS_LOB.APPEND(v_text,
                                case when p_self.sdo_ordinates(i) is null 
                                     then 'NULL' 
                                     else to_char(round(p_self.sdo_ordinates(i),p_ord_round)) 
                                 end);
            If ( i <> p_self.sdo_ordinates.LAST ) THEN
               If ( v_linefeed=CHR(10) And ( 0 = MOD(i,p_self.get_dims())) ) Then 
                  SYS.DBMS_LOB.APPEND(v_text,','||v_linefeed);
               Else 
                  SYS.DBMS_LOB.APPEND(v_text,','); 
               End If;
            END IF;
        END LOOP;
        SYS.DBMS_LOB.APPEND(v_text,'))'||v_linefeed);
      END IF;
      Return v_text;
    End ST_AsText;
    /
    show errors
    Which you use as follows:
    insert into power_poles values(3,1,sdo_geometry(3001,null,null,sdo_elem_info_array(1,1,1),sdo_ordinate_array(2,2,1)),3);
    commit;
    
    SELECT 'INSERT INTO POWER_POLES (LINE_ID,PRIORITY,POINT_GEOM,FID) VALUES ('||
     DECODE(LINE_ID,NULL,'NULL',LINE_ID)|| ',' ||
     DECODE(PRIORITY,NULL,'NULL',PRIORITY) ||',' ||
     CASE WHEN POINT_GEOM IS NULL 
          THEN 'NULL'
          ELSE dbms_lob.substr( ST_AsText(a.POINT_GEOM,0.05,3,0),4000,1)
      END || ',' || ----POINT_GEOM is of SDO_GEOMETRY type.
     DECODE(FID,NULL,'NULL',FID) ||');' as insertSQL
     FROM POWER_POLES a;
    
    -- Results
    --
    INSERTSQL
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    INSERT INTO POWER_POLES (LINE_ID,PRIORITY,POINT_GEOM,FID) VALUES (1,1,MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1,1,NULL),NULL,NULL),1);
    INSERT INTO POWER_POLES (LINE_ID,PRIORITY,POINT_GEOM,FID) VALUES (2,1,MDSYS.SDO_GEOMETRY(2001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),MDSYS.SDO_ORDINATE_ARRAY(2,2)),2);
    INSERT INTO POWER_POLES (LINE_ID,PRIORITY,POINT_GEOM,FID) VALUES (3,1,MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),MDSYS.SDO_ORDINATE_ARRAY(2,2,1)),3);
    The function, ST_AsText(), is a rework of one of the many functions I make available in a new book I am coauthoring with Siva Ravada that will be published by PACKT later in this year.

    HTH

    regards
    Simon
  • 4. Re: Concatenating SDO_GEOMETRY value with a string
    690485 Newbie
    Currently Being Moderated
    Hi Byran, Stefan and Simon,

    Thank you very much for your suggestions. The explanations are fabulous :)

    Initially, before dropping my question here, I thought of managing the issue as shown below

    SDO_UTIL.FROM_WKTGEOMETRY(a.POINT_GEOM.GET_WKT())+

    But, Im curious know, what is the difference between addressing the issue by SDO_UTIL.FROM_WKTGEOMETRY(a.POINT_GEOM.GET_WKT())+ and
    CASE WHEN POINT_GEOM IS NULL
    THEN 'NULL'
    ELSE 'SDO_GEOMETRY(''' || dbms_lob.substr( a.POINT_GEOM.get_wkt(), 4000, 1 ) ||''','||DECODE(a.point_geom.sdo_srid,NULL,'NULL',TO_CHAR(a.point_geom.sdo_srid))
    END || '),' || ----POINT_GEOM is of SDO_GEOMETRY type.
    DECODE(FID,NULL,'NULL',FID) ||');' as insertSQL
    FROM POWER_POLES a;
    I have implemented the above said logic and generated INSERT statements successfully and even able to insert the rows. But, I have observed that, though original X and Y values of SDO_GEOMETRY are of 3 decimal precision, after re-inserting the same rows using the INSERT statement generated by SELECT statement, I notice that the X and Y values are having high precision.

    Original POINT_GEOM value
    MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(14891.201,26275.359,null),null,null)

    Newly inserted same geometry using generated INSERT statement using above logic
    MDSYS.SDO_GEOMETRY(2001,null,MDSYS.SDO_POINT_TYPE(14891.2009999999991123331710696220397949,26275.3590000000003783497959375381469727,null),null,null)

    Am I missing something in our logic and is there any way to get rid of such a laaaaarge precision?

    Please suggest and thanks in advance.

    Regards,
    Kumar
  • 5. Re: Concatenating SDO_GEOMETRY value with a string
    Simon Greener Journeyer
    Currently Being Moderated
    Kumar,

    The imprecision comes from the GET_WKT() (note the sdo_util and sdo_geometry method call the same underlying code).

    How to fix the precision issue?

    Since you have points it is easy:
    update POWER_POLES p set p.geom = sdo_geometry(p.geom.sdo_gtype,p.geom.sdo_srid,sdo_point_type(round(p.geom.sdo_point.x,3), sdo_point_type(round(p.geom.sdo_point.y,3),NULL),NULL,NULL);
    For linestrings and polygons see my RoundOrdinates function at http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/289/rounding-coordinates-or-ordinates-in-sdo_geometry

    regards
    Simon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points