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

# Convert a table with SDO_GEOMETRY point to LRS-point

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

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

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