How to convert a spatial Oracle LOB object type to a Geodataframe listlike geometry object column

Question

I have in my dataframe one column with cx_Oracle.LOB object [2D polygon type], made with a SDO_UTIL.TO_WKTGEOMETRY SQL function and I want to create a geodataframe, and set my geometry column the geometry returned from the SDO_UTIL.TO_WKTGEOMETRY function to a valid geodataframe listlike geometry.

The Geodataframe function return error when i set the geometry column with the WKTGEOMETRY made with SDO_UTIL.TO_WKTGEOMETRY function. I work with PyCharm application.

my dataframe:

dfparc = pd.DataFrame(dfparc.values, columns=['PAR_ID', 'PAR_NUM', 'geometry', 'AREA_HA', 'DCF'])

print(type(dfparc.geometry[0]))

class 'cx_Oracle.LOB'>

print(dfparc.geometry[0])

POLYGON ((225215.22 411994.91, 225214.02 411912.51, 225339.62 411916.91, 225339.62 411995.31, 225279.22 411995.31, 225215.22 411994.91))

My Geodataframe:

geoparc = gp.GeoDataFrame(dfparc, crs=102160, geometry=dfparc.geometry)

returned errors:

...Python35-32\lib\site-packages\geopandas\geodataframe.py", line 50, in __init__
     self.set_geometry(geometry, inplace=True)
...Python35-32\lib\site-packages\geopandas\geodataframe.py", line 149, in set_geometry
    raise TypeError("Input geometry column must contain valid geometry objects.") TypeError: Input geometry column must contain valid geometry objects.

I suppose the problem could be in the type of object returned from SDO_UTIL.TO_WKTGEOMETRY function that is not available for geodataframe geometry data type.

Answer 1

I hesitate to give this answer as I cannot test it, however, one way to avoid dealing with CLOB data types is to convert the result to VARCHAR2.

From the help page on SDO_UTIL.FROM_WKTGEOMETRY (you are calling the TO_ but the usage notes on the FROM_ page reference the TO_):

You can use the SQL function TO_CHAR to convert the resulting CLOB to VARCHAR2 type.

This would make your SQL look something like:

select PAR_ID, PAR_NUM, 
  TO_CHAR(SDO_UTIL.TO_WKTGEOMETRY(PAR_POLIGONO)) as geometry, 
  round(par_area/10000,3) as AREA_HA, DCF 
from sig_parcela_vw 
where PAR_NUM IN ('4117654001', '4126925016')