Hi,
I am working with sqlalchemy and the extension geoalchemy to connect to my database and to execute a query.
code:
from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData
from sqlalchemy.sql import and_, select
from geoalchemy import Geometry, GeometryExtensionColumn
from geoalchemy import *
import cx_Oracle
import json
engine = create_engine('oracle+cx_oracle://TEST_3D:limo1013@10.40.33.160:1521/sdetest')
metadata = MetaData(engine)
# Loading tables
building = Table(
'building',
metadata,
GeometryExtensionColumn('centroid_geom', Geometry(2, srid= 431467)),
#GeometryExtensionColumn(Geometry, Geometry(2)),
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([building.c.id, surface_geometry.c.geometry, objectclass.c.classname] #surface_geometry.c.geometry,
).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,
)
)
for row in connection.execute(query):
print(row)
It works fine. At the end I get this result:
(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingWallSurface')
(1508351.0, <cx_Oracle.OBJECT object at 0x030A7D40>, u'BuildingWallSurface')
(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingWallSurface')
(1508351.0, <cx_Oracle.OBJECT object at 0x030A7D00>, u'BuildingWallSurface')
(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingWallSurface')
(1508351.0, <cx_Oracle.OBJECT object at 0x030A7D40>, u'BuildingWallSurface')
(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingWallSurface')
(1508351.0, <cx_Oracle.OBJECT object at 0x030A7D00>, u'BuildingWallSurface')
(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingGroundSurface')
<cx_Oracle.OBJECT object at 0x03099620== (x=... y=...)
My question is how can I convert these objects to json?
At the end I will have a json.
For standard objects I use
print json.dumps([dict(r) for r in connection.execute(query)])
But this doesn´t work with geometries (not serializable).
How can I handle this problem?