Home » Server Options » Spatial » Convert SDO_Geometry object in GeoJSON (Oracle Locator 11g)
Convert SDO_Geometry object in GeoJSON [message #653660] Wed, 13 July 2016 09:16 Go to next message
moehre
Messages: 43
Registered: June 2016
Member
hi,

I have some 3d geometries in Locator.
The problem is I need these objects in the geojson, json format!

Is it possible to convert SDO_GEOMETRY objects in GeoJSON objects?
For example in PostgreSQL I can use ST_AsGeoJSON(geometry) thats it.
In Locator there is no similar function I gues. And I can not use Transformations for example SDO_UTIL.TO_WKBGEOMETRY() because of the restrictions for locator regarding 3D data...

This is really sad!!!
I found one function sdo2geojson http://spatialdbadvisor.com/oracle_spatial_tips_tricks/206/sdo2geojson this is working in sql plus but the result are only in 2D and not 3D? So he skips the z-value???

Does someone has an idea how to solve this problem in Locator or is it not possible?
I have a webserver and I need to read the geometry out of the database through an sql statement and convert this to json or at last to a WKB...
Re: Convert SDO_Geometry object in GeoJSON [message #653665 is a reply to message #653660] Wed, 13 July 2016 16:22 Go to previous message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
Simon Greener has posted a lot of good code on the web and on the OTN forums. I like his work.
I modified his function and named the modified version sdo2geojson3d.
I used commented lines along the left margin above each line that I modified.
I have posted a copy of that below, followed by an example usage.

-- modified function by Simon Greener:
create or replace
function sdo2geojson3d(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) 
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_geometry      mdsys.sdo_geometry := p_geometry;
 
  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,
-- added p_z parameter below
                       p_z        in number,
                       p_relative in boolean)
    return varchar2
  As
  Begin
      return '[' || 
             case when p_relative 
                  then round (p_x - v_mbr.sdo_ordinates(1),v_precision) || ',' || 
                       round (p_y - v_mbr.sdo_ordinates(2),v_precision) || ',' ||  
-- added p_z below
                       round (p_z - v_mbr.sdo_ordinates(3),v_precision)
-- added p_z below
                  else round(p_x,v_precision) || ',' || round(p_y,v_precision) || ',' || round(p_z,v_precision) 
              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 || '[' || 
                           round(p_geometry.SDO_POINT.X,v_precision) || ',' || 
                           round(p_geometry.SDO_POINT.Y,v_precision) || ']}';
      else
          v_temp_string := v_temp_string || '[' || 
                           round(p_geometry.sdo_ordinates(1),v_precision) || ',' || 
                           round(p_geometry.sdo_ordinates(2),v_precision) || ']}';
      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;
  v_temp_string := v_temp_string || v_type_tag || v_type || ',' || v_coord_tag;
 
  -- 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);
-- added z below
        v_temp_string := formatCoord(v_vertices(1).x,v_vertices(1).y,v_vertices(1).z,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
-- added z below
            v_temp_string := ',' || formatCoord(v_vertices(j).x,v_vertices(j).y,v_vertices(j).z,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 (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);
-- added z below
          v_temp_string := '[' || formatCoord(v_vertices(1).x,v_vertices(1).y,v_vertices(1).z,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
-- added z below
              v_temp_string := ',' || formatCoord(v_vertices(j).x,v_vertices(j).y,v_vertices(j).z,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
  v_temp_string := ']}';
  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 Sdo2GeoJson3d;
/

-- example usage:
SCOTT@orcl_12.1.0.2.0> select iscompress as CFLAG,
  2  	    case when iscompress=0 then 'Ordinary' else 'Compressed' end as CFLAGTTYPE,
  3  	    geojson
  4  from  (select (level-1) as iscompress,
  5  		   sdo2geojson3d
  6  		     (MDSYS.SDO_GEOMETRY
  7  			(3003,31467,NULL,
  8  			 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
  9  			 MDSYS.SDO_ORDINATE_ARRAY
 10  			   (3481797.954,5384186.137,625.799,
 11  			    3481798.012,5384185.994,625.799,
 12  			    3481798.012,5384185.994,639.956,
 13  			    3481797.954,5384186.137,639.956,
 14  			    3481797.954,5384186.137,625.799)),
 15  		      1,level-1,level-1) as geojson
 16  	    from   dual
 17  	    connect by level < 3)
 18  /

     CFLAG CFLAGTTYPE
---------- ----------
GEOJSON
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         0 Ordinary
{"type":"Polygon","coordinates":[[[3481798,5384186.1,625.8],[3481798,5384186,625.8],[3481798,5384186,640],[3481798,5384186.1,640],[3481798,5384186.1,625.8]]]}

         1 Compressed
{t:F,b:[3481797.954,5384185.994,625.799,3481798.012,],g:{t:PG,c:[[[0,.1,0],[.1,0,0],[.1,0,14.2],[0,.1,14.2],[0,.1,0]]]}}


2 rows selected.
Previous Topic: UPDATE-SET Command does not work
Next Topic: Create JSON in new column by using sdo2geojson3d
Goto Forum:
  


Current Time: Mon Nov 20 07:47:53 CST 2017

Total time taken to generate the page: 0.02715 seconds