Oracle Spatial - loading geographic data - Area

 Following the previous post - Oracle Spatial - SQL based Geographic data - Intro, few examples of loading data polygons/areas of geographical data.

I will cover here:

• Manual creation
• Using a string 
• Loading GeoJSON
  

Polygon/Area Simple Example - Manual creation 

Step 1: Lets assume I have a table area with 2 columns (ID, Area_Name)  and 2 lines 

Step 2:

I will add Geometry column to Area1 table:

alter table Area1 add (geometry SDO_GEOMETRY);

and update a data row:

update AREA1
set geometry = SDO_GEOMETRY(
                 2003,                                         -- 2003 - two dimensional polygon
                 4326,                                         -- our usual coordinate system for lon/lat
                 NULL, -- ONLY for points
                 SDO_ELEM_INFO_ARRAY(1, 1003, 1),    -- Signifies simple exterior polygon,
                 SDO_ORDINATE_ARRAY(    -- lon/lat values
                    34.809512,    32.099221,  -- First point and last point must be the same                                                   34.809737,    32.098608,
                    34.809225,    32.098161,
                    34.809683,    32.09664,
                    34.81074,    32.097122,
                    34.813604,    32.097365,
                    34.813782,    32.098037,
                    34.81293,    32.098534,
                    34.811657,    32.098696,
                    34.810434,    32.099269,
                    34.809512,    32.099221  -- First point and last point must be the same
                 )
           )
where id=1                    -- Updating only the Pond row
;

In case you are interested, the maximum SDO_ORDINATE_ARRAY size is 1,048,576 numbers.

I will verify with select statement: 

 

Next I will update the other row and run step 3 and step 4 for indexing.

Step 3: Before creating a spatial index, we must insert a row of spatial metadata. 

INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'AREA1',  -- table name
  'GEOMETRY',    -- geometry column name
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', -180, 180, 0.005),
    SDO_DIM_ELEMENT('Y', -90, 90, 0.005)),
   4326           -- indicates longitude/latitude coordinates (coordinate system)
 );

Step 4: Create a spatial Index for performance

CREATE INDEX AREA1_SIDX ON
      AREA1 (
          GEOMETRY
      )
          INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

 

 

Polygon/Area Simple Example - Using points (Long/Lat) String

We could do it better if we had a function that loads a string (CLOB better due to size constraints) of Longitude/Latitude sets and return SDO_GEOMETRY Polygon (2003,4326 like in the previous case)? Albert Godfrind in this Stack Overflow thread created great solution:

He created a function that does exactly that.

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

 Now if I want to update the second row in my Area1 Table I can do the following:

update Area1
set GEOMETRY =
string_to_geom('34.813169,32.105909,34.813792,32.105355,34.808766,32.102542,34.808417,32.103285,34.813169,32.105909')
where id=2;

Now both of the rows are fine.

Steps 3 & 4 were done in the previous section.

 

Polygon/Area GeoJSON based example

 GeoJSON (see at https://geojson.org/) is a standard in the industry. 

GeoJson structure from LiveLabs workshop

Naturally, GeoJSON is still a JSON file. Oracle has now many JSON oriented options. There is an Autonomous JSON Database in Oracle cloud or simple drag and drop UI in "regular" Autonomous Database for JSON files loading. Yet, we have basic JSON support even in earlier versions such as 12c.

 In the Spatial Data LiveLab here (Lab 3) we have an example of loading data from files ans setting region tables in Autonomous Database. I will do a simple manual version of that process, that is less trivial that what is done in the Autonomous example. Here is an example how to do it by Jeff Kemp.

Unlike Jeff, I will set the geometry column as SDO_GEOMETRY and not JSON.

So our JSON file has the following structure:

 

We want to load a JSON file into the database. First you can check the data using http://geojson.io site. Just drop the file on the map.

The JSON file is located in some storage that is mapped in the DB to a Directory. Access to directory required by spatial user to load JSON File. If needed system user / Admin should run something like:

GRANT read on DIRECTORY TEMP_DIR to user_name;
 

I will create a table named Region_GEOJSON that contains a single clob column named features:

create table Region_GEOJSON ( features clob, constraint RG_chk check ( features is json ));

Then load the file regions.geojson

declare
    b bfile := bfilename('TEST_DIR','regions.geojson');
    c clob;
  l_dest_offset   integer := 1;
  l_src_offset    integer := 1;
  l_bfile_csid    number  := 0;
  l_lang_context  integer := 0;
  l_warning       integer := 0;
  begin
    dbms_lob.open(b);
    dbms_lob.createtemporary(c,true);
    dbms_lob.loadclobfromfile (
    dest_lob      => c,
    src_bfile     => b,
    amount        => dbms_lob.getlength(b),
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
    insert into Region_GEOJSON values (c);
    commit;
    dbms_lob.fileclose(b);
    dbms_lob.freetemporary(c);
  end;
  /
 and see the data:

Next I can create table Regions that 

create table Regions that parses the JSON file:
select j.*
    from    Region_geojson,
            json_table(features, '$.features[*]'  -- features is the name of my CLOB column in the prev. table                                                                      -- while '$.features[*]' is the standard syntax
                columns (
                    type    varchar2(128) path '$.type',                   
                    geometry SDO_GEOMETRY PATH '$.geometry',
                    region  varchar2(256) path '$.properties.REGION'
            )) j;

 this is the result:

 

Now we have our data and spatial column in a table the next steps are similar to what we did before... Update metadata and create an index:

1. Have you data in a table.
2. Set Spatial column.
3. Update the USER_SDO_GEOM_METADATA table about the new spatial data source.
4. Set Spatial Index.

 INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'REGIONS',  -- table name
  'GEOMETRY',    -- geometry column name
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', -180, 180, 0.005),
    SDO_DIM_ELEMENT('Y', -90, 90, 0.005)),
   4326           -- indicates longitude/latitude coordinates (coordinate system)
 );

CREATE INDEX REGIONS_SIDX ON
      REGIONS (
          GEOMETRY
      )
          INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

Now I can check the size of each region:

Select
    REGION,
     SDO_GEOM.SDO_AREA(geometry, 0.005, 'unit=SQ_KM') Reg_area
From Regions;

The full list of units of measures can be obtained using the following select:

 select distinct short_name, unit_of_meas_name
from sdo_units_of_measure
where unit_of_meas_type = 'area';

 

And last tip... Having problems with your data? SDO_UTIL.RECTIFY_GEOMETRY can fix various problems in your geometries.

See here additional Recommendations for Loading and Validating Spatial Data.