Oracle Spatial - loading geographic data - Line

 Following the previous post - Oracle Spatial - SQL based Geographic data - Intro, few examples of loading lines of geographical data. Line is usually used when we have routes.

I will cover here:

• Manual creation
• Loading GeoJSON

Simple example - Manual Creation of line based on few points

Step 1 & Step 2: Lets assume we have 3 geographic data points that creates a line. 

I will create a table with few descriptions and the line:

create table Line0 as
select  
100 as code,
'Morning Walk' as descript,
SDO_GEOMETRY(
                 2002,                                         -- 2002 - two dimensional line
                 4326,                                         -- our usual coordinate system for lon/lat
                 NULL, -- ONLY for points
                 SDO_ELEM_INFO_ARRAY(1, 2, 1),    --line
                 SDO_ORDINATE_ARRAY(    -- lon/lat values
                    34.809512,    32.099221, 
                    34.810434,    32.099269,
                    34.810434,    32.099999
                 )
            ) as geometry
from dual;

Check it works fine:

And create the spatial index:

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

INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'Line0',  -- 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 Line0_SIDX ON
      Line0 (
          GEOMETRY
      )
          INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

Line 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 of the Tornado Path file.

So our JSON file has the following structure:

 We want to load a JSON file into the database.

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 Line_GEOJSON that contains a single clob column named CLOB_JSON:

 Load the file tornado_paths.geojson

declare
    b bfile := bfilename('TEST_DIR','tornado_paths.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 Line_GEOJSON values (c);
    commit;
    dbms_lob.fileclose(b);
    dbms_lob.freetemporary(c);
  end;
  /

Next I can create table Line1 based on the JSON file data structure:

Create table Line1 as
select j.*
    from    Line_geojson,
            json_table(CLOB_JSON, '$.features[*]'  
                columns (
                    type    varchar2(128) path '$.type',                   
                    geometry SDO_GEOMETRY PATH '$.geometry',
                    YR NUMBER path '$.properties.YR',
                    LOSS NUMBER path '$.properties.LOSS',
                    L_Key NUMBER path '$.properties.KEY'
            )) j;

And check the result:

Finally create the spatial index:

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

INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'Line1',  -- 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 Line1_SIDX ON
      Line1 (
          GEOMETRY
      )
          INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

 

Lets check the length of each tornado path using the SDO_GEOM.SDO_LENGTH function:

 select
    L_Key,
    YR,
    SDO_GEOM.SDO_LENGTH(geometry,0.005, 'unit=KM') Length
from Line1;