GEOMETRY ST_GeomFromWKB(binary wkb) on spark using Java Language

Question

I have a dataframe with Binary column type :

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|BinaryGeometry                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|[00 00 00 00 01 03 00 00 00 01 00 00 00 11 00 00 00 04 00 F0 00 DC CC 1A C0 87 14 01 81 1E 1B 41 40 FC FF EF 00 68 AA 1A C0 BF EE 57 20 85 19 41 40 04 00 F0 00 8C 86 1A C0 CC DC 8B DC AE 1A 41 40 FF FF EF 00 44 74 1A C0 CA 9D 5D 61 10 1C 41 40 FF FF EF 00 64 63 1A C0 BF 1F 98 0B 3A 1D 41 40 FF FF EF 00 44 47 1A C0 E4 6B A0 DD CE 1D 41 40 FC FF EF 00 D8 2B 1A C0 54 E4 71 67 6D 1C 41 40 FF FF EF 00 44 1A 1A C0 BF 1F 98 0B 3A 1D 41 40 02 00 F0 00 80 0B 1A C0 0D 80 00 13 2F 23 41 40 02 00 F0 00 B0 35 1A C0 CC F6 23 F8 BD 26 41 40 04 00 F0 00 0C 43 1A C0 73 1A 44 AF 16 26 41 40 02 00 F0 00 40 5A 1A C0 FF 54 9C 7C 2D 27 41 40 02 00 F0 00 50 68 1A C0 87 6E B9 42 44 28 41 40 02 00 F0 00 00 7C 1A C0 78 2B 85 BA F5 26 41 40 FC FF EF 00 18 91 1A C0 49 96 6F 58 C6 28 41 40 02 00 F0 00 B0 BC 1A C0 91 FA 4B 0E 7F 20 41 40 04 00 F0 00 DC CC 1A C0 87 14 01 81 1E 1B 41 40] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I want to Convert the Well Known Binary wkb into a Geometry.

From my researsh I found that ST_GeomFromWKB function returns an ST_Geometry value, which is transformed from a LONG BINARY value containing the Well Known Binary (WKB) representation of an ST_Geometry.

I'm trying to apply that on my dataframe , But the BinaryGeometry column is a Binary type like an Array[Byte].

My question is : how to get the LONG BINARY value from Binary column on spark ?

I tried hex function .

df.withColumn("BinaryGeometry",hex(col("BinaryGeometry")))
        .withColumn("BinaryGeometry",expr("ST_GeomFromWKB(BinaryGeometry)"))

I get the following output witch is not correct:

POINT (0 0)

EDIT

|-- BinaryGeometry: binary (nullable = true)

See also http://www.h2gis.org/docs/dev/ST_GeomFromWKB/

check this ; https://stackoverflow.com/questions/57636127/how-to-create-binarytype-column-using-multiple-columns-of-a-pyspark-dataframe — melissa maya, Aug 06 '20 at 09:44
I found a problem close to mine since I take the data from mysql , but I didn't understand the issus. https://stackoverflow.com/a/60577841/13518708 — melissa maya, Aug 06 '20 at 11:49
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/219340/discussion-between-melissa-maya-and-someshwar-kale). — melissa maya, Aug 06 '20 at 13:26
check this example- https://github.com/DataSystemsLab/GeoSpark/blob/fcd3fd87d519603656fbf1755f40be388915a7af/sql/src/test/java/org/datasyslab/geosparksql/adapterTestJava.java#L158 — Som, Aug 06 '20 at 14:47
Thank you , I tried that `Dataset dataaa = sparkSession.sql("select ST_GeomFromWKB(df.BinaryGeometry) as geomdata from df") ;` , But I get that :`java.lang.ClassCastException: [B cannot be cast to org.apache.spark.unsafe.types.UTF8String` , The problem is the type of my data — melissa maya, Aug 06 '20 at 15:05

score 1 · Answer 1 · answered Aug 13 '20 at 10:44

I resolved the problem as following :

the problem is in the loading of the database table, we must use the following code:

Dataset <Row> df_zones =        sparkSession.read().format("jdbc").option("url", "").option("driver", "com.mysql.jdbc.Driver").option("dbtable", "(select ST_AsWKT(geom) as BinaryGeometry  from zones) as t").option("user", "root").option("password", "").load();

GEOMETRY ST_GeomFromWKB(binary wkb) on spark using Java Language

1 Answers1