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:
Have you data in a table.Set Spatial column.- Update the USER_SDO_GEOM_METADATA table about the new spatial data source.
- 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.
