Forum Stats

  • 3,770,460 Users
  • 2,253,117 Discussions


How to handle big amount of SDO_GEOMETRIES with cx_Oracle

limor Member Posts: 38
edited Aug 15, 2016 8:22AM in Python


I build a webserver with and cx_Oracle to fetch SDO_GEOMETRIES from Oracle Database.

Now I fetch the 3D-Data and than I convert the cx_Oracle.Objects to a json.

But another consideration is that  I do not have to build the json in the Webserver. I can already build the json in the Oracle DB (in a new column). Than I can execute a query to get this json.

But I think this doesnt make so much sense.  Because this json is in Oracle a CLOB Type and this I have to read out in cx_Oracle like the sdo_Geometry (cx_Oracle.Object).

Does anyone has experience to fetch a big amount of 3d geometries and build a json from this with cx_Oracle???



  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,643 Employee
    edited Aug 8, 2016 11:37PM

    Is this related to…  ?

    If you are using Oracle's JSON data type (stored as CLOB), or even just using a CLOB without all the added features of Oracle's JSON support, why not fetch it directly as a LOB and then work with it in cx_Oracle as JSON?

  • limor
    limor Member Posts: 38
    edited Aug 15, 2016 8:22AM

    How do you mean fetch it like a LOB.

    cx_oracle cannot handle both data types . Does it make sense to use a LOB instead to fetch the sdo_geometry and then to read the coordinates like this:

    for id, geometry, classname in cursor:

                mo = geometry.SDO_ORDINATES.aslist()


                    "building_nr":id,"geometry": {

                      "type":"polygon","coordinates":zip(*[iter(mo)] * 3),}, "polygon_typ":classname,})

    There exists the function sdo2geojson3d but this returns a CLOB-Type.

    What is the fastest way: -> 1.) fetch sdo_geom object and read the coordinates inside python webserver (example above)

                                            -> 2.) using a pl/sql wrapper inside Oracle

                                            -> 3.) using SDO_UTIL.GETVERTICES()

                                            -> 4.)  fetch a LOB-Object (as you mentioned)?

    Regarding to point three I can also use the function SDO_UTIL.GETVERTICES() to get the coordinates out of the sdo_geometry objects.

    I tried this in cx_Oracle + python and it is really fast (under one second).

    My result is:

    (1314867, 3500936.67, 5394350.72, 439.17400000000004, 'BuildingWallSurface')(1314867, 3500938.59, 5394348.52, 439.17400000000004, 'BuildingWallSurface')

    But I need a JSON and it is written that the ouput is not serializable!?

    I did it in this way:

    for id,x,y,z, classname in cursor:


              "building_nr":id,"geometry": {

                   "type":"polygon","coordinates":[(x, y, z)],}, "polygon_typ":classname,})

    return result

    My result is now:

    [{'building_nr': 1314867, 'geometry': {'type': 'polygon', 'coordinates': [(3500936.67, 5394350.72, 439.17400000000004)]}, 'polygon_typ': 'BuildingWallSurface'}, ...

    The problem is I get only one point geometry.

    But for example there exists 5 points for this wall surface. I need all points with the same building_nr (id)!

This discussion has been closed.