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;
