5 Replies Latest reply: Mar 22, 2013 6:45 PM by Simon Greener RSS

    Concatenating SDO_GEOMETRY value with a string

    690485
      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
          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
            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
              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
                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
                  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