Skip to content

Instantly share code, notes, and snippets.

@pauldzy

pauldzy/apex_geojson2sdo.prc

Last active June 24, 2019 23:36
Show Gist options
  • Save pauldzy/fe3b01ff1c88520a01d165a6f54e65fa to your computer and use it in GitHub Desktop.
Save pauldzy/fe3b01ff1c88520a01d165a6f54e65fa to your computer and use it in GitHub Desktop.
Download ZIP
APEX_JSON GeoJSON to SDO_GEOMETRY Utility
Raw
apex_geojson2sdo.prc
CREATE OR REPLACE PROCEDURE apex_geojson2sdo(
pInput IN CLOB
,pOutput OUT MDSYS.SDO_GEOMETRY
,pReturnCode OUT INTEGER
,pStatusMessage OUT VARCHAR2
)
AUTHID CURRENT_USER
AS
l_values APEX_JSON.T_VALUES;
str_test VARCHAR2(4000 Char);
str_test2 VARCHAR2(4000 Char);
int_count PLS_INTEGER;
int_dims PLS_INTEGER;
ary_ords MDSYS.SDO_ORDINATE_ARRAY;
sdo_temp MDSYS.SDO_GEOMETRY;
PROCEDURE get_dims(
p_values IN APEX_JSON.T_VALUES
,out_dims OUT INTEGER
,out_type OUT VARCHAR2
)
AS
str_test2 VARCHAR2(255 Char);
BEGIN
out_type := APEX_JSON.GET_VARCHAR2(
p_path => 'type'
,p_values => p_values
);
IF out_type = 'Point'
THEN
out_dims := APEX_JSON.GET_COUNT(
p_path => 'coordinates'
,p_values => p_values
);
ELSIF out_type IN ('MultiPoint','LineString')
THEN
out_dims := APEX_JSON.GET_COUNT(
p_path => 'coordinates[1]'
,p_values => p_values
);
ELSIF out_type IN ('MultiLineString','Polygon')
THEN
out_dims := APEX_JSON.GET_COUNT(
p_path => 'coordinates[1][1]'
,p_values => p_values
);
ELSIF out_type = 'MultiPolygon'
THEN
out_dims := APEX_JSON.GET_COUNT(
p_path => 'coordinates[1][1][1]'
,p_values => p_values
);
ELSIF out_type = 'GeometryCollection'
THEN
str_test2 := APEX_JSON.GET_VARCHAR2(
p_path => 'geometries[1].type'
,p_values => p_values
);
IF str_test2 = 'Point'
THEN
out_dims := APEX_JSON.GET_COUNT(
p_path => 'geometries[1].coordinates'
,p_values => p_values
);
ELSIF str_test2 = 'LineString'
THEN
out_dims := APEX_JSON.GET_COUNT(
p_path => 'geometries[1].coordinates[1]'
,p_values => p_values
);
ELSIF str_test ='Polygon'
THEN
out_dims := APEX_JSON.GET_COUNT(
p_path => 'geometries[1].coordinates[1][1]'
,p_values => p_values
);
ELSE
RAISE_APPLICATION_ERROR(
-20001
,'error unknown geometry collection type ' || str_test2 || '.'
);
END IF;
ELSE
RAISE_APPLICATION_ERROR(
-20001
,'error unknown geometry type ' || str_test || '.'
);
END IF;
RETURN;
END get_dims;
FUNCTION test_ordinate_rotation(
p_input IN MDSYS.SDO_ORDINATE_ARRAY
,p_num_dims IN INTEGER
) RETURN VARCHAR2
AS
int_lb PLS_INTEGER := 1;
num_x NUMBER;
num_y NUMBER;
num_lastx NUMBER := 0;
num_lasty NUMBER := 0;
num_area NUMBER := 0;
BEGIN
WHILE int_lb <= p_input.COUNT
LOOP
num_x := p_input(int_lb);
num_y := p_input(int_lb + 1);
num_area := num_area + ( (num_lasty * num_x ) - (num_lastx * num_y) );
num_lastx := num_x;
num_lasty := num_y;
int_lb := int_lb + p_num_dims;
END LOOP;
IF num_area > 0
THEN
RETURN 'CW';
ELSE
RETURN 'CCW';
END IF;
END test_ordinate_rotation;
FUNCTION parse_point(
p_values IN APEX_JSON.T_VALUES
,p_root IN VARCHAR2
,p_dims IN INTEGER
) RETURN MDSYS.SDO_GEOMETRY
AS
sdo_output MDSYS.SDO_GEOMETRY;
num_x NUMBER;
num_y NUMBER;
num_z NUMBER;
BEGIN
num_x := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[1]'
);
num_y := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[2]'
);
IF p_dims = 2
THEN
RETURN MDSYS.SDO_GEOMETRY(
2001
,8307
,MDSYS.SDO_POINT_TYPE(
num_x,num_y,NULL
)
,NULL
,NULL
);
ELSE
num_z := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[3]'
);
RETURN MDSYS.SDO_GEOMETRY(
3001
,8307
,MDSYS.SDO_POINT_TYPE(
num_x,num_y,num_z
)
,NULL
,NULL
);
END IF;
END parse_point;
FUNCTION parse_linestring(
p_values IN APEX_JSON.T_VALUES
,p_root IN VARCHAR2
,p_dims IN INTEGER
) RETURN MDSYS.SDO_GEOMETRY
AS
sdo_output MDSYS.SDO_GEOMETRY;
int_count PLS_INTEGER;
int_ords PLS_INTEGER;
BEGIN
int_ords := 1;
sdo_output := MDSYS.SDO_GEOMETRY(
p_dims * 1000 + 2
,8307
,NULL
,SDO_ELEM_INFO_ARRAY(1,2,1)
,SDO_ORDINATE_ARRAY()
);
int_count := APEX_JSON.GET_COUNT(
p_path => p_root
,p_values => l_values
);
FOR i IN 1 .. int_count
LOOP
sdo_output.SDO_ORDINATES.EXTEND(p_dims);
sdo_output.SDO_ORDINATES(int_ords) := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[' || TO_CHAR(i) || '][1]'
);
int_ords := int_ords + 1;
sdo_output.SDO_ORDINATES(int_ords) := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[' || TO_CHAR(i) || '][2]'
);
int_ords := int_ords + 1;
IF p_dims = 3
THEN
sdo_output.SDO_ORDINATES(int_ords) := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[' || TO_CHAR(i) || '][3]'
);
int_ords := int_ords + 1;
END IF;
END LOOP;
RETURN sdo_output;
END parse_linestring;
FUNCTION parse_polygon(
p_values IN APEX_JSON.T_VALUES
,p_root IN VARCHAR2
,p_dims IN INTEGER
) RETURN MDSYS.SDO_GEOMETRY
AS
sdo_output MDSYS.SDO_GEOMETRY;
str_orient VARCHAR2(3 Char);
int_count PLS_INTEGER;
int_count2 PLS_INTEGER;
int_ords PLS_INTEGER;
int_elem PLS_INTEGER;
int_ring PLS_INTEGER;
ary_ring MDSYS.SDO_ORDINATE_ARRAY;
BEGIN
int_elem := 1;
int_ords := 1;
sdo_output := MDSYS.SDO_GEOMETRY(
p_dims * 1000 + 3
,8307
,NULL
,SDO_ELEM_INFO_ARRAY()
,SDO_ORDINATE_ARRAY()
);
int_count := APEX_JSON.GET_COUNT(
p_path => p_root
,p_values => l_values
);
FOR i IN 1 .. int_count
LOOP
sdo_output.SDO_ELEM_INFO.EXTEND(3);
sdo_output.SDO_ELEM_INFO(int_elem) := int_ords;
int_elem := int_elem + 1;
IF i = 1
THEN
sdo_output.SDO_ELEM_INFO(int_elem) := 1003;
int_elem := int_elem + 1;
ELSE
sdo_output.SDO_ELEM_INFO(int_elem) := 2003;
int_elem := int_elem + 1;
END IF;
sdo_output.SDO_ELEM_INFO(int_elem) := 1;
int_elem := int_elem + 1;
int_count2 := APEX_JSON.GET_COUNT(
p_path => p_root || '[' || TO_CHAR(i) || ']'
,p_values => l_values
);
ary_ring := MDSYS.SDO_ORDINATE_ARRAY();
int_ring := 1;
FOR j IN 1 .. int_count2
LOOP
ary_ring.EXTEND(p_dims);
ary_ring(int_ring) := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[' || TO_CHAR(i) || '][' || TO_CHAR(j) || '][1]'
);
int_ring := int_ring + 1;
ary_ring(int_ring) := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[' || TO_CHAR(i) || '][' || TO_CHAR(j) || '][2]'
);
int_ring := int_ring + 1;
IF p_dims = 3
THEN
ary_ring(int_ring) := APEX_JSON.GET_NUMBER(
p_values => l_values
,p_path => p_root || '[' || TO_CHAR(i) || '][' || TO_CHAR(j) || '][3]'
);
int_ring := int_ring + 1;
END IF;
END LOOP;
str_orient := test_ordinate_rotation(
p_input => ary_ring
,p_num_dims => p_dims
);
sdo_output.SDO_ORDINATES.EXTEND(ary_ring.COUNT);
IF ( i = 1 AND str_orient = 'CW' )
OR ( i > 1 AND str_orient = 'CCW' )
THEN
FOR j IN REVERSE ary_ring.COUNT .. 1
LOOP
sdo_output.SDO_ORDINATES(int_ords) := ary_ring(j);
int_ords := int_ords + 1;
END LOOP;
ELSE
FOR j IN 1 .. ary_ring.COUNT
LOOP
sdo_output.SDO_ORDINATES(int_ords) := ary_ring(j);
int_ords := int_ords + 1;
END LOOP;
END IF;
END LOOP;
RETURN sdo_output;
END parse_polygon;
BEGIN
pReturnCode := 0;
IF pInput IS NULL
OR pInput = 'Null'
OR pInput = 'Undefined'
THEN
RETURN;
END IF;
BEGIN
APEX_JSON.PARSE(
p_values => l_values
,p_source => pInput
);
EXCEPTION
WHEN OTHERS
THEN
pReturnCode := -10;
pStatusMessage := 'Invalid JSON, unable to parse.';
RETURN;
END;
BEGIN
get_dims(
p_values => l_values
,out_dims => int_dims
,out_type => str_test
);
EXCEPTION
WHEN OTHERS
THEN
RAISE;
pReturnCode := -20;
pStatusMessage := 'Input is not GeoJSON geometry object.';
RETURN;
END;
IF str_test = 'Point'
THEN
pOutput := parse_point(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates'
);
ELSIF str_test = 'MultiPoint'
THEN
int_count := APEX_JSON.GET_COUNT(
p_path => 'coordinates'
,p_values => l_values
);
FOR i IN 1 .. int_count
LOOP
IF pOutput IS NULL
THEN
pOutput := parse_point(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates[' || TO_CHAR(i) || ']'
);
ELSE
pOutput := MDSYS.SDO_UTIL.APPEND(
pOutput
,parse_point(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates[' || TO_CHAR(i) || ']'
)
);
END IF;
END LOOP;
ELSIF str_test = 'LineString'
THEN
pOutput := parse_linestring(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates'
);
ELSIF str_test = 'MultiLineString'
THEN
int_count := APEX_JSON.GET_COUNT(
p_path => 'coordinates'
,p_values => l_values
);
FOR i IN 1 .. int_count
LOOP
IF pOutput IS NULL
THEN
pOutput := parse_linestring(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates[' || TO_CHAR(i) || ']'
);
ELSE
pOutput := MDSYS.SDO_UTIL.APPEND(
pOutput
,parse_linestring(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates[' || TO_CHAR(i) || ']'
)
);
END IF;
END LOOP;
ELSIF str_test = 'Polygon'
THEN
pOutput := parse_polygon(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates'
);
ELSIF str_test = 'MultiPolygon'
THEN
int_count := APEX_JSON.GET_COUNT(
p_path => 'coordinates'
,p_values => l_values
);
FOR i IN 1 .. int_count
LOOP
IF pOutput IS NULL
THEN
pOutput := parse_polygon(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates[' || TO_CHAR(i) || ']'
);
ELSE
pOutput := MDSYS.SDO_UTIL.APPEND(
pOutput
,parse_polygon(
p_values => l_values
,p_dims => int_dims
,p_root => 'coordinates[' || TO_CHAR(i) || ']'
)
);
END IF;
END LOOP;
ELSIF str_test = 'GeometryCollection'
THEN
int_count := APEX_JSON.GET_COUNT(
p_path => 'geometries'
,p_values => l_values
);
FOR i IN 1 .. int_count
LOOP
str_test2 := APEX_JSON.GET_VARCHAR2(
p_path => 'geometries[' || TO_CHAR(i) || '].type'
,p_values => l_values
);
IF str_test2 = 'Point'
THEN
sdo_temp := parse_point(
p_values => l_values
,p_dims => int_dims
,p_root => 'geometries[' || TO_CHAR(i) || '].coordinates'
);
ELSIF str_test2 = 'LineString'
THEN
sdo_temp := parse_linestring(
p_values => l_values
,p_dims => int_dims
,p_root => 'geometries[' || TO_CHAR(i) || '].coordinates'
);
ELSIF str_test2 = 'Polygon'
THEN
sdo_temp := parse_polygon(
p_values => l_values
,p_dims => int_dims
,p_root => 'geometries[' || TO_CHAR(i) || '].coordinates'
);
ELSE
RAISE_APPLICATION_ERROR(
-20001
,'err'
);
END IF;
IF pOutput IS NULL
THEN
pOutput := sdo_temp;
ELSE
pOutput := MDSYS.SDO_UTIL.APPEND(
pOutput
,sdo_temp
);
END IF;
END LOOP;
ELSE
pOutput := NULL;
pReturnCode := -20;
pStatusMessage := 'Unsupported GeoJSON geometry type of ' || str_test || '.';
RETURN;
END IF;
END apex_geojson2sdo;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment