Read and Write sdo_geometry field in spark/GeoSpark(Sedona) from Oracle Table

Question

i'm using geospark(sedona) with pyspark: is possible read from Oracle a sdo_geometry type and write in a table in Oracle with sdo_Geometry field? in my app: i'm able to read :

db_table = "(SELECT sdo_util.to_wktgeometry(geom_32632) geom FROM  geodss_dev.CATASTO_GALLERIE cg WHERE rownum <10)"  <---Query on Oracle Db 
df_oracle = spark.read.jdbc(db_url, db_table, properties=db_properties)
df_oracle.show()
df_oracle.printSchema()

but when i write:

df_oracle.createOrReplaceTempView("gallerie")
df_write=spark.sql("select ST_AsBinary(st_geomfromwkt(geom)) geom_32632 from gallerie") <--query with Sedona Library on tempView Gallerie
print(df_write.dtypes)
df_write.write.jdbc(db_url, "geodss_dev.gallerie_test", properties=db_properties,mode="append")

i have this error:

ORA-00932: inconsistent data types: expected MDSYS.SDO_GEOMETRY, got BINARY

there is a solution for write sdo_geometry type? thanks Regards

Answer 1

You are reading the geometries in serialized formats: WKT (text) in your first example, WKB (binary) in the second.

If you want to write those back as SDO_GEOMETRY objects, you will need to deserialize them back. This can be done in two ways:

• Using the SDO_GEOMETRY constructor:

insert into my_table(my_geom) values (sdo_geometry(:wkb))

or

insert into my_table(my_geom) values (sdo_geometry(:wkt))

• Using the explicit conversion functions:

insert into my_table(my_geom) values (sdo_util.from_wkbgeometry(:wkb))

or

insert into my_table(my_geom) values (sdo_util.from_wktgeometry(:wkt))

I have no idea how you can express this using geospark. I assume it does allow you to specify things like a list of columns to write to, and a list of input values ?

What definitely does not happen is an automatic transformation from the serialized format (binary or text) to a geometry object. There are actually a number of serialized format in addition to the oldish WKT and WKB: GML and GeoJSON are the main alternatives. But those two need explicit calls to the transformation functions.

EDIT: About your second example: instead of stacking two function calls, you can just do:

SELECT sdo_util.to_wkbgeometry(geom_32632) geom ...

Also, in both examples, you can use the object methods instead of the function calls. The result will be the same (the methods just call those same functions anyway), but the syntax is a bit more compact. IMPORTANT: this requires using aliases!.

SELECT cg.geom_32632.get_wkt() geom 
FROM geodss_dev.CATASTO_GALLERIE cg 
WHERE rownum <10

SELECT cg.geom_32632.get_wkb() geom 
FROM geodss_dev.CATASTO_GALLERIE cg 
WHERE rownum <10