4

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.

Improve this question
3
  • 1
    As you probably already realized, what you have in your 'Centroid" column is a hex representation of the WKB blob. The function GeomFromWKB is expecting the blob, and not the string hex representation. I don't see any obvious way to revert from the string representation back to a binary blob.
    – Micha
    Jun 7, 2012 at 14:48
  • I looked for hours for an opposite of the SQLite function Hex() to no avail. Strange
    – Stev_k
    Jun 7, 2012 at 16:01
  • It looks like this python function might be close to what you are trying to do: binascii - Convert between binary and ASCII
    – Get Spatial
    Jun 7, 2012 at 17:20

1 Answer 1

Reset to default
2

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.