0

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

Catanzaro
  • 1,312
  • 2
  • 13
  • 24

1 Answers1

0

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
Albert Godfrind
  • 1,978
  • 1
  • 12
  • 20
  • I dont want use only Database library like Cx_Oracle o jaydbapi i want use Spark/Sedona library: df_write=spark.sql("select ST_AsBinary(st_geomfromwkt(geom)) geom_32632 from gallerie") this is a query where i use ST_AsBinary and st_geomfromwkt that are functions Sedona.. first I read from Database with a query where i can use all Oracle function, but for use geom in Dataframe i must convert geom to text. After i put this D.F. in a tem_view (gallerie) and i must use function's Sedona for convert and write on oracle. I think that Sedona dont have a function for convert txt to SDO_GEOMETRY – Catanzaro Mar 04 '22 at 11:05
  • Whatever library you use, you are free to use any SQL syntax: YOU write the queries as you like. As for converting serialized geometries (WKT or WKB) to sdo_geometry, I also doubt that whatever you use has that capability. One possibility would be to make it so that the conversion happens in the database, via a combination of view and trigger. – Albert Godfrind Mar 05 '22 at 16:28
  • Looking at your original question: « is possible read from Oracle a sdo_geometry type and write in a table in Oracle with sdo_Geometry field? » the most efficient way for that is to execute a `insert into … select … from…` i.e. just tell the database to do the copy – Albert Godfrind Mar 05 '22 at 16:34
  • ok but in spark so I dont can use geometry fields – Catanzaro Mar 07 '22 at 08:55