[GIS] Spatialite – get geometry from WKB

Question

I have a table of WKB HexString values, of the form:

01010000000812368FA5D106C01C25B4DAE5AE4A40

in a column called 'Centroid' in spatialite

I want to create a geometry from these values. When I run:

select GeomFromWKB(x'01010000000812368FA5D106C01C25B4DAE5AE4A40');

I get a valid geometry. However, if I run:

select GeomFromWKB('x'||"'"||Centroid||"'") from table;

I get invalid geometries. It seems that it is impossible to insert these hex values as binary values. I think may be due to Sqlite thinking that the bracked value in the second statement is a string literal and not being able to transform it into a blob/binary literal.

Does anyone have any solutions either within spatialite itself or using a third party library in python or something?

Note that I have also tried:

select GeomFromEWKB(Centroid) from table;

I seem to recall doing this successfully before in PostGIS, but I'd rather use spatialite if possible.

Answer 1

Using Spatialite.
If the string is for example '0x0101000000081236FFF5D106C01C25B4DEE5AE4A40'

SELECT CastToBlob(replace('0x0101000000081236FFF5D106C01C25B4DEE5AE4A40','0x',''),1) AS x FROM table    
SELECT CastToBlob(replace([WKBhexAStextDUMPfromSOMEdbColumn],'0x',''),1) AS x FROM table