2 Replies Latest reply: May 28, 2014 8:24 PM by Barbara Boehmer RSS

    Convert a table with SDO_GEOMETRY point to LRS-point

    9c056d7f-44ba-4815-b6ed-133e9056e036

      I have a table with SDO_GEOMETRY point , then i want to convert the point to the LRS POINT, and add the time column as the 'measure' . the table like this

      TIME         VARCHAR(50)

      ID             NUMBER

      LON          NUMBER

      LAT           NUMBER

      SPEED     NUMBER

      DIR           NUMBER

      GEOM      SDO_GEOMETRY        (DATA LIKE THIS :  MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(114.294465,30.613948,NULL),NULL,NULL)  )

       

      I want to convert the SDO_POINT_TYPE to LRS POINT and add  the TIME column as the measure.

      SDO_DIM_ARRAY(

        SDO_DIM_ELEMENT('LON', -180, 180, 0.005),

        SDO_DIM_ELEMENT('LAT', -90, 90, 0.005),

        SDO_DIM_ELEMENT('TIME', 0, 24, 0.005)  )           something like this 

      i know little about PL/SQL language so i don't know how to convert. could you please write down the correct santance.

        • 1. Re: Convert a table with SDO_GEOMETRY point to LRS-point
          Barbara Boehmer

          I don't know why your time column is VARCHAR or what format your time data is in, but you will need to extract a numeric value from it between 0 and 24, representing the number of hours in the day.  You may need to use SUBSTR and/or TO_DATE and TO_CHAR to do that, depending on how your data is stored.  If you need help with that portion of the problem, then please post some sample time data.  I have provided a brief example below, assuming that the time data is numeric data that is stored in a VARCHAR column.

           

          SCOTT@orcl12c> -- If you have a table and data like this:

          SCOTT@orcl12c> CREATE TABLE a_table

            2    (TIME         VARCHAR(50),

            3      ID         NUMBER,

            4      LON         NUMBER,

            5      LAT         NUMBER,

            6      SPEED         NUMBER,

            7      DIR         NUMBER,

            8      GEOM         SDO_GEOMETRY)

            9  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT INTO a_table (time, id, lon, lat) VALUES

            2    (12, 8307, 114.294465, 30.613948)

            3  /

           

          1 row created.

           

          SCOTT@orcl12c> COLUMN TIME FORMAT A4

          SCOTT@orcl12c> SELECT * FROM a_table

            2  /

           

          TIME         ID        LON        LAT      SPEED        DIR

          ---- ---------- ---------- ---------- ---------- ----------

          GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

          --------------------------------------------------------------------------------

          12         8307 114.294465  30.613948

           

           

           

          1 row selected.

           

          SCOTT@orcl12c> -- and you created your geom data like this:

          SCOTT@orcl12c> UPDATE a_table

            2  SET    geom =

            3          MDSYS.SDO_GEOMETRY

            4            (2001, id,

            5             MDSYS.SDO_POINT_TYPE (lon, lat, NULL),

            6             NULL, NULL)

            7  /

           

          1 row updated.

           

          SCOTT@orcl12c> SELECT * FROM a_table

            2  /

           

          TIME         ID        LON        LAT      SPEED        DIR

          ---- ---------- ---------- ---------- ---------- ----------

          GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

          --------------------------------------------------------------------------------

          12         8307 114.294465  30.613948

          SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(114.294465, 30.613948, NULL), NULL, NULL

          )

           

           

          1 row selected.

           

          SCOTT@orcl12c> -- then you can update it like this:

          SCOTT@orcl12c> UPDATE a_table

            2  SET    geom =

            3          SDO_LRS.CONVERT_TO_LRS_GEOM

            4            (MDSYS.SDO_GEOMETRY

            5           (2001, id,

            6            MDSYS.SDO_POINT_TYPE (lon, lat, time),

            7            NULL, NULL),

            8             SDO_DIM_ARRAY(

            9           SDO_DIM_ELEMENT('LON', -180, 180, 0.005),

          10           SDO_DIM_ELEMENT('LAT', -90, 90, 0.005),

          11           SDO_DIM_ELEMENT('TIME', 0, 24, 0.005)))

          12  /

           

          1 row updated.

           

          SCOTT@orcl12c> SELECT * FROM a_table

            2  /

           

          TIME         ID        LON        LAT      SPEED        DIR

          ---- ---------- ---------- ---------- ---------- ----------

          GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

          --------------------------------------------------------------------------------

          12         8307 114.294465  30.613948

          SDO_GEOMETRY(4401, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(

          114.294465, 30.613948, 12, NULL))

           

           

          1 row selected.

          • 2. Re: Convert a table with SDO_GEOMETRY point to LRS-point
            Barbara Boehmer

            The following is an alternative two-step method.

             

            SCOTT@orcl12c> -- starting data:

            SCOTT@orcl12c> SELECT * FROM a_table

              2  /

             

            TIME         ID        LON        LAT      SPEED        DIR

            ---- ---------- ---------- ---------- ---------- ----------

            GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

            --------------------------------------------------------------------------------

            12         8307 114.294465  30.613948

            SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(114.294465, 30.613948, NULL), NULL, NULL

            )

             

             

            1 row selected.

             

            SCOTT@orcl12c> -- add time:

            SCOTT@orcl12c> UPDATE a_table t

              2  SET    t.geom.sdo_point.z = t.time

              3  /

             

            1 row updated.

             

            SCOTT@orcl12c> -- convert to lrs:

            SCOTT@orcl12c> UPDATE a_table

              2  SET    geom =

              3          SDO_LRS.CONVERT_TO_LRS_GEOM

              4            (geom,

              5             SDO_DIM_ARRAY(

              6           SDO_DIM_ELEMENT('LON', -180, 180, 0.005),

              7           SDO_DIM_ELEMENT('LAT', -90, 90, 0.005),

              8           SDO_DIM_ELEMENT('TIME', 0, 24, 0.005)))

              9  /

             

            1 row updated.

             

            SCOTT@orcl12c> SELECT * FROM a_table

              2  /

             

            TIME         ID        LON        LAT      SPEED        DIR

            ---- ---------- ---------- ---------- ---------- ----------

            GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

            --------------------------------------------------------------------------------

            12         8307 114.294465  30.613948

            SDO_GEOMETRY(4401, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(

            114.294465, 30.613948, 12, NULL))

             

             

            1 row selected.