I have a business development goal to use Oracle Spatial to store our coordinate data. Currently, we receive coordinates from scientists who are taking measurements in marine areas and these are stored in our Oracle database as either numeric pairs for points, or long varchar arrays for polygons. However, we would like to improve our management of these data by using Oracle Spatial.

The coordinate information we get from scientists normally comes in CSV files, with data attached and gets loaded into Oracle tables as entries in fields.

I know that I could manually enter the vertices into SDO_ORDINATE_ARRAY but we regularly get hundreds of coordinate pairs being supplied in one CSV file, which makes the manual route very inefficient.

Could someone please advise me if there is a way to populate the contents of the SDO_ORDINATE_ARRAY by pulling the information out from the other tables in the database where it is already stored?

An example of what I've tried is below:

Test table called GEOMTEST consisting of NAME varchar2(50) COORDS varchar2(4000) COORD_GEOM SDO_GEOMETRY

I've populated name with the area of interest 'Cardigan Bay' in Wales, UK. COORDS is my polygon stored as an array in varchar2. This was imported from a CSV file. COORD_GEOM is what I wish to transfer the content of COORDS into.

I attempted to run this piece of code but received an error:

insert into geomtest (coord_geom) values(SDO_GEOMETRY(2003,4326,null,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY values(select coords from geomtest));

I am using Toad as my client, and the error was "ERROR: line 18. column 120, ending line 18, column 125: Found 'values': A reserved word cannot be used as an identifier.

I presume this is related to my use of the select statement within the SDO_ORDINATE_ARRAY part of the INSERT statement but am uncertain as to how to proceed.

I would be grateful for any advice,

Many thanks

Sean

CodePudding user response：

Unfortunately, you cannot directly pass a string containing numbers to the SDO_ORDINATE_ARRAY constructor. One solution is to write a custom string tokenizer function that will parse the string of coordinates into individual numbers, and build an SDO_ORDINATE_ARRAY object. Here is one:

create or replace function tokenize (str clob)
return sdo_ordinate_array
is
  s clob := str||',';
  i number;
  j number;
  t sdo_ordinate_array := sdo_ordinate_array();
begin
  i := 1;
  loop
    j := instr(s, ',', i);
    exit when j = 0;
    t.extend();
    t(t.count) := to_number(substr(s,i,j-i));
    i := j 1;
  end loop;
  return t;
end;
/
show errors

And here is how it works. First let's create a simple table with a couple of examples:

drop table geomtest purge;
create table geomtest ( 
  id          number,
  name        varchar2(50 char), 
  coords      clob, 
  coord_geom  sdo_geometry 
);

insert into geomtest (id, name, coords)
values (
  2686,
  'TX/Mitchell', 
  '-101.17416, 32.527592, -101.17417, 32.523998, -101.1836, 32.087082, -100.82121, 32.086479, -100.66497, 32.085278, -100.66024, 32.5252, -101.17416, 32.527592'
);

insert into geomtest (id, name, coords)
values (
  2769,
  'TX/Yoakum',
  '-103.05616, 33.388332, -103.06416, 32.958992, -102.59455, 32.958733, -102.59436, 33.388393, -103.05616, 33.388332'
);
commit;

Then let's use the tokenizer function to update the geometry column:

update geomtest 
set coord_geom = sdo_geometry(2003,4326,null,sdo_elem_info_array(1,1003,1),tokenize(coords));
commit;

Check the results:

SQL> select * from geomtest;

  ID NAME        COORDS                                                                                                                                                        COORD_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2686 TX/Mitchell -101.17416, 32.527592, -101.17417, 32.523998, -101.1836, 32.087082, -100.82121, 32.086479, -100.66497, 32.085278, -100.66024, 32.5252, -101.17416, 32.527592  SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(-101.17416, 32.527592, -101.17417, 32.523998, -101.1836, 32.087082, -100.82121, 32.086479, -100.66497, 32.085278, -100.66024, 32.5252, -101.17416, 32.527592))
2769 TX/Yoakum   -103.05616, 33.388332, -103.06416, 32.958992, -102.59455, 32.958733, -102.59436, 33.388393, -103.05616, 33.388332                                             SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(-103.05616, 33.388332, -103.06416, 32.958992, -102.59455, 32.958733, -102.59436, 33.388393, -103.05616, 33.388332))

2 rows selected.

NOTES

1. I used a CLOB column for storing the coordinates. A 4000 bytes string is too small to hold any serious geometry (unless all your shapes are very simple - just a few points).

2. There are more efficient ways if doing this string-to-geometry conversion, but they imply that you use geometry-oriented strings notations: GeoJSON, WKT, GML. Those are naturally supported by Oracle. They also allow more complex structures like multi-polygons or polygons with holes.

EDIT: I rewrote the function to directly return a SDO_GEOMETRY object instead. This makes it easier to use:

create or replace function string_to_geom (str clob)
return sdo_geometry
is
  s clob := str||',';
  i number;
  j number;
  t sdo_ordinate_array := sdo_ordinate_array();
begin
  i := 1;
  loop
    j := instr(s, ',', i);
    exit when j = 0;
    t.extend();
    t(t.count) := to_number(substr(s,i,j-i));
    i := j 1;
  end loop;
  return sdo_geometry (2003, 4326, null, sdo_elem_info_array (1,1003,1), t);
end;
/
show errors

User it like this:

update geomtest 
set coord_geom = string_to_geom(coords);
commit;