SDO2GeoJSON
Recently, like many before me, I had need to create a function that converted a single SDO_GEOMETRY object to GeoJSON.
The function I created is below.
Note that this function:
- Does not support attributes (it could do via passing in a refCursor or table of record);
- Nor does nor does it address how to create a whole GeoJSON document from a collection of SDO_GEOMETRY records.
The latter could be done via a function that processed a SQL statement or refCursor with the appropriate parameters (filename etc).
One thing about GeoJSON is that it is uses the same keywords over and over again within a FeatureCollection. This could mean that a GeoJSON collection would repeat keywords eg MultiLineString as many times as there were records in the SQL statement. This would mean the collection would use more network bandwidth for communication. One approach could be to replace the keywords with unique short-name tags. I have done this in this function even though it is non-standard.
Additionally, I have wondered if some improvement in size could be won if the each geometry’s mbr is applied to the coordinates in order to make them “relative”, perhaps saving space. This non-standard idea is included as an “idea”.
CREATE OR REPLACE
FUNCTION sdo2geojson(p_geometry IN sdo_geometry,
p_decimal_places IN pls_integer DEFAULT 2,
p_compress_tags IN pls_integer DEFAULT 0,
p_relative2mbr IN pls_integer DEFAULT 0,
p_num_fmt IN varchar2 DEFAULT '999.9999999')
RETURN CLOB deterministic
/* Note: Does not support curved geometries.
* If required, stroke geometry before calling function.
* If Compressed apply bbox to coordinates.....
* { "type": "Feature",
* "bbox": [-180.0, -90.0, 180.0, 90.0],
* "geometry": {
* "type": "Polygon",
* "coordinates": [[ [-180.0, 10.0], [20.0, 90.0], [180.0, -5.0], [-30.0, -90.0] ]]
* }
* ...
* }
*/
AS
v_relative BOOLEAN := CASE WHEN p_relative2mbr<>0 THEN TRUE ELSE FALSE END;
v_result CLOB;
v_type varchar2(50);
v_compress_tags BOOLEAN := CASE WHEN p_compress_tags<>0 THEN TRUE ELSE FALSE END;
v_feature_key varchar2(100) := CASE WHEN v_compress_tags THEN 'F' ELSE '"Feature"' END;
v_bbox_tag varchar2(100) := CASE WHEN v_compress_tags THEN 'b:' ELSE '"bbox":' END;
v_coord_tag varchar2(100) := CASE WHEN v_compress_tags THEN 'c:' ELSE '"coordinates":' END;
v_geometry_tag varchar2(100) := CASE WHEN v_compress_tags THEN 'g:' ELSE '"Geometry":' END;
v_type_tag varchar2(100) := CASE WHEN v_compress_tags THEN 't:' ELSE '"type":' END;
v_temp_string varchar2(30000);
v_precision pls_integer := nvl(p_decimal_places,2);
v_i pls_integer;
v_num_rings pls_integer;
v_num_elements pls_integer;
v_element_no pls_integer;
v_vertices mdsys.vertex_set_type;
v_element mdsys.sdo_geometry;
v_ring mdsys.sdo_geometry;
v_mbr mdsys.sdo_geometry;
v_rGeom mdsys.sdo_geometry;
v_dims pls_integer;
FUNCTION hasRectangles( p_elem_info IN mdsys.sdo_elem_info_array )
RETURN Pls_Integer
IS
v_rectangle_count NUMBER := 0;
v_etype pls_integer;
v_interpretation pls_integer;
v_elements pls_integer;
BEGIN
IF ( p_elem_info IS NULL ) THEN
RETURN 0;
END IF;
v_elements := ( ( p_elem_info.COUNT / 3 ) - 1 );
<<element_extraction>>
FOR v_i IN 0 .. v_elements LOOP
v_etype := p_elem_info(v_i * 3 + 2);
v_interpretation := p_elem_info(v_i * 3 + 3);
IF ( v_etype IN (1003,2003) AND v_interpretation = 3 ) THEN
v_rectangle_count := v_rectangle_count + 1;
END IF;
END loop element_extraction;
RETURN v_rectangle_Count;
END hasRectangles;
FUNCTION hasCircularArcs(p_elem_info IN mdsys.sdo_elem_info_array)
RETURN BOOLEAN
IS
v_elements NUMBER;
BEGIN
v_elements := ( ( p_elem_info.COUNT / 3 ) - 1 );
<<element_extraction>>
FOR v_i IN 0 .. v_elements LOOP
IF ( ( /* etype */ p_elem_info(v_i * 3 + 2) = 2 AND
/* interpretation*/ p_elem_info(v_i * 3 + 3) = 2 )
OR
( /* etype */ p_elem_info(v_i * 3 + 2) IN (1003,2003) AND
/* interpretation*/ p_elem_info(v_i * 3 + 3) IN (2,4) ) ) THEN
RETURN TRUE;
END IF;
END loop element_extraction;
RETURN FALSE;
END hasCircularArcs;
FUNCTION GetNumRings( p_geometry IN mdsys.sdo_geometry,
p_ring_type IN INTEGER DEFAULT 0 /* 0 = ALL; 1 = OUTER; 2 = INNER */ )
RETURN NUMBER
IS
v_ring_count NUMBER := 0;
v_ring_type NUMBER := p_ring_type;
v_elements NUMBER;
v_etype pls_integer;
BEGIN
IF ( p_geometry IS NULL ) THEN
RETURN 0;
END IF;
IF ( p_geometry.sdo_elem_info IS NULL ) THEN
RETURN 0;
END IF;
IF ( v_ring_type NOT IN (0,1,2) ) THEN
v_ring_type := 0;
END IF;
v_elements := ( ( p_geometry.sdo_elem_info.COUNT / 3 ) - 1 );
<<element_extraction>>
FOR v_i IN 0 .. v_elements LOOP
v_etype := p_geometry.sdo_elem_info(v_i * 3 + 2);
IF ( v_etype IN (1003,1005,2003,2005) AND 0 = v_ring_type )
OR ( v_etype IN (1003,1005) AND 1 = v_ring_type )
OR ( v_etype IN (2003,2005) AND 2 = v_ring_type ) THEN
v_ring_count := v_ring_count + 1;
END IF;
END loop element_extraction;
RETURN v_ring_count;
END GetNumRings;
PROCEDURE ADD_Coordinate( p_ordinates IN OUT nocopy mdsys.sdo_ordinate_array,
p_dim IN NUMBER,
p_x_coord IN NUMBER,
p_y_coord IN NUMBER,
p_z_coord IN NUMBER,
p_m_coord IN NUMBER,
p_measured IN BOOLEAN := FALSE,
p_duplicates IN BOOLEAN := FALSE)
IS
FUNCTION Duplicate
RETURN BOOLEAN
IS
BEGIN
RETURN CASE WHEN p_ordinates IS NULL OR p_ordinates.COUNT = 0
THEN FALSE
ELSE CASE p_dim
WHEN 2
THEN ( p_ordinates(p_ordinates.COUNT) = p_y_coord
AND
p_ordinates(p_ordinates.COUNT-1) = p_x_coord )
WHEN 3
THEN ( p_ordinates(p_ordinates.COUNT) = CASE WHEN p_measured THEN p_m_coord ELSE p_z_coord END
AND
p_ordinates(p_ordinates.COUNT-1) = p_y_coord
AND
p_ordinates(p_ordinates.COUNT-2) = p_x_coord )
WHEN 4
THEN ( p_ordinates(p_ordinates.COUNT) = p_m_coord
AND
p_ordinates(p_ordinates.COUNT-1) = p_z_coord
AND
p_ordinates(p_ordinates.COUNT-2) = p_y_coord
AND
p_ordinates(p_ordinates.COUNT-3) = p_x_coord )
END
END;
END Duplicate;
BEGIN
IF ( p_ordinates IS NULL ) THEN
p_ordinates := NEW mdsys.sdo_ordinate_array(NULL);
p_ordinates.DELETE;
END IF;
IF ( p_duplicates OR NOT Duplicate() ) THEN
IF ( p_dim >= 2 ) THEN
p_ordinates.extend(2);
p_ordinates(p_ordinates.count-1) := p_x_coord;
p_ordinates(p_ordinates.COUNT ) := p_y_coord;
END IF;
IF ( p_dim >= 3 ) THEN
p_ordinates.extend(1);
p_ordinates(p_ordinates.COUNT) := CASE WHEN p_dim = 3 AND p_measured
THEN p_m_coord
ELSE p_z_coord
END;
END IF;
IF ( p_dim = 4 ) THEN
p_ordinates.extend(1);
p_ordinates(p_ordinates.COUNT) := p_m_coord;
END IF;
END IF;
END ADD_Coordinate;
FUNCTION Rectangle2Polygon(p_geometry IN mdsys.sdo_geometry)
RETURN mdsys.sdo_geometry
AS
v_dims pls_integer;
v_ordinates mdsys.sdo_ordinate_array := NEW mdsys.sdo_ordinate_array(NULL);
v_vertices mdsys.vertex_set_type;
v_etype pls_integer;
v_start_coord mdsys.vertex_type;
v_end_coord mdsys.vertex_type;
BEGIN
v_ordinates.DELETE;
v_dims := p_geometry.get_dims();
v_etype := p_geometry.sdo_elem_info(2);
v_vertices := sdo_util.getVertices(p_geometry);
v_start_coord := v_vertices(1);
v_end_coord := v_vertices(2);
-- First coordinate
ADD_Coordinate( v_ordinates, v_dims, v_start_coord.x, v_start_coord.y, v_start_coord.z, v_start_coord.w );
-- Second coordinate
IF ( v_etype = 1003 ) THEN
ADD_Coordinate(v_ordinates,v_dims,v_end_coord.x,v_start_coord.y,(v_start_coord.z + v_end_coord.z) /2, v_start_coord.w);
ELSE
ADD_Coordinate(v_ordinates,v_dims,v_start_coord.x,v_end_coord.y,(v_start_coord.z + v_end_coord.z) /2,
(v_end_coord.w - v_start_coord.w) * ((v_end_coord.x - v_start_coord.x) /
((v_end_coord.x - v_start_coord.x) + (v_end_coord.y - v_start_coord.y)) ));
END IF;
-- 3rd or middle coordinate
ADD_Coordinate(v_ordinates,v_dims,v_end_coord.x,v_end_coord.y,v_end_coord.z,v_end_coord.w);
-- 4th coordinate
IF ( v_etype = 1003 ) THEN
ADD_Coordinate(v_ordinates,v_dims,v_start_coord.x,v_end_coord.y,(v_start_coord.z + v_end_coord.z) /2,v_start_coord.w);
ELSE
Add_Coordinate(v_ordinates,v_dims,v_end_coord.x,v_start_coord.y,(v_start_coord.z + v_end_coord.z) /2,
(v_end_coord.w - v_start_coord.w) * ((v_end_coord.x - v_start_coord.x) /
((v_end_coord.x - v_start_coord.x) + (v_end_coord.y - v_start_coord.y)) ));
END IF;
-- Last coordinate
ADD_Coordinate(v_ordinates,v_dims,v_start_coord.x,v_start_coord.y,v_start_coord.z,v_start_coord.w);
RETURN mdsys.sdo_geometry(p_geometry.sdo_gtype,p_geometry.sdo_srid,NULL,mdsys.sdo_elem_info_array(1,v_etype,1),v_ordinates);
END Rectangle2Polygon;
FUNCTION formatCoord(p_x IN NUMBER,
p_y IN NUMBER,
p_relative IN BOOLEAN)
RETURN varchar2
AS
BEGIN
RETURN '[' ||
CASE WHEN p_relative
THEN TRIM(to_char(round(p_x - v_mbr.sdo_ordinates(1),v_precision),p_num_fmt)) || ',' ||
TRIM(to_char(round(p_y - v_mbr.sdo_ordinates(2),v_precision),p_num_fmt))
ELSE TRIM(to_char(round(p_x,v_precision),p_num_fmt)) || ',' ||
TRIM(to_char(round(p_y,v_precision),p_num_fmt))
END ||
']';
END formatCoord;
BEGIN
IF ( p_geometry IS NULL ) THEN
RETURN NULL;
END IF;
-- Currently, we do not support compound objects
--
IF ( p_geometry.get_gtype() NOT IN (1,2,3,5,6,7) ) THEN
RETURN NULL;
END IF;
DBMS_LOB.createtemporary (lob_loc => v_result, cache => TRUE);
v_type := CASE WHEN v_compress_tags
THEN CASE p_geometry.get_gtype()
WHEN 1 THEN 'P'
WHEN 2 THEN 'LS'
WHEN 3 THEN 'PG'
WHEN 5 THEN 'MP'
WHEN 6 THEN 'MLS'
WHEN 7 THEN 'MPG'
END
ELSE CASE p_geometry.get_gtype()
WHEN 1 THEN '"Point"'
WHEN 2 THEN '"LineString"'
WHEN 3 THEN '"Polygon"'
WHEN 5 THEN '"MultiPoint"'
WHEN 6 THEN '"MultiLineString"'
WHEN 7 THEN '"MultiPolygon"'
END
END;
v_temp_string := '{';
IF ( p_geometry.get_gtype() = 1 ) THEN
v_temp_string := v_temp_string || v_type_tag || v_type || ',' || v_coord_tag;
IF (p_geometry.SDO_POINT IS NOT NULL ) THEN
v_temp_string := v_temp_string || '[' ||
TRIM(to_char(round(p_geometry.SDO_POINT.X,v_precision),p_num_fmt)) || ',' ||
TRIM(to_char(round(p_geometry.SDO_POINT.Y,v_precision),p_num_fmt)) || ']}';
ELSE
v_temp_string := v_temp_string || '[' ||
TRIM(to_char(round(p_geometry.sdo_ordinates(1),v_precision),p_num_fmt)) || ',' ||
TRIM(to_char(round(p_geometry.sdo_ordinates(2),v_precision),p_num_fmt)) || ']}';
END IF;
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => 1,
buffer => v_temp_string );
RETURN v_result;
END IF;
IF ( v_relative ) THEN
v_mbr := SDO_GEOM.SDO_MBR(p_geometry);
IF ( v_mbr IS NOT NULL ) THEN
v_temp_string := v_temp_string ||
v_type_tag || v_feature_key || ',' ||
v_bbox_tag || '[' ||
v_mbr.sdo_ordinates(1) || ',' ||
v_mbr.sdo_ordinates(2) || ',' ||
v_mbr.sdo_ordinates(3) || ',' ||
v_mbr.sdo_ordinates(4) || ',' ||
'],' || v_geometry_tag || '{';
END IF;
END IF;
IF p_geometry.get_gtype() IN (5,6,7) THEN
v_temp_string := v_temp_string || v_type_tag || v_type || ',' || v_coord_tag || '[';
ELSE
v_temp_string := v_temp_string || v_type_tag || v_type || ',' || v_coord_tag;
END IF;
-- Write header
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => 1,
buffer => v_temp_string);
IF ( hasCircularArcs(p_geometry.sdo_elem_info) ) THEN
RETURN NULL;
END IF;
v_num_elements := mdsys.sdo_util.GetNumElem(p_geometry);
<<for_all_elements>>
FOR v_element_no IN 1..v_num_elements LOOP
v_element := mdsys.sdo_util.EXTRACT(p_geometry,v_element_no); -- Extract element with all sub-elements
IF ( v_element.get_gtype() IN (1,2,5) ) THEN
IF (v_element_no = 1) THEN
v_temp_string := '[';
elsif ( v_element.get_gtype() = 2 ) THEN
v_temp_string := '],[';
END IF;
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
v_vertices := mdsys.sdo_util.getVertices(v_element);
v_temp_string := formatCoord(v_vertices(1).x,v_vertices(1).y,v_relative);
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
<<for_all_vertices>>
FOR j IN 2..v_vertices.COUNT loop
v_temp_string := ',' || formatCoord(v_vertices(j).x,v_vertices(j).y,v_relative);
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
END loop for_all_vertices;
ELSE
IF (v_element_no = 1) THEN
v_temp_string := '[';
ELSE
v_temp_string := '],[';
END IF;
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
v_num_rings := GetNumRings(v_element);
<<for_all_rings>>
FOR v_ring_no IN 1..v_num_rings Loop
v_ring := MDSYS.SDO_UTIL.EXTRACT(p_geometry,v_element_no,v_ring_no); -- Extract ring from element .. must do it this way, can't correctly extract from v_element.
IF ( v_ring_no > 1 ) THEN -- inner ring needs reversing
v_rGeom := Sdo_Geometry(v_dims*1000+2,v_ring.Sdo_Srid,NULL,Sdo_Elem_Info_Array(1,2,1),v_ring.Sdo_Ordinates);
v_ring.Sdo_Ordinates := Mdsys.Sdo_Util.Reverse_Linestring(v_rGeom).Sdo_Ordinates;
END IF;
IF (hasRectangles(v_ring.sdo_elem_info)>0) THEN
v_ring := Rectangle2Polygon(v_ring);
END IF;
IF ( v_ring_no > 1 ) THEN
v_temp_string := ',';
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
END IF;
v_vertices := mdsys.sdo_util.getVertices(v_ring);
v_temp_string := '[' || formatCoord(v_vertices(1).x,v_vertices(1).y,v_relative);
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
<<for_all_vertices>>
FOR j IN 2..v_vertices.COUNT loop
v_temp_string := ',' || formatCoord(v_vertices(j).x,v_vertices(j).y,v_relative);
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
END loop for_all_vertices;
v_temp_string := ']'; -- Close Ring
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
END Loop for_all_rings;
END IF;
END LOOP for_all_elements;
-- Closing tag
IF p_geometry.get_gtype() IN (5,6,7) THEN
v_temp_string := ']]}';
ELSE
v_temp_string := ']}';
END IF;
IF ( v_relative AND p_geometry.get_gtype() <> 1 ) THEN
v_temp_string := v_temp_string || '}';
END IF;
DBMS_LOB.WRITE(lob_loc => v_result,
amount => LENGTH (v_temp_string),
offset => DBMS_LOB.GETLENGTH(v_result)+1,
buffer => v_temp_string );
RETURN v_result;
END Sdo2GeoJson;
I tested this with the following.
SELECT CFLAG,
CASE WHEN iscompress=0 THEN 'Ordinary' ELSE 'Compressed' END AS CFLAGTTYPE,
geojson
FROM (SELECT (level-1) AS iscompress, sdo2geojson(sdo_geometry(2001,NULL,sdo_point_type(3312345,5212345,NULL),NULL,NULL),1,(level-1),(level-1)) AS geojson FROM dual CONNECT BY level < 3
UNION ALL SELECT (level-1) AS iscompress, sdo2geojson(sdo_geometry(2001,NULL,NULL,sdo_elem_info_array(1,1,1), sdo_ordinate_array(312345,5212345)),1,(level-1),(level-1)) AS geojson FROM dual CONNECT BY level < 3
UNION ALL SELECT (level-1) AS iscompress, sdo2geojson(sdo_geometry(2005,NULL,NULL,sdo_elem_info_array(1,1,4), sdo_ordinate_array(312345,5212345,322345,5222345,332345,5232345,342345,5242345)),1,(level-1),(level-1)) AS geojson FROM dual CONNECT BY level < 3
UNION ALL SELECT (level-1) AS iscompress, sdo2geojson(sdo_geometry(2002,NULL,NULL,sdo_elem_info_array(1,2,1), sdo_ordinate_array(312345,5212345,322345,5222345,332345,5232345,342345,5242345)),1,(level-1),(level-1)) AS geojson FROM dual CONNECT BY level < 3
UNION ALL SELECT (level-1) AS iscompress, sdo2geojson(sdo_geometry(2006,NULL,NULL,sdo_elem_info_array(1,2,1,5,2,1), sdo_ordinate_array(312345,5212345,322345,5222345,332345,5232345,342345,5242345)),1,(level-1),(level-1)) AS geojson FROM dual CONNECT BY level < 3
UNION ALL SELECT (level-1) AS iscompress, sdo2geojson(sdo_geometry(2003,NULL,NULL,sdo_elem_info_array(1,1003,3,5,2003,3),sdo_ordinate_array(312345,5212345,322345,5222345,332345,5232345,342345,5242345)),1,(level-1),(level-1)) AS geojson FROM dual CONNECT BY level < 3
UNION ALL SELECT (level-1) AS iscompress, sdo2geojson(sdo_geometry(2007,NULL,NULL,sdo_elem_info_array(1,1003,3,5,1003,3),sdo_ordinate_array(312345,5212345,322345,5222345,332345,5232345,342345,5242345)),1,(level-1),(level-1)) AS geojson FROM dual CONNECT BY level < 3
);
-- Results
CFLAG CFLAGTTYPE GEOJSON
----- ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 Ordinary {"type":"Point","coordinates":[3312345,5212345]}
1 Compressed {t:P,c:[3312345,5212345]}
0 Ordinary {"type":"Point","coordinates":[312345,5212345]}
1 Compressed {t:P,c:[312345,5212345]}
0 Ordinary {"type":"MultiPoint","coordinates":[[312345,5212345],[322345,5222345],[332345,5232345],[342345,5242345]]}
1 Compressed {t:F,b:[312345,5212345,342345,5242345,],g:{t:MP,c:[[0,0],[10000,10000],[20000,20000],[30000,30000]]}}
0 Ordinary {"type":"LineString","coordinates":[[312345,5212345],[322345,5222345],[332345,5232345],[342345,5242345]]}
1 Compressed {t:F,b:[312345,5212345,342345,5242345,],g:{t:LS,c:[[0,0],[10000,10000],[20000,20000],[30000,30000]]}}
0 Ordinary {"type":"MultiLineString","coordinates":[[312345,5212345],[322345,5222345]],[[332345,5232345],[342345,5242345]]}
1 Compressed {t:F,b:[312345,5212345,342345,5242345,],g:{t:MLS,c:[[0,0],[10000,10000]],[[20000,20000],[30000,30000]]}}
0 Ordinary {"type":"Polygon","coordinates":[[[312345,5212345],[322345,5212345],[322345,5222345],[312345,5222345],[312345,5212345]],[[332345,5232345],[342345,5232345],[342345,5242345],[332345,5242345],[332345,5232345]]]}
1 Compressed {t:F,b:[312345,5212345,342345,5242345,],g:{t:PG,c:[[[0,0],[10000,0],[10000,10000],[0,10000],[0,0]],[[20000,20000],[30000,20000],[30000,30000],[20000,30000],[20000,20000]]]}}
0 Ordinary {"type":"MultiPolygon","coordinates":[[[312345,5212345],[322345,5212345],[322345,5222345],[312345,5222345],[312345,5212345]]],[[[332345,5232345],[342345,5232345],[342345,5242345],[332345,5242345],[332345,5232345]]]}
1 Compressed {t:F,b:[312345,5212345,342345,5242345,],g:{t:MPG,c:[[[0,0],[10000,0],[10000,10000],[0,10000],[0,0]]],[[[20000,20000],[30000,20000],[30000,30000],[20000,30000],[20000,20000]]]}}
14 ROWS selected
I hope this is of use to someone.