How to convert SDO_GEOMTRY in GeoJSON

Question

I work with sqlalchemy and geoalchemy and will convert my results in geojson. With the normal way like this :

print json.dumps([dict(r) for r in connection.execute(query)])

it is not possible because cx_Oracle.Objets not serializable! I can have access through the separate attributes like this one:

result = connection.execute(query)
result2 = result.fetchone()[0]
print result2.SDO_ORDINATES

Here is my Programm:

#!/usr/bin/env python
# coding: utf8
#from __future__ import absolute_import, division, print_function
from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData
from sqlalchemy.sql import and_, select
from geoalchemy import Geometry, GeometryExtensionColumn
from geoalchemy import *
from geoalchemy.oracle import oracle_functions
from geoalchemy.oracle import OracleComparator
import cx_Oracle
import json
import sdo

#def main():
engine = create_engine('oracle+cx_oracle://TEST_3D:[email protected]:1521/sdetest')
metadata = MetaData(engine)

# Loading tables 
building = Table(
    'building',
    metadata,
    GeometryExtensionColumn('centroid_geom', Geometry(2, srid= 431467)),
    autoload=True,
    autoload_with=engine
)
GeometryDDL(building)

thematic_surface = Table('thematic_surface', metadata, autoload=True)
surface_geometry = Table('surface_geometry', metadata, autoload=True)
objectclass = Table('objectclass', metadata, autoload=True)

connection = engine.connect()

# define the query
query = select([(surface_geometry.c.geometry)]  #building.c.id, surface_geometry.c.geometry, objectclass.c.classname
).where(
    and_(
        building.c.grid_id_400 == 4158,
        building.c.id == thematic_surface.c.building_id,
        thematic_surface.c.lod2_multi_surface_id == surface_geometry.c.root_id,
        surface_geometry.c.geometry != None,
        thematic_surface.c.objectclass_id == objectclass.c.id,
    )
)
# Execute and print the result of the query
#print json.dumps([dict(r) for r in connection.execute(query)])
result = connection.execute(query)

I will convert all of my cx_Oracle.Objects in a GeoJSON but how? In the Internet the is a function sdo2geojson that works in sql developer fine but of course this function is unknouwn for python...

I hope someone can help me???

Answer 1

This is using the (as yet unreleased) version of cx_Oracle which supports binding of objects and other more advanced uses of objects. Using the sample provided with cx_Oracle for demonstrating the insertion of geometry, the following code will transform the object created in that way into JSON. The ObjectRepr() function included below should work for any object returned from Oracle. It simply reads the metadata on the object and turns the object into a dictionary of attributes or a list of values.

import cx_Oracle
import json

connection = cx_Oracle.Connection("user/pw@tns")
typeObj = connection.gettype("SDO_GEOMETRY")
cursor = connection.cursor()
cursor.execute("""
        select Geometry
        from TestGeometry
        where IntCol = 1""")
obj, = cursor.fetchone()

def ObjectRepr(obj):
    if obj.type.iscollection:
        returnValue = []
        for value in obj.aslist():
            if isinstance(value, cx_Oracle.Object):
                value = ObjectRepr(value)
            returnValue.append(value)
    else:
        returnValue = {}
        for attr in obj.type.attributes:
            value = getattr(obj, attr.name)
            if value is None:
                continue
            elif isinstance(value, cx_Oracle.Object):
                value = ObjectRepr(value)
            returnValue[attr.name] = value
    return returnValue

print("JSON:", json.dumps(ObjectRepr(obj)))